How to Reconcile a Weekly Ecommerce Report

Reconcile your ecommerce data before you ask AI to explain it. Keep store orders, refunds, ad costs, and attributed campaign revenue in separate fields. Give each metric a source, timezone, currency, and period. Your report is ready to interpret when someone else can follow the numbers back to the exports.

What should you check in the source files?

Export	Fields	First check
Store orders	Order ID, product, revenue, payment time	Cancellations, discounts, and multiple items per order
Refunds	Refund date, order ID, amount	Whether the report assigns refunds to the refund week or original sale
Advertising spend	Campaign ID, cost, timezone, currency	A shared date range and an explicit currency conversion rule when needed
Attribution	Attributed revenue, window, and order scope	Keep the platform's attributed result in a separate column

Record the business definitions at the top of the report. If a definition changes, mark the date and rebuild comparable history before drawing a week-over-week conclusion.

Why can adding store and ad revenue overstate the result?

Suppose a store records $8,000 in orders and $500 in refunds. The ad report shows $3,000 in attributed revenue and $700 in spend. If those attributed sales are already in the store order export, adding the two revenue figures double-counts them. In this example, after-refund store revenue is $7,500. The $700 of ad cost and $3,000 of attributed revenue each answer a different operating question. Check how the platform defines attributed revenue before using its number. TikTok Product GMV Max reporting

What should you hand to the model?

Keep the source exports and use a pivot table or a documented query to summarize by date and product. At line-item granularity, count distinct order IDs for orders and sum item quantities for units. Give the model the metric, source, filter, formula, and result. Before you use the output, trace one day and one product back to the raw records. Google Sheets pivot table documentation

What belongs in the final review?

Check that grouped totals match the source, that labels show the correct week and currency, and that the proposed actions have owners and dates. Flag missing data next to the decision it affects. If you are reviewing the campaign every day as well, use the GMV Max daily review checklist so the daily and weekly records can be reconciled.