Create SQL Queries for Multiple Journeys
Learning Objectives
After completing this unit, you’ll be able to:
- Guide journeys using SQL Query activities, staging tables, and Journey Builder features.
- Customize your SQL queries of data views for the best performance.
New Journeys Need a New Data Extension
Isabelle’s first plan to use decision splits worked well, so she wants to apply it to more activities. Her expanded plan includes multiple marketing efforts based on how her contacts engaged in a previous journey. If a contact responded well to the welcome journey, she wants to include them in more promotional journeys based on their interests. If a contact didn’t respond to the welcome email, they won’t receive additional promotions. This solution involves looking at a group of emails, all within the same journey. The setup is similar to the use case we covered in the last unit, but the data extension looks a little different because it has a few more fields. Isabelle creates a data extension with these fields.
- SubscriberKey
- EventDate
- LinkName (this is the tracking alias)
- Journey Name
- Email Name
Sample SQL for the Query Activity and Automation
Isabelle again uses a similar setup as in the previous unit. Instead of focusing on a specific email, she wants to look at click behavior and engagement for all emails in a journey.
The problem is that data extensions that contain ongoing information on clicks tend to grow. And grow. And grow. This eventually slows down any queries. Isabelle wants to make sure that her queries are as speedy and efficient as possible, so she decides to use a small query that creates a staging data extension with just the data necessary for the activity. Think of this data extension as the abridged version of the larger data extension, giving the system just what it needs to accomplish a task quickly.
SELECT c.SubscriberKey, c.EventDate, c.LinkName, c.triggerersenddefinitionobjectid FROM [_Click] c with (nolock)
This query links that intermediate click data extension (named ClickStaging) to the Journey and Journey Activity data views. Isabelle is using these data views in the expanded version of her query to bring in additional journey information. Don’t worry—the Journey and Journey Activity data views aren’t likely to fill up with information as quickly as the data extension holding the click information.
SELECT c.SubscriberKey, c.EventDate, c.LinkName, j.JourneyName, ja.ActivityName as 'Email Name'
FROM [ClickStaging] c with (nolock)
join [_JourneyActivity] ja with (nolock)
On c.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
Join [_Journey] j
On ja.VersionID = j.VersionID
WHERE ja.ActivityType in ('EMAIL','EMAILV2')
AND J.JourneyStatus - 'active'
So why use the data views? These views hold a rolling 180 days' worth of data that records in near real time. This lower latency makes sure your activities get the most recent possible information. You will need to account for these refreshes when assigning your wait times. In other words, make sure the refreshes occur during the wait times, or you risk sending messages with outdated information or to email contacts who should not receive the email.
Isabelle can query for engagement behavior in many different ways using SQL and data views, even when links wrapped with AMPscript prevent her from using the standard engagement split.
Sum It Up
Because every journey is unique, your account configuration will look different than Isabelle’s. But this use case introduces concepts and sample code that you can use to improve the efficiency of your journeys and the performance of your sends.