The send is done in a journey so we were not able to differentiate between the records based on JOBID. I am aware that Batch ID does differ, but how to I map which batchID corresponds to what records in my sendable Data Extension.
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.Sign up to join this community
DataViews are working with SubscriberKey and SubscriberID. Based on your question, I would assume that you would need to use SubscriberID.
As Journeys are trigger and not batch based, you loose the JOBID reference. However, the BatchID will do the trick.
Depending on your setup, processing speed can and will be an issue for DataViews. There are some simple rules to follow such as:
Here would be my approach:
1: Create a DE holding all sends
SELECT S.SubscriberKey , S.JobID , S.BatchID , EventDate FROM _Sent S INNER JOIN _Job J ON S.JobID = J.JobID WHERE J.Category != 'Test Send Emails'
2: Adding Open data to the DE:
SELECT S.SubscriberKey , S.JobID , S.BatchID , O.Opens FROM [Your DE] S INNER JOIN ( SELECT SubscriberKey , JobID , BatchID , COUNT(EventDate) AS Opens FROM _Open GROUP BY SubscriberKey , JobID , BatchID ) O ON S.SubscriberKey = O.SubscriberKey AND S.JobID = O.JobID AND S.BatchID = O.BatchID
3: Adding Clicks:
SELECT S.SubscriberKey , S.JobID , S.BatchID , C.Clicks FROM [Your DE] S INNER JOIN ( SELECT SubscriberKey , JobID , BatchID , COUNT(EventDate) AS Clicks FROM _Click GROUP BY SubscriberKey , JobID , BatchID ) C ON S.SubscriberKey = C.SubscriberKey AND S.JobID = C.JobID AND S.BatchID = C.BatchID
You can add more data like unsubscribes, bounces, journey name to the DE.
If you only want to filter the data by your journey, you could extend step #1 as followed:
SELECT S.SubscriberKey , S.JobID , S.BatchID , EventDate FROM _Sent S INNER JOIN _Job J ON S.JobID = J.JobID LEFT JOIN _JourneyActivity JA ON JA.JourneyActivityObjectID = J.TriggererSendDefinitionObjectId LEFT JOIN _Journey JO ON JO.VersionId = JA.VersionId WHERE J.Category != 'Test Send Emails' AND ( JO.JourneyName = 'YOUR JOURNEY NAME' OR JA.ActivityName = 'YOUR EMAIL ACTIVITY NAME INSIDE THE JOURNEY' )
To tie down tracking data to the specific instance of a contact in a Journey, you will need to generate a unique identifier for the record as you load into your journey entry data extension and then use send logging to capture that unique identifier alongside the JobID, ListID, BatchID and SubscriberID as emails are sent by the journey.
If you don't have send logging enabled already in your account, raise a case with Salesforce Support to give you access to the Send Log data extension template.
Create a Send Log data extension in your account using the Send Log data extension template and add a field to capture the unique identifier for instances of Journey Builder records. I'm going to refer to this field as "JourneyInstanceId" here. Make it of type "text", nullable and 36 characters long.
Add a similar field your your Journey Entry data extension.
Initially load your data from the Enhanced FTP into a staging data extension.
5. Run a query similar to this to insert your records from your staging table into the journey entry data extension:
SELECT *, NewID() AS JourneyInstanceID FROM [MyStagingTable]
6. Use the Send Log data extension to relate the system data views back to your journey entry data along these lines:
SELECT j.JourneyInstanceID, s.SubscriberKey, COUNT(DISTINCT s.JobID) AS EmailsSent FROM [MyJourneyEntryTable] j INNER JOIN [MySendLogTable] sl ON j.JourneyInstanceID = sl.JourneyInstanceID INNER JOIN _Sent s ON sl.JobID = s.JobID AND sl.ListID = s.ListID AND sl.BatchID = s.BatchID AND sl.SubscriberID = s.SubscriberID GROUP BY j.JourneyInstanceID, s.SubscriberKey