Collection and normalization of weekly management reporting from 5 sources with quality checks and a template for the manager

Initial situation - I run a small e-commerce business (D2C) and every week I manually summarize indicators from different systems, which is why the report appears late and with errors. You need to turn this into a sustainable routine that can be repeated every week, one checklist at a time.

Technical environment and sources - Google Workspace, Google Sheets, Looker Studio. Data sources: 1) downloads of orders and returns from Shopify to CSV, 2) advertising expenses from Meta Ads and Google Ads via regular CSV exports, 3) warehouse and purchasing in Airtable, 4) payments and commissions in Stripe CSV, 5) salary and contractor table in Google Sheets.

What needs to be done is 1) develop a unified data format and file structure for the weekly batch of downloads so that any new downloads can be included in the report without manual editing, 2) build a management reporting model in Google Sheets with normalization and reconciliations, 3) describe the routine process as step-by-step instructions and implement data quality control.

The expected result is a Google Sheets file with tabs Raw, Mapping, Normalized, Checks, Report, where Raw accepts source CSV without manually editing formulas, Mapping stores reference books and matching rules, Normalized contains cleaned unified tables, Checks shows errors and discrepancies, Report gives final metrics for the week and cumulatively for the month. Plus a short 2-4 page instruction in Google Docs - what to upload and in what order, where to insert, how to interpret warnings.

Limitations - no writing of separate services and no access to paid connectors. Google Apps Script inside a table and standard Google Sheets functions are acceptable. Confidential data - work only in my Google Drive, access by invitation. You cannot transfer data to third-party storage facilities.

Metrics that should be in the report are revenue and returns, gross profit by product and channel, advertising expenses, CAC and ROAS, payment commissions, COGS by SKU, balances and turnover, operating expenses, margin by week. Where the source data does not allow an exact match, the assumptions and calculation logic must be clearly stated.

Quality checks - reconciliation of the number of orders and amounts between Shopify and Stripe by week, control of duplicate orders, control of negative margins, control of unmatched SKUs and unknown advertising campaigns, control of missing weeks in a series. All checks should produce clear messages and a list of lines to correct.

Acceptance criteria - 1) I download a new set of CSV according to the instructions and receive an updated report without manually editing formulas and without correcting ranges, 2) on a test set of the last 8 weeks, the report is recalculated correctly and repeatably, 3) the Checks tab shows zero critical errors or displays specific lines where the problem is, 4) the time for weekly report preparation after implementation is no more than 30 minutes on my part.

Materials from me - access to existing tables, examples of uploads over the past weeks, a list of metrics that I currently calculate manually, a short call for 30 minutes at the beginning and at the end.