Replicating Dashboard "Sales Summary" + "Category Sales" from the Orders API

Questions
1

Replicating Dashboard “Sales Summary” + “Category Sales” from the Orders API

Category: Orders API / Reporting

I pull orders via SearchOrders, store them in my own DB, and compute daily per-location reports myself. I want to match the Dashboard “Sales Summary” and “Category Sales” using only Orders API (+ Payments API where needed).

I don’t want the Reporting API — I already store the raw orders and want the exact field-level formulas.

Our numbers today are close but not exact. Pseudocode below shows what we currently do — please point at what’s wrong.

What we fetch + filter today

POST /v2/orders/search, looped per (location_id, business_date), paged by cursor:

{ "location_ids": ["<LOC>"],
  "query": { "filter": { "date_time_filter": {
    "created_at": { "start_at": "<startUtc>", "end_at": "<endUtc>" } } } } }

Filter after fetch: keep order if state ∈ {OPEN, COMPLETED} AND net_amount_due_money == 0 (our “closed check”).

Fields we read:

  • Order: state, created_at, source.name, net_amount_due_money, total_money, total_tax_money, total_tip_money, total_discount_money, total_service_charge_money, return_amounts.{total,tax,tip,discount}_money, tenders[].{id,type,amount_money,other_details.source}, refunds[].{tender_id,amount_money}

  • Line item: {name, variation_name, quantity, gross_sales_money, catalog_object_id} + catalog drill-down variation → item → reporting_category → name

money() below = amount in cents; absent → 0.

Questions (with current pseudocode)

1. Reporting day = 3 AM–3 AM Pacific

startUtc = toUtc(date     @ 03:00 PT);  endUtc = toUtc(date+1 @ 03:00 PT)
keep created_at in [startUtc, endUtc)

  • Does the Dashboard bucket on created_at, closed_at, or something else?

  • Is the day-cutoff (reporting hours) seller-configurable and readable via API?

2. Closed checks

isClosedCheck = state ∈ {OPEN, COMPLETED} AND money(net_amount_due_money) == 0

  • Is net_amount_due_money == 0 the right “closed check” rule? Is the state guard needed?

  • Does Sales Summary include only closed checks, or open amounts too?

3. Category Sales (items sold + gross)

cat = reportingCategory(line_item.catalog_object_id)
group by cat: sum(int(quantity)), sum(money(gross_sales_money))

  • Group by reporting_category or legacy category_id?

  • Is gross_sales_money before discounts and tax?

  • How do returned line items affect category qty/gross? How is “Uncategorized” handled?

4. Gross Sales + order count

orderGross  = (money(total_money) - money(total_tax_money) - money(total_tip_money)) + money(total_discount_money)
orderNet    = (money(total_money) - money(total_tax_money) - money(total_tip_money))
            - (money(return_amounts.total) - money(return_amounts.tax) - money(return_amounts.tip))
orderCount += (orderNet >= 0) ? +1 : -1

  • Should Gross Sales instead be Σ line_items.gross_sales_money (before discounts/returns/tax/tip)?

  • How are fully-returned / exchanged orders counted?

5. Item sales + service charges

serviceCharges += money(total_service_charge_money)   // tracked only, not in gross/net

  • Is “Item Sales” = sum of line-item gross (pre-discount), separate from “Gross Sales”?

  • Are service charges in Gross Sales or separate? Is tax-on-service-charge included?

6. Returns, Discounts & Comps

returns   += (money(return_amounts.total) - money(return_amounts.tax) - money(return_amounts.tip)) + money(return_amounts.discount)
discounts += money(total_discount_money) - money(return_amounts.discount)

  • Is return_amounts.total_money the right “Returns” basis (tax-inclusive)?

  • Are comps separate from discounts? If so, which field identifies a comp?

7. Net Sales

netSales += orderNet      // see §4
  • Is Net Sales = Gross − Discounts&Comps − Returns, excluding tax/tip?

8. Deferred sales — we don’t compute this yet

  • Is “Deferred Sales” = gift cards sold? Identify via line-item item_type, or the Gift Cards API?

  • Is it excluded from Gross/Net and added into Total Sales?

9. Taxes

taxes += money(total_tax_money) - money(return_amounts.tax)
  • Correct? Include tax on service charges?

10. Tips

tips += money(total_tip_money) - money(return_amounts.tip)
  • Correct?

11. Refunds by amount — we use it only to net payments, not as its own line

for refund in refunds: payments[ tenderMap[refund.tender_id] ] -= money(refund.amount_money)

  • Is “Refunds by amount” = Σ refunds[].amount_money?

  • Confirm it’s distinct from Returns (e.g. exchanges: returned-item value ≠ net money refunded), so it must not be derived from return_amounts?

12. Total Sales — formula to validate

total_sales = net_sales + deferred_sales + taxes + tips - refunds_by_amount
  • Is this the Dashboard’s definition? If not, what is it?

13. Total payments collected — not emitted yet

  • Is it Σ tenders[].amount_money - Σ refunds[].amount_money?

  • Or from Payments API (Payment.total_money), joined via tender.payment_id / Payment.order_id?

14. Payment methods (card / cash / gift card redeemed / other)

for tender in tenders:
    method = (type == OTHER && other_details.source) ? source : type
    payments[method] += money(tender.amount_money)
// CASH→Cash, CARD→Credit, SQUARE_GIFT_CARD→GiftCard, DOORDASH/UBEREATS/…→channel, else Other
// refunds subtracted per method via refund.tender_id (see §11)

  • Split driven by tenders[].type (+ other_details.source for OTHER) — correct?

  • Is “gift card redeemed” just type == SQUARE_GIFT_CARD (vs gift-card sales in §8)?

15. Fees (processing / cost-plus) — not captured yet

  • Orders carry no fees, right? Use Payments API Payment.processing_fee[] (amount_money, type ∈ {INITIAL, ADJUSTMENT})?

  • Does type = ADJUSTMENT = cost-plus/interchange adjustment, and can it post on a later date (so I should re-pull a trailing window)?

  • Any API for the fee schedule itself? Are fees credited back on refunds?

TL;DR: I want the exact field-level formula for each Sales Summary line + Category Sales column, the right timestamp/day-cutoff, the closed-check rule, and confirmation that deferred sales and fees need the Gift Cards / Payments APIs.

2

Thanks for the thorough write-up!

Before I dive into field-level corrections, I want to gently push back on one thing: have you scoped this effort with the Reporting API? I know you mentioned not wanting it, but it was designed explicitly for this use case. We have prebuilt Views that automatically compute the same Sales Summary and Category Sales figures the Dashboard shows so you don’t have to sort out manual formulas, edge cases around comps vs discounts, no guessing at day cutoffs, etc.

The Reporting API is both faster to implement and self-maintaining as it inherits Dashboard formula changes automatically. Could you share a bit more about your hesitation around using it?

3

Hi Ashley, thanks for the nudge — that’s fair, and I’m genuinely open to switching if it checks out. Let me share where my hesitation comes from, framed as the conditions I’d need to satisfy:

1. Custom business-day cutoff (3 AM–3 AM PT). My original blocker was that the reporting timeframe defaults to a calendar day (12:00 AM–11:59 PM). Our business day runs 3:00 AM–3:00 AM Pacific (sales between midnight and 3 AM belong to the prior day). Can the Reporting API bucket on a custom day-cutoff like that, or apply an offset — and does it match whatever the Dashboard “reporting hours” setting does? If I can’t reproduce the 3 AM boundary exactly, the totals won’t line up with what we report internally.

2. Stability. Is the Reporting API still in beta/alpha? A big reason I built on the Orders API is longevity — I don’t want a report that suddenly breaks or changes shape on us. What’s the current support status?

3. Granularity — per-order detail with exact timestamps. This is probably the deciding factor. We don’t just need daily roll-ups; we need hourly-bucketed sales and product mix, which requires order-level (and ideally line-item-level) rows carrying the exact order datetime. The Views look pre-aggregated — can the Reporting API return per-order / per-line-item granularity with timestamps, or is it summary-only? If it’s only aggregated, we’d still need the Orders API for the hourly breakdown.

4. Migration cost. Our pipeline is already built around storing raw orders. That’s not a hard no — if the three points above check out (custom cutoff, stability, and order-level granularity), we’d seriously consider moving the summary figures to the Reporting API and keeping Orders only where we need the detail.

Appreciate any guidance on those.