How do you analyze point-of-sale data in Metabase?
POS reporting is built for a single location and a single day: an end-of-day total, a shift report, one store's trend. The questions that decide anything are comparisons — this location against that one, this month against the same month last year, menu mix against food cost, labor against sales. Those need the data somewhere it can be joined and kept. In Metabase that means flattening checks and items into a SQL database once, then building on top.
TL;DR — Flatten deeply nested POS payloads into three tables:
pos_checks (one row per check or sale), pos_check_items (one row per line item), and pos_locations. Stamp every row with business date, not calendar date, and keep voids, comps, and discounts in separate columns.Which tools does this cover?
- Toast — checks, check items (selections) and modifiers, discounts, comps, and voids, payments and tender types
- Lightspeed — sales, sale line items and discounts, products and product categories, outlets and registers
- Square — payments and POS for retail, food service, and mobile sellers; same check-and-item shape, with settlement and processing fees attached
The three differ mostly in what nests inside what. Toast is restaurant-shaped — an order holds checks, a check holds selections, a selection holds modifiers — so the modeling job is flattening that once, correctly. Lightspeed X-Series is retail-shaped, with sales, line items, products, outlets, and registers, and it carries item cost alongside price, which makes gross margin computable without a separate inventory feed. Square sits across both worlds and brings the fee side with it.
If you also sell online, the same orders-and-items thinking applies to your store — see the e-commerce category and Shopify, and use one definition of order value across both or don't compare them.
What is the shared POS data model?
Every POS in this category maps onto three tables. Build them as models, not per-question SQL against raw JSON:
| Table | Grain | Key columns | Used for |
|---|---|---|---|
pos_checks | One row per check (Toast) or sale (Lightspeed) | check_id, location_id, business_date, opened_at, closed_at, status, gross_sales, discount_amount, net_sales, tax_amount | Net sales, average check, daypart trends |
pos_check_items | One row per line item on a check | item_id, check_id, business_date, product_id, item_name, category, quantity, unit_price, unit_cost, discount_amount, net_amount | Menu and category mix, item margin |
pos_locations | One row per location or outlet | location_id, name, timezone, region, opened_on | Like-for-like location comparisons |
Settle three conventions before the first dashboard. Business date on every row, so late-night trade lands on the right trading day. One stance on tax — Lightspeed X-Series reports both tax-exclusive and tax-inclusive totals, and choosing per card is how two dashboards end up disagreeing about revenue. And a fixed status filter for what counts as a sale: closed sales only, with parked tabs and voided checks excluded in the model layer so no question can forget.
How do you connect a POS to Metabase?
- Scheduled API pull — the main route for both tools. Toast: nightly
ordersBulkpulls per location, flattened to check and item tables, within 20 requests per second globally and 5 per second per location. Lightspeed X-Series: pagedsalespulls against a pinned date-based API version, authenticated through a private app. - Connector route, with caveats — Airbyte's community
source-lightspeed-retailcovers X-Series across 24 streams, but every stream is full-refresh only, so schedule it off-hours. Toast has no managed connector on Airbyte or Fivetran; Fivetran publishes a Connector SDK template you build and deploy yourself. - CLI route — for a first look, flatten an export to CSV and load it with
mb upload csv, which creates a table and a model in one step. Good for proving the model shape before you invest in a pipeline.
Metabase reads the resulting SQL database. Per-tool setup lives on the Toast, Lightspeed, and Square pages.
What can you analyze across POS platforms?
- Average check size — net sales per non-voided check, by daypart, dining option, and outlet
- Net sales by location and business date — the like-for-like comparison the POS itself won't give you
- Menu and category mix — units and revenue per item, with week-over-week movement to catch items quietly fading
- Discount, comp, and void rates — three separate leaks, each with its own owner
- Gross margin — item revenue less item cost, which Lightspeed line items support directly and Toast needs recipe data for
- Platform fee rate — processing and marketplace commission as a share of gross sales; delivery commission dwarfs card processing
- Repeat purchase rate — where loyalty or a customer record identifies the guest; state the coverage
Which dashboards should you build?
- Restaurant & POS sales — net sales by location and business date, average check by daypart, discounts and voids
- Product performance — menu and category mix, units, and item-level margin
- Store overview — for operators who sell online too, on one shared definition of order value
- Repeat purchase — return rate among identified guests, with coverage shown next to it
Common mistakes
Grouping sales by calendar date instead of business date.→ A check closed at 1:30am belongs to the previous trading day. Use the POS's business date everywhere, including filters, or every weekend comparison is wrong.
Netting voids, comps, and discounts into one number.→ They are three different things — a sale that never happened, a sale given away, and margin given up. Keep them in separate columns and report each rate on its own.
Blending dine-in and third-party delivery in one average.→ Marketplace orders have different ticket sizes and lose a large share to commission. Segment by dining option and report delivery net of commission.
Mixing tax-exclusive and tax-inclusive totals across cards.→ Pick one convention in the model, name the column so the choice is visible, and keep tax as its own column.
Building item mix on unflattened order JSON.→ Orders nest checks, checks nest items, items nest modifiers. Flatten once in the pipeline; flattening per card guarantees inconsistent numbers.
Assuming one Lightspeed pipeline covers every Lightspeed.→ X-Series (retail) and K-Series (hospitality) are different products with different APIs and hosts. Confirm which one you're on before building.
Related
Analytics
Integrations
Dashboards
Metrics
FAQ
Why not just use the reporting inside the POS?
Because POS reporting is built for one location and one day — end-of-day totals, a shift report, a single-store trend. The questions that actually drive decisions compare things: this location against that one, this month against the same month last year, menu mix against food cost, labor against sales. Those need the data somewhere it can be joined to other systems and kept beyond the vendor's retention window. That is what a SQL database plus a POS sales dashboard gives you.
Do Toast and Lightspeed have MCP servers?
Neither ships an MCP server for your data (verified July 2026). Toast has none at all — community servers exist but are not vendor-affiliated, and one popular search result named “toast-mcp” is an unrelated Windows notification server. Lightspeed publishes only a documentation MCP server for the K-Series docs portal, which searches the API reference and returns docs, not sales. Both guides are therefore API-led: a scripted export an assistant can write and run.
Do I need to be a Toast partner to get API access?
No — that belief is out of date. Toast API access is no longer partner-only: restaurants on an RMS Essentials or higher subscription can self-generate credentials from the Toast Partner Integrations page in Toast Web, using an employee account with the Manage Integrations permission. Self-serve access is read-only by design — every granted scope ends in
:read — which suits analytics exactly. Two caveats: self-serve Standard API access is US-only at present, and there is no managed connector, only a Fivetran Connector SDK template you build and deploy yourself. See the Toast guide.Which Lightspeed product am I actually on?
Check before you write any code — the product split is a genuine trap. X-Series is retail point of sale (formerly Vend, on
retail.lightspeed.app); K-Series is hospitality (on lsk.lightspeed.app); R-Series and C-Series are older retail and eCom products. They have different APIs, hosts, and docs. Airbyte's community connector covers X-Series only, with 24 streams and no incremental sync — every stream is full-refresh, so large catalogs need off-hours scheduling. And plan auth around private apps: personal access tokens are being retired, and accounts created after January 2026 get private apps only. Details on the Lightspeed guide.Why does business date matter so much?
Because business date is not calendar date. A check opened at 11:40pm and closed at 1:30am belongs to the prior trading day, and if you group by the timestamp's calendar date those sales land on the wrong day — which makes Friday look weak and Saturday look strong, every single week. Toast gives you
businessDate directly; use it everywhere, including in filters, and derive an equivalent for any POS that doesn't supply one.How should I treat voids, comps, and discounts?
As three different things, never netted into one number. A void removes a check or item that was never really sold — it is a loss-prevention signal. A comp is a real sale given away, usually for service recovery — it is a hospitality cost with an owner. A discount is margin deliberately given up on a sale that still happened — it is a pricing decision. Blend them and you lose all three signals at once. Report each as its own rate against gross sales, by location and by employee where the data allows.