Data & Analytics

"Without data, you're just another person with an opinion." — W. Edwards Deming

Last chapter — and the one everything else has been feeding into. Clean tracking, automations, the whole stack exists so you can turn raw numbers into decisions. I'm skipping the "what is a KPI" stuff; you know vanity metrics are vanity metrics. What you want is the hands-on part: where the data lives, the SQL to pull it, and the dashboard to show it.

One opinion up front that shapes this whole chapter: I do NOT use GA4 — it's slow, sampled, and a pain to trust. My stack is self-hosted Plausible for web analytics, first-party data in my own database (Supabase / Postgres), and the ad platforms' own numbers — joined in BigQuery when I want the full picture. Everything below is built on that, not GA4.

My actual analytics stack

Three sources, one place to join them, one place to look at them:

1. Web analytics — self-hosted Plausible. Lightweight, cookieless, no sampling, and the raw data is MINE in its own database. One script tag, no consent-banner circus.
2. First-party / revenue — Supabase (Postgres). Signups, subscriptions, Stripe events, whatever the product actually does. The source of truth for money.
3. Ad platforms — Google Ads, Meta. Spend and clicks live there; I pull them via API/export rather than trusting their conversion counts.

Open-source web analytics you can run on a cheap VPS for the price of the box — no GA4, no cookie banner, no 14-month data cap. I'm a cheapskate, and self-hosting Plausible is the cheapskate's dream: unlimited sites, your data, ~$5/mo. Use it to see real-time and historical visitors with zero sampling, track goals and custom events (signups, button clicks) via its Events API, and query the underlying ClickHouse/Postgres directly when the dashboard isn't enough.

BigQuery for the join you actually care about

BigQuery

Google's warehouse, and where the magic happens: pull spend from the ad platforms and revenue from your own database into one place, then write ONE query that tells you true ROAS — actual closed revenue per dollar spent, not the platform's flattering self-report. You don't need GA4's BigQuery export — load a CSV, schedule a Postgres → BigQuery sync, or just dump tables in. Once the data's there, SQL answers questions no dashboard will. Here's the kind of query that earns its keep — true ROAS by campaign, joining ad spend to real first-party revenue:

SELECT
  s.campaign,
  ROUND(SUM(s.cost), 2)            AS spend,
  COUNT(DISTINCT r.customer_id)    AS customers,
  ROUND(SUM(r.revenue), 2)         AS revenue,
  ROUND(SUM(r.revenue) / NULLIF(SUM(s.cost), 0), 2) AS roas
FROM `proj.ads.spend_daily` s
LEFT JOIN `proj.app.revenue` r
  ON r.utm_campaign = s.campaign
 WHERE s.date BETWEEN '2026-06-01' AND '2026-06-30'
GROUP BY s.campaign
ORDER BY roas DESC;

That single query is the thing every platform dashboard refuses to show you, because it makes the platform look worse. The NULLIF is how you avoid a divide-by-zero blowing up the result on a campaign with no spend yet. The same first-party events can answer a proper signup cohort / retention slice — the kind of question GA4's UI simply won't let you ask.

Pulling first-party data from Supabase

Your database is the only source that knows who actually paid — don't make it a black box. Three ways I get data out, in order of how often I reach for them. 1. Run SQL right in the dashboard (SQL Editor) for a one-off answer — this month's paid signups by source, done in 10 seconds:

select
  utm_source,
  count(*)                                  as signups,
  count(*) filter (where plan != 'free')    as paid,
  round(
    count(*) filter (where plan != 'free')::numeric
    / nullif(count(*), 0) * 100, 1)         as paid_pct
from users
where created_at >= date_trunc('month', now())
group by utm_source
order by paid desc;

2. Hit the auto-generated REST API (PostgREST) from a script or Looker connector when you want it live, not a one-off:

curl "$SUPABASE_URL/rest/v1/users?select=utm_source,plan&created_at=gte.2026-06-01" \
  -H "apikey: $SUPABASE_KEY" \
  -H "Authorization: Bearer $SUPABASE_KEY"

3. Sync the table into BigQuery (scheduled job or connector) when you want to JOIN it against ad spend for the true-ROAS query above. That's the whole reason the warehouse exists.

Build the dashboard in Looker Studio

Looker Studio

Free, connects straight to BigQuery, Sheets, Plausible, and the ad platforms. Build the report ONCE, it refreshes itself, you share a link — the hours marketers waste rebuilding the same monthly PDF by hand is genuinely tragic. How I stand one up, fast:

1. Add a data source: Resource → Manage data sources → BigQuery → point it at the true-ROAS query ("Custom Query", paste the SQL, so the warehouse does the math, not Looker).
2. Drop a Scorecard for the one number that matters — ROAS or paid signups — top-left, big.
3. Add a time-series below it, plus a date-range control + comparison (vs previous period) so every number has context baked in.
4. Add a table by campaign, sorted by spend, with conditional-format colour on ROAS (green/red) so the eye finds winners and losers instantly.
5. Share → "Anyone with the link can view". Never export a PDF again.

Dashboard rules I live by: most important number top-left; one question per chart; always a comparison (a number with no "vs what" is useless); and ruthlessly cut — a clean 5-chart dashboard beats a cluttered 30-chart one. Build it for the reader: a client wants "are we making money and is it growing?" answered first, big and clear. Detail lives further down for whoever digs.

Don't lie with charts (especially to yourself)

Quick soapbox. It's stupidly easy to mislead with a chart, and even easier to do it by accident and fool yourself. The classics, none of which you should do:

• Truncated y-axis — starting at 80 instead of 0 to make a 2% wobble look like a cliff.
• Cherry-picked date range — the one good week, trend conveniently cropped out.
• Percentages with no base — "300% growth!" from 1 sale to 4. Cool. Four sales.
• Correlation dressed as causation — "we posted more AND sales rose," ignoring the discount you were running.

Present honestly: axes at zero unless you've a real reason (and say so); enough history that the trend is the story; always a comparison vs target or last period; and lead with the plain-English takeaway — "leads up 20%, cost flat, let's scale this." The chart supports the sentence; it isn't the point.

AI as your analyst

This is where it comes together. The job that used to need a dedicated data person, you now do by pasting a query result into an AI and asking it to find the story — a pattern-spotter on call 24/7, fast and cheap. Use it to:

• "Here's my last 90 days of campaign data as CSV — what's working, what's wasting money, what should I test next?"
• Write or fix the BigQuery/Postgres SQL to pull exactly the slice you want.
• Spot anomalies ("why did signups dip last Tuesday?") and propose hypotheses to check.
• Turn a pile of rows into a plain-English summary you drop straight into a client update.

The winning combo is human + AI, not human OR AI. AI does the crunching and drafting; you bring the judgement, business context, and sanity check. That's the whole philosophy of this course in one sentence.

Export a CSV, drop it in your IDE, and just ask

My favourite no-dashboard analysis trick: export the data as a CSV, drag the file into my IDE, and ask Claude questions about it in plain English. Export from anywhere — Google Ads, Supabase, Plausible, a sheet — drop it into the project, then just ask:

• Which campaigns have a CPA over €100 with falling conversions?
• Group these leads by source and show me the conversion rate for each.
• Find the rows where spend went up but revenue didn't.

Claude reads the file, writes the query or script, and hands you the answer. Ask it to clean, reshape or de-dupe the data and it'll edit the CSV directly.

When a one-off question becomes one you ask every week, that's the signal to promote it to a real Looker Studio dashboard or a cron — not before.

And that's a wrap 🎬

That's the course. You made it. We covered a LOT — productivity, tools, hygiene, the technical plumbing, automation, and now data. One thread runs through all of it: the Renaissance Marketer wins on BREADTH and LEVERAGE. You don't need to be the world's best at any one thing — good enough across the whole stack, then tools and AI to multiply yourself, so one person punches like a team. And remember: speed beats perfection. Don't sit on this — pick ONE thing and set it up today. Self-host Plausible. Write one BigQuery join. Build one Looker scorecard. Then another tomorrow. Above all, keep learning — the tools change every few months now. Now go do the work of a whole team. Solo. You've got everything you need. 🚀

Sweet, you've completed this section! 🥳