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
Best Answer
Sent, Unsubscribes, Opens, and Clicks in Past Month
I. Data Views
SFMC Dataviews required for this query include:
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:
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
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.
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.
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.
Happy Marketing Clouding!