Reporting · Data Integration
Export Amazon Ads Data to Power BI, Tableau, Qlik & BigQuery
Connect one campaign report first, then build a view or query whose totals match the source.
Also available in: 中文版 · Deutsch
Amazon Ads data usually reaches an analytics system as a downloaded report, authorized connector output or API result. Power BI, Tableau and Qlik build analysis views; BigQuery stores and queries data and can feed a dashboard later. Start with the same campaign daily file and follow the branch for your tool.
Understand how data reaches the dashboard
The simplest first connection is advertising report → CSV file → destination tool → campaign daily view. Replace the manual file with an updateable source when recurring reporting is needed. Refreshing a chart does not fetch a new Amazon report: an unchanged source still produces old data.
A file workflow does not require every reader to develop an API integration. Check report coverage and authorization for an existing connector; custom Amazon Ads API extraction requires its own approved access and maintenance. This tutorial does not assume that a particular product always has—or never has—a native Amazon Ads connector.
Prepare the first daily campaign file
Obtain a dated Sponsored Products campaign report from the correct account. Retain its file and generation time. Each row needs a clear account, campaign and date, with campaign ID, name, impressions, clicks, cost, orders, attributed sales and currency. Map the actual export's column names before import.
Practice with the four-row CSV sample. It is fictional teaching data, not an Amazon export format: two campaigns across two days, one example account, USD, total cost 100 and sales 400. The following instructions use its English headers and do not claim a verified live-account integration.
Connect the file to your tool
Power BI Desktop
Choose Get Data → Text/CSV. In the transform step, set profile_id and campaign_id to Text, date to Date, and cost and sales to decimal numbers, then load. Create a table with dates or campaign names and sums of cost and sales. See Microsoft's CSV connector guide for interface details.
Tableau Desktop
Choose Text File in Connect, open the CSV and check date, string and numeric types on the data-source page. On a worksheet, use campaign and date as dimensions and summed cost and sales as measures. Follow Tableau's text-file instructions for the connection.
Qlik Cloud Analytics
Upload the data file into an accessible space, add it to an analytics application and load it. Check date and number parsing; treat campaign IDs as identifiers, not additive amounts. Build a sheet table using date, campaign and cost/sales measures. Permissions and entry points depend on the environment; see Qlik's file-import guide.
BigQuery
You need the relevant project and dataset permissions; storage or queries may incur charges. Create a dataset and a table from an uploaded CSV, skipping its header. Set profile_id, campaign_id, campaign_name and currency to STRING, date to DATE, count columns to INTEGER, and cost and sales to NUMERIC. Do not let inference turn long IDs into numbers. See Google's loading quickstart.
To reload this full sample, target the same dedicated teaching table and choose Overwrite table under Advanced options → Write preference, not Append. Overwrite removes old data and can reset table settings. Use it only for this disposable full sample, never blindly on production history or partitioned tables. See Google's write-behavior guide. The reloaded result must still have four rows, cost 100 and sales 400.
For a sample table named demo.ads_campaign_daily, this query checks campaign totals. Substitute your own project, dataset and table name. It illustrates analysis; it does not connect to your advertising account.
SELECT campaign_id, MAX(campaign_name) AS campaign_name,
SUM(cost) AS cost, SUM(sales) AS sales,
SAFE_DIVIDE(SUM(cost), SUM(sales)) AS acos
FROM demo.ads_campaign_daily
GROUP BY campaign_idBuild a daily view with correct totals
Keep dates and campaign identities meaningful. The sample aggregates to cost/sales of 40/80 for Cup_Exact and 60/320 for Cup_Auto: total 100/400, or 25% ACOS. Divide aggregated cost by aggregated sales rather than averaging row percentages. Do not silently report 0% when sales are zero.
Use only campaign daily data for the first view. Joining a campaign total directly to its many search-term rows repeats the same spend. Align grain and join conditions before combining tables, and keep account, currency and ad product explicit.
Refresh the source before refreshing the view
For files, obtain and validate the new report, replace the file or table the tool actually reads, then refresh the view. A cloud service may not be able to read a desktop computer's local path; configure supported storage, credentials or a gateway as required by that product.
Reloading a full snapshot should replace its scope, not append copies. For long-running history, update matching daily records and revisit recent dates whose attributed orders may change. Version the source files, display reporting dates separately from the last successful load, and alert on failures while retaining the previous successful result.
Reconcile the completed connection
Compare row count, cost and sales with the source using identical accounts, dates and filters. The sample should produce four rows, 100 cost and 400 sales. Test a repeated load: it must not produce eight rows or cost 200. Check shorter replacement files for leftover rows and confirm that failed refreshes are visible.
The BI branches finish with a reconciled campaign table. BigQuery finishes with a queryable, reloadable table; it does not need a chart first. Test permissions, refresh behavior and costs in your own environment before production use. For the meaning of dashboard metrics, return to the advertising report guide.

I've worked in Amazon operations for five years, with more than $10M in sales. I now build practical tools that help sellers reduce repetitive advertising work and avoid manual errors.