Track refund rate and reasons in a dashboard

01/The build

How to build it

1. 01

   Pin the metric definition and the reason list before you touch the API

   Needs a human

   Decide two numbers up front, because 'refund rate' means nothing until you say so. The headline rate is refunded orders ÷ total orders over the same window; carry a second, dollar-based rate (refunded amount ÷ net sales) right beside it, because a store with many tiny partial refunds and one with a few full refunds look identical on a count and very different on dollars. Then fix a closed list of about eight reason buckets — for apparel that's usually Too small, Too large, Defective/damaged, Not as described, Wrong item sent, Changed mind, Arrived late, and Other — because a reason you can't put in a fixed column is a reason you can't chart. Write these down; every later step maps to this list.

2. 02

   Make Shopify actually capture a structured reason

   Needs a human

   This is the part everyone skips, and it's why most refund dashboards are just a dollar total. A plain refund (Orders → Refund) records only a free-text note, so if that's your flow you have to enforce one rule the whole team follows: the refund note must begin with a code from your fixed list — 'TOO_SMALL — ...' — or the order gets tagged refund-reason:too-small. Better, switch returns onto Shopify's native Returns flow, where each return line item carries a structured return_reason enum (SIZE_TOO_SMALL, SIZE_TOO_LARGE, DEFECTIVE, WRONG_ITEM, NOT_AS_DESCRIBED, STYLE, COLOR, UNWANTED, OTHER) plus an optional note — a real dropdown captured at the source instead of a sentence you have to parse later. Pick one source of the reason and make it the only one; a dashboard built on inconsistently-written notes will always have a giant 'Other' bar.

3. 03

   Lay out three tabs: orders_log, refunds_log, dashboard

   Needs a human

   Split the sheet so raw data and presentation never mix. orders_log is append-only, one row per order — order_id, order_name, created_at (store-local), net_total, channel — and exists purely to be the denominator for the rate. refunds_log is one row per refunded line item, not per refund, so the product and SKU cuts work: refund_id, line_id, order_id, order_name, refund_created_at, refunded_amount, refund_kind (full/partial), reason (normalized to your list), reason_raw, product_title, sku, quantity, days_since_order, channel, last_synced. One row per line item is the call that lets you answer 'which product gets returned and why' instead of only 'how much money went out the door'. The dashboard tab never holds data — it only reads these two with formulas.

4. 04

   Pull orders and refunds with a time-driven Apps Script

   Runs itself

   From the sheet, open Extensions → Apps Script and write a function that calls the Shopify Admin API on a time-driven trigger every 30 minutes — refund rate is an analytics number you read weekly, so polling is plenty and keeps the whole build to just Shopify and Google. Page through orders with updated_at_min set to the last successful run (saved in PropertiesService), following the Link header until there's no next page, and keep the API token in Script Properties, never in a cell. Append each order to orders_log for the denominator, and for every order that has entries in its refunds[] array, hand each refund to the logging step. If you already run n8n, the refunds/create webhook is the near-real-time alternative — but for this metric the scheduled pull is the simpler, honest default.

5. 05

   Write one normalized row per refunded line item

   Runs itself

   For each refund, walk its refund_line_items and write a row per line, so a three-item refund becomes three rows tied to one refund_id. Pull the refunded amount from the refund's transactions (the money actually returned, which for a partial refund is less than the line's full price), set refund_kind by comparing the refunded total to the order total, and compute days_since_order from the order's created_at. Map the reason in the same pass — Shopify's return_reason enum or your note/tag convention — into your fixed bucket, and write the original text into reason_raw so nothing is lost. Handle the refunds that have no product line at all: a shipping-only refund or a goodwill credit has no refund_line_items, so log it as a single row with reason 'Goodwill/adjustment' rather than dropping it, or your dollar rate will understate.

6. 06

   Upsert on refund_id + line, and bucket anything uncoded

   Runs itself

   Because the poller re-reads recently-updated orders on every run, the same refund will come back around — so before writing, look for the refund_id and line_id already in the log and update that row in place instead of appending a duplicate. A refunded total that keeps growing every 30 minutes is the classic symptom of skipping this. Any reason that doesn't map cleanly to your fixed list goes to a single 'Uncoded' bucket, never silently dropped — that bucket is the honest measure of how well the capture step is working. If 'Uncoded' is 40% of your refunds, the dashboard is telling you to fix the capture, not the products.

7. 07

   Build the dashboard on live QUERY formulas

   Runs itself

   On the dashboard tab, compute the rate by joining the two logs over matching date windows: refunded orders (distinct order_id in refunds_log for the week) ÷ orders (rows in orders_log for the week), with the dollar rate as refunded_amount ÷ net_total beside it. Add the cuts that actually drive decisions — reasons ranked as a share of refunds via a QUERY group-by, the top products and SKUs by both refund count and refunded dollars, the full-versus-partial split, and average days_since_order. Put a SPARKLINE of weekly refund rate across the top so a slow creep is visible at a glance, and drop a single cell showing the 'Uncoded' share as your trust gauge. Because every figure is a formula over the logs, each new row recalculates the whole dashboard on its own.

8. 08

   Backfill once, surface freshness, and know the ceiling

   Needs a human

   Run the script once with a wide updated_at_min to backfill a few months of history so the trend has a baseline, then let the 30-minute trigger keep it current. Put a cell at the top of the dashboard reading the newest last_synced — 'Updated 12 min ago' — so a stalled script shows as a stale timestamp instead of looking like a week with no returns. Mind the two real limits: Apps Script caps a single execution at six minutes, so a large backfill has to run in dated chunks, and a Google Sheet tops out at ten million cells. Past a few hundred refunds a month, that's the honest point to move the log into a database with a BI tool on top — and we'll tell you when you're there rather than selling you a spreadsheet fighting its own size.

02/The stack

What it’s built on

Shopify

System of record for orders and refunds. The Admin API's orders endpoint (filtered by updated_at_min) carries each order's refunds[] array with the refunded line items and amounts, and Shopify's native Returns flow attaches a structured return_reason to each returned line — which is what makes a reason you can chart rather than parse out of a free-text note.

Google Sheets

Holds the whole thing: an append-only orders_log and refunds_log, plus a dashboard tab whose QUERY, SPARKLINE, and group-by formulas recalculate the rate and the reason breakdown the instant a row lands. Apps Script — built into Sheets — is the poller, so the entire build lives in tools you already have, with no third service.Google Sheets and Apps Script are free within generous quotas; the honest limits are Apps Script's six-minute-per-run cap and the ten-million-cell ceiling per file. That comfortably covers most stores' refund volume — a very high-volume shop eventually wants a real database behind it.

03/Questions

Before you build