E-commerce Analytics Dashboards

The brand’s numbers lived in eight places, and they never agreed. It sells home goods on its own online store and on Amazon, and its data was spread across the store, Amazon Seller Central, Google Ads, Meta Ads, its email tool, Google Analytics, the warehouse system and the accounting software.

Every Monday morning someone exported files and rebuilt the same spreadsheet. The figures rarely matched, because each tool counted revenue, refunds, discounts and even days differently. Nobody could see true profit per product after ads, fees, shipping and returns. Amazon stock-outs were spotted after sales had stopped, and leadership questions waited in a queue for the one analyst.

Before connecting a single source, we agreed what each number means. Two weeks of interviews with leadership, marketing, operations and finance produced one written definition for every metric the business runs on, including:

• net revenue: after discounts, refunds and returns, not before;
• contribution margin: per order and per product, after cost of goods, payment and marketplace fees, shipping, packaging, returns and ad spend;
• marketing efficiency: total revenue against total ad spend, alongside return on ad spend by channel and campaign;
• new versus returning customers, customer acquisition cost and lifetime value by first-purchase month;
• days of stock cover, by product and by channel, including stock held at Amazon.

The pipeline syncs every source into one BigQuery warehouse automatically: the online store, Amazon, Google Ads, Meta Ads, email, Google Analytics, the warehouse system and the accounting software. Most sources sync daily, and ad platforms sync every few hours during big sales, when spend moves fast. Time zones and currencies are normalised on the way in, so a day means the same thing in every source.

On top of the raw data, dbt models turn each agreed definition into code, once. Every dashboard, report and AI answer reads from these same models, so the same question always gets the same number.

Every model has tests for missing, duplicated or late data. When a source fails to sync or a figure looks wrong, the team is alerted before anyone reads a wrong number.

Each team has dashboards built around its own decisions:

• leadership: a daily snapshot of sales, margin and spend by channel, with the week against the last;
• marketing: spend and return by channel and campaign, new versus returning customers, and acquisition cost;
• operations: stock cover, inbound shipments and products at risk of running out;
• finance: profit and loss by channel each month, reconciled to the accounting software.

Before launch, every dashboard was checked against finance’s month-end figures until they matched.

Stock cover is calculated for every product, in the warehouse and at Amazon, from recent sales and each supplier’s lead time. When a product is heading for a stock-out, the operations team is alerted while there is still time to reorder or move stock, not after the listing has gone quiet.

Anyone in the company can ask a question in plain English, in Slack or in the dashboards: which products lost margin last month and why, or how the spring sale compared with last year’s. The AI analyst answers from the same defined metrics, showing the figure, a chart and the query behind it, so every answer can be checked.

It is built to be trusted. It uses only the defined metrics, it follows the same access rules as the dashboards, and when the data to answer a question doesn’t exist, it says so instead of guessing. Every Monday morning, it also writes a short summary of the week for the leadership team: what moved, what to watch, and links to the details.

Airbyte syncs every source into BigQuery, dbt builds and tests the models, and Metabase serves the dashboards. The AI analyst runs on Claude, querying only the defined metrics through a controlled set of tools, and answers in Slack and the dashboards. Everything runs on Google Cloud, with access for each team set by role. At handover, the team was trained to add metrics and dashboards themselves.

The Monday spreadsheet has been retired, and every team now works from the same numbers. Margins are visible per product after every cost, stock warnings arrive before stock runs out, and leadership asks its own questions and gets answers straight away, with the working shown.