0

So I am exporting data views using the automation studio for each of the data view objects (open, click etc.) to FTP then from there our backend guy takes these csv files and inserts them into another platform.

The problem is that my counts in Marketing Cloud when I use query studio are way higher than our external platform. Is there something I am missing in how MC tabulates data vs our platform? Anything I haven't thought of to look into?

Below is a sample code of one of my queries which exports our data to FTP. Thanks!

SELECT  o.AccountID
    ,o.OYBAccountID
    ,o.JobID
    ,o.ListID
    ,o.BatchID
    ,o.SubscriberID
    ,o.SubscriberKey
    ,o.EventDate
    ,o.Domain
    ,o.IsUnique
    ,o.TriggererSendDefinitionObjectID
    ,o.TriggeredSendCustomerKey
    ,cal.uuid__c AS AccountUUID
    ,cal.User_uuid__c AS UserUUID
FROM _Open o with(nolock)
inner join [contact_all] cal
on o.subscriberKey = cal.id
WHERE EventDate > dateadd(hh,-2,getdate())
4

Without knowing exactly how your external processes work it will be tough to say for certain. Something to potentially look in to would be the Unique factor of these results. You are extracting Opens and with this SQL, you are pulling ALL opens instead of the unique ones. As such, there is a possibility that you have duplicate results when comparing it to your system (if your system is de-duping these). This being said, you could add a condition for the IsUnique field to be set to 1, however, there will still be situations where duplicate rows will manifest because the Unique field is generated by a combination of fields including the EventDate. When a security system on a receiving end subscriber is set to open and click links in incoming emails, sometimes this happens so fast it makes duplicate EventDate values for separate open events for the same subscriber and in turn writes two or more IsUnique records to the Open table. I would validate if your external system is de-duplicating this data by Job and Subscriber because this would explain the discrepancy.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.