Consolidate tracking data for Journey Builder email sends

When it comes to emails sent from Journey Builder, the tracking data is available either directly in each email activity inside the journey, or in Email Studio under Tracking > Journey Builder Sends. But what if you wanted to see consolidated tracking data for all your journeys’ performance in one place? With this simple SQL query, you can make this available for your Salesforce Marketing Cloud users.

Create a Data Extension to hold results

First, let’s create a Data Extension where you will keep the results of your query. Here’s is a proposed structure I have used:

  • JourneyName – text, 100
  • VersionNumber – number
  • EmailName – text, 100
  • JourneyStatus – text, 100
  • Sent – number
  • Opened – number
  • Clicked – number
  • Bounced – number
  • Unsubscribed – number

Create an SQL query

Now let’s create the following SQL query in Automation Studio and automate it:

SELECT
jou.JourneyName,
jou.VersionNumber,
j.EmailName,
jou.JourneyStatus,
COUNT(s.JobID) AS Sent,
COUNT(o.JobID) AS Opened,
COUNT(c.JobID) AS Clicked,
COUNT(b.JobID) AS Bounced,
COUNT(u.JobID) AS Unsubscribed
FROM _Job j LEFT JOIN _Sent s ON j.JobID = s.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
LEFT JOIN _Click c ON s.JobID = c.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and s.SubscriberID = c.SubscriberID and c.IsUnique = 1
LEFT JOIN _Bounce b ON s.JobID = b.JobID and s.ListID = b.ListID and s.BatchID = b.BatchID and s.SubscriberID = b.SubscriberID and b.IsUnique = 1
LEFT JOIN _Unsubscribe u ON s.JobID = u.JobID and s.ListID = u.ListID and s.BatchID = u.BatchID and s.SubscriberID = u.SubscriberID and u.IsUnique = 1
LEFT JOIN _JourneyActivity ja ON j.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
LEFT JOIN _Journey jou ON ja.VersionID = jou.VersionID
WHERE jou.JourneyName IS NOT NULL AND jou.JourneyStatus = 'Running'
GROUP BY jou.JourneyName, jou.VersionNumber, jou.JourneyStatus, j.EmailName

The above query will pull tracking data for each email send activity included in the latest version of each journey in the Business Unit. If you would like it to grab the data also from previous journey versions, simply remove the last condition from the query, AND jou.JourneyStatus = 'Running'.

Here is a preview of the final results:

If you’re running the SQL in Query Studio and see an error message, just ignore it and press Run once more.


Questions? Comments?

Leave a comment below or email me at zuzanna@sfmarketing.cloud.


3 thoughts on “Consolidate tracking data for Journey Builder email sends

  1. Daniel

    Great query! I would remove IsUnique = 1 for clicks and use combo of primary keys for unique clicks per email. Per data view click documentation IsUnique is at the job level and will only count the first click against the job, even if they multiple URLs.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s