Every digital publisher runs on the same three systems: a CMS that holds the content, an analytics tool that tracks the traffic, and a CRM that knows who pays. And in my experience, those three systems have never properly met.
The result is a specific kind of frustration. The marketing team pulls numbers from the analytics tool. The subscription team pulls from the CRM. The editorial team asks "how did our politics section do this month?" and gets three different answers, none of which are wrong — they're just counting different things.
This post is part 1 of a 3-part series where I build a complete analytics platform for The Meridian Post, a fictional digital newspaper, and use it to walk through how I'd solve this problem for a real newsroom. Everything is live and working: a full year of realistic data, dbt models, a semantic layer, and a dashboard you can click through. Part 1 covers the business context, why this data is genuinely hard to work with, and the architecture I landed on. Parts 2 and 3 go deep on the dbt modeling and the semantic layer + dashboard.
The business: what a newsroom actually needs to know
Strip away the dashboards and the stakeholder meetings, and a digital newsroom with a paywall needs answers to a short list of questions:
- Is traffic growing? Not just pageviews: are we reaching more actual people, on which sections, from which channels?
- What content earns its keep? Which articles drive engaged reading, and which just drive bounces?
- Is the paywall working? How many people hit it, how many click, and how many eventually subscribe?
- Where do subscribers come from? Not the last click, but the realistic multi-week journey from first read to paying.
- Are we growing the paying base or churning it? New subscribers minus cancellations, and the churn hazard behind them.
Those questions sound simple. Answering them is not, because the data lives in three places that don't talk to each other.
The pain points: why publisher analytics is genuinely hard
I want to be concrete here, so let me put real numbers on it. The demo year behind this series (April 2025 → March 2026) contains:
- 203,758 events across 41,537 sessions in the GA4 data, of which 70,149 are pageviews, all of them article pages
- 1,000 articles from the CMS, written by 12 authors across 8 sections
- 535 registered accounts in the CRM, of which 263 have subscribed at some point; 210 are active at the end of the window, with 78 new subscriptions and 53 churns during the year
Now, the problems:
1. The reader only fully exists in one of the three systems. GA4 sees an anonymous visitor, a device rather than a person; the only handle it offers is a cookie-level
client_key. The CMS holds editorial metadata (articles, authors, sections, publish times) and nothing about readers at all. The only system that knows a reader as a person is the CRM, where an account is created at sign-up, and even then an account isn't necessarily a payer: 272 of the demo's 535 registered accounts never subscribed. The first job is stitching anonymous behavior to identity, and knowing exactly where that join becomes possible. Nobody hands it to you done.2. The GA4 UI answers marketing questions, not business questions. The native interface is good for answering "how did organic traffic trend" and useless for "what was the last article a subscriber read before they converted, and which channel brought them to that session?" That second question requires the raw event data, and the only way to get it is the BigQuery export.
3. The BigQuery export is raw for a reason. Anyone who's worked with the GA4 export schema knows it: daily-sharded
events_YYYYMMDD tables, nested RECORD columns, event parameters stored as key/value JSON with typed value fields. It's a faithful dump of what the collection layer saw — deliberately unmodeled. traffic_source is only populated on session-start events. Sessions are reconstructed from a ga_session_id parameter, not a column. You can write one-off queries against it, but you can't build a business on it.4. Metric drift eats self-serve analytics alive. What's a "session"? GA4's definition involves 30 minutes of inactivity and a fresh campaign landing. What's an "active subscriber"? The CRM says billing-active; the analytics tool says showed-up-this-month. If every analyst resolves these questions themselves, in their own SQL and their own spreadsheet, you get the three-answers problem from the opening paragraph, permanently. That's the same failure I unpacked in how to run a self serve analytics platform efficiently.
5. Subscription journeys don't fit in a session. In the demo data, a typical subscriber journey spans multiple sessions over a median of ~69 days, seeing between 2 and 12 paywall impressions before converting. The paywall funnel (28,190 impressions, 1,327 clicks at 4.7% CTR, 78 subscriptions from 22,721 exposed sessions) only makes sense if the data model joins sessions across days and attributes conversions across that whole window. No out-of-the-box tool does this correctly.
None of these problems is exotic. They're the standard condition of publisher data. Which is why the fix has to be architectural, not a better chart.
The architecture: one pipeline, four layers
Here's the system I built for the series:
The four-layer pipeline, end to end. Everything below is open and explorable: the repo and the live dashboard.
Layer 1 — Raw: the GA4 export schema, faithfully. The foundation is the exact GA4 BigQuery export schema: same daily sharding, same nested records, same key/value event params, same quirk where
traffic_source only exists on session-start events. It runs on PostgreSQL (hosted on Neon). Alongside it, the CMS and CRM exports land as plain tables. Why so literal? Because the raw layer is a contract. If your raw layer matches the real GA4 export, everything you build on top transfers directly to production data. This is the part most analytics portfolios fake, and it's the part that makes the demo real.Layer 2 — Transformation: dbt. On top of the raw layer runs dbt, using the Velir dbt-ga4 package (v6.2.0) ported to Postgres. That gives us the same sessionization, attribution and page logic the community package ships for BigQuery, plus newsroom-specific marts on top: article performance joined CMS×GA4, traffic-source channel groupings, a paywall funnel, and a subscription last-touch model that answers "what did this subscriber read before they paid?" It runs daily, incrementally, with a 3-day reprocessing window that mirrors GA4's own late-arriving-data behavior. Part 2 covers this layer in detail.
Layer 3 — Semantic layer: Cube.js. Every metric gets exactly one definition, written down before it gets encoded: what a session is, what engaged time means, how "Active Subscribers" handles churn. That catalog (21 metrics, 22 dimensions) becomes 14 Cube.js cubes served over the dbt marts. This is the layer that kills metric drift: the dashboard, a data scientist's notebook, and an analyst's ad-hoc query all hit the same definitions. Part 3 covers this layer.
Layer 4 — Consumption: a Next.js dashboard. Five pages (Overview, Audience, Paywall, Content, Subscriptions) served by Cube's API on Cloud Run, rendered with Tremor components on Vercel. It's deliberately boring technology; the interesting decisions all happened upstream.
The stack, end to end: PostgreSQL (Neon) → dbt → Cube.js → Next.js, orchestrated by GitHub Actions. No exotic tools. Each layer is the boring, industry-standard choice for its job, which is the point. The interesting part is the seams between the layers, and that's where this series lives.
Why this shape and not something else
A few choices deserve justification, because the obvious alternatives fail in specific ways:
Why not just use the GA4 UI? Because it can't join your CMS or CRM, can't compute cross-session subscription attribution, and won't let other tools consume consistent metrics. The export exists precisely for questions the UI can't answer.
Why Postgres instead of BigQuery? Two reasons. First, the demo runs on Neon: serverless, cheap, real SQL, no warehouse billing surprises. Second, and more honestly: it proves the dbt-ga4 logic isn't BigQuery-magic. Porting it to Postgres means the same patterns work wherever your client's data actually lives.
Why a semantic layer instead of querying marts directly? Because layer 2 answers questions correctly, and layer 3 answers them consistently. The marts define facts; the semantic layer defines business meaning, including the messy ones like churn-aware subscriber counts and "pageviews that can't be attributed to a session" (473 of them in the demo data, 0.7%, and yes, that's the kind of edge case the catalog documents explicitly rather than hides). For the business case, I've written this up separately: semantic layer: what are the benefits for the business?.
Why a custom dashboard instead of a BI tool? For the demo, a purpose-built dashboard shows the full stack in one place. In a client engagement, this layer would just as often be Looker Studio or Metabase pointed at the same semantic layer. The architecture doesn't change.
Coming up
- Part 2 — From Raw Events to Reliable Marts: Modeling Newsroom Data with dbt. The ingestion pipeline, sessionization, the dbt-ga4 port, incremental strategies, and the modeling decisions (and mistakes) behind the marts.
- Part 3 — One Definition of "Active Subscriber": Semantic Layer + Dashboard. Building the metric catalog, encoding it in Cube.js, and wiring up the Next.js dashboard, including what the semantic layer buys you that raw SQL doesn't.
Meanwhile, the whole thing is open and explorable:
- 📊 Live dashboard: five pages over the full demo year
- 🐙 The Meridian Post demo repo: schema, dbt project, Cube models, metric catalog
- 👨💻 And if your team has its own version of the three-systems problem, that's exactly the work I do. Get in touch.