Emails Opened vs. Emails Delivered - By Lead

[Anonymous]

I'm looking for an easier way to do this process, hopefully you can help!

We have a sales team of about 300 people and want an easy way to generate a report with the following information.

Initial Variables are Emails Delivered vs Opened in a Set Time Period - (Ex: Last Month)

Say we send 10 emails, we want to find out how many emails each person opened.

Lead 1 = 7 emails opened out of 10 delivered

Lead 2 = 5 emails opened out of 10 delivered

Lead 3 = 9 emails opened out of 10 delivered

We would prefer to have this represented in a trend report or bar graph showing each sales person and the total # of emails opened vs. delivered for a given time period. We discussed lead scoring, but I'm not sure that model will work given the nature of our sales team and hiring/firing changes.

Currently this is a very manual process for each email campaign:

- Export List of "Email was Delivered"

- Export List of "Opened Email"

- Repeat process for each campaign and merge each list into a master spreadsheet, run a Pivot Table to get a total number of appearances for each lead.

Can anyone think of a more simple process using either Marketo Analytics Reporting or SalesForce build a custom report?

[Grégoire_Miche2 · Accepted solution]

Hi Kyle Rassel,

There is a feature in Lead performance reports that few people know of : custom columns, that may do the trick. Here is how to use it :

1. Create 10 smart lists, one for each of your 10 emails, using the "Opened email" filter. name them in a short and yet easy to recognize way.
2. Go to analytics, start creating a "lead performance" report.
3. Go to the "Smart List" tab of the report and filter it to restrict to only to those leads who were sent the emails.
4. Go to the setup tab
5. Edit the "group by" setting and set it to "email address", so that your report will have 1 line for each email address in the DB. You can also user group by full name,
6. On the right, in the "settings" columns, you will see the "custom columns" setting. Drag it to the center pane
7. in the dialog, select each of your 10 smart lists and validate. Each smart list will create a column in the report
8. Run the report and potentially export it to see all the data. You may have to change the "export row available" setting in the setup if you have more than 5000 lines in your report.

-Greg

[Grégoire_Miche2]

Hi again Kyle,

And also, you can create a formula field that will be the concatenation of multiple fields (Lead ID, Last Name, Full Name, email, company name) and use it as a group by setting in the report. You'll be sure to have a fully deduplicated report and also enough info on the report to make it usable.

-Greg

[Anonymous]

This was really helpful Grégoire, thank you!

[Grégoire_Miche2]

You are welcome

[Anonymous]

Thank you so much for your help on this - you have saved me so much time!

[Grégoire_Miche2]

Hi again Kyle,

Just mark is as resolved.

-Greg

[Grégoire_Miche2]

A last point on this to answer a DM question from Laura Kimball​

You will get a table that has 0 and 1 in every cell. 0 means the lead (given by the row) does not belong to this column (the number of opened emails). The "1" indicates the lead belongs to this column.

You should have only one "1" per row. Having a greater number ("2", "3", ...) or multiple "1" in the row indicates you have duplicate leads in the row.

-Greg

[Anonymous]

Hi Greg,

Thank you so much for responding. I do believe I set it up correctly. How do I set up that formula field that strings together the multiple fields you mentioned (Lead ID, Last Name, Full Name, email, company name)?

Best regards,

Laura

[Grégoire_Miche2]

Hi Laura,

• Go to admin -> Field Management
• The rest is there: Create and Use a Concatenated String (Formula) Field - Marketo Docs - Product Docs

Your formula will be something like this : {{lead.firstname}} {{lead.lastname}}, {{account.company}} (ID: {{lead.id}}) - {{lead.email address}}

Greg