Warehousing and Reporting

12.1 Two warehouses, two questions

A lead-generation business asks two kinds of question about its measurement data, and each kind wants a different store. The first question is behavioral and pre-conversion: which channel, which page, which service or county page produced the session that produced the lead. GA4 was built to answer that class of question, and the property under study, 534525683, holds the raw material for it: page_view with page_type and a slug, the client and server lead events, the acquisition dimensions. The second question is economic and post-conversion: of the leads that arrived, how many the office booked, at what value, per service, against what ad spend. GA4 cannot answer that second class, because the outcome it depends on happens weeks later, offline, on a phone call the analytics tag never sees.

This chapter treats the warehouse and the reporting surface as the resolution of that split. The warehouse layer has two destinations, and I built to both. The GA4 export lands raw, unsampled event rows in BigQuery dataset analytics_534525683, which preserves the behavioral history past the property's retention horizon. The operational lead store is the site's own PostgreSQL database, the contact_submissions table extended with outcome columns, which holds the economic truth that GA4 structurally lacks. The reporting surface then splits along the same seam: BigQuery serves the analyst who reconstructs multi-year seasonality, and a native dashboard at /admin/insights, driven entirely by Postgres, serves the office that needs to see close rate and revenue per service this week. Chapter 11, "The Offline-Revenue Loop," describes how outcomes flow back from that Postgres store toward Google's ad systems. This chapter describes how the same outcomes, and the raw GA4 stream, become reporting a human reads.

12.2 The GA4 to BigQuery daily export

I linked GA4 property 534525683 to BigQuery in Google Cloud project gen-lang-client-0444184725, US multi-region location, daily export frequency, streaming export off. The link populates dataset analytics_534525683, one dataset per property by GA4 convention, with a partitioned table per day named events_YYYYMMDD plus an events_intraday_ table when intraday collection is active. Each row is one event. Each row carries the full nested payload: event_name, event_timestamp, the event_params repeated record, the user_properties repeated record, device, geo, traffic_source, and the pseudonymous user_pseudo_id. The custom dimensions registered in Chapter 8, "Event Taxonomy and Custom Dimensions" (page_type, service_slug, location_slug, cta_location, service_type, and the rest), arrive inside event_params as key plus typed value, so the full taxonomy survives the export at event grain.

The mechanics of link creation carried a governance lesson that Chapter 15, "Access and Permissions Model," treats in full and that I note here because it shaped the workflow. The Admin API call bigQueryLinks.create returned HTTP 403, "The caller does not have permission," for the automation service account even after I granted it GA4 Administrator and the project role roles/bigquery.admin. The link-creation flow makes GA grant its own managed export service account project-level IAM, and that grant requires project-IAM-admin authority the automation account did not hold and should not hold for a one-time convenience. The property owner created the link through the GA4 UI. The Admin API then confirmed the resulting state read cleanly: dailyExportEnabled: true, streaming disabled. Automation read the configuration it could not write.

12.3 The cost analysis, done as arithmetic rather than assumption

Practitioners hesitate at BigQuery because the pricing model bills on bytes scanned, and "unlimited raw event export" sounds like a bill waiting to arrive. For this property the fear is misplaced, and the way to retire it is arithmetic against the free tier rather than a reassuring adjective.

BigQuery's free tier grants 10 GB of active storage and 1 TB of query scanning per month at no charge. The property produces roughly 5 MB of event data per month. Put those two numbers beside each other. At 5 MB per month, monthly storage sits three orders of magnitude under the 10 GB storage grant, and the export consumes none of the query allowance because export writes rows, it does not scan them. Storage stays at zero cost. Queries stay at zero cost, because no realistic analytical workload against a table that accretes 5 MB a month approaches 1 TB scanned in a billing period; you would need to scan the entire dataset on the order of two hundred thousand times in a month to exhaust the grant. The one line item that does bill is streaming export, and daily export does not stream. It writes each day's partition in a batch. I left streaming off deliberately, which is why the confirmed configuration shows dailyExportEnabled: true with streaming disabled: the daily batch is the free path, and this business has no realtime warehouse need that would justify the streaming charge.

The storage math also sets the honest horizon. Ten gigabytes of grant divided by five megabytes a month is two thousand months of accumulation before storage alone breaches the free tier, and long before that arithmetic limit BigQuery's own rule reclassifies any partition untouched for ninety days as long-term storage at half the active rate. The practical reading: for a business at this volume, the GA4 to BigQuery export is free, and will remain free for as long as anyone reading this dissertation will operate the property. The cost objection does not survive contact with the numbers. What remains is the analytical case, which is where the export earns its place.

12.4 Why the raw export beats the fourteen-month retention cap

Chapter 5, "Audit Findings," recorded that event data retention sat at TWO_MONTHS, the GA4 floor, and Chapter 6, "The Server-Side Measurement Protocol," recorded that Phase 0 raised it to FOURTEEN_MONTHS with a single dataRetentionSettings PATCH. That change matters, and it is also not enough, and the gap between "fourteen months" and "not enough" is the reason the BigQuery export is not redundant with a well-configured GA4 property.

The nuance sits in what the retention setting governs. GA4 standard aggregate reports retain their data effectively indefinitely; the retention cap bites only Explorations and funnels that read event-scoped or user-scoped custom dimensions. That is precisely the analysis this business needs. The demand is seasonal in a way that is specific and repeating: termite swarms drive spring inquiries, rodents drive inquiries in the cooler months, and the whole point of measuring by service_slug and location_slug is to see those curves and staff and spend against them. A fourteen-month window shows one spring and one rodent season. It cannot show this spring against last spring, because last spring's event-scoped detail has aged out by the time this spring arrives. Year-over-year seasonal comparison, the single most decision-useful view for a seasonal local operator, is exactly the view a fourteen-month cap denies at the granularity that makes it useful.

The BigQuery export removes the horizon. Once daily rows land in analytics_534525683, they persist under the practitioner's storage policy, not GA4's retention rule, and the free-tier math above says that policy can be "keep everything" for two thousand months. Spring 2026 rows sit beside spring 2027 rows sit beside spring 2028 rows, at full event grain, with service_slug and location_slug intact, queryable with a WHERE clause on the partition date. The export converts a fourteen-month rolling window into an accumulating multi-year archive. For a business whose core analytical question is "is this termite season bigger than last termite season, and in which counties," that conversion is the difference between a report that can answer the question and one that structurally cannot.

Two further properties of the raw export reinforce the case, and both connect to Chapter 13, "Data Quality and Governance." First, the export is unsampled. GA4's in-product Explorations apply sampling above quota thresholds and apply thresholding that suppresses rows to protect against re-identification; Chapter 5 noted that Google Signals, had it been left on, would have triggered exactly that thresholding and hidden rows at this low volume. BigQuery rows arrive before either mechanism runs, so a count in BigQuery is a true count. Second, the export is reprocessable. A taxonomy error caught later, a slug that was miscategorized, a value map that changed, can be recomputed against the historical rows in place, because the raw parameters are all present. The GA4 UI freezes its aggregations; the warehouse keeps the source. That reprocessability is the warehouse's contribution to governance, and Chapter 13 treats it as the backstop that makes taxonomy changes recoverable rather than destructive.

A minimal seasonal query against the dataset shows the shape of what the export enables:

SELECT
  FORMAT_DATE('%Y-%m', PARSE_DATE('%Y%m%d', event_date)) AS month,
  (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'service_slug') AS service_slug,
  COUNT(*) AS leads
FROM `gen-lang-client-0444184725.analytics_534525683.events_*`
WHERE event_name = 'generate_lead'
  AND _TABLE_SUFFIX BETWEEN '20260101' AND '20281231'
GROUP BY month, service_slug
ORDER BY month, leads DESC;

The events_* wildcard with a _TABLE_SUFFIX range spans as many years as the archive holds. No GA4 Exploration can express that range once the underlying detail has aged past fourteen months. The warehouse can.

12.5 The native Insights dashboard at /admin/insights

The BigQuery export answers the behavioral, multi-year question. It does not answer the economic one, because the GA4 stream never carried a booked-job value in the first place. That answer lives in Postgres, and I built its reporting surface as a native dashboard at /admin/insights, rendered by the Next.js application from its own database with no external business-intelligence tool in the path.

The dashboard reads the contact_submissions table, which Chapter 11 describes gaining the outcome columns stage, job_value, and closed_at through a Drizzle schema change plus a psql ALTER TABLE migration against the production database, alongside the auth-guarded lead-status editor that lets the office set stage (new, contacted, quoted, won, lost) and a booked job value. Those columns are the raw material the dashboard aggregates. The surface has four parts.

The KPI tiles report total leads, this month versus last month, close rate, and booked revenue. Close rate is won leads over closed leads; booked revenue is the sum of job_value on won rows. The month-over-month tile reads the created_at column and contrasts the current calendar month against the prior one, which is the cadence a small operator actually reviews.

The pipeline view groups open leads by stage, so the office sees how many sit at new, contacted, and quoted, and where leads stall between stages. That is an operational view GA4 has no concept of, because stage is a fact the office writes after the analytics event fired.

The magnitude views break leads down by service, by source, and by month, each rendered as single-series bars. I held the visual grammar deliberately plain, consistent with the site's monochrome design system: single-series magnitude bars in one ink tone, with green and red reserved for won and lost so that outcome reads at a glance and nothing else competes for the color budget. The design refuses the multi-series, multi-hue chart that invites false comparison across incommensurable categories.

The empty states are honest. Where no outcome data exists yet, a tile reads "mark leads won/lost to populate" rather than rendering a zero that a reader could misread as a measured result. Chapter 13 argues that an honest empty state is a data-quality control, not a cosmetic nicety, because a fabricated zero corrupts trust in every other number on the page. The dashboard applies that principle: it declines to draw a close-rate bar until closed leads exist to compute one.

This surface replaced a Looker Studio task that the audit had queued and that turned out to be blocked. The replacement was not a downgrade. It was the better instrument for the question, for reasons the next two sections set out.

12.6 Why the dashboard shows what GA4 structurally cannot

Close rate and revenue per service are not hard for GA4 to display. They are impossible for it to display, and the distinction is structural rather than a matter of configuration effort.

GA4 observes the lead event. It records that a generate_lead fired, with a service_type parameter and a provisional value from the internal value map that Chapter 6 documents (termite 400, fumigation 500, rodent 180, and so on). What GA4 cannot observe is what happened to that lead after the office picked up the phone. The booking, the quoted price, the win or the loss, the actual job_value, all of it occurs offline, days or weeks later, and enters the system through the admin editor writing to Postgres, not through any tag firing in a browser. GA4 has no row to join that outcome onto. The generate_lead event and the booked job are the same business fact separated by time and channel, and only the Postgres row, keyed by the lead and carrying both the inbound detail and the outcome columns, holds both halves.

Close rate, therefore, requires a numerator GA4 never receives. Revenue per service requires summing a job_value GA4 never receives. The provisional value on the GA4 lead event is an estimate the code assigns at submission time to feed value-based bidding later; it is not the booked amount, and Chapter 6 is explicit that it is provisional and internal and never shown to a customer. A revenue-per-service report built on that estimate would report the estimate, not the revenue. The dashboard reports the revenue, because it reads the column the office wrote.

This is the load-bearing reason the reporting surface splits in two. The offline-revenue loop of Chapter 11 pushes a version of these outcomes back toward Google's ad systems so that bidding can learn from them, and that path has real value for cost-per-booked-job visibility even at this property's volume. It does not make GA4 the place to read close rate. The office-facing operational truth, the number a manager looks at to decide whether termite leads convert better than rodent leads and are therefore worth more ad spend, lives in the database and reads best from a surface that queries the database directly. GA4 is the acquisition instrument. Postgres is the outcome ledger. The dashboard reports the ledger.

12.7 Three reporting architectures, and why the native one won

I weighed three ways to build the outcome-reporting surface, and the choice among them turned on where the join between lead and outcome happens and what accounts a non-technical office must hold to see a number.

A Looker Studio report is the reflexive choice, and it was the original plan. Looker Studio connects to a data source, renders tiles and charts without code, and shares by link. Its cost is a dependency: it needs an external Google account with access provisioned, a live connector to either Postgres or the BigQuery export, and a sharing posture that the office has to maintain. For a two-person office that already signs into the site's own admin, that is one more account, one more login, one more surface to break. The audit's Looker Studio task was blocked in practice, which turned the theoretical dependency into a concrete stoppage.

A BigQuery-backed notebook is the analyst's choice. It offers the most power: arbitrary SQL against the raw analytics_534525683 export, joins across years, statistical work in a Colab or notebook environment. Its cost is that it serves an analyst, not an office. It presumes someone who writes SQL, opens a notebook, and interprets output. The multi-year seasonal query in section 12.4 belongs in exactly this environment. The weekly "how many did we book" question does not, because the person asking it does not write SQL and should not have to.

The native database-driven dashboard is the choice I shipped. It runs inside the Next.js application the office already uses, behind the same requireAdmin() guard that Chapter 11 describes protecting the lead editor, and it queries the production Postgres directly. That last property is the decisive one. The outcome columns and the lead records are the same rows in the same table; the dashboard joins to them with no connector, no export lag, no second copy that can drift. It needs no external account, because it is the application. It renders the moment the office marks a lead won, because it reads the row the editor just wrote. Looker Studio would have joined to a copy; the notebook would have needed an analyst; the native dashboard joins directly to the lead records and serves the office without either. For this property, at this volume, with this operator, the native surface won on the criterion that matters most: the shortest, freshest path from the outcome the office records to the number the office reads.

None of this retires BigQuery. The two surfaces answer different questions and coexist. The warehouse serves multi-year behavioral seasonality to whoever will run SQL against it. The native dashboard serves this-month economic outcomes to the office. Building both was correct precisely because neither could do the other's job.

12.8 The lead-generation KPI framework

The dashboard's tiles are not an arbitrary selection. They instantiate the KPI framework a lead-generation business should run, which differs from the ecommerce framework GA4's defaults assume. There is no purchase, no cart, no online revenue; Chapter 5 recorded that the inert purchase key event could not even be deleted through the API. The metrics that matter are three, and each depends on data the earlier chapters put in place.

Cost-per-booked-job is ad spend divided by won leads. It is the truest efficiency metric a phone-and-form business has, because it prices the outcome the business actually sells rather than the click or the form submission that precedes it. Computing it needs two inputs the property did not previously connect: spend, which arrives once the Google Ads link that Chapter 9, "Identity, Attribution, and Value," treats is in place, and booked jobs, which the stage = won rows now supply. The dashboard holds the denominator today; the loop of Chapter 11 carries it toward the numerator.

Lead-to-job rate, the close rate, is won leads over closed leads. It measures the quality of leads a channel or a service delivers, which cost-per-lead cannot see. Two channels can deliver leads at the same cost while one closes at twice the rate of the other, and only the lead-to-job rate exposes the difference. The dashboard surfaces it directly from the stage column, and the by-source breakdown makes it comparable across acquisition channels once volume permits.

Revenue-per-service is summed job_value grouped by service. It is the input to the value map that feeds bidding and the input to the human decision about where to point ad spend. If termite jobs book at 400 and ant jobs at 100, the business should not pay the same to acquire both, and revenue-per-service is the evidence that sets the ratio. The provisional value map in the code encodes a first guess at these ratios; the dashboard's revenue-per-service view is how the office replaces the guess with measured reality over time.

Volume tempers every one of these, and honesty requires stating it. The property runs roughly 46 paid clicks and 500 sessions a month, below the thresholds where Google's data-driven attribution stabilizes, historically near 400 conversions per 28 days, and below where value-based Smart Bidding engages, roughly 15 to 30 conversions a month. The KPI framework does not depend on those automated systems firing. Its first job is human visibility: giving the office cost-per-booked-job and lead-to-job rate as numbers a person reads and acts on, months or years before the volume grows enough for the algorithms to act on them too. The dashboard delivers that visibility now. The warehouse preserves the history that will let the algorithms, and the analyst, act on it later.

12.9 The division of labor

The warehouse and the reporting surface resolve into a clean division that a practitioner can carry to the next property. BigQuery dataset analytics_534525683 holds raw, unsampled, effectively permanent GA4 event history, at zero cost for a business of this size, and its reason to exist is the multi-year seasonal comparison that a fourteen-month retention cap forbids at useful granularity. The native /admin/insights dashboard holds the economic truth GA4 structurally cannot, close rate and revenue per service, because it reads the offline outcomes the office writes to Postgres and joins them directly to the lead records with no external account in the path. Chapter 11 feeds those same outcomes back toward the ad systems; Chapter 13 treats the export's unsampled rows and the dashboard's honest empty states as the governance backstops they are. Two questions, two warehouses, two surfaces, each built for the reader who asks its question.