Query for Emails sent in the past month, unsubscribes opens, clicks for each

Question

Ok this seems like it should be a fairly simple query, but I'm not getting anywhere. What makes it worse is that all the data is stored in several tables.

I basically need to recreate the monthly send report but with only a few columns. It would be great if SFMC allowed you to customize which columns you want to see in a report, but they don't. So I'm stuck with this.

Let's assume today is August 1st. I'd like a Query that recognizes that and pulls all sends for July 1-31. If it's July 1, then the Query would know that and pull June 1-30. I somehow feel like that level of intelligence in a query isn't possible, but if not I'll just go back 32 days and trim what I don't need in the CSV file.

Then I want to pull all emails sent, grouped by name with total and unique opens and clicks for each, as well as total unsubscribes.

I realize this is a lot to ask, so if you can't help with the exact query, please just give me a hint as to what I need to group and sub selects and all that because I still do not full understand how to do those all. I would post the query I have, but it's only pulling a few items from one table so I don't think it's useful to you.

but here it is

SELECT
job.JobID,
job.EmailName,
job.EmailSubject,
job.SchedTime

FROM _job AS job

JOIN _open AS o
ON job.JobID = o.JobID

WHERE 
job.SchedTime > DateAdd(Day,-3,GetDate())

I tried to add a group by, but it warned me about aggregates and that meant i had to remove some of my selects. I tried count(), but not really sure how that would help me. I know this probably needs a sub select, I am just not sure how to write it

Answer 1

Sent, Unsubscribes, Opens, and Clicks in Past Month

I. Data Views

SFMC Dataviews required for this query include:

_Job
_Sent
_Unsubscribe
_Open
_Click

II. Methodology

Querying this many dataviews within SFMC within one SQL query is a bad idea; due to the 30-minute timeout rule, you’ll probably run into issues. It’s recommended to break this up into parts for each portion:

1) JobID Driver Query/DE -- Header includes a) jobid, b) email metadata
2 - 5) Sent/Unsub/Open/Click Stage Query/DE -- Header includes a) jobid and b) associated counts
6) Production Query/DE -- Header includes a) jobid, b) email metadata, and c) associated counts

NOTE: jobid + batchid + subscriberid acts as a unique identifier for a send within these dataviews.

III. Query: Driver

The results from this _job query will “drive” all of the other queries, act as the backbone of the report. We need to grab the email metadata within the time frame we need.

III-A. Previous Month Logic

In MS SQL Server, we need to use DATEPART(m, DATEADD(m, -1, GETDATE())) to get the previous month.

III-B. Previous Month's Year Logic

We also need to ensure the year is aligned. Although these dataviews will only go back 6 months, we still want to be diligent to write reusable SQL that is accurate and definite. If we could go beyond 6 months, if the previous month in a query was July, our query would get all July data regardless of year. We need DATEPART(yyyy, DATEADD(m, -1, GETDATE())).

III-C. SQL Query

SELECT
    a.jobid
    ,a.emailname
    ,a.emailsubject
    ,a.schedtime
FROM _job a
WHERE DATEPART(m, a.schedtime) = DATEPART(m, DATEADD(m, -1, GETDATE()))
AND DATEPART(yyyy, a.schedtime) = DATEPART(yyyy, DATEADD(m, -1, GETDATE()))

IV. Query: Stages

The stages are pretty concise and simple, but each of these stages need to be individual query objects to avoid timeouts.

IV-A. SQL Query: Stage Sent

This might be the only staging query that uses a particular combination of counting the jobid/batchid/subscriberid. This is up to the user and based on what the user is looking for, but if we want to see each individual send per jobid, we’ll want to include the batchid AND subscriberid (in SQL example below). If we want to see each user sent to per each jobid, we would just need subscriberid.

SELECT
    a.jobid
    ,COUNT(a.batchid + a.subscriberid) AS sent
FROM _sent a
INNER JOIN driver_jobid_DE b
    ON a.jobid = b.jobid
GROUP BY
    a.jobid

IV-B. SQL Query: Stage Unsubs/Opens/Clicks

Again, how we count here depends on the user needs. In this example, we’ll just grab user count per jobid.

SELECT
    a.jobid
    ,COUNT(a.subscriberid) AS stage_count_col /* useful identifier here, like 'unsubs'/'opens'/'clicks' */
FROM associated_dataview a
INNER JOIN jobid_driver_DE b
    ON a.jobid = b.jobid
GROUP BY
    a.jobid

V. Query: Production Report

Time to join everything together into one table. The "sent" and "jobid" DEs will act as our backbone. We'll use ISNULL() logic to ensure NULL values become zeroes.

SELECT
    b.jobid
    ,b.emailname
    ,b.emailsubject
    ,b.schedtime
    ,ISNULL(a.sent, '0') AS sent
    ,ISNULL(c.unsubs, '0') AS unsubs
    ,ISNULL(d.opens, '0') AS opens
    ,ISNULL(e.clicks, '0') AS clicks
FROM stage_sent_DE a
INNER JOIN driver_jobid_DE b
    ON a.jobid = b.jobid
LEFT JOIN stage_unsubs_DE c
    ON a.jobid = c.jobid
LEFT JOIN stage_opens_DE d
    ON a.jobid = d.jobid
LEFT JOIN stage_clicks_DE e
    ON a.jobid = e.jobid

Happy Marketing Clouding!