If your Monday morning still starts with exporting send stats from native reports, pasting them into a spreadsheet and reconciling them by hand before the marketing review, you are spending hours on work that Salesforce Marketing Cloud can do for you. The platform exposes system data views that you can query with SQL in a Query Activity, store in a summary Data Extension and feed to any BI tool, all on a nightly schedule. In this tutorial you will build that reporting foundation step by step: a summary table, an incremental query, an automation and the pitfalls that quietly corrupt dashboards. By the end you will have a pattern you can plug into Tableau, Looker Studio, Power BI or a lightweight CloudPage.
Why native reports hit a ceiling
Email Studio and Journey Builder reports are fine for quick answers: sends, opens, clicks. They fall short when you need to join results with customer attributes, compare business units or chart a 12-month trend. The same three problems come up again and again:
- tracking data is only retained for a limited time, so year-over-year comparisons need your own archive;
- you cannot easily join send events with CRM data or business segments;
- manual exports introduce errors, and no two people ever build them the same way.
The fix is to treat your account as a small analytics database and materialize the metrics you care about.
Data views: your raw material
Data views are read-only system tables, available only inside a Query Activity. The most useful ones for an email dashboard are _Sent, _Open, _Click, _Bounce, _Unsubscribe and _Job. They all share JobID (plus ListID, BatchID and SubscriberID), which is what lets you join them.
Watch out for machine opens
Mailbox privacy protections now generate a large share of automated opens. Use the IsUnique flag and any bot-detection metrics available in your account to temper open rates, and make unique click rate your headline KPI. Treat opens as a secondary signal.
Step 1: create the summary table
Start with a Data Extension called DE_Reporting_Daily with these columns: ReportDate (Date), JobID (Number), EmailName (Text), Sent, Delivered, UniqueOpens, UniqueClicks, Bounces and Unsubs (Number). Make ReportDate and JobID a composite primary key so the Update action never creates duplicates.
Step 2: write the incremental SQL
Rather than rebuilding the whole history on every run, process only the last three days. Late opens and clicks get picked up without making the query heavier.
SELECT
CAST(GETDATE() AS DATE) AS ReportDate,
j.JobID,
j.EmailName,
COUNT(DISTINCT s.SubscriberID) AS Sent,
COUNT(DISTINCT s.SubscriberID) - COUNT(DISTINCT b.SubscriberID) AS Delivered,
COUNT(DISTINCT o.SubscriberID) AS UniqueOpens,
COUNT(DISTINCT c.SubscriberID) AS UniqueClicks,
COUNT(DISTINCT b.SubscriberID) AS Bounces,
COUNT(DISTINCT u.SubscriberID) AS Unsubs
FROM _Job j
INNER JOIN _Sent s ON s.JobID = j.JobID
LEFT JOIN _Open o ON o.JobID = s.JobID AND o.SubscriberID = s.SubscriberID AND o.IsUnique = 1
LEFT JOIN _Click c ON c.JobID = s.JobID AND c.SubscriberID = s.SubscriberID AND c.IsUnique = 1
LEFT JOIN _Bounce b ON b.JobID = s.JobID AND b.SubscriberID = s.SubscriberID
LEFT JOIN _Unsubscribe u ON u.JobID = s.JobID AND u.SubscriberID = s.SubscriberID
WHERE j.DeliveredTime >= DATEADD(DAY, -3, GETDATE())
GROUP BY j.JobID, j.EmailName
Set the Query Activity to Update into DE_Reporting_Daily. Recent jobs are rewritten each run while older history stays frozen.
Step 3: calculate rates at read time
Store raw counts and compute ratios when you read the data. You avoid compounding rounding errors, and you can roll up by week or campaign without distorting averages.
SELECT
DATEPART(ISO_WEEK, ReportDate) AS WeekNumber,
SUM(Sent) AS Sends,
CAST(SUM(UniqueClicks) * 100.0 / NULLIF(SUM(Delivered), 0) AS DECIMAL(5,2)) AS UniqueClickRate
FROM DE_Reporting_Daily
GROUP BY DATEPART(ISO_WEEK, ReportDate)
The key insight: an average of rates is never the rate of the totals. Always sum numerators and denominators first, then divide.
Step 4: automate it in Automation Studio
Schedule an automation to run nightly, ideally after your big send windows, with three activities in sequence: the summary Query Activity, a second query for weekly aggregates, and, if you feed an external tool over SFTP, a Data Extract plus File Transfer. Add an error notification to your CRM team. A query that fails silently is the surest way to show stale numbers in a steering meeting.
Performance guardrails
Queries on data views have a maximum run time. Keep the date window tight, filter on EventDate where you can and avoid SELECT *. If a query gets too heavy, split it by event type into intermediate Data Extensions and combine them in a final step.
Step 5: connect the dashboard
You have three realistic options depending on your data maturity:
- a daily SFTP extract consumed by Looker Studio, Power BI or Tableau;
- a sync to your warehouse (Snowflake, BigQuery) through a connector or the REST API;
- an internal CloudPage that reads the Data Extension with AMPscript or SSJS for lightweight tracking with no third-party tool.
Whichever you pick, write down the definition of every metric next to the dashboard. Arguments about the "real" click rate vanish once everyone reads the same formula.
Common mistakes
Duplicates from joining on JobID alone without SubscriberID, rates calculated on sends instead of delivered messages, time zones forgotten when grouping by day, and Data Extensions without a primary key that balloon with every run. Always validate your query against a known send and compare it with the native report before going live.
Key takeaways
Materialize your metrics. Store daily counts in a dedicated Data Extension to get past native retention and keep your history.
Go incremental. Rewrite only the last few days using a composite primary key and the Update action.
Calculate rates at read time. Sum numerators and denominators before dividing to avoid misleading averages.
Lead with unique clicks. Machine opens inflate open rates, so keep them as a secondary signal.
Automate and monitor. Schedule the chain in Automation Studio with error alerts so stale numbers never reach a meeting.
Want a reliable SFMC dashboard without burning your weekends? Get in touch with CGC-Agency for a reporting audit.
