Emails Opened vs. Emails Delivered - By Lead | Community
Skip to main content
A
Anonymous
November 10, 2015
Solved

Emails Opened vs. Emails Delivered - By Lead

  • November 10, 2015
  • 3 replies
  • 4282 views

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?

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Grégoire_Miche2

    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

    3 replies

    Grégoire_Miche2
    Grégoire_Miche2
    New Participant
    February 5, 2016

    Hi Kyle,

    For the future, another way to get this done is to create a couple for score fields:

    • Nb Emails delivered
    • Nb Emails Opened

    And have 2 smart campaigns that add +1 to these 2 scores each time one received or opens an email.

    -Greg

    Grégoire_Miche2
    Grégoire_Miche2
    New Participant
    February 5, 2016

    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

      A
      Anonymous
      February 5, 2016

      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
      Grégoire_Miche2
      New Participant
      February 5, 2016

      Hi Laura,

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

      Greg

      Grégoire_Miche2
      Grégoire_Miche2Accepted solution
      New Participant
      November 11, 2015

      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
        Grégoire_Miche2
        New Participant
        November 11, 2015

        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

        Terms & ConditionsAccessibility statement

        Loading footer...