Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics › Business Intelligence for Marketing

Loading Ad Platform Data into a Warehouse

How to land ad platform cost and conversion data in a warehouse: connectors or exports, refresh windows for late conversions, currency and renamed campaigns.
Guideadvanced10 min readUpdated

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 (CPA), return on ad spend (ROAS) 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.

RouteHow it worksStrengthsWatch for
Managed connectorA service copies platform reports into the warehouse on a scheduleQuick to set up; schema changes handled by the serviceIts sync schedule and refresh window are its own; check how far back each sync reloads
Platform's scheduled exportsThe platform delivers a scheduled report or file to storage you controlNo third party; the platform's own field namesFixed report shapes; delivery failures can be silent
Your own code against the reporting interfaceScheduled jobs call the platform's reporting interface and write tablesFull control of fields, grain and reload windowsMaintenance 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 keys; 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.

  1. 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.
  2. 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 Deduplication after the fact is harder than not duplicating.
  3. 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.
  4. 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.

Illustrative example — not a real result

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.

LoadPlatform A purchasesPlatform B purchases
Wednesday, the next morning182141
Thursday214168
Saturday233189
The following Tuesday241201
Two weeks later244207

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 Attribution 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 Convention, 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.

Frequently asked questions

How many days should the refresh window cover?

As many as it takes for a day's platform numbers to stop changing in your own accounts, which depends on your conversion lag, the platforms' attribution windows and how late offline or uploaded conversions arrive. Measure it rather than guessing: keep a snapshot of each load, plot how the conversions for one activity date grow by load date, and set the window to cover the period over which they still move materially. Revisit it when attribution settings change or a new conversion source is added.

Should the warehouse store platform conversions at all?

Yes, labelled as what they are. Platform conversions are the platform's own view, counted under its attribution settings, and they are what the channel team optimises against, so reports need them. They should not be summed across platforms or treated as the business's outcome; the warehouse's job is to hold them beside spend, beside analytics and beside orders from the system of record, so that the differences can be explained rather than argued about.

Why does the warehouse not match the platform interface for last week?

Usually because the interface has kept counting late conversions while the warehouse still holds the figures from an earlier load, or because the two are cut on different time zones, currencies or attribution settings. Compare a closed period that is older than your refresh window, in the account's own currency and time zone, under the same conversion setting. If they still differ, look for campaigns missing from the load, duplicated days and changed field definitions.

Terms used on this page

Where this page is used

Part of these learning paths

Also related

Sources

  1. About conversion tracking — Google Ads Help
  2. About attribution settings — Meta Business Help Centre

Related