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:
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.
Leave a comment below or email me at email@example.com.
3 thoughts on “Consolidate tracking data for Journey Builder email sends”
Thanks a bunch. Found this very useful.
how to add custome dates or last 3 months metrics in this sql query
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.