[SalesForce] the primary key in data view – sent and data view – open

I want to create a data extension consisting of total sends, their send datetime value & total opens.

There isn't any standard report in marketing cloud which provides time of send. To fetch this data I'm trying to create a custom query between two tables (data views), _Sent & _Open.

Now, as far as I could understand, combination of JobId & Subscriber Id should map between two tables.

  1. How do I apply join using combination (or concatenate) of these two fields ?
  2. What is the respective primary key for these two tables ?

Best Answer

It can be handled with joins in SQL.

You would link the _Sent DV with the _Open DV using a matching on JobID.

Below is a sample query, assuming you have a DE with the following fields: JobID | TotalSent | SentDate | OpenDate and has the pkey set as JobID.

SELECT s.JobID
, st.TotalSent
, s.EventDate as SentDate
, o.OpenCount
FROM ([_Sent] s
LEFT JOIN (
 SELECT Count(op.*) as OpenCount
 FROM [_Open] op
) o
ON s.JobID = o.JobID
LEFT JOIN (
  SELECT COUNT(se.*) as TotalSent
  FROM [_Sent] se
) st
ON s.JobID = st.JobID
WHERE s.EventDate >= DATEADD(day,-7, GETDATE())

*I put a date limit of 7 days as this SQL is very process heavy. You could potentially separate this out into multiple queries or even just pushing the DV into DEs and running on those instead would make it more efficient.

This will give you the total sent and total opens. To gather just unique opens, you would need to target the IsUnique attribute inside of the _Open DV.

Now to note, there are no primary keys inside of any of the Data Views in SFMC, but there are quite a few fields that can be used to match with other DEs, etc.

Related Topic