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 |
+-------------------------------+--------------------------+--------+--------------------+
7
  • What does the "_Open" table look like? Fields and a few records would help Nov 17, 2015 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, 2015 at 11:53
  • How to select Unique values in SOQL Check whether this will be helpful Nov 18, 2015 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 Nov 18, 2015 at 18:31
  • What's the primary key on your target Data Extension? Nov 18, 2015 at 19:36

3 Answers 3

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'
2
  • Still returns only a single event when I know there are multiple event dates.
    – Realrx7
    Nov 18, 2015 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? Nov 18, 2015 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.

2
  • 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, 2016 at 23:26
  • I've been trying to get SFMC to clarify isUnique for a month. Oct 23, 2016 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

1
  • 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, 2015 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.