0

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.

Tag: DataView

2
  • How does your data find its way into the data extension? – Macca Apr 15 '20 at 8:09
  • starts with a file drop into ftp, where automation studio then recognizes the file based on a file name pattern and then uses an import activity to import the data into the Data Extension. – Moraish Kapoor Apr 15 '20 at 12:45
0

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:

  • Do a copy of the DataView as they are live data tables and can be blocked quite often
  • Split your SQL queries into smaller sections

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'
        )
0

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.

  1. 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.

  2. 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.

  3. Add a similar field your your Journey Entry data extension.

  4. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.