Extracting Email Activities for Detailed Reporting

Level 1

Extracting Email Activities for Detailed Reporting

Getting Email Activity History from the Marketo REST API endpoints and into a data warehouse for advanced reporting and visualizations can be intimidating the first time you dig into it, especially if you are trying to answer complex questions like “Show me all the email open activity for person records in the United States for the last 3 months for all Marketo programs with a program tag of “XYZ”?.  While that may sound daunting, this post will provide a step-by-step guide on how to use the NON-BULK REST API endpoints to not only pull EMAIL ACTIVITY from the Marketo REST API, but help with some of the nuances of navigating the results returned from the endpoints.


“Get Activity Types”

Start here as this endpoint is used to list out all the baseline and custom activities in a Marketo instance along with the specific attributes (fields) by Activity Id.  Every Marketo activity has a corresponding Activity Id and there are over 60 baseline Activity Types out-of-the-box.  Each Activity Type has different attributes/fields (e.g. a “Filled Out Form” activity will include an attribute for “Form Fields” whereas the activity type for “Viewed Web Page” will contain the URL of the web page).

Endpoint Reference Documentation:  https://developers.marketo.com/rest-api/endpoint-reference/lead-database-endpoint-reference/#!/Activ...

Common Email-related Activity Types are:

  • Send Email
  • Email Delivered
  • Email Bounced
  • Unsubscribe Email
  • Open Email
  • Click Email
  • Email Bounced Soft

“Get Paging Token”

When using the non-bulk REST API endpoints to pull Activity data, you must first get a “Paging Token”.  A Paging Token is basically a “since” date/timeframe (e.g. “get me all activities since Jan 1, 2019 6am”).  The “nextPageToken” value in the response will be used in the NEXT REST API call to “Get Lead Activities” and note that subsequent “Get Lead Activities” REST API calls will include a new/updated “nextPageToken” Paging Token value to be used in the next activity call.  Once the “Get Lead Activities” endpoint does not return a new Paging Token in the response, you have retrieved all relevant activity type details for that timeframe.

Endpoint Reference Documentation:  https://developers.marketo.com/rest-api/endpoint-reference/lead-database-endpoint-reference/#!/Activ...


“Get Lead Activities” – Where the MAGIC Happens!

Once you have a Paging Token and you know the Activity Types you want to extract from your Marketo instance, you can now pull the actual Activity detail via this “Get Lead Activities” endpoint.  Note that depending on your “since datetime” Paging Token and the number of activity types you are requesting, this call could return a large amount of JSON in the response.

Endpoint Reference Documentation:  https://developers.marketo.com/rest-api/endpoint-reference/lead-database-endpoint-reference/#!/Activ...


“Get Lead by Id”

Most customers will want to extract targeted fields for their main Person database in Marketo (e.g. so that data warehouse reports/visualizations can be run for “all Clicked Email activities for Person records in the United States vs. International”).  These person fields/attributes can be extracted from the Marketo Person database via the “Get Person by Id” endpoint.

Note that there are other methods for pulling Person data from Marketo as well (e.g. Bulk Person Extract, Get Lead by Filter Type, etc.).

Endpoint Documentation Reference:  https://developers.marketo.com/rest-api/endpoint-reference/lead-database-endpoint-reference/#/Leads


“Get Email by Id”

Because this post is focused on pulling Email-related Activities from a Marketo instance, it is recommended that you pull the Email metadata via the “Get Email by Id” endpoint and save this Email-related metadata into your Data Warehouse/reporting database.

Note that you can also use the “Get Email by Name” or “Get Emails” endpoints as well to pull this email metadata from your Marketo instance.

Endpoint Reference Documentation:


Now to the Email Related Activities and Linking back to the Marketo PROGRAM

A common question I hear is how can I link Program Metadata to a specific Email Activity?  In our question at the top of this post remember we wanted to be able to slice and dice the activity data by Person Attributes (e.g. Person records in the US) and Program Attributes (e.g. all Programs with a Program Tag of “XYZ”).  How can we tie the specific Email activities back to a specific Program?  How can we "stitch" all this data and all these ID's that the Activity History responds with together?


Tying the Email-activity JSON response back to a Person is pretty straight-forward as EVERY Activity returned by the Marketo Activity API includes the Marketo ID of the Person the activity belongs to.  If you extract Person Metadata as outlined earlier in this post into a table in your data warehouse, it should be pretty straight-forward to "stitch" the Activity Id/Table back to the Person Id/Table.


Tying an Email-activity to Email metadata is also pretty straight-forward as EVERY EMAIL Activity includes the Email ID of the Email Asset in the "primaryAttributeValueId" field of the JSON response.  If you extract Email Metadata as outlined earlier in this post into a table in your data warehouse, you get the idea...


But how do we tie an Email Activity with just a "Campaign ID" back to a specific Program?


Depending on the Program Type that the Email is a part of in Marketo, you actually might use different endpoints/approaches to get the relevant metadata for most common reporting/visualization scenarios.  Documentation on the different Marketo Program Types can be found here:  https://docs.marketo.com/display/public/DOCS/Understanding+Programs


“Default” Email Programs – “Get Campaign by Id” then “Get Program by Id”

This first scenario is for an email that is part of a “Default” Program Type in Marketo (note that you should already have the Email and Person metadata/attributes as defined in the previous section of this document).

For an email activity that is part of a “Default” Program, once you have the activity details from the “Get Lead Activities” explained above, you can use the “Get Campaign by Id” endpoint, passing in the “campaignId” attribute from the “Get Lead Activities” response to get the Campaign metadata details, which will include the Program Id (note that most customers find that the metadata in the Campaign endpoints do not contain information relevant to custom reporting, however it’s necessary to call this endpoint for “Default” programs in order to get the Program Id).

With the Program Id attribute from the Campaign, you can then call the “Get Program by Id” endpoint to get the detailed Program metadata.  The response for this API call will return the Program Tags and Period Costs that were setup on the Program.

Note that you can call the “Get Tokens by Folder Id” by passing in this Program Id and “folderType=Program” if you want to retrieve Program Token details.


“Email” Program Types – “Get Smart Campaign by Id” then “Get Program by Name”

For an “Email” batch program type, you can then use the “Get Smart Campaign by ID” endpoint, passing in the “campaignId” attribute (this is different than the “Default” Program Type where you use the “Get Campaign by Id”) to get the Campaign metadata.  Most customers find that making this API call for “Email” batch program types does NOT produce any results that are relevant for additional reporting.

The “Get Smart Campaign by Id” endpoint does not return the Program Id in the results, so it is recommended that you use the “Get Program by Name” endpoint using the Program Name from the Activity (you will need to parse the program name from the activity – which is everything before the “.” in the “primaryAttributeValue” of the activity).


“Engagement/Nurture” Program Types – A Combination and Post for Another Time

Because you can add emails directly to an Engagement Program or add the email(s) inside a Default Program that is inside the Engagement Program, how you access Email-related Activities for an Engagement Program can vary.  You will use a combination potentially of the endpoints outlined in Scenarios 1 and 2 above.  Let’s stop here and save this one for a post for another time…


Putting to All Together

The above approach is just one method that I've seen be successful with customers in the past.  Note that you can also parse the "primaryAttributeValue" in the JSON response for Email-related activities as that value includes both the Program Name (which has to be unique in Marketo), then a period ("."), and then the Email Asset Name.  Some customers opt for parsing this string to get their "stitching" process started.  Either approach will work.


Some customers choose to pull these detailed activities for targeted programs or for programs in specific folders in their Marketo instance.  Note that you can also be more “generic” in your approach and pull ALL Program Metadata and ALL Person Metadata, etc. and do the “stitching” together of data and Activity Id’s to Campaign Id’s to Program Id’s to Person Id’s all INSIDE a Data Warehouse via queries. 


As is the case with a lot of things in Marketo, there are several possible ways to answer the question posed at the top of this post -  but in my experience, executing TARGETED, NON-BULK MARKETO API calls as described in this post will help you understand what you get back from the Marketo API, how you can “stitch” in all together, and how you can build a data model to get the reports and visualizations you need for your Email-Related reporting.

Sr. Technical Consultant - Adobe/Marketo