When a report should move
The data sources guide's decision table already says when a warehouse beats a direct connector: several platforms whose metrics must sit side by side, business logic such as margins or deduplication, sampled or thresholded analytics data, many viewers or frequent refreshes, history beyond a product's retention, and a team that can write SQL. For an existing report, the symptoms are what usually start the conversation.
| Symptom | What it usually means | Why a table helps |
|---|---|---|
| Pages take a long time to load, worst on Monday mornings | Many queries per page against live connectors, blends re-run per chart | One small table answers each chart quickly and serves the cache |
| Quota or configuration errors on busy days | Connector APIs exhausted by viewers and charts | A warehouse source queries the table, not the product's API |
| Two reports disagree on the same KPIA metric tied directly to an objective. | The logic lives in report-level calculated fields that drifted | The definition lives once, in the query that builds the table |
| A blend's totals are wrong or unexplained | Grain mismatch or unmatched keys between sources | The join is written, tested and documented in SQL |
| The report needs data the connectors cannot join | CRM outcomes, margins, offline conversions | Any source in the warehouse can be joined |
The marketing reporting troubleshooting page covers the wider question of why reporting takes too long; this tutorial assumes that the answer for one important report is a warehouse table. Move the reports that cause trouble, not every report: a single platform's report on a direct connector that loads quickly and causes no disputes has nothing to gain.
Before you start
- The warehouse holds the data. The GA4Google's event-based analytics platform. BigQuery export linked and running, as the BigQuery export basics guide describes, and the other sources the report uses (ad platform cost, CRM outcomes) landing in the same warehouse on a schedule.
- Someone who writes SQL, at the level of the SQL for marketers guide: joins, date grains and the fan-out pitfalls.
- Edit rights on the report and its data sources, and the ability to create data sources on the warehouse.
- The report's owner and its main viewers identified, because they will review the reconciliation and the side-by-side period.
- A place for definitions, ideally the metrics layer described in the metrics layer guide.
- An agreed side-by-side period, long enough to include at least one normal reporting cycle and the meeting where the report is used.
Inventory the report
List everything the report contains, page by page: each chart and scorecard with its dimensions, metrics, filters and date range; each control; each data source with its connector, credentials and freshness; each blend with its tables, join keys and join types; and each calculated field, at source or chart level, with its formula. Note who views the report, how often, and any scheduled delivery. The inventory usually reveals charts nobody uses and fields defined twice; mark them, because the move is the moment to drop them rather than rebuild them.
Write down every metric's definition
For each metric the report shows, write the definition the metrics layer guide describes: name, formula, grain, filters, source and owner. Take the current behaviour as the starting point, read from the inventory, not the intended behaviour, and note where they differ. Ratios such as ROASRevenue generated per unit of ad spend. and CPACost of each conversion. are defined as a sum divided by a sum, as the calculated fields tutorial explains. The channel groupingRules that assign each visit or conversion to a channel, such as organic search or email, from its source, medium and campaign values. deserves its own entry, because the report probably uses the GA4 connector's channel dimension and the table will need rules written in SQL. Agree the definitions with the report's owner before any SQL is written.
Build the reporting table
Write the query that produces one table at the grain the pages use, usually date by channel, adding campaign only if a page needs it. Join the sources in SQL instead of in a blend, with the spine and join type chosen as the blending tutorial teaches, and store numerators and denominators rather than ratios. Keep daily rows even for a weekly report, so the date control still works. Refresh the table on a schedule that matches the sources' arrival, and record the query in version control with the definitions it implements.
CREATE OR REPLACE TABLE `project.reporting.channel_daily` AS WITH web AS ( SELECT date, CASE WHEN medium IN ('cpc', 'ppc') THEN 'Paid search' WHEN medium = 'paid_social' THEN 'Paid social' WHEN medium = 'organic' THEN 'Organic search' WHEN medium = 'email' THEN 'Email' WHEN source = '(direct)' THEN 'Direct' ELSE 'Other' END AS channel, SUM(sessions) AS sessions, SUM(bookings) AS bookings, SUM(booking_revenue) AS booking_revenue FROM `project.reporting.ga4_sessions_daily` GROUP BY date, channel ), cost AS ( SELECT date, channel, SUM(cost) AS cost FROM `project.reporting.ad_cost_daily` GROUP BY date, channel ) SELECT COALESCE(w.date, c.date) AS date, COALESCE(w.channel, c.channel) AS channel, IFNULL(w.sessions, 0) AS sessions, IFNULL(w.bookings, 0) AS bookings, IFNULL(w.booking_revenue, 0) AS booking_revenue, IFNULL(c.cost, 0) AS cost FROM web w FULL OUTER JOIN cost c ON w.date = c.date AND w.channel = c.channel;Reconcile the table with the current report
Before anyone sees the new numbers, compare them with the old report and with the source systems, metric by metric, for a closed period. Set the same date range in both, total each metric, then break the totals by channel and by week. Every difference gets an explanation in writing. Expected ones include the export's exact counts against the interface's estimates, which the export guide describes; your SQL channel rules against the connector's channel dimension; and ad cost converted at a different rate or synced at a different time by a partner connector. A difference with no explanation is a defect in the query, and the move waits until it is found.
Create the warehouse data source
Create a reusable data source on the table with the warehouse connector, never an embedded one, so other reports can use it. Choose credentials deliberately: the data sources guide explains that owner's credentials let viewers see the data without warehouse access but stop working when the owner's account is suspended, while viewer's credentials require each viewer to have access. Set field types and default aggregations at the source, hide fields the report does not need, and add the ratio fields as aggregated calculated fields. Set the data freshness to match the table's refresh schedule; the performance guide explains why a window shorter than the data's real update cycle only defeats the cache.
Repoint the charts and rebuild the fields
Work on a copy of the report, never the live one. Page by page, point each chart and control at the new data source and select its dimensions and metrics again from the source's fields. Rebuild each chart-level calculated field as a field in the source or, better, as a column in the table, and delete the blends as their charts move. Drop the charts the inventory marked as unused. Keep the layout, titles and default date ranges the viewers know, so that the only thing that changes for them is the speed and the definitions note on each page.
Run old and new side by side
Share the new copy with the report's owner and its main viewers for the agreed period, alongside the old report. Compare the headline figures at each reporting cycle and collect questions: most will be about the differences the reconciliation already explained, which is why that write-up should be linked from the new report. Fix anything new in the query, not in the report. Record the load time of the busiest pages in both versions. The period ends when the owner signs off, not when the calendar says so.
Switch viewers and retire the old sources
Replace the old report's link wherever it is used: bookmarks, wiki pages, scheduled deliveries and slide decks. Either move the old report to an archive folder with a note pointing to the new one, or remove viewers' access to it, so nobody keeps using it by habit. Then retire what the table replaced: the blends, the connector-based data sources that no other report uses, and any partner connector subscription that existed only for this report. Check each source's other uses before deleting it, and transfer ownership of anything left to an account that will outlive the project.
A report moved (illustrative)
Pebblekite Holidays rebuilds its weekly channel report
Pebblekite Holidays, an invented holiday company, had a weekly channel report with five pages built on four connectors: the GA4 connector, the Google Ads connector, a partner connector for paid social and a sheet of CRM bookings. Two blends joined cost to sessions and bookings by channel and week, and nine calculated fields held the channel rules and ratios. The headline page took about 3 minutes 10 seconds to load on a Monday morning, and the finance team's booking total never matched.
The analyst built channel_daily from the GA4 export, the ad cost tables and the booking system's daily
extract, and reconciled a closed quarter:
| Metric | Old report | Reporting table | Difference |
|---|---|---|---|
| Sessions | 418,900 | 412,300 | −1.6% |
| Bookings | 2,231 | 2,184 | −47 |
| Booking revenue | £3,128,400 | £3,061,900 | −2.1% |
| Paid search cost | £96,310 | £96,310 | none |
| Paid social cost | £90,110 | £88,240 | −£1,870 |
Two differences needed explaining beyond the expected gap between the export and the interface's estimated sessions. Bookings and revenue fell because the old report counted bookings from the CRM sheet, which included 47 bookings later cancelled; the table took confirmed bookings from the booking system, the finance team's definition, which is why finance had never matched. Paid social cost fell because the partner connector converted spend at its own daily rate, while the warehouse used finance's rate. Both were written up and signed off by the marketing director.
After a month side by side, the new report replaced the old. The headline page loads in about 6 seconds, the two blends and the partner connector subscription are gone, and the booking total matches finance's.
Ownership after the move
A warehouse table is not maintenance-free; it moves the maintenance from the report to the query. The performance guide is plain that someone must own the queries, watch them when exports change, and pay the storage and query bills. Make that ownership explicit before the old report is retired.
| Responsibility | What it covers | Who usually holds it |
|---|---|---|
| The query | The SQL, its version history and changes to it | The analyst or analytics engineer who built it |
| The schedule | That the table refreshes after its sources land, and alerts when it does not | The same person, or a data team |
| The definitions | The metrics layer entries the query implements | The report's business owner, with the analyst |
| The data source | Credentials, field types, freshness and sharing | The report's owner |
| The report | Pages, charts and viewers | The report's owner |
Changes now follow one route: a definition changes in the metrics layer, then in the query, and the report picks it up without a field being edited. A request to add a "quick" calculated field to the report is a request to change the definition, and should be treated as one. The reporting and BI comparison describes the shift this represents, from reports that compute to reports that display. Check the table's row counts and totals after every refresh, and review the reconciliation whenever an upstream source changes, such as a new channel, a renamed campaign convention or a change to the GA4 export.
Common mistakes
- Moving every report. A quick, undisputed report on a direct connector gains nothing and adds a query to maintain.
- Writing SQL before the definitions. The table then encodes whatever the analyst assumed, and the reconciliation becomes an argument.
- Storing ratios in the table. Rolled up by a chart, per-row ratios are averaged and wrong.
- A weekly table for a report with a date control. Keep daily rows so viewers can still choose periods.
- Skipping the reconciliation. The first viewer to spot a difference decides the new report is wrong, and the old one lives on.
- Repointing the live report. Viewers see half-moved pages; work on a copy and switch when it is signed off.
- Freshness shorter than the refresh. The cache expires before the table has changed.
- Leaving the old sources running. Blends, connectors and subscriptions nobody retires keep costing quota, money and confusion.
- No named owner for the query. The table stops refreshing after an upstream change, and the report shows last month's figures with nobody noticing.
The Looker Studio section links the rest of the series, and the GA4 BigQuery export basics guide covers the export that feeds the analytics half of most reporting tables.
