Explain Compliance Drops Faster

See the Decision Intelligence Workflow in Action

This workflow handles a recurring diagnostic task: determining whether a category compliance decline is primarily caused by inventory shortages, execution drift in stores, or a mismatch between the planogram and the physical store setup. It compares current and prior compliance periods, isolates affected stores, applies attribution rules, and turns the result into a usable operating brief. The manual burden is not the decision itself; it is the repeated cross-domain querying, compliance calculation, store bucketing, and action-list formatting required before judgment can begin.

Data Warehouse Integration

The workflow depends on warehouse-level access to audit, store master, and inventory data so the specialist is not manually exporting and stitching evidence across separate reporting views. It computes period-over-period compliance movement, joins store-level operational context, and applies consistent attribution rules across the affected population.

The cross-domain requirement is central: audit observations explain what the store looked like, planogram expectations define what should have been present, and inventory snapshots show whether the shelf condition was operationally supportable.

• store_compliance_audits
• store_master
• store_inventory_daily

CREATE TABLE retail_store_dev.merchandising.store_compliance_audits (
  audit_date DATE,
  store_id STRING,
  category_id STRING,
  compliance_pct DOUBLE,
  missing_facings_count INT,
  wrong_placement_count INT,
  promo_display_missing_count INT
);

SELECT
  m.region_id,
  a.store_id,
  AVG(CASE WHEN a.audit_date BETWEEN string">'{start_date}' AND string">'{end_date}' THEN a.compliance_pct END) AS compliance_curr_pct,
  AVG(
    CASE
      WHEN a.audit_date BETWEEN date_add(string">'{start_date}', -datediff(string">'{end_date}',string">'{start_date}')-1)
                           AND date_add(string">'{start_date}', -1)
      THEN a.compliance_pct
    END
  ) AS compliance_prev_pct
FROM retail_store_dev.merchandising.store_compliance_audits a
JOIN retail_store_dev.merchandising.store_master m
  ON a.store_id = m.store_id
WHERE m.region_id = string">'{region_id}'
  AND a.category_id = string">'{category_id}'
GROUP BY m.region_id, a.store_id
HAVING (compliance_prev_pct - compliance_curr_pct) >= {compliance_drop_threshold_pct};

CREATE TABLE retail_store_dev.merchandising.store_master (
  store_id STRING,
  region_id STRING,
  district_id STRING,
  store_format STRING,
  open_date DATE
);

SELECT store_id, region_id, district_id, store_format, open_date
FROM retail_store_dev.merchandising.store_master
WHERE region_id = string">'{region_id}';

CREATE TABLE retail_store_dev.merchandising.store_inventory_daily (
  snapshot_date DATE,
  store_id STRING,
  sku_id STRING,
  category_id STRING,
  on_hand_units INT,
  on_order_units INT,
  dc_lead_time_days INT
);

SELECT snapshot_date, store_id, sku_id, category_id, on_hand_units, on_order_units, dc_lead_time_days
FROM retail_store_dev.merchandising.store_inventory_daily
WHERE category_id = string">'{category_id}'
  AND snapshot_date BETWEEN string">'{start_date}' AND string">'{end_date}';

The Business Case for Automation

Primary Valuation Metric: % of compliance incidents resolved with a single approved remediation plan in the first review meeting