automations.help
Book a teardown

Reporting & dashboards

Build a real-time sales dashboard in Google Sheets

Every week you export a Shopify CSV, paste it into the same spreadsheet, fix the columns, and rebuild the same pivot — to land on a number you could have had automatically. This wires it up instead: each paid order appends itself to a raw log tab in Google Sheets within a minute or two, a scheduled reconcile job catches anything the webhook missed, and a dashboard tab built on QUERY formulas recalculates the moment a row lands. Build it yourself with the steps below, or we build it on your own Shopify and Google accounts, reconcile it against your Shopify Analytics, and hand you the keys so you own it with no monthly fee. Free teardown of the report you rebuild by hand first.

Difficulty
Intermediate
Time to build
3-5 hrs
Updated
  • Shopify
  • Google Sheets
  • n8n

The manual way today

Once a week — usually Monday — someone opens Shopify, exports an orders CSV for the date range, drops it into the spreadsheet, deletes last week's rows, fixes the columns that imported as text instead of numbers, re-points the pivot table at the new range, and reads off net sales, order count, and AOV. If anyone wants it cut a different way — by channel, by new-versus-repeat, by week-over-week — that's another manual pivot built from scratch. The spreadsheet that's supposed to save time has become a weekly chore in its own right.

Half an hour a week sounds trivial, so it never gets fixed — but it's half an hour gated behind one person remembering to do it, in one specific format, which means the report is always stale by the time anyone reads it and quietly missing on the weeks that person is out. The worse cost is that a number you only see on Mondays can't catch a problem that started on Wednesday: a channel that broke, a discount that ran too hot, a soft week that's already half over. A report you rebuild by hand is a rear-view mirror you only glance at once a week — exactly when a real dashboard would have told you mid-week, on its own.

What running looks like

A Google Sheet that keeps itself current. Paid orders land in a raw, append-only log tab within a minute or two of checkout, refunds and edits update the row they belong to instead of adding a phantom sale, and a separate dashboard tab built on QUERY and pivot formulas shows today, this week, and month-to-date — net sales, orders, AOV, units, new-versus-repeat, top products, and a week-over-week trend — recalculating the instant a new row appears. A scheduled job reconciles against the Shopify Admin API so a missed webhook self-heals, and a visible 'last updated' timestamp means a stale sheet can never quietly masquerade as a slow sales day.

01The build

How to build it

  1. Design the two tabs on paper before you wire anything

    Needs a human

    Split the sheet in two from the start: a raw 'orders_log' tab that is append-only and never touched by a human, and a 'dashboard' tab that only reads from it with formulas. The log tab gets one row per order with fixed columns — order_id, order_name, created_at (store-local ISO), financial_status, subtotal, discounts, tax, total, refunded_amount, units, customer_orders_count (1 = new), channel/source_name, and a last_synced timestamp. Decide now which sales figure is your headline (Shopify's net sales is gross minus discounts minus returns) so the dashboard reconciles with Shopify Analytics later instead of becoming a fourth number nobody trusts. Building formulas on a schema you haven't pinned down is how dashboards rot — settle the columns first.

  2. Subscribe n8n to the Shopify orders/paid webhook

    Runs itself

    Register a webhook on the orders/paid topic (Settings → Notifications → Webhooks, or via the Admin API) pointing at an n8n Webhook node. Use orders/paid, not orders/create — orders/create fires on draft and unpaid orders too, so you'd log carts that never become sales. Also subscribe orders/updated and refunds/create on the same workflow so edits and refunds flow in. Verify the webhook HMAC header against your shared secret in the first n8n node and reject anything that doesn't match, so the only thing that can write to your sheet is genuinely Shopify.

  3. Append each paid order as one row — never overwrite

    Runs itself

    From the webhook payload, map the fields to your log columns and append with the Google Sheets node's Append operation (the Sheets API spreadsheets.values.append), which finds the next empty row atomically. Do not use a write-to-range node keyed on a row number — two orders landing in the same second would clobber each other. Convert created_at into store-local time as you write it, sum line_items[].quantity for units, and read customer.orders_count to mark new versus repeat. Stamp last_synced with the current time so the row carries proof of when it landed.

  4. Upsert on order_id so refunds and edits don't double-count

    Runs itself

    Shopify delivers webhooks at-least-once, and orders/updated fires every time an order changes — so the same order_id will arrive more than once. Before appending, look up the order_id in the log (a MATCH against the order_id column, or a lookup node): if it's new, append; if it already exists, update that existing row in place. Route refunds/create the same way — find the order's row and write the refunded amount into the refunded_amount column rather than adding a new negative row. This single rule is the difference between a dashboard that mirrors Shopify and one that drifts a little further off every refund.

  5. Add a scheduled reconcile job so a missed webhook self-heals

    Runs itself

    Webhooks get dropped — n8n restarts, a deploy blips, Shopify retries and gives up. A dashboard that silently misses orders is worse than no dashboard. Add a second n8n workflow on a Schedule Trigger (every 15 minutes is plenty) that calls the Admin API for orders with updated_at_min set to the last successful run, paginating until there's no next cursor, and upserts each one through the exact same order_id logic as the webhook path. Store the last-run timestamp in n8n static data or a cell on a hidden 'meta' tab. Run this once with a wide date range first to backfill your history, then let the schedule keep it honest.

  6. Build the dashboard tab on live formulas, not pasted values

    Runs itself

    On the dashboard tab, read the log with QUERY so the math is genuinely live — for example =QUERY(orders_log!A:M, "select sum(...) where ... ") for today's net sales, with a date filter built off TODAY(). Lay out the cards you actually use: today and yesterday, week-to-date, month-to-date, AOV (net ÷ orders), units, the new-versus-repeat split, and a small top-products table via a QUERY group-by. Add the week-over-week comparison — this week against the same days last week — because a bare number means little without a baseline, and DTC sales are weekly-seasonal. Drop a SPARKLINE of daily net sales across the row so the trend is visible at a glance. Because these are formulas over the log, every new appended row recalculates the dashboard on its own.

  7. Surface freshness and keep the sheet from outgrowing itself

    Needs a human

    Put a single cell at the top of the dashboard reading the newest last_synced from the log — 'Updated 3 min ago' — so a stale sheet is obvious instead of looking like a quiet morning. Mind two real Sheets limits as you scale: the spreadsheet's 10-million-cell ceiling and the Sheets API write quota (roughly 300 writes/min per project), which a high-volume store can brush against if every order writes individually. When the log gets long, archive rows older than your reporting window to a 'history' tab or a second file and keep the dashboard pointed at the live window — and know the honest ceiling: past a few hundred orders a day, this is the moment to graduate to a real database with Looker Studio on top, not to fight the spreadsheet.

02The stack

What it’s built on

Shopify
System of record for the sales. The orders/paid, orders/updated, and refunds/create webhooks push events the moment they happen, and the Admin API (orders with updated_at_min) powers the backfill and the reconcile job. Shopify's own Analytics is the figure your dashboard reconciles against so the team trusts it.
Google Sheets
The living dashboard. A raw append-only log tab holds one row per order; a dashboard tab reads it with QUERY, pivot, and SPARKLINE formulas that recalculate the instant a row lands. It's free, everyone already knows it, and you can layer in your own non-Shopify columns.Google Sheets is free, and the Sheets API is free within generous quotas (about 300 writes/min per project, a 10-million-cell ceiling per file). That covers this comfortably for most stores; it's also the honest limit — a very high-volume shop eventually wants a real database behind it.
n8n
The glue that does all the work: a Webhook node receives paid orders and verifies the HMAC, a lookup decides append-versus-update, the Google Sheets node writes the row, and a second scheduled workflow paginates the Admin API to reconcile anything the webhooks missed.n8n is open-source and self-hostable for free, which covers this end to end; their cloud tier just saves you running the server yourself. Even a busy store's order volume sits well inside the smallest plan.

03Questions

Before you build

Is this actually real-time?

Near-real-time, and worth being precise about: a paid order appends to the sheet within a minute or two of checkout via the webhook, and the dashboard formulas recalculate the instant that row lands — so the math itself is genuinely live. It is not a streaming database measured in milliseconds, because a Google Sheet isn't one. For a sales dashboard you glance at through the day, an order showing up a minute or two after it's placed is real-time in every way that matters, and far ahead of a CSV you rebuild on Mondays.

Why Google Sheets instead of Shopify Analytics or Looker Studio?

Because you already live in Sheets, and you want cuts Shopify's canned reports don't give you — your own channel groupings, a custom new-versus-repeat definition, or columns blended in from outside Shopify. Sheets is free, instantly shareable, and editable by anyone on the team without learning a BI tool. The honest tradeoff is scale: past a few hundred orders a day the sheet gets heavy, and that's the point to move the raw log into a database and put Looker Studio on top. Until then, Sheets is the lowest-friction dashboard that exists.

Won't refunds and edited orders make the numbers drift?

Only if you append blindly. The build upserts on order_id: a new order appends a row, but an edit or a refund finds the existing row and updates it — refunds write into a refunded_amount column rather than adding a phantom negative sale. Because Shopify delivers webhooks at-least-once and orders/updated fires on every change, that match-then-update rule is what keeps one order as exactly one row. Get that right and net sales on the dashboard tracks Shopify instead of slowly diverging.

What happens if n8n is down or a webhook gets missed?

The sheet self-heals. Webhooks do get dropped, so the build doesn't trust them alone — a scheduled job runs every fifteen minutes, asks the Admin API for every order changed since its last successful run, and upserts each one through the same order_id logic. Anything the webhook missed during an outage gets pulled in on the next pass. A 'last updated' cell on the dashboard reads the newest sync time, so if the whole pipeline ever stalls, you see a stale timestamp rather than mistaking an empty hour for a slow sales day.

Will the sheet get slow or hit a limit?

It can, and the build plans for it. Google caps a spreadsheet at 10 million cells and the Sheets API at roughly 300 writes per minute per project, so a log that grows forever will eventually crawl. The fix is to keep the live log to your reporting window and archive older rows to a 'history' tab or a separate file, with the dashboard pointed at the live window. If you're doing hundreds of orders a day, that's the honest signal to graduate to a database — and we'll tell you when you're there rather than selling you a spreadsheet that's fighting its own size.

Can I build this myself, or should you build it?

Every tab, column, webhook topic, field mapping, and edge case is written out above on purpose — if you've got a few hours and some API comfort, build it yourself and own it outright. If you'd rather not own the HMAC check, the upsert-on-order_id logic, the reconcile job, and the QUERY formulas, we'll build it on the Shopify and Google accounts you already use, reconcile it against your Shopify Analytics, document every piece, and hand you the keys. You own the workflow and the sheet — no monthly retainer, and nothing stops working if you walk away. It starts with a free 20-minute teardown of the report you rebuild by hand, not a contract.

Reporting & dashboards

Send a daily revenue digest to Slack

Every morning starts the same way: open Shopify, wait for the dashboard to load, read yesterday's number, close the tab. This does it for you. Once a day it pulls yesterday's orders from Shopify, totals net sales, order count, AOV, and units, compares it to the same weekday last week so the number actually means something, and posts a clean digest to a Slack channel before you've finished your coffee. Build it yourself with the steps below, or we build it on your own accounts, wire the math and the comparison, and hand you the keys so you own it with no monthly fee. Free teardown of what you check by hand every morning first.

  • Shopify
  • Slack

Intermediate2-4 hrs

Reporting & dashboards

Automate a daily ad spend vs revenue report

To know if yesterday's ad spend paid off, someone opens Ads Manager for the spend, opens Shopify for the revenue, and does the division in their head — across two timezones and two definitions of a 'sale.' This does it for you. Once a day it pulls yesterday's spend from Meta, yesterday's revenue from Shopify, lines the two day-windows up so they're actually comparable, computes blended ROAS (and shows Meta's own attributed ROAS next to it so the gap is visible), and posts one clean line to Slack. Build it yourself from the steps below, or we build it on your own accounts, reconcile the windows and the currency, and hand you the keys so you own it with no monthly fee. Free teardown of how you check whether ads are working first.

  • Meta Ads
  • Shopify
  • Slack

Intermediate3-5 hrs

Reporting & dashboards

Auto-alert on a sudden drop in sales

When checkout breaks or an ad set gets paused, orders don't error out — they just stop, and the store looks fine until the next morning's numbers come in low. This watches the order flow in near-real-time, learns what a normal Tuesday 2pm looks like for your store, and pings a Slack channel within the hour when sales fall off a cliff against that baseline — with a triage checklist so someone can go look before the lost revenue stacks up. Build it yourself with the steps below, or we build it on your own accounts — the baseline, the sustained-breach logic, the cooldown so it never spams you — and hand you the keys so you own it with no monthly fee. Free teardown of where your store actually goes quiet first.

  • Shopify
  • Slack

Advanced4-8 hrs

Reporting & dashboards

Track refund rate and reasons in a dashboard

Your refunds line goes up and all you can see is the dollar total — not what percentage of orders it is, whether it's getting worse, or which products and reasons are driving it. This wires the answer up: a Google Apps Script pulls every refund and its return reason from the Shopify Admin API into a logged-and-normalized refunds tab, alongside an orders tab for the denominator, and a dashboard tab built on QUERY formulas shows refund rate over time, reasons ranked, and the products doing the returning — recalculating on its own as new rows land. Build it yourself with the steps below, or we build it on your own Shopify and Google accounts, reconcile it against your Shopify reports, and hand you the keys so you own it with no monthly fee. Free teardown of your current refund reporting first.

  • Shopify
  • Google Sheets

Intermediate3-5 hrs