[SalesForce] How to query distinct triggered event dates

I've tried 50 different variations and still get the same result. I want to query the _Open table on ET's servers and return all unique events for a subscriber. When I go into the triggered event tracking, there are multiple records for the same subscriber, but when I query the _Open table, I always get a single record back.

Triggered Event Table:

+-------------------------------+-------------------------------+--------------------+
|         SubscriberKey         |             Email             |      OpenTime      |
+-------------------------------+-------------------------------+--------------------+
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/9/2015 2:20 PM  |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/9/2015 2:41 PM  |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/12/2015 6:48 AM |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/12/2015 6:48 AM |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/15/2015 5:33 AM |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/15/2015 5:33 AM |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/15/2015 5:56 AM |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/15/2015 5:56 AM |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/15/2015 6:02 AM |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/15/2015 6:02 AM |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/15/2015 7:39 AM |
| ExactTargetSupport@client.com | ExactTargetSupport@client.com | 10/16/2015 6:00 AM |
+-------------------------------+-------------------------------+--------------------+

My query:

   SELECT DISTINCT(TriggeredSendCustomerKey), 
   SubscriberKey, JobID, EventDate
   FROM _Open
   Where TriggeredSendCustomerKey = 'Trigger_Name'
   OR TriggeredSendCustomerKey = 'Trigger_Name_B'

What returns:

+-------------------------------+--------------------------+--------+--------------------+
|         SubscriberKey         | TriggeredSendCustomerKey | JobID  |     EventDate      |
+-------------------------------+--------------------------+--------+--------------------+
| ExactTargetSupport@client.com | Trigger-Name             | 900353 | 10/16/2015 6:00 AM |
+-------------------------------+--------------------------+--------+--------------------+

Best Answer

SoQL does not have distinct, so I am assuming you are using SQL. If you want to code it into SoQL then the samples above help.

If you are trying to run a DISTINCT query in sql (not SOQL), then you can't select all the fields like you're doing. Try the below query, I think those parenthesis might be hurting you.

 SELECT DISTINCT TriggeredSendCustomerKey, 
   SubscriberKey, JobID, EventDate
   FROM _Open
   Where TriggeredSendCustomerKey = 'Trigger_Name'
   OR TriggeredSendCustomerKey = 'Trigger_Name_B'

Related Solutions

[SalesForce] How to use SQL ORDER BY statement in marketing cloud (error says requires TOP)

I have tested the query on my end and it seems to be getting the MIN date as shown below,

1. NOT GROUPED data view query/screenshot showing 2 JobID/ContactID records with the same values but 2 different datetime stamps using the following query:

SELECT opens.[EVENTDATE] AS [OpenDate]
, opens.[JOBID] AS [JobID]
, opens.[SUBSCRIBERKEY] AS [ContactID]
FROM _Open opens WITH (NOLOCK)

2. GROUPED data view query/screenshot showing a single record JobID/ContactID records with the MIN datetime stamp using the following query:

SELECT MIN(opens.[EVENTDATE]) AS [OpenDate]
, opens.[JOBID] AS [JobID]
, opens.[SUBSCRIBERKEY] AS [ContactID]
FROM _Open opens WITH (NOLOCK)
GROUP BY opens.[JOBID], opens.[SUBSCRIBERKEY]

Hope this clears things up for you.

NOTE: if you ever wish to do an ORDER BY in future, use this following line of code

ROW_NUMBER() OVER (PARTITION BY SubscriberKey, JobID ORDER BY EventDate ASC) AS Row

then in your WHERE clause do this

WHERE Row = 1

this will return the first record only which will be your minimum datetime.

Query to Combine Data from 3 different Data View Tables

Considerations:

Source Data Extensions do have info in the mentioned fields since you pull the data not directly from the Data Views but from respective Data Extensions
Target Data Extension has a fully correct configuration to retrieve mentioned fields (e.g. Primary Keys, non-nullable fields, and fields' length)

Updated SQL:

SELECT
JobDataView.JobID, 
JobDataView.EmailID,
JobDataView.EmailName, 
JobDataView.EmailSubject,
JobDataView.FromName, 
JobDataView.FromEmail,
OpenDataView.SubscriberKey,
OpenDataView.EventDate AS OpenEventDate,
ClickDataView.EventDate AS ClickEventDate,
ClickDataView.URL, 
ClickDataView.LinkName,
ClickDataView.LinkContent
FROM JobDataView
LEFT JOIN OpenDataView ON OpenDataView.JobID = JobDataView.JobID
LEFT JOIN ClickDataView ON ClickDataView.JobID = JobDataView.JobID
WHERE
JobDataView.CreatedDate >= Convert(date, GetDate()-30) AND
JobDataView.CreatedDate < Convert(date, GetDate())

Notes:

You can have both EvenDates from Open and Click DEs by simply naming them differently
There is a difference between Full and Left Joins, in your case, it would be sufficient enough to use Left one
I have included the WHERE clause with the CreatedDate field from Job DE to select the last 30 days (feel free to specify another Date field for filtering)

Best Answer

Related Solutions

[SalesForce] How to use SQL ORDER BY statement in marketing cloud (error says requires TOP)

Query to Combine Data from 3 different Data View Tables

Related Topic