Reporting Automation Vol 2

Summary

In this article, we dive deeper into aggregation to start analyzing marketing campaign performance in an automated fashion. Using a subscription product as the example, we’ll walk through a cohort-based table transformation using SQL and finish with two must-know marketing KPIs: CAC and ROI.

Question: Is our campaign working?

In the previous article, we discussed how we can pull reporting from different data sources. Now, the data we pulled landed in the warehouse like this: one line per event or user — like signup, purchase, etc. — each with a timestamp and other columns. It’s a massive table with so many rows and columns, and we need to transform it to make it usable.

To achieve this, we typically turn the raw table into a daily summary table. For every campaign or ad on each date, we have a single row. We add/keep breakdown columns like device and country, then sum the metrics that matter, like signups or paid subscriptions. Here’s the same screenshot from the previous article:

Now you might ask - how do I do that?

Let’s look at this example.

Paid Subscription Example

A SaaS company offers a free signup and a paid subscription for their online product.

We care about two events — or “conversions”:

• Sign Up – the date a free account was created (acquisition event)
• Subscription – the date that same user starts paying (monetization event)

For marketing attribution, we want to cohort (aggregate) on the signup date, then later pull in any subscription revenue the cohort generates. That keeps credit with the campaign that actually brought the user in — rather than whichever ad happened to be live when they clicked “Upgrade.”

Here’s a sample SQL query we can use to achieve this:

  -- 1: Capture the last 30 days of new users + their UTM fields
WITH user_data AS (
  SELECT
    u.id AS user_id,
    DATE_TRUNC('day', u.created_time)::date AS signup_date,
    um.country, um.city, // user's GEO data
    um.device,
    um.utm_source, um.utm_medium, um.utm_campaign // UTM fields = Attribution
  FROM core.users u
  LEFT JOIN events.user_metadata um
    ON um.user_id = u.id
  WHERE u.created_time >= CURRENT_DATE - INTERVAL '30 days'
),

-- 2: Grab any paid subscriptions for those users
subscription_data AS (
  SELECT
    s.user_id,
    s.product_name,
    s.price,
    s.est_ltv // estimated LTV for the subscription
  FROM billing.subscriptions s
  WHERE s.user_id IN (SELECT user_id FROM user_data)
)

-- 3: Roll everything up by cohort & campaign fields
SELECT
  ud.signup_date,
  ud.country, ud.city,
  ud.device,
  ud.utm_source, ud.utm_medium, ud.utm_campaign,
  sd.product_name,
  COUNT(DISTINCT ud.user_id) AS user_signups,
  COUNT(DISTINCT sd.user_id) AS subscriptions,
  SUM(sd.price) AS revenue,
  SUM(sd.est_ltv) AS ltv
FROM user_data ud
LEFT JOIN subscription_data sd ON sd.user_id = ud.user_id
GROUP BY 1,2,3,4,5,6,7,8
ORDER BY 1;

What This Gives You

• One row per signup date (cohort) × campaign attribution through UTM
• Sign-up count, subscriber count, revenue, and LTV
• The final table is ready for time-series charts or cost joins in your BI tool

Dropping in Spend and Calculating KPIs

As soon as this table is materialized, attach your daily ad-spend table on the same keys (date + campaign). This allows you to calculate these two marketing KPIs immediately:

KPI	Formula	What it tells you
CAC (Customer Acquisition Cost)	Spend / user_signups	Cost to acquire one free user
ROAS (Return on Ad Spend)	Revenue / Spend	Dollars back per dollar spent

Tip: If you prefer payback-period, just use CAC and LTV from the same row.

Conclusion

You now have a single, trusted aggregation layer that marries marketing spend, sign-ups, and subscription revenue—cohorted correctly and ready for BI. In Volume 3, we’ll dive deeper into the actual analyses. Stay tuned!