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

view raw Journey-consolidated-tracking.sql hosted with ❤ by GitHub

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.

---