Amazon Ads to BigQuery: how to load it before Amazon deletes it

Can you send Amazon Ads to BigQuery natively?

No. Neither Amazon nor Google offers a built-in Amazon Ads to BigQuery transfer. I checked the supported sources for BigQuery Data Transfer Service on docs.cloud.google.com in November 2026: the marketing sources are Facebook Ads, HubSpot, Klaviyo and Mailchimp, plus Google's own products, and Amazon appears only as Amazon S3 and Amazon Redshift, which are storage and warehouse sources, not advertising data.

That leaves four practical routes: a managed connector that reads the Amazon Ads API for you, your own pipeline on that API, Amazon Marketing Stream feeding hourly data into AWS and then into BigQuery, or downloading reports from the console and uploading them. Every route except the last reads the same Amazon API underneath, so Amazon's retention and attribution rules apply whichever you pick.

Which Amazon Ads to BigQuery route should you choose?

Pick a managed connector if the marketing team owns reporting and nobody wants to maintain code. Pick Amazon Marketing Stream only if you need hourly bid and budget data and already run AWS. Build your own API pipeline if you manage many profiles across marketplaces and have an engineer. Use manual uploads only for a one-off audit.

How much does it cost to get Amazon Ads into BigQuery?

For most sellers the connector subscription is the main cost and BigQuery is close to free. The prices below are what I read on each vendor's pricing page in November 2026; they change often, so check before you buy.

Windsor.ai's Basic plan is $19 a month billed annually ($23 monthly) for 3 data sources with daily refresh, and Standard is $99 a month annually ($118 monthly) for 7 sources with hourly refresh; BigQuery is a destination on all plans and the forever free plan covers 1 source and 1 account. Coupler.io's Starter plan is $24 a month annually ($32 monthly) for 3 accounts with daily refresh, Active is $99 a month annually for 15 accounts, and Pro is $199 a month annually for 50 accounts with hourly refresh. Estuary publishes $0.50 per GB moved plus $100 a month per connector instance.

On Google's side, the first 10 GiB of storage and the first 1 TiB of on-demand queries each month are free, then queries cost $6.25 per TiB. Amazon Ads rows are compact, so an ad dataset rarely leaves the free tier on its own. If you take the Marketing Stream route, add AWS charges for the queue or Firehose stream, storage in Amazon S3, and data leaving AWS. Google's Amazon S3 transfer into BigQuery carries no orchestration fee after Google's August 2026 pricing change, as reported by cloudmagazin.com.

• Do not build on the BigQuery sandbox: Google's documentation says sandbox tables expire after 60 days, which defeats the point of archiving Amazon data.
• Search term reports are the largest Amazon table by far, so check how a usage-priced tool counts them before you switch them on.
• Each marketplace is a separate advertising profile, and some connector plans count each profile as an account.

Why does Amazon's data retention make BigQuery urgent?

Because Amazon deletes detailed ad data after two to three months. According to Intentwise's explainer on Amazon report retention, the Ads API returns Sponsored Products data for up to 95 days and Sponsored Brands and Sponsored Display data for 60 days. Reports downloaded from the console last 60 to 90 days depending on the report: 90 days for targeting and advertised product reports, 60 for search term and purchased product reports.

In practice this means a connector that reads Amazon live cannot show last year's Prime Day, last quarter's search terms or any year-on-year trend. BigQuery fixes that only from the day you start loading. The first job on any Amazon Ads to BigQuery project is therefore a backfill of everything Amazon still holds, followed by a daily load that never deletes old rows. Start with search term data, because it has the shortest life and is the most useful for keyword and negative-keyword decisions.

A worked example: a seller in three marketplaces

Take a brand selling in the US, UK and Germany, each a separate Amazon Ads profile, with about 400 advertised products and 3,000 active keywords and targets. It wants daily campaign, product and target performance, weekly search terms, and a Data Studio dashboard that compares ad spend with total sales from Seller Central.

Daily target-level rows come to roughly 3,000 targets times 365 days, about 1.1 million rows a year, plus 146,000 product rows and a few thousand campaign rows. Search term reports add the most volume, perhaps several million rows a year for an account this size. Even at 400 bytes a row the whole dataset stays in the low gigabytes, inside the 10 GiB free storage for the first year or so. Report queries against date-partitioned summary views scan megabytes.

The real decision is the connector. Three profiles fit Coupler.io's Starter plan, or Windsor.ai's Basic plan if Amazon Ads is one of three sources, at $19 to $32 a month depending on vendor and billing term, provided the plan counts profiles as accounts the way you expect. Add Seller Central and Google Ads as sources and the next tier up, around $99 a month annually at either vendor, becomes likely. This is my own estimate from the published prices above, not a quote; your row counts depend on targets, search terms and marketplaces.

Why don't Amazon Ads numbers in BigQuery match the console?

Usually because of attribution windows. Amazon reports sales and purchases as separate metrics per window: the API exposes 1, 7, 14 and 30-day variants such as sales7d, sales14d, purchases7d and purchases30d, as Adriel's Amazon Ads data reference documents. The console defaults differ by ad product, typically 7 days for Sponsored Products and 14 for Sponsored Brands and Display. A view that mixes sales14d for one product with sales7d for another produces a ROAS figure nobody can reconcile.

Late conversions are the second cause. Attributed sales keep arriving until the window closes, so a pipeline that only loads yesterday understates the last two weeks. Reload at least the last 14 days, and 30 if you report 30-day metrics, on every run. Time zones are the third: each marketplace profile reports in its own time zone, so a UK day and a US day do not line up. Store the profile time zone and convert only in a view.

How do you connect Amazon Ads to BigQuery step by step?

The steps are the same for most connectors. The ones people skip, the backfill, the attribution choice and the reconciliation, decide whether the data can be trusted.

• Create a Google Cloud project with billing enabled and a BigQuery dataset in the region where your other ecommerce data lives.
• Make sure the Amazon user authorising the connector has access to every advertising profile and marketplace you need, including any DSP entity.
• Authorise Amazon Ads in the connector, select all profiles, and choose reports: campaigns, ad groups, advertised products, targets and search terms for each ad type.
• Choose one attribution window per ad type, load the matching sales and purchase metrics, and record the choice in the table description.
• Backfill everything Amazon still returns in the first run, starting with search terms and Sponsored Brands and Display, which expire first.
• Partition tables by date and set each run to reload the last 14 to 30 days rather than append.
• Reconcile one full month of spend, clicks and attributed sales against the console for each marketplace before building the Data Studio report.

How should Amazon Ads tables be designed in BigQuery?

Design around profiles, currencies and windows. One Amazon business can hold several marketplaces in different currencies and time zones, and every sales column carries an attribution window, so the schema has to make all three explicit.

Partition fact tables by date and cluster them by profile and campaign. Keep one table per report type rather than one wide table, because Sponsored Products, Brands and Display expose different fields. Store spend and sales in the marketplace currency with a currency column, and convert to one reporting currency in a view using a dated exchange rate table. Keep campaign, ad group and keyword names in dimension tables keyed on Amazon's IDs.

• Profile ID, marketplace, currency and time zone on every fact row.
• Sales and orders columns named with their window, for example sales_7d and sales_14d, never just sales.
• A merge on the last 14 to 30 days on every run, so late conversions replace earlier figures.
• ASIN and SKU carried through, so ad data joins to Seller Central orders and inventory.
• A summary view for Data Studio that exposes ACoS and TACoS with the window stated in the field name.

When is Amazon Ads to BigQuery not worth it?

When you run one marketplace, manage campaigns day to day in the console, and only need the last few weeks in a report. A partner connector straight into Data Studio is simpler, and my Amazon Ads to Data Studio guide covers that route. Even then, consider a cheap daily archive into a Google Sheet so history is not lost.

BigQuery earns its place when you need history beyond Amazon's 60 to 95 days, several marketplaces in one model, ad spend joined to Seller Central sales for TACoS, or Amazon beside Google Ads, Meta and GA4 in one report. Data Studio blends stop at 5 sources with equality joins only, while BigQuery can union every channel into one table.

How I handle Amazon Ads to BigQuery projects

I start with a free review of your profiles, marketplaces and what your reports need to answer. Then I recommend the cheapest route that will hold up, run the backfill first so nothing more expires, and quote a fixed price for the pipeline, the table design, the summary views and the Data Studio dashboard.

Everything is built in your Google Cloud project and your connector account, with the attribution choices, reload window and views documented, so nothing depends on my login. Retail dashboards I've led at Greenwolf Tech Labs track revenue growth, conversion rate, sell-through and online against in-store sales, and Amazon advertising data fits the same model once it sits in BigQuery.