Most multi-channel brands start reporting with exports: a Seller Central download, a Shopify CSV, a NetSuite saved search, all stitched together in a spreadsheet. It works until the questions get harder. What is contribution margin by SKU after fees, ads and returns? Which channel is growing revenue but losing money?
Answering those reliably requires a pipeline and a model. This guide covers both, from source inventory to metric definitions.
Inventory your sources
Before building anything, list each source, what it provides, and how you can extract it incrementally.
| Source | Key data | Typical extraction method | Incremental key |
|---|---|---|---|
| Amazon SP-API | Orders, order items, settlements, financial events, FBA inventory, returns | Orders API, Reports API, Finances API | Last updated date; settlement ID |
| Amazon Ads API | Campaign, ad group and advertised product spend | Asynchronous reports | Report date (with lookback) |
| Walmart Marketplace API | Orders, returns, reconciliation reports, inventory | REST endpoints and reports | Order date; report period |
| Shopify Admin API | Orders, refunds, products, inventory levels | GraphQL, bulk operations | updated_at |
| eBay APIs | Orders, finances transactions, payouts | Fulfillment and Finances APIs | Modification date |
| NetSuite | Items, COGS, sales transactions, journals, vendors, landed cost | SuiteAnalytics Connect, SuiteQL, saved searches via RESTlets | Last modified date |
| 3PL or WMS | Shipments, shipping cost, inventory snapshots | API, SFTP files | Ship date; file date |
Note which sources restate history. Marketplace financial data changes after the fact: refunds post weeks later, ad attribution adjusts, and reimbursements arrive long after the original sale. Your pipeline must handle this.
Load incrementally, with lookback windows
Full reloads are simple but get slow and expensive, and they can hit API rate limits. Incremental loads are the default. A robust pattern:
- Extract records changed since the last successful watermark, minus a lookback window (for example, several days for orders, longer for advertising data).
- Land raw data as-is in a staging schema, including the full JSON payload where practical. Raw data lets you rebuild models without re-calling APIs.
- Merge into cleaned tables using natural keys:
MERGEin Snowflake and BigQuery, andMERGEor delete-insert patterns in Redshift. - Advance the watermark only after the merge succeeds.
Store load metadata (source, window start and end, row counts, status) in a control table. When a number looks wrong in Power BI, this is where you start.
Layering
A three-layer approach keeps responsibilities clear:
- Raw: exact copies of source data, append-only.
- Staging: typed, deduplicated and renamed columns, one table per source entity.
- Marts: business-ready fact and dimension tables that Power BI reads.
Transformation tools such as dbt work well for the staging and mart layers, because they version-control SQL and run tests alongside models.
Model a star schema
Power BI performs best with a star schema: narrow fact tables surrounded by descriptive dimensions, joined with single-direction relationships. A core e-commerce model looks like this.
Fact tables
- fact_orders: grain is one row per order line. Columns include order date, channel key, SKU key, quantity, gross sales, discounts, shipping revenue, tax, and fulfillment method.
- fact_settlements: grain is one row per settlement line item, summarized by order, SKU and fee type. Columns include posted date, settlement ID, channel key, SKU key, amount type and amount. This is where fees, refunds and reimbursements live.
- fact_ad_spend: grain is one row per day, campaign and advertised SKU.
- fact_inventory_snapshot: grain is one row per day, SKU and location.
Dimension tables
- dim_sku: internal SKU, product name, brand, category, unit cost, active flag, and the NetSuite item ID.
- dim_channel: channel (Amazon, Walmart, Shopify), marketplace or storefront, region, and fulfillment model where relevant.
- dim_date: a full calendar with fiscal periods matching NetSuite.
- dim_location: warehouses, 3PLs and FBA.
Keep facts at their natural grain. Resist pre-aggregating settlements to monthly totals in the warehouse. Power BI can aggregate, but it cannot recover detail you threw away.
Solve SKU and ASIN mapping early
Every channel identifies products differently: Amazon uses ASINs and seller SKUs, Walmart uses item IDs and SKUs, Shopify uses variant IDs, and NetSuite uses internal item IDs. If these do not resolve to one internal SKU, every margin number is wrong.
Build a SKU mapping table with one row per channel listing:
- Channel and marketplace
- Channel identifier (ASIN, seller SKU, item ID, variant ID)
- Internal SKU
- Pack quantity multiplier (for multi-packs)
- Bundle flag, with component breakdown in a separate table
- Effective start and end dates, since listings get remapped
Route unmapped identifiers into an exceptions table and surface them on a data quality report page. Do not silently drop them.
Test data quality
Automated tests catch problems before an executive does. Useful tests include:
- Uniqueness of primary keys in every fact and dimension table.
- Referential integrity: every SKU key in a fact table exists in
dim_sku. - Freshness: the latest order date for each channel is within an expected window.
- Volume: daily row counts within a reasonable range of recent history.
- Reconciliation: settlement totals by settlement ID equal the deposit amount; NetSuite revenue by period is within tolerance of warehouse revenue.
- Accepted values: fee types and channel names come from known lists.
Fail the pipeline, or at least flag the dashboard, when critical tests fail.
Set a realistic refresh cadence
Not every dataset needs the same frequency.
- Orders and inventory: hourly or more often, if operations teams use them intraday.
- Advertising: daily, with a rolling lookback to capture attribution updates.
- Settlements and financial events: daily.
- NetSuite COGS and journals: daily, plus a final run after month-end close.
On the Power BI side, Import mode is fast but limited by scheduled refresh allowances, which depend on licensing (Pro allows up to eight scheduled refreshes per day; Premium and Fabric capacities allow more). DirectQuery against Snowflake, Redshift or BigQuery gives fresher data at the cost of query performance and warehouse compute. Composite models let you import large history and query recent data directly. Incremental refresh policies in Power BI reduce refresh time for large fact tables.
Define metrics once
Write metric definitions down, implement them in one place (a semantic model or mart), and publish the definitions with the report.
| Metric | Definition |
|---|---|
| Gross sales | Item price before discounts, excluding tax |
| Net sales | Gross sales − discounts − refunds |
| Marketplace fees | Referral, fulfillment, storage and other channel fees |
| Contribution margin | Net sales − COGS − marketplace fees − fulfillment and shipping costs − advertising |
| Contribution margin % | Contribution margin ÷ net sales |
| ACoS | Ad spend ÷ ad-attributed sales |
| TACoS | Ad spend ÷ total sales for the same scope and period |
| Return rate | Units refunded ÷ units sold |
Be explicit about choices that change results: whether contribution margin includes storage fees, whether refunds are dated by order date or refund date, and whether TACoS uses gross or net sales. Different teams using different definitions is the most common reason reports disagree.
How Audaxim AI helps
Audaxim AI builds pipelines from Amazon, Walmart, Shopify, eBay and NetSuite into Snowflake, Redshift or BigQuery, and models them for Power BI, Tableau or Looker. We handle incremental loads, SKU mapping, data quality tests and metric definitions so your reports reconcile to your books.