What is the primary key in data view - sent and data view - open?

Question

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 ?

Answer 1

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.

Answer 2

There are examples in the salesforce documentation which gives you an idea of how to join these data views to extract the required information. Below are some of them. These queries should be helpful to build the query that you are looking for.

Query to get opens in the last 30 days

Query to find Subscribers with No Opens or Clicks