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-downvariation → 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 == 0the right “closed check” rule? Is thestateguard 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_categoryor legacycategory_id? -
Is
gross_sales_moneybefore 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_moneythe 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 viatender.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.sourceforOTHER) — 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.
