How to build Toast sales dashboards in Metabase

# 1. Exchange machine-client credentials for a bearer token
TOKEN=$(curl -s -X POST \
  https://ws-api.toasttab.com/authentication/v1/authentication/login \
  -H 'Content-Type: application/json' \
  -d '{"clientId":"'"$TOAST_CLIENT_ID"'",
       "clientSecret":"'"$TOAST_CLIENT_SECRET"'",
       "userAccessType":"TOAST_MACHINE_CLIENT"}' \
  | jq -r '.token.accessToken')

# 2. Pull yesterday's orders for one restaurant (paged, 5 req/s cap)
curl -s -G https://ws-api.toasttab.com/orders/v2/ordersBulk \
  -H "Authorization: Bearer $TOKEN" \
  -H "Toast-Restaurant-External-ID: $TOAST_RESTAURANT_GUID" \
  --data-urlencode "startDate=$(date -u -v-1d +%Y-%m-%dT00:00:00.000Z)" \
  --data-urlencode "endDate=$(date -u +%Y-%m-%dT00:00:00.000Z)" \
  --data-urlencode "pageSize=100" \
  > toast-orders.json

# 3. Flatten orders -> checks -> selections into a CSV for upload
jq -r '.[] | .checks[]? | [
  .guid, .openedDate, .closedDate, .amount, .totalAmount,
  (.appliedDiscounts | map(.discountAmount) | add // 0),
  (.voided | tostring)
] | @csv' toast-orders.json > toast-pos-checks.csv

# Install the Metabase CLI
npm install -g @metabase/cli

# Log in (opens your browser; requires Metabase v62+)
mb auth login --url https://your-metabase.example.com

# Load a checks export — creates a table AND a model
mb upload csv --file toast-checks.csv --collection root

# Refresh that same table later from a new export
mb upload replace <table-id> --file toast-checks.csv

Create a polished Metabase dashboard for Toast pos sales analytics.
Work end to end: get the data into Metabase if it isn't there yet, then build.

Goal: Help the team understand net sales by location, average check, item mix, discounts and voids, and tender mix from Toast data.

Step 1 — Find or load the data:
- First, check what already exists in Metabase (search for toast tables and
  models). If durable Toast data is already present — a warehouse sync, a
  direct database connection, or an earlier upload — use it and skip to Step 2.
- If nothing is there, pull a scoped, summarized export by scripting the Toast Orders API:
  checks, plus check items (selections) and modifiers, discounts, comps, and voids, payments and tender types.
  Write each result to a CSV, then load it with the Metabase CLI — run
  "mb upload csv --file <export>.csv" so each upload creates a table and a
  ready-to-query model. Use "mb upload replace <table-id> --file <export>.csv"
  to refresh an existing table instead of creating duplicates.

Step 2 — Inspect before querying:
Do not assume exact table or column names. Inspect available fields, products,
locations or countries, currencies, and date ranges before creating trend cards.

Important:
- Build on whatever data is present; don't claim Metabase connects natively to
  Toast — it reads a database or CLI-uploaded tables.
- Separate gross amounts, platform and processing fees, taxes, and refunds into
  distinct columns; never let one card silently mix gross and net.
- A single CSV is a point-in-time snapshot: only build trend cards if there is
  a usable date column or multiple periods have been uploaded.
- Sales trends need complete business days; late-closed checks and end-of-day adjustments mean the last day or two keeps moving.
- If a metric can't be computed from the data present, say so on the card
  instead of approximating it.

Dashboard title: Toast POS Sales Overview

Sections:
1. Executive summary: Net sales; Average check; Checks; Discount rate;
   Void rate.
2. Sales: Net sales by location and day; checks by daypart; year-over-year.
3. Menu: Top items by revenue and units; category mix; modifier attach rate.
4. Leakage: Discounts and comps; voids by location; refunds after close.
5. Settlement: Tender mix; processing fee rate; tips; deposits vs. sales.

Filters: Date range, Location, Daypart, Dining option, Revenue center, Employee.

Output: Build the dashboard if you have permission; otherwise provide the exact
questions, SQL, model definitions, and layout. Include caveats for any metric
that cannot be calculated from the available data.

The core trading view, on business date, with voids excluded.

WITH local_checks AS (
  SELECT
    c.business_date,
    c.net_sales,
    -- Daypart is a wall-clock idea, so read the hour in the venue's
    -- local time. Toast timestamps are UTC; convert with the location
    -- timezone before extracting the hour (skip if already local).
    EXTRACT(
      HOUR FROM (c.opened_at AT TIME ZONE 'UTC' AT TIME ZONE l.timezone)
    ) AS local_hour
  FROM pos_checks c
  JOIN pos_locations l ON l.location_id = c.location_id
  WHERE c.is_voided = false
    AND c.business_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
  business_date,
  -- Late night covers 22:00–04:59 so a post-midnight close counts as
  -- late night, not breakfast.
  CASE
    WHEN local_hour BETWEEN 5 AND 10 THEN 'breakfast'
    WHEN local_hour BETWEEN 11 AND 15 THEN 'lunch'
    WHEN local_hour BETWEEN 16 AND 21 THEN 'dinner'
    ELSE 'late night'
  END AS daypart,
  COUNT(*) AS checks,
  ROUND(SUM(net_sales), 2) AS net_sales,
  ROUND(AVG(net_sales), 2) AS avg_check,
  ROUND(
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY net_sales)::numeric, 2
  ) AS median_check
FROM local_checks
GROUP BY business_date, daypart
ORDER BY business_date DESC, daypart;

Which items carry the menu, and which are quietly fading.

WITH weekly AS (
  SELECT
    date_trunc('week', business_date)::date AS week,
    item_name,
    category,
    SUM(quantity) AS units,
    SUM(net_amount) AS revenue
  FROM pos_check_items
  WHERE is_voided = false
  GROUP BY 1, 2, 3
)
SELECT
  week,
  item_name,
  category,
  units,
  ROUND(revenue, 2) AS revenue,
  ROUND(
    100.0 * (units - LAG(units) OVER (
      PARTITION BY item_name ORDER BY week
    )) / NULLIF(LAG(units) OVER (
      PARTITION BY item_name ORDER BY week
    ), 0), 1
  ) AS units_wow_pct
FROM weekly
ORDER BY week DESC, revenue DESC;

Three different leaks, kept separate so each has an owner.

SELECT
  l.name AS location,
  date_trunc('month', c.business_date)::date AS month,
  ROUND(
    100.0 * SUM(c.discount_amount)
    / NULLIF(SUM(c.gross_sales), 0), 2
  ) AS discount_rate_pct,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE c.is_voided)
    / NULLIF(COUNT(*), 0), 2
  ) AS void_rate_pct,
  ROUND(SUM(c.gross_sales) FILTER (WHERE c.is_voided), 2) AS voided_value
FROM pos_checks c
JOIN pos_locations l ON l.location_id = c.location_id
GROUP BY l.name, month
ORDER BY month DESC, discount_rate_pct DESC;