2

Struggling for a while with trying to extract basic statistics of our company emails status.

What we want to get is a query that will extract the following stats.:

Probably taken from Send Log table: JobID, SubscriberID, provider_internal_name (we have that in the send_log data extension)

Probably taken from other ET tables: EmailName, Subject_name, Send_date, Email sent #, Email delivered #, Total click throughs #, Unique click throughs #, Emails opened #, Unique emails opened #

Everything for the last 3 months.

Here is the query currently as written:

SELECT sd.jobid jobid , 
       sd.SubID sub_id , 
       sd.provider_internal_name providerinternalname, 
       s.eventdate sent_date, 
       o.eventdate open_date, 
       o.IsUnique open_unique, 
       c.eventdate click_date,
       c.url click_url,
       c.linkname click_linkname,
       c.linkcontent click_linkcontent,
       c.IsUnique click_unique , 
       j.EmailName, 
       j.EmailSubject 

FROM sendlog sd 
INNER JOIN _sent s on sd.jobid=s.jobid and sd.SubID=s.subscriberid 
INNER JOIN _Job j 
ON Sd.JobID = j.JobID 
LEFT JOIN _click c 
ON c.jobid=sd.jobid AND c.subscriberid=sd.SubID 
LEFT JOIN _open o 
ON o.jobid=sd.jobid AND o.subscriberid=sd.SubID 
WHERE s.eventdate > '2014-04-01'
3
  • Do you currently have any code you have tried that isn't working? Are you doing this as a query activity, or as an extract activity? Is this with the API or directly in the Application? Commented Apr 2, 2014 at 12:38
  • I have no code but tried many queries that didnt gave results back. trying to run it in a query activity and then results in a data extension. Working with the application itself in the query activity.
    – kfir
    Commented Apr 3, 2014 at 8:44
  • As a suggestion for future posts, including some sample of code will help your questions get resolved quicker. This one in particular, you could just have some syntax wrong with your query that could easily be corrected, but without any code it's more of a guess than a solution. Hoping I can help get this answered for you. Commented Apr 7, 2014 at 11:38

4 Answers 4

1

I'm bumping this for users who need an answer according to the new ExactTarget documentation. Some fields have changed and data extensions have been renamed.

The sendlog data extension now uses jobid and subscriberid and individual tracking data extension names can be found here and use SendId and SubscriberID. The _Job data extension is now SendJobs. The below query should get you started on something like Email Open Rate since 5/1/17:

SELECT
  EmailName
, SUM(Send_Flag) as Sends
, SUM(Open_Flag) as Opens
, CAST(SUM(Open_Flag)/SUM(Send_Flag) AS decimal(16, 2)) Open_Rate
FROM (
    SELECT DISTINCT 
      sd.jobid jobid
    , s.SubscriberKey
    , j.EmailName
    , CAST(CASE WHEN (s.Event IS NOT NULL) THEN 1 ELSE 0 END Send AS INT) Send_Flag
    , CAST(CASE WHEN (o.Event IS NOT NULL) THEN 1 ELSE 0 END Open AS INT) Open_Flag
    FROM sendlog sd 
    INNER JOIN Sent s ON sd.jobid = s.sendid AND sd.subscriberid = s.subscriberid
    INNER JOIN SendJobs j ON sd.jobid = j.sendid
    LEFT JOIN Opens o ON sd.jobid = o.sendid AND sd.subscriberid = o.subscriberid
    WHERE j.SentTime > '01-May-2017'
) raw
GROUP BY EmailName

To get is data out you follow the Data Extension > Query > Extract > File Transfer pattern in SFMC.

0

Thanks! This is the query I wrote. Meanwhile, there are no results but might be taking some time to run:

select 
  sd.jobid 
, sd.subid sub_id  
, sd.provider_internal_name providerinternalname
, s.eventdate sent_date
, o.eventdate open_date
, o.isunique open_unique
, c.eventdate click_date
, c.url click_url
, c.linkname click_linkname
, c.linkcontent click_linkcontent
, c.isunique click_unique 
, j.emailname
, j.emailsubject
from sendlog sd
inner join _sent s on sd.jobid=s.jobid and sd.subid=s.subscriberid
inner join _job j on sd.jobid = j.jobid
left join _click c on c.jobid=sd.jobid and c.subscriberid=sd.subid
left join _open o on o.jobid=sd.jobid and o.subscriberid=sd.subid
where s.eventdate >  '2014-04-01'
3
  • Hi, is this meant to be a comment to one of the other answers? is this just to show your finalized query? did you get help/insight from another answer? or is this a complete new answer
    – Saariko
    Commented Apr 7, 2014 at 12:04
  • Just added the query i've wrote to get feedback and tips if i did somthing wrong..
    – kfir
    Commented Apr 7, 2014 at 13:05
  • @kfir know that your question is still marked as 'unresolved', you can change that by clicking the (V) button on the left side of any of the given answers. Commented Nov 12, 2014 at 7:05
0

Using the documentation for the Query Activity, you should be able to piece together some sort of query that will work to pull this data into a target data extension.

The target data extension will need to have all the fields you want to put in one place. In your case, you would need to have a data extension with following column names:

  • JobID
  • SubscriberID
  • provider_internal_name
  • EmailName
  • Subject_name
  • Send_date
  • Email sent #
  • Email delivered #
  • Total click throughs #
  • Unique click throughs #
  • Emails opened #
  • Unique emails opened #

Using fairly standard SQL(2005), you should be able to join a few tables:

  • Send Log
  • _Job
  • _Click
  • _Open
  • _Sent

You will need to work with some GROUP_BY and nesting to get it exactly how you want it. Without some sample code, I don't really know where your specific issue is, but this is the right path. I would suggest starting simple like getting back -

  • JobID
  • SubscriberID
  • provider_internal_name
  • EmailName
  • Subject_name

By running something like

select 
sl.jobid
, sl.subid as subscriberid
, sl.provider_internal_name
, j.emailname
, j.emailsubject as subject_name
from [send log] as sl 
inner join _job as j on sl.jobid = j.jobid

That should get you close.

Once you have the data

The easiest way to get data out of ExactTarget is to use Data Extracts.

The two extracts you would be particularly interested in are data extension extracts and tracking extracts.

You can create automations that will run these extracts and place them in your Enhanced FTP account, or another file transfer location you choose.

The result of your query can then be extracted. All of this can be ran with an automation to perform the query and the extract.

5
  • Is it possible to add the send log data into one of these data extractions?
    – kfir
    Commented Apr 6, 2014 at 7:19
  • The send log is a data extension, so you can run an extract with that as well. Commented Apr 6, 2014 at 11:39
  • Thanks a lot for your advices here, my question is if i can get the send log data along with the tracking extract data combined in 1 file?
    – kfir
    Commented Apr 7, 2014 at 8:18
  • 1
    Thanks!This is the query i wrote, meanwhile there are no results but might be taking some time to run:
    – kfir
    Commented Apr 7, 2014 at 11:50
  • It can take some time depending on the data set, yes. If you find you are getting no results still, try simplifying the query to get some initial success, then add to that. Commented Apr 7, 2014 at 12:03
0

So I need to write again on this same post since our company queries stopped working!. It might happen due to large data sets but I'm not sure why since SFMC direct support is not supporting and helping with such Queries.

Any help here will be much appreciated. I only wanted to find the bounce, open and click that is from the list of sent emails, but when I add the _open table it makes it too heavy to run and will never come back. This query below is the basic one of Open rates only that is not working right now:

SELECT 
  s.subscriberid as subscriber_id
, sd.provider_internal_name as provider
, s.eventdate as sent_date
, o.IsUnique AS open_u
FROM sendlog sd 
JOIN _Sent s ON sd.jobid = s.jobid AND sd.subid = s.subscriberid
LEFT OUTER JOIN _open o ON sd.jobid = o.jobid AND sd.subid = o.subscriberid and o.isunique = 1
WHERE s.eventdate >DATEADD (day, -8, GETDATE())

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .