Data Mapping & KPI Catalog

Why this catalog matters

Teams need a clear map of where numbers come from, consistent KPI definitions they can trust, and simple rules for turning raw system events into daily dashboard actions. Without that map, reports disagree, managers lose trust, and frontline decisions suffer. This catalog is a practical, implementable reference you can use during ETL design, dashboard builds, audits, and training.

What this guide provides

• A concise mapping of common data sources to the fields you actually need and example column types.
• Recommended transformation rules and canonical KPI formulas so different locations and systems report the same way.
• Practical transform examples and simple SQL/SQL-like snippets you can adapt.
• Example dashboard widgets and the data each widget requires.
• Daily and weekly data quality checks, owner responsibilities, and a governance flow for mapping changes.
• Next steps for turning the catalog into a living, ownable asset (including interactive mapping and submission ideas).

Common data sources and the fields to extract (with example types)

Start by identifying which systems produce the following core records and the minimum fields to extract from each. Example SQL-friendly column types are shown in parentheses to help designers and engineers.

• Point-of-Sale (POS): ticket_id (string), order_time (timestamp), location_id (string), register_id (string), server_id (string), item_id (string), item_name (string), modifier (string), unit_price (decimal), quantity (integer), discount_amount (decimal), refund_flag (boolean), payment_type (string), net_sale_amount (decimal), ticket_total (decimal), covers (integer)
• Inventory / Recipe System: item_id (SKU string), on_hand_qty (decimal), unit_cost (decimal), unit_of_measure (string), recipe_id (string), recipe_ingredient_id (string), ingredient_qty_per_recipe (decimal), yield_factor (decimal)
• Payroll / Time & Attendance: employee_id (string), clock_in (timestamp), clock_out (timestamp), scheduled_hours (decimal), pay_rate (decimal), labor_cost (decimal), position (string), location_id (string)
• Purchasing / AP: purchase_order_id (string), vendor_id (string), invoice_date (date), received_items (json or exploded rows: sku, qty, unit_cost), freight_allocations (decimal)
• Reservations / Bookings: reservation_id (string), booking_time (timestamp), covers_reserved (integer), source_channel (string)
• Waste / Prep Logs: waste_event_id (string), event_time (timestamp), item_id (string), qty (decimal), reason_code (string), estimated_cost (decimal)
• Front-of-House Feedback / Reviews: feedback_id (string), date (date), ticket_id (string, nullable), rating (integer), comment (text), tags (array)

Practical data model & mapping advice

Maintain a canonical item table that joins POS item identifiers to a canonical menu_item_id and recipe_id. Keep mapping metadata: last_updated, updated_by, mapping_status (mapped/unmapped/seasonal) and location_scope (global/location-specific). Store a simple CSV or a managed table with these columns as a baseline mapping template.

Suggested mapping CSV columns

• pos_item_id, pos_item_name, pos_modifier, canonical_menu_item_id, canonical_name, recipe_id, portion_size, item_category, location_override, mapping_status, mapping_notes, last_updated_by, last_updated_at

Key transformation rules (practical, source-agnostic)

1. Unified time and daypart: normalize timestamps to the location's local timezone; assign each order to a named daypart (breakfast, lunch, dinner, late-night) using a documented daypart table. Store both the raw timestamp and the normalized local timestamp for traceability.
2. Canonical item mapping: map POS item_ids and modifier combinations to a canonical menu_item_id that links to recipe and cost data. Treat modifiers that change price or cost as separate mapped items where appropriate. Record mapping exceptions for seasonal or temporary items and mark them as such.
3. Net vs gross sales: define sales consistently. Example: Net Sales = Gross Sales - discounts - refunds - comps. Record how taxes and surcharges are treated and whether tips are included anywhere in the flow.
4. Labor split: allocate labor cost by shift and department where possible (kitchen vs FOH). When employees work mixed roles, use scheduled hours, timecard role codes, or shift tags to split costs.
5. Inventory cost alignment: link purchases to costing using a chosen inventory valuation method (FIFO, weighted average). Record the method and any assumptions used in the metadata.
6. Recipe yield and prep loss: apply recipe yield factors and trim/waste allowances when calculating theoretical cost of goods sold (CGS). Store yield factors with versioning so historical comparisons remain valid.
7. Exclude non-operational events: filter test transactions, training tickets, or manager comp codes to avoid inflating sales or skewing metrics. Maintain a clear, auditable list of excluded transaction types.

Canonical KPI definitions (with data source notes)

Use simple, repeatable formulas and show the data sources required for each. When possible, store intermediate aggregates (e.g., daily_net_sales, daily_cogs) to speed dashboards and simplify validation.

• Net Sales (period) = SUM(net_sale_amount) from POS where refund_flag = false and excluded_flags = false.
• Covers = COUNT_DISTINCT(ticket_id) when covers is null OR SUM(covers) when covers is present. Prefer SUM(covers) if covers is reliably recorded; otherwise document fallback to COUNT_DISTINCT(ticket_id).
• Average Check = Net Sales / Covers. Document treatment of comps and large group discounts.
• Food Cost % (period) = (Food COGS / Food Sales) * 100. Food COGS = sum of ingredient costs used (recipe yields applied) for sold items. Food Sales = portion of Net Sales attributable to food items as defined in the canonical menu_item table.
• Labor Cost % = (Labor Cost / Net Sales) * 100. Labor Cost should include wages, payroll taxes, and benefits where possible; explicitly state scope used.
• Prime Cost % = ((Labor Cost + COGS) / Net Sales) * 100.
• Sales per Labor Hour = Net Sales / Total Paid Hours (use paid hours not scheduled hours for comparability).
• Inventory Turnover (period) = COGS / Average Inventory Value (define averaging window: weekly/period start+end average).
• Waste Rate = Waste Cost / Total Food Cost OR Waste Qty / Produced Qty — choose one definition and apply it consistently. Document chosen method and typical thresholds.
• Gross Margin per Item = (Item Price - Item Cost) * Quantity Sold. Use canonical item cost after recipe yields and any portioning losses.

Example transform snippets (adapt as needed)

These are conceptual SQL-like snippets for common transforms. Adapt to your SQL dialect or ETL tool.

Normalize time and assign daypart

SELECT order_id,
  order_time AT TIME ZONE location_tz AS local_time,
  CASE
    WHEN local_time::time BETWEEN '06:00' AND '10:59' THEN 'breakfast'
    WHEN local_time::time BETWEEN '11:00' AND '15:59' THEN 'lunch'
    WHEN local_time::time BETWEEN '16:00' AND '21:59' THEN 'dinner'
    ELSE 'late-night'
  END AS daypart
FROM pos_orders;

Map POS items to canonical items

SELECT p.*, m.canonical_menu_item_id, m.recipe_id
FROM pos_line_items p
LEFT JOIN canonical_item_map m
  ON p.item_id = m.pos_item_id
  AND (p.modifier IS NULL OR m.pos_modifier = p.modifier);

Example dashboard widgets and required inputs

• Today vs Yesterday Sales Line – requires POS net sales timestamped by minute/hour and location timezone.
• Top 10 Items by Margin (Week) – requires POS sales, canonical item mapping, and canonical item cost from recipes.
• Labor Heatmap by Shift – requires timecards (clock_in/out), scheduled hours, and sales by hour.
• Food Cost Trend (13 weeks) – requires weekly COGS and food sales.
• Waste Events Log – requires tagged waste entries with reason codes and estimated cost.
• Inventory Snapshot and Turnover – requires on-hand valued inventory and purchase history.

Daily and weekly data quality checks (operational checklist)

Implement automated checks that alert owners. Suggested default thresholds are illustrative — adapt to your business and review during rollout.

• Missing or future timestamps in POS or timecards — flag and quarantine records.
• Duplicate ticket_ids or purchase_order_ids — flag duplicates for reconciliation.
• Negative or zero prices where not expected — flag items with unit_price <= 0 except known promo SKUs.
• Unmapped POS item_ids or modifiers flagged for review — create a daily feed of unmapped items.
• Large single refunds or voids > 5% of daily sales flagged and require a comment/explanation (adjust threshold to local norms).
• Inventory count deviations exceeding a configurable threshold (e.g., 10%) vs expected on-hand calculated from sales and purchases — schedule reconciliation.
• Employee hours that exceed scheduled hours by a large margin without a corresponding overtime record — flag for payroll review.

Owner responsibilities (who owns what)

• Data Owner (Operations Lead) – defines business rules, dayparts, and approves KPI definitions; reviews high-level anomalies and signs off on policy changes.
• Data Steward (Location Manager / Analyst) – maintains canonical item mappings, approves exceptions, triages unmapped items, and validates daily quality checks for their scope.
• Technical Owner (Data Engineer / IT) – extracts and transforms source data, schedules ETL jobs, implements data validation rules, and maintains data lineage documentation.
• Dashboard Owner (Business Analyst / Manager) – builds and maintains dashboards, ensures widget data matches canonical KPIs, and communicates changes to users.
• Audit Cadence – daily operational checks, weekly reconciliation (inventory vs purchases vs sales), and monthly review of KPI definitions and mapping drift.

Practical rollout checklist (implementation-ready)

1. Agree on canonical KPI definitions with operations and finance stakeholders and publish a versioned KPI document.
2. Inventory all data sources and confirm extract frequency, API limits, and responsible system owners.
3. Build or update a mapping table linking POS items to canonical_menu_item_ids and recipes; seed it with known mappings and use a staging area for unmapped items.
4. Implement transformation rules in the ETL layer, deploy daily data-quality checks and alerting, and store intermediate aggregates (daily_sales, daily_cogs) for validation.
5. Deploy a small set of prioritized dashboard widgets (sales, labor, food cost, top items) and validate with managers for two weeks; collect mapping exceptions and adjust rules.
6. Iterate: update mappings, adjust dayparts, and refine KPIs based on feedback and observed anomalies. Maintain a changelog for mapping and KPI updates.

Governance & versioning

Keep canonical KPI definitions, mapping tables, and transformation rules in version-controlled documents or managed tables. Require a simple change request and approval flow for adjustments that affect reported KPIs. Record who changed what, why, and the effective date so historical reporting remains explainable.

Next steps and recommended enhancements (practical opportunities)

Turn this catalog into a living asset: publish the canonical definitions and mapping tables where teams can edit them, track changes, and submit exceptions. Two practical capability suggestions:

• Create an interactive mapping form that lets location staff submit unmapped POS items (including sample ticket snippet, photo of POS screen, and suggested mapping). Store submissions via the platform's data submission endpoint so stewards can triage and update the canonical map.
• Build a simple operational dashboard or alert that highlights mapping gaps, failing data checks, or sudden KPI drift and routes issues to the appropriate Data Steward or Technical Owner with context and links to supporting records.

Quick links (suggested)

• Item mapping template (CSV)
• Daily data quality checklist (shared)
• Canonical KPI definitions (versioned document)

Capability opportunities & implementation notes

This guide is ready to be enhanced by interactive and data-collection capabilities. Practical next development steps include:

• Implement an Interactive Mapping Form (rendered to HTML inputs) so staff can submit unmapped items; use the Content Data Submission endpoint to store submissions for triage. This reduces manual email or chat noise and creates an auditable queue.
• Automate daily quality checks and alerting using ETL or orchestration tooling so stewards receive concise, actionable notifications rather than raw logs.
• Expose a living canonical item map as an editable table with role-based permissions so local teams can propose location-specific overrides while enterprise owners maintain global standards.

Keep this guide practical: aim for consistent, explainable numbers that frontline managers can rely on for daily decisions. Document every rule and treat the mapping tables as living artifacts that evolve with menu and system changes.