POS → Dashboard Mapping Guide (essential fields, transformations, and common pitfalls)

Why mapping POS to dashboards matters

Dashboards are only as useful as the data behind them. Poor field mapping or sloppy transformations produce confusing daily reports that frontline teams distrust and ignore. This guide helps operators and analysts extract the right POS fields, normalize them, and turn them into reliable KPIs that support timely decisions on the floor.

What this guide gives you

• A prioritized list of POS fields to capture
• Recommended transformations for common daily metrics
• Concrete examples and sample SQL snippets you can adapt
• A data quality checklist and monitoring suggestions
• Common pitfalls and how to prevent them

Priority fields to extract

Start by ensuring your extract includes these fields (or equivalents) at the lowest meaningful grain — usually the line-item and payment level. If a field is missing, plan how to derive it reliably.

• Order and line-item fields: order_id, line_id, menu_item_id (SKU), item_name, item_price, quantity, modifiers (eg. half, extra), portion_size
• Timestamps: order_timestamp, item_sent_to_kitchen_ts, payment_timestamp. Capture timezone and store as UTC whenever possible.
• Transaction context: check_id / receipt_id, server_id, table_id, terminal_id, channel (dine-in/takeout/delivery), shift_id
• Guest and covers: covers (guest count) or an approach to infer covers (eg. items flagged as covers or guests per check)
• Payments: tender_type (cash/credit/gift), tender_amount, card_type if available, tips, rounding adjustments
• Promotions/discounts: discount_id, discount_amount, discount_reason (comp, promo, employee), loyalty_redemption
• Voids/refunds: void_flag, void_reason, refund_amount, refunded_line_id
• Service charges and fees: service_fee_amount, fee_type (gratuity, surcharge)

Recommended transformations for reliable daily KPIs

Below are common KPIs and how to compute them from raw POS fields.

1) Sales and hourly curve

Aggregate net sales by hour using payment_timestamp (or order_timestamp if payments often occur later). Net sales = sum(item_price*quantity) - discounts - comps + service_fees.

2) Covers and average check

Use covers if POS has accurate guest count per check. If not, infer covers conservatively (eg. max(1, number_of_items / typical_items_per_guest)). Average check = net sales / covers (or per check if covers absent).

3) Items per cover

items_per_cover = total_items_sold / covers. Use normalized item counts (modifiers that change portion size should adjust the item count).

4) Void rate and comp dollars

Void rate = number_of_voided_lines / total_lines. Comp $ = sum(discount_amount where discount_reason = 'comp'). Track both count and dollars.

5) Refunds and chargebacks

Separate refunds that reduce net sales from tips adjustments. Monitor refunds as a percent of sales and by reason.

6) Mix and menu-level KPIs

Compute margin drivers per item: item_sales, item_cost (if inventory/cost mapping exists), contribution margin = item_sales - item_cost. Identify top sellers by count and by margin contribution.

Concrete SQL examples (adapt these to your schema)

These snippets are intentionally simple. Use your environment's SQL dialect and join keys.

-- Hourly sales curve (payment timestamp)
SELECT
  DATE_TRUNC('hour', payment_timestamp) AS hour,
  SUM(item_price * quantity) - SUM(discount_amount) + SUM(service_fee_amount) AS net_sales
FROM pos_line_items
GROUP BY 1
ORDER BY 1;

-- Average check and covers (per day)
SELECT
  DATE(order_timestamp) AS day,
  SUM(net_sales) / SUM(coalesce(covers, inferred_covers)) AS avg_check,
  SUM(coalesce(covers, inferred_covers)) AS total_covers
FROM (
  SELECT
    order_id,
    DATE(order_timestamp) AS order_date,
    SUM(item_price * quantity) AS sales,
    SUM(discount_amount) AS discounts,
    SUM(service_fee_amount) AS fees,
    SUM(item_price * quantity) - SUM(discount_amount) + SUM(service_fee_amount) AS net_sales,
    MAX(covers) AS covers,
    CASE WHEN MAX(covers) IS NULL THEN GREATEST(1, SUM(quantity) / 2.5) ELSE NULL END AS inferred_covers
  FROM pos_line_items
  GROUP BY order_id, DATE(order_timestamp)
) orders
GROUP BY DATE(order_timestamp);

Common pitfalls and how to avoid them

• Double-counting discounts: Discounts may appear at both line and check levels. Decide on a single canonical application (prefer check-level discount allocation back to lines proportionally when you need per-item margin).
• Missing modifiers or portion info: Treat modifiers that change price or portion as separate fields (modifier_id, modifier_price, portion_multiplier) rather than embedded text.
• Inconsistent item naming: Normalize menu_item_id and maintain a master menu table to avoid aliasing ("Fried Rice" vs "Fried Rice - GF").
• Timestamp drift and late payments: Use both order and payment timestamps. Build rules for sessionization (eg. assign items to the shift of order_timestamp but attribute sales to payment_timestamp for cashflow metrics).
• Channel confusion: Clearly tag orders by channel (in-house, third-party delivery, direct delivery). Third-party delivery fees and commissions must be accounted for separately to avoid overstating net margins.
• Voids versus returns: Voided items removed before the check is closed differ from refunds processed after. Track both separately with reasons and actor (server vs manager).

Data quality checklist (daily)

1. Are hourly sales totals consistent with POS daily close totals? (tolerance e.g. ±1%)
2. Are there any orders with NULL or future timestamps?
3. Are discount totals and tender totals aligned with net sales? (discounts + net_sales + taxes = gross?)
4. Are top-selling items stable or did naming changes create artificial spikes?
5. Are voids/comp rates within expected bounds for the location/shift?
6. Do payment tenders sum to expected deposits? Flag large unallocated tip or cash variances.

Operational suggestions for successful mapping

• Start simple: build daily leaderboards and one reliable hourly curve first. Expand to margins and item-level profitability once the basics are trusted.
• Keep a mapping registry: document each POS field, its meaning, sample values, and transformation rules. Store it where analysts and operators can edit it when menus or POS configs change.
• Version your transformations: tag queries and derived tables with a schema and transformation version so you can trace changes that affect historical KPIs.
• Automate QA checks and alert when key ratios (eg. refunds/sales) exceed thresholds.
• Collaborate with ops: validate inferred covers, modifiers, and comp rules with shift leads before rolling metrics to managers.

Next steps and templates

Use this guide as a checklist when connecting a new POS or building a dashboard. Typical next deliverables:

• POS-to-master-menu mapping table (menu_item_id → canonical_item_id → cost)
• Discount allocation rules (check-level → line-level)
• Daily QA queries that run after ETL and report anomalies

If you'd like, the next iteration can provide an interactive mapping form (to capture a new POS schema), a reusable SQL library for common POS systems, and ready-to-deploy dashboard templates for hourly sales, covers, and void trends.