How to properly join 3 Tracking Extracts to create a YTD Clicked Link Tracking Report for all types of email sends (email names)

Question

I would like to build a YTD Click Tracking report to replicate similar but better reporting I do currently with Queries/Automations where I join the click data view with the job data view and subscribers data view. I use this query frequently to find out which links were clicked but dataviews are limited to retrieve only as far back as far as 6months.

Select Distinct clk.EventDate, sb.EmailAddress, j.emailname, clk.LinkName, clk.LinkContent
FROM _click clk
INNER JOIN _job j ON clk.jobid = j.jobid
INNER JOIN ent._subscribers sb on clk.subscriberid = sb.subscriberid

I believe to do this it requires only these 3 tracking extracts which I have pulled YTD 2023 for each using the API which I have downloaded.

1. Sent Tracking Extract - Sent.csv
2. SendJobs Tracking Extract - SendJobs.csv
3. Click Tracking Extract - Clicks.csv

From the fields I saw in each Sent extract, while there is no JobID field to join with, there is a SendID field in every extract which might work for using as the JobID, in addition to SubscriberKey, EmailAddress, and SubscriberID.

As mentioned, I want to improve the reporting by including additional fields present in these extracts so I'll want to include the following 16 fields from each extract.

1. Sent Tracking Extract - Sent.csv

• SendID
• SubscriberKey
• EmailAddress
• SubscriberID
• EventDate (which I will rename to SentDate)

2. SendJobs Tracking Extract - SendJobs.csv

• FromName
• FromEmail
• SentTime
• Subject
• EmailName

3. Click Tracking Extract - Clicks.csv

• EventDate (which I will rename to ClickDate)
• URL
• Browser
• EmailClient
• OperatingSystem
• Device

Using a tool, I did a merge of these 3 CSV data by doing a 2 lookups (get any matches and ignore matches) (A) from Sent.csv and SendJobs.csv and (B) the resulted join from (A) with Clicks.csv

I suspect this may not be properly joining all the necessary columns and will likely require doing compound joins such as also joining SubscriberIDs on subscriberIDs. However, only the Sent.csv tracking extract has a SubscriberID field, where both the SendJobs and Click Tracking Extracts do not have SubscriberID field.

I'm concerned that while the lookup on SendID between these performed output it may not accurately represent both the send activity and click activity collectivity for each unique clicked link and this might be more complicated if there is a mix of both triggered send emails and commercial sends where the subscriberKey can vary for the different email addresses (18 length ContactID for subkeys on some triggered sends from Salesforce vs the email addresses as subkeys for the majority of emails).

What can I do to ensure accuracy?

Answer 1

You can try!

SELECT
    s.SendID,
    s.SubscriberKey,
    s.EmailAddress,
    s.SubscriberID,
    s.SentDate,
    sj.FromName,
    sj.FromEmail,
    sj.SentTime,
    sj.Subject,
    sj.EmailName,
    c.ClickDate,
    c.URL,
    c.Browser,
    c.EmailClient,
    c.OperatingSystem,
    c.Device
FROM 
    Sent s
INNER JOIN 
    SendJobs sj ON s.SendID = sj.SendID
LEFT JOIN 
    Clicks c ON s.SendID = c.SendID AND s.EmailAddress = c.EmailAddress
WHERE
    s.SendID IS NOT NULL AND s.EmailAddress IS NOT NULL
ORDER BY 
    s.SentDate DESC, c.ClickDate DESC;

The code uses INNER JOIN between Sent and SendJobs on SendID. A LEFT JOIN is then performed between this result and Clicks, joining on both SendID and EmailAddress. This ensures that even if there are no clicks for a particular sent email, the sent record still appears in the result.