I've been looking for some validation regarding tracking data for emails. It seems that when it comes to comparing results of querying the _Sent data view and either of the standard or Discovery tracking reports the data for Total Sends is consistent. However, when it comes to comparing queries to measure Uniques from _Open data views or _Click or _Bounce data views for Transactional (Triggered) sends there are discrepancies between them.
For example, below is a query which retrieves all recipients for a particular email for the previous day.
SELECT
s.SubscriberKey as 'SubscriberKey',
j.EmailName as 'EmailName',
s.EventDate as 'SentDate',
o.EventDate as 'OpenDate'
FROM _Sent s INNER JOIN _Job as j ON s.JobID = j.JobID
LEFT JOIN _Open o ON s.JobID = o.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and s.SubscriberID = o.SubscriberID and o.IsUnique = 1
WHERE j.EmailName = '01_Delivery_48hours'
AND s.EventDate >= CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 101)
AND s.EventDate < CONVERT(varchar(10), DATEADD(day, 0, GETDATE()), 101)
Using inspiration from a previous salesforce stackexchange post on Opens and Clicks, made use of a similar query to retreive total recipients who were sent a particular email from the 'Previous Full Day' and also opened.
With this query above I used BatchIDs in hope of improving the accuracy. I mentioned earlier than this email is a trigger and not a commercial send. It seems when I run this type of open query on commercial send emails the results match everywhere but when comparing reports for Transactional Sends I see disperaties. I've already confirmed that I have all triggers being captured in the SendLog.
However, when looking at the following built in reports from Marketing Cloud I received different numbers for the opens with my query usually having the large number. The numbers are off by a difference of 50 and I am not sure of the reason.
- Send Classification by Email Tracking Report
- Account Summary Report
These two reports which are older legacy reports inherited from Classic Emails but also replicated in Discovery Reports usually match in their Total Sends, Total Unique Opens, Total Unique Clicks, etc. However, even they are different than the newer 'Discover custom report' that you can make by drag and drop a custom view using specific filters and measures in columns in 'Analytics Builder'. I found using a custom Analytics Discovery Report filtering the same email and date range, the 'Unique Opens' and 'Unique Clicks' were closer to my numbers although still around 30 or less.
When I reached out to SFMC Premium Support for insight they said the reports differ because the data is collected and analyzed differently. However, outside of possible timing differences of when the analysis might start and end they didn't elaborate as to specifics on the data collected from querying open data view vs. the built-in tracking reports.
I have checked around and seen a myriad of possible reasons. One explanation could be the tracking tag might be analyzed differently in the different tracking methods/reports which could subsequently be obscured between various users using different email clients where some clients don't register opens or clicks as well as others.
Another could that some tracking methods continuously measure the opens after the sending period has completed where as others might be a fixed value that doesn't update. I think the later might be the case with the Send Classification and Account Summary report and might also be true for the Discovery Report even though the open, clicks and bounces don't match the other Tracking Reports.
I would greatly appreciate if someone with more experience could provide not only a definitive explanation but a solution for how to make the opens query match the standard reports, if possible.
