Discrepancies between API Data and Marketo Email Reports

OliverW
Level 1

Discrepancies between API Data and Marketo Email Reports

Hello everyone,

 

in our department, I'm responsible for visualizing our marketing data. At the moment we are using Tableau as a Dashboard solution and so far it's working well.

For some weeks now, I'm trying to create Tableau dashboards for our Marketo email performance. Therefore, I'm using the Marketo Tableau Connector to access the data (the connector is based on the Marketo REST API).

After creating the dashboard, I started to compare it with our Marketo internal Email Reports and discovered some differences. Of course, I started to look for mistakes in my calculations, but after some time I realized that the API provides different data.

In the picture, you can see the Email Report from Marketo and the API Data in Tableau. In most cases, the data is about the same order of magnitude, but never identical. Especially "Opens" seem to differ more than the other KPIs. (same email, date range and filters)

Data Marketo vs Tableau (API).jpg

 

Current calculations of my Tableau KPIs on email level shown in the picture:

Total Send: COUNT(distinct) LeadID IF ActivityTypeID=6

Total Delivered: Total Send - Total Hard Bounces - Total Soft Bounces (used in picture)

Total Delivered v2: COUNT(distinct) LeadID IF ActivityTypeID=7 (also doesn't provide same figures as in the email report)

Total Opens: COUNT(distinct) LeadID IF ActivityTypeID=10

 

I already know from other discussions in Marketo Nation that Marketo tracks/calculates opens different in the internal Email Report than in the API. Does anyone have a solution for this and for the other KPIs like "Sent" and "Delivered"?

 

Thank you very much in advance and have a nice day!

Best regards,

 

Oliver Wischnewski

 

 

Tags (3)
3 REPLIES 3
Balkar_Singh
Level 9 - Community Advisor + Adobe Champion

Re: Discrepancies between API Data and Marketo Email Reports

While the data does differ, as what we get from email performance is the performance data of "email" and what we try to fetch from API is activity data of a "person". I still think there's some confusion about how Marketo labels some of the metrics in Email Performance Reports. After checking about how Marketo bases this on certain rules, there still seems to be confusion if we try to compare.

 

E.g. Open Data, as per Email Performance Reports is - "People who opened the email at least once" - does this count as Distinct Count? Why not, if not? Similar to what you point, have experienced that in such cases the values are not exactly the same. And when I look at activity log, it is in fact a distinct count. So if a person opened it twice, a distinct count is one - but it doesn't seems distinct in email performance reports. May be I am interpreting the description on hover incorrectly?

Balkar_Singh_0-1599748233708.png

If we want to match what we generate in dashboard exactly to the Marketo's email performance, it needs to have the same rules. However, I think we can define our own rules as well, which are based on a person's activity. But definitely looking for ways and better thoughts of addressing this.

 

#opinion

SanfordWhiteman
Level 10 - Community Moderator

Re: Discrepancies between API Data and Marketo Email Reports

An Open is only logged once per person per email in Marketo, and there is never a second such event in the Activity Log.

 

The principal reason for the discrepancies @OliverW mentions (as Oliver has also found independently) is that in a Marketo report, a Click results in an implied Open being registered as well, even if there was no Open ever logged. The concept being that on a human level, we consider the person to have opened (= read/looked at) the email if they are known to have followed a link. This is not of course actually true when it comes to mail scanners, but it's an accurate way to describe the human UX.

 

You can make a 3rd-party reporting system do the same thing: write a custom SQL view, for example, that uses the presence of a Click to flip Open to true as well.

Balkar_Singh
Level 9 - Community Advisor + Adobe Champion

Re: Discrepancies between API Data and Marketo Email Reports

An Open is only logged once per person per email in Marketo, and there is never a second such event in the Activity Log. - This made things so clear. Thanks a lot!