3

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 |
+-------------------------------+--------------------------+--------+--------------------+
  • What does the "_Open" table look like? Fields and a few records would help – Sebastian Kessel Nov 17 '15 at 23:33
  • AccountID (int), JobID (int), ListID (int), SubscriberKey (varchar), EventDate (date/time), TriggeredSendCustomerKey (varcahr). Example record is included in OP. – Realrx7 Nov 18 '15 at 11:53
  • How to select Unique values in SOQL Check whether this will be helpful – Priyalakshmi Nov 18 '15 at 15:11
  • The OP is showing me what is returning... I'd like to see what the raw table looks like. The first table you posted above has different fields than the second it would be awesome if you could add the rest.... I'll attempt an answer anyway – Sebastian Kessel Nov 18 '15 at 18:31
  • What's the primary key on your target Data Extension? – Adam Spriggs Nov 18 '15 at 19:36
1

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'
  • Still returns only a single event when I know there are multiple event dates. – Realrx7 Nov 18 '15 at 19:56
  • Can you update your first table in the OP so we can see the values of "TriggeredSendCustomerKey", "EventDate" and "JobID" for each row? – Sebastian Kessel Nov 18 '15 at 20:16
2
+50

I would write your query like this. The IsUnique = 1 will limit it only unique opens by a subscriber for a specific send.

select 
TriggeredSendCustomerKey
, subscriberkey
, jobid
, EventDate 
from _Open
where 
IsUnique = 1
and TriggeredSendCustomerKey in ('Trigger_Name','Trigger_Name_B')

The System Data View schemas can be found on this page on the SFMC Help site: Query Activity

One nuance about Triggered Send JobIDs: unlike Guided/UI Send jobs, Triggered Send JobIDs stay the same until the Trigger is republished.

  • If a subscriber was sent two emails in the same batch (duplicate) and they opened each, will there be two records in _Open for that subscriber with IsUnique = 1? I suspect not, but do not have the appropriate data to filter through (all sends are de-duplicated.) – Daniel Oct 23 '16 at 23:26
  • I've been trying to get SFMC to clarify isUnique for a month. – Adam Spriggs Oct 23 '16 at 23:29
0
    List<_Open> lst= [select  SubscriberKey, JobID, EventDate
       FROM _Open];

     Map<String,_Open> mapit=new Map<String,_Open>();

    for(_Open trial:lst){
        mapit.put(trial.Trigger_Name,trial);
    }

system.debug('logssss'+mapit.values());

Try it.

Use Maps and make 'Trigger_Name' and 'Trigger_Name_B' as key for the Map

  • 3
    This answer would be applicable for SOQL, however the question is about ExactTarget / Marketing Cloud queries - which are SQL Server 2005 based. – Mark Pond Nov 18 '15 at 18:11

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.