Reporting Automation Vol 3

Summary

In Vol 1 and Vol 2, we built the pipeline — APIs pulling spend data, a Python ETL loading it into Snowflake, and a SQL aggregation layer that cohorts users by signup date and attaches subscription revenue. Now the table exists. This article covers what to actually do with it: which analyses to run, how to write them in SQL, and what decisions each one drives.

Where We Left Off

At the end of Vol 2, you have a table in your data warehouse that looks something like this:

signup_date	utm_source	utm_campaign	device	user_signups	subscriptions	revenue	spend
2025-03-01	google	brand_search	mobile	142	18	$540	$1,200
2025-03-01	meta	retargeting_v2	desktop	67	14	$420	$610
...	...	...	...	...	...	...	...

One row per cohort (signup date) × attribution slice. Spend, signups, and revenue are all on the same row, ready to analyze. Let's walk through the four analyses that give you the most signal.

Analysis 1: Spend and Conversion Trend Over Time

The first thing to check is the trend. Are signups and subscriptions growing week over week? Is spend staying flat while performance improves, or are you spending more to get less?

SELECT
  DATE_TRUNC('week', signup_date)    AS week,
  SUM(spend)                         AS total_spend,
  SUM(user_signups)                  AS total_signups,
  SUM(subscriptions)                 AS total_subscriptions,
  ROUND(SUM(spend) / NULLIF(SUM(user_signups), 0), 2)       AS cac,
  ROUND(SUM(revenue) / NULLIF(SUM(spend), 0), 2)            AS roas
FROM campaign_performance
WHERE signup_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Plot cac and roas as line charts in your BI tool. If CAC is creeping up over time, you're either saturating your audience or your creative is wearing out. If ROAS is dropping but signups are holding steady, the subscription conversion rate is the culprit — a product problem, not a marketing one.

Analysis 2: Channel Mix — Where Is the Spend Going?

Once you have the trend, break it down by channel. It's common for one channel to be subsidizing a poor-performing one when you only look at totals.

SELECT
  utm_source,
  SUM(spend)                                                  AS total_spend,
  ROUND(100.0 * SUM(spend) / SUM(SUM(spend)) OVER (), 1)     AS spend_pct,
  SUM(user_signups)                                           AS total_signups,
  SUM(subscriptions)                                          AS total_subscriptions,
  ROUND(SUM(spend) / NULLIF(SUM(user_signups), 0), 2)        AS cac,
  ROUND(SUM(revenue) / NULLIF(SUM(spend), 0), 2)             AS roas
FROM campaign_performance
WHERE signup_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY total_spend DESC;

The spend_pct column — using a window function over the total — tells you the channel mix at a glance. A channel that takes 40% of budget but produces 15% of subscriptions is a clear reallocation candidate. Compare CAC across channels, not just volume.

Analysis 3: Funnel Conversion Rate by Campaign

CAC and ROAS are summary numbers. The funnel rate — what percentage of signups actually convert to paid — tells you where in the funnel each campaign is winning or losing.

SELECT
  utm_source,
  utm_campaign,
  SUM(user_signups)                                                        AS signups,
  SUM(subscriptions)                                                       AS subscriptions,
  ROUND(100.0 * SUM(subscriptions) / NULLIF(SUM(user_signups), 0), 1)     AS conversion_rate_pct,
  ROUND(SUM(spend) / NULLIF(SUM(user_signups), 0), 2)                     AS cac,
  ROUND(SUM(revenue) / NULLIF(SUM(spend), 0), 2)                          AS roas
FROM campaign_performance
WHERE signup_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1, 2
HAVING SUM(user_signups) >= 20   -- filter out low-volume noise
ORDER BY conversion_rate_pct DESC;

The HAVING clause is important — campaigns with 3 signups and 1 conversion look like they have a 33% conversion rate, but that's statistical noise. Set a minimum volume threshold before drawing conclusions.

A campaign with a high signup count but low conversion rate tells you it's attracting the wrong users — either the targeting is too broad, or the ad creative is setting the wrong expectations. A high conversion rate but low volume is a scale opportunity.

Analysis 4: Week-Over-Week Comparison

Trends are easier to read when you anchor them to a specific comparison period. This query computes the current week vs. the prior week for each channel, so you can spot sudden shifts immediately.

WITH weekly AS (
  SELECT
    utm_source,
    DATE_TRUNC('week', signup_date)                 AS week,
    SUM(spend)                                      AS spend,
    SUM(user_signups)                               AS signups,
    SUM(subscriptions)                              AS subscriptions
  FROM campaign_performance
  WHERE signup_date >= CURRENT_DATE - INTERVAL '14 days'
  GROUP BY 1, 2
),

pivoted AS (
  SELECT
    utm_source,
    MAX(CASE WHEN week = DATE_TRUNC('week', CURRENT_DATE - INTERVAL '7 days')
             THEN signups END)                      AS signups_prior_week,
    MAX(CASE WHEN week = DATE_TRUNC('week', CURRENT_DATE)
             THEN signups END)                      AS signups_this_week,
    MAX(CASE WHEN week = DATE_TRUNC('week', CURRENT_DATE - INTERVAL '7 days')
             THEN spend END)                        AS spend_prior_week,
    MAX(CASE WHEN week = DATE_TRUNC('week', CURRENT_DATE)
             THEN spend END)                        AS spend_this_week
  FROM weekly
  GROUP BY 1
)

SELECT
  utm_source,
  signups_prior_week,
  signups_this_week,
  ROUND(100.0 * (signups_this_week - signups_prior_week)
        / NULLIF(signups_prior_week, 0), 1)         AS signups_wow_pct,
  spend_prior_week,
  spend_this_week,
  ROUND(100.0 * (spend_this_week - spend_prior_week)
        / NULLIF(spend_prior_week, 0), 1)           AS spend_wow_pct
FROM pivoted
ORDER BY signups_this_week DESC;

This is the table you want in your Monday morning report. If signups are down 20% week-over-week on Meta but spend is flat, something changed — creative fatigue, an audience overlap, or a platform issue. Catching it at the weekly cadence gives you time to react before the monthly numbers look bad.

Putting It in a BI Tool

All four of these queries can be turned into views in your warehouse and connected directly to a BI tool. If you're using Sigma, each view maps cleanly to a chart or pivot table — no additional transformation needed at the BI layer. The trend analysis becomes a line chart, channel mix becomes a stacked bar, and the WoW comparison becomes a table with conditional formatting on the percentage columns.

The goal is to have these views refresh automatically overnight (via your Airflow DAG from Vol 1), so when you open the dashboard in the morning the data is already there.

Conclusion

The four analyses above — trend, channel mix, funnel conversion, and week-over-week — cover the majority of questions a marketing team asks on a daily basis. They're built entirely on the aggregation table from Vol 2, which means no additional data pulls are needed. The pipeline you built does the heavy lifting; these queries just surface the signal.

In Vol 4, we'll look at how to set up automated alerts — so instead of checking a dashboard every morning, the dashboard tells you when something is wrong.