What platform data is for in a warehouse
The reporting versus BI comparison puts "bring spend beside it" as the step after landing the analytics export, and that is the main job: spend is the one figure only the ad platforms hold, and every cost metric needs it. Cost per acquisition (CPACost of each conversion.), return on ad spend (ROASRevenue generated per unit of ad spend.) and customer acquisition cost are all spend over an outcome, and once spend is in the warehouse the outcome can come from the system of record rather than from the platform's own reporting.
The data serves three purposes, and each has its own requirement.
- Cost beside outcomes. Spend by day, channel and campaign, joined to sessions, leads and orders. Requires clean campaign keys and settled spend.
- The platform's own view. Platform conversions and conversion value, which channel teams optimise against and which reports need beside the business's figures. Requires the attribution settings that produced them.
- History. Platforms change interfaces, settings and retention; the warehouse keeps what was loaded. Requires raw loads that are never overwritten without a record.
Ways to get it there
There are three routes, and most teams use more than one.
| Route | How it works | Strengths | Watch for |
|---|---|---|---|
| Managed connector | A service copies platform reports into the warehouse on a schedule | Quick to set up; schema changes handled by the service | Its sync schedule and refresh window are its own; check how far back each sync reloads |
| Platform's scheduled exports | The platform delivers a scheduled report or file to storage you control | No third party; the platform's own field names | Fixed report shapes; delivery failures can be silent |
| Your own code against the reporting interface | Scheduled jobs call the platform's reporting interface and write tables | Full control of fields, grain and reload windows | Maintenance whenever the interface changes; needs an engineer and monitoring |
None is right for every platform. A small team usually starts with a managed connector and moves a platform to its own code only when the connector cannot provide a field, a grain or a reload pattern it needs. Whichever route, the questions to ask are the same: which fields and grain, how often, how far back each run reloads, how failures are reported and who owns it. The guide to dashboard data sources and connectors draws the related distinction between live connectors and scheduled copies on the reporting side, including that a partner source is only as fresh as its last sync.
What to load and at what grain
Load at the finest grain the model and the questions need, usually day by account by campaign, and finer where creative or keyword analysis needs it. Aggregating in the warehouse is easy; disaggregating is impossible.
For each row, load:
- Identifiers and names. Account, campaign and, where loaded, ad group and ad IDs, alongside their current names. IDs are the join keysThe shared field, such as date and channel, a campaign ID or a click ID, on which rows from two data sources are matched in a blend or a query.; names are descriptions that change.
- Delivery and cost. Spend, impressions and clicks, in the account's currency.
- Platform conversions. Conversions and conversion value by conversion action or event, not only the platform's total, so that a report can choose the purchase action without the newsletter sign-ups mixed in. Where a platform reports click-through and view-through credit separately, load them separately.
- Context. The account's currency and time zone, and the attribution setting each conversion figure was counted under.
- Load metadata. The load timestamp, the route and the date range the load covered, on every row.
The marketing data model guide describes the spend fact table these rows feed and the campaign mapping table that ties each platform's campaign IDs to one business key, which is what lets spend from several platforms meet sessions and orders.
Late conversions and refresh windows
A platform records each conversion against the date of the ad interaction it credits, not the date of the purchase, and keeps adding conversions to that date for as long as its attribution window allows. The conversion lag glossary entry explains the consequence: the most recent days of any platform report are always incomplete and fill in over the following days or weeks. Uploaded and offline conversions make the tail longer, because they are dated back to the click when they arrive.
A warehouse that loads each day once, the night after, freezes that day at its least complete. The weekly report then shows recent days with too few conversions, cost per acquisition too high and return too low, and the error is largest exactly where decisions are made. Because the shortfall repeats every week, it looks like a trend.
The fix is a refresh window: on every load, reload a trailing run of days and replace them rather than appending.
- Measure the lag. Keep a snapshot table that records each load's figures by activity date and load date. After a few weeks, plot how one activity date's conversions grow across loads; the point where they stop moving materially is your window.
- Reload the window on every run. Delete the rows for the trailing days and insert the fresh ones, or merge on the row's keys, in one transaction. Appending the same day twice is the classic duplication, and DeduplicationRemoving double counts when one conversion is reported by more than one source. after the fact is harder than not duplicating.
- Mark recent days as provisional. The metrics layer guide recommends stating the restatement window in the metric definition; reports should shade or label the days still inside it.
- Settle older periods. Outside the window, rows are final unless a deliberate backfill reloads them, and a backfill is recorded in the load log like any other change.
Spend can be restated too, so check in your own snapshots whether it moves after the first load, and include it in the window if it does.
Nimbleroot Supplements watches last week's conversions rise
Nimbleroot Supplements, an invented supplements brand, loads two ad platforms into its warehouse every night, each load appending the previous day. The Monday report keeps showing the latest week's cost per purchase about a fifth higher than the week before, and the paid social budget is cut twice before an analyst checks the platform interface and finds the old weeks have caught up. A snapshot table shows how the purchases credited to one Tuesday grow with each load.
| Load | Platform A purchases | Platform B purchases |
|---|---|---|
| Wednesday, the next morning | 182 | 141 |
| Thursday | 214 | 168 |
| Saturday | 233 | 189 |
| The following Tuesday | 241 | 201 |
| Two weeks later | 244 | 207 |
The appended table held 182 and 141 for that Tuesday for ever, between a quarter and a third below the eventual figures. The team switches both platforms to delete-and-reload of the trailing 14 days on every run, adds a provisional flag to the latest seven days in the weekly report, and keeps the snapshot table to check the window each quarter. The apparent weekly decline disappears, and the budget cut is reversed.
Attribution settings travel with the numbers
A platform conversion is not a fact about the customer; it is the platform's count under its own rules. Google's documentation on conversion tracking describes measuring the actions people take after interacting with an ad, and the GA4 attribution settings guide notes that each Google Ads conversion action carries its own attribution model and conversion window. Meta's help centre describes its attribution setting as the window after someone clicks or views an ad within which a conversion is credited to it, chosen per ad set. Two numbers loaded into the same column can therefore have been counted under different rules.
Store the rule with the number. For each conversion figure, keep the conversion action or event, the AttributionAssigning conversion credit to touchpoints. model where the platform has one, the click and view windows, and whether view-through credit is included. Keep a settings history table, with the date each setting changed, because a change in window or model breaks the series on that date and the report needs to show it. When a platform reports click-through and view-through conversions separately, keep both and let the metric definition choose.
And never add platform conversions across platforms. A customer who interacted with two platforms' ads is counted by both, so the sum exceeds what the business sold. The platform versus analytics discrepancies guide explains the causes of the gap and how to set one business standard beside the platforms' own figures.
Currency, time zones and renamed campaigns
Currency. Each account reports in its own currency, and a business running accounts in several currencies needs one reporting currency. Convert in the cleaned layer, once, at a documented convention such as a daily or monthly rate, and keep the original amount and currency beside the converted one so that the platform's figure can always be reproduced.
Time zones. Platforms report each day in the account's time zone, as the SQL for marketers guide notes, while the analytics export uses the property's and the warehouse often stores UTC. Daily rows cannot be re-cut into another zone's days after the fact, so either align the account's time zone with the business's reporting zone when accounts are set up, or document that platform days are account days and accept small mismatches at the edges of each day. Where a finer time grain is available from the platform, convert from that.
Renamed and deleted campaigns. Campaigns are renamed to fix typos, add markets or follow a new Naming ConventionAn agreed pattern for naming campaigns, ads, events and tracking parameters so that reports can group them reliably., and joins on names break each time. Join on IDs, keep names in a history table with the date each was seen, and let the campaign mapping table carry the business's own key. A deleted or removed campaign may stop appearing in later extracts, so a full reload of an old period can return less than was loaded at the time; keep the raw loads rather than relying on the platform to reproduce history. The academy exercise on planning how ad platform data lands for a home cleaning franchise practises these decisions, from refresh windows for late conversions to renamed campaigns, and the monthly reconciliation of loads with invoices.
Schema changes. Platforms rename fields, retire metrics and change definitions. A load that silently writes nulls into a renamed column is worse than one that fails. Test each load for missing columns, unexpected nulls and sudden drops in volume; the tutorial on data tests for marketing tables shows how to write those checks as queries that run with every load.
Reconciling with invoices and interfaces
Loaded data should be reconciled in two directions every month, with the differences written down.
Against the platform interfaces. For a closed period older than the refresh window, compare warehouse spend, clicks and conversions by account and campaign with the platform's own report, in the account's currency and time zone and under the same conversion setting. They should agree closely. A gap points to a campaign missing from the load, a duplicated day, a filter in the extract or a changed field definition.
Against invoices. Spend in the platform is not the same as what is billed. Invoices can include taxes, credits and adjustments, agency fees and currency conversion, and they arrive on their own timing. The guide to tracking spend against budget separates committed, spent and invoiced amounts and owns the reconciliation to the ledger; the warehouse supplies the spent figure by day and campaign, and the differences against invoices are explained in that process rather than patched into the loaded data. The tutorial on setting up a marketing budget tracker shows where that daily spent figure lands beside commitments, invoices and accruals for each budget line.
The BI hub shows where the loaded tables sit among the model, the metrics layer and dashboard governance.
Common mistakes
- Appending each day once. Recent days freeze at their least complete and look like a decline.
- Reloading by appending. The same day twice doubles spend and conversions.
- Loading names without IDs. Every rename breaks the join.
- Loading only total conversions. The purchase action cannot be separated from the micro-conversions.
- Dropping the attribution setting. Nobody can tell which window produced which figure, or when it changed.
- Summing conversions across platforms. Shared customers are counted by every platform that touched them.
- Converting currency in dashboards. Each report uses its own rate and none can be reproduced.
- Reconciling last week. Compare only periods older than the refresh window.
