Email Performance Report - how to show per record data

[Robert_Villanue]

Hi,

I would like to know if there's a way to create an email performance report that would provide the per record details? Our stakeholders are not only concern about the percentages of delivered, opened etc., but they would also need to know the exact records who were delivered, opened, click.

Our team usually do this by building smartlists (was sent, was delivered etc.) but it is too much time consuming as we also need to consolidate all excel files then manipulate via pivot table. Is there any other much efficient way to do this or is there any other report that we can use aside the email performance? Please help and thank you in advance!

[Darshil_Shah1]

You can do this by using Revenue Cost Explorer, where-in you can combine email stats with lead information using "email analysis" report type. Refer Email Analysis in RCE here : https://docs.marketo.com/display/public/DOCS/Email+Analysis

[Ajay_Mamgain2]

If you are looking for individual metrics, in that case, you would need to leverage REST APIs to extract the activities and lead data from Marketo. If you are looking for email data then use the below ID in Activities REST APIs and in case of other activities you may need to extract the activity list using rest API as well. Are you planning to create the analysis in Excel or using any BI tool. I have done the analysis in both so I might be able to assist you with that. 

id                name                                          description

6	Send Email	Send Marketo Email to a person
7	Email Delivered	Marketo Email is delivered to a lead/contact
8	Email Bounced	Marketo Email is bounced for a lead
9	Unsubscribe Email	Person unsubscribed from Marketo Emails
10	Open Email	User opens Marketo Email
11	Click Email	User clicks on a link in a Marketo Email
27	Email Bounced Soft	Campaign Email is bounced soft for a lead

[Darshil_Shah1]

I would not recommend this until its extremely required,  as the response for the getLeadActivities is limited to only 300 lead activities  per call and extracting all the data for all the lead activities would actually require to page through all the activities by making multiple API calls and this would inturn consume the API limits of the instance.
Also if you wish to use Excel or Power BI as a data analysis tool then anyhow you can export the RCE reports and ingest them to data analysis tool.

[Ajay_Mamgain2]

We can extract more data using Next Paging token in a single call and create a loop to extract until there is "no more result".  You can also pass an activity date so that you don't extract all data at once and rather do an incremental extract. Even if you think you have too much data you can do a Bulk Extract using REST APIs. 

It doesn't consume that many API calls if you follow the above strategy and also you can monitor from the API calls from the backend, but the long term benefits are that you can get all the relevant data with a single click in Excel or any BI tool. 

[Darshil_Shah1]

Passing next paging token to get more data will actually consume API limit as a separate call will be made to activities API endpoint. Also btw RCE will export the data directly in to excel.

[Ajay_Mamgain2]

RCE is a different module altogether, and I would not recommend adding or purchasing the module just to get Email data which can be easily exported using REST APIs. If API limit is the issue you can use Bulk Extract to export last 30 days data in that way you don't have to worry about the limit.

[Darshil_Shah1 · Accepted solution]

@Robert_Villanue If your instance already has the RCE package then would recommend using it, else as @Ajay_Mamgain2 said you can utilise activity API! 😀

[Robert_Villanue]

Hi, Both,

Thank you very much for your responses.  @Darshil_Shah1 sadly, RCE is not part of our company's package. 😞

As for API, @Ajay_Mamgain2, could you provide an example or lead me to a tutorial on how to use it? I've checked the links you provided on one of your responses and I was overwhelmed (codes!) with what I saw 🙂

[Ajay_Mamgain2]

@Robert_Villanue I agree it might be a little overwhelming for someone who has not used the Marketo APIs as well. If you want I can show you a small demo how to do that using Postman and later you can scale it further according to your need. I will send you the email and we can pick it up from there... I hope it sounds good to you as well.