Salesforce Tracking Data to Google Big Query

Question

We have a need to send the tracking data on a daily basis (open, clicks, unsubscribes, etc. ) fromMarketing Cloud to Google Big Query for analytics purposes. Is there a recommendation on integration options. thanks,

Answer 1

The most straightforward way would be to configure a 'Tracking Extract'

Tracking extracts provide granular tracking data for import from Email Studio into external systems. Use tracking extracts to export granular data regarding several different aspects of email send jobs, such as clicks, bounces, and survey data, from Marketing Cloud. Then, import that information into an automation or system.

You can then add this extract to an automation, and drop a file on a ftp server to be consumed by your external system

Answer 2

Without being a BigQuery expert, I would suggest to do following:

1. Create an S3 bucket on AWS
2. Use AWS Transfer, to provide SFTP functionality to S3
3. Add above bucket as a new file location
4. Set up a daily automation run
5. Add Extract Activity with a Tracking Extract to automation
6. Add File Transfer activity to automation (to upload the tracking extract to S3)
7. Use Amazon S3 Transfers to push data to BigQuery

Answer 3

It is also possible via Marketing Cloud SOAP Api is you wish to make direct Api Calls from your external application.

You can make calls to Retrieve OpenEvent, ClickEvent, etc. filtering on a date range, particular Send, particular TriggeredSend, or a combination.

There is some information on doing this in these two recent topics:

Marketing Cloud SOAP API Get Subscriber Specific Email Metrics

Marketing Cloud SOAP API - Derive Email Template from SendEvent

You can also enable SendLogging (Which is not on by default and requires a support request) and create a SendLog DataExtension for additional functionality -- this DE does not track Events, but it will keep track of each send and subscribers linked to the send for cross referencing on the Events, if needed.

Answer 4

You could use Big Query's REST API for this. The following endpoint can be used to stream data into a table:

POST /bigquery/v2/projects/{projectId}/datasets/{datasetId}/tables/{tableId}/insertAll

Here's the link to the documentation with more options: https://cloud.google.com/bigquery/docs/reference/rest