Demo Retail — Analyst Knowledge Base

Synthetic client · generated by the toolkit · no real company, schema, or data

Demo Retail — Analyst Knowledge Base

Generated: 2026-04-10 | Stack: PostgreSQL + Metabase (no dbt) | Scope: full platform | Role: analyst

Day-1 orientation: This KB covers all 5 source tables, the 7 core business metrics, known data bugs, and safe SQL patterns. Read sections 5 and 7 before writing any query — they will save you from 3 common mistakes that cost analysts hours the first week.

---

1. What is Demo Retail?

Demo Retail is an e-commerce store selling consumer goods (Electronics, Clothing, Home, Sports, Books) across 3 regions: EU, US, and APAC. The business has ~500K lifetime orders, ~80K active customers, and 3 years of transaction history. Revenue reporting is in EUR — all amounts are FX-converted at load time, including US and APAC orders.

The data team is 2 analysts working from a shared operational PostgreSQL database, with Metabase connected for nightly-refresh dashboards. There is no dbt, no transformation layer, and no formal metric catalog yet — installing dbt and defining canonical metrics are the two highest-priority improvements (see the discovery report).

Key open business questions driving current analysis:

• EU conversion rate dropped from 38% to 31% — root cause unknown
• Which product categories have the highest return rates? (margins impact)
• Are premium customers churning faster than standard customers?
• How does AOV trend by plan_tier over the last 12 months?

---

2. Architecture Overview

No live warehouse connection in this session — generated from static file analysis.

---

3. Table Inventory

Source Tables (public schema)

---

orders

---

customers

---

products

---

order_items

Revenue formula: unit_price * quantity * (1 - discount_pct)

---

returns

Known bug: Duplicate rows for Jan 2025 return events. Fixed in pipeline Feb 2025, but duplicates remain in the table. See §7 for the safe deduplication filter.

---

Metric Definitions (Informal)

No formal YAML catalog exists yet. These definitions come from schema_notes.md and represent the canonical formulas — both analysts should use these until the semantic layer is built.

---

4. Entity Relationship Map

customers (grain: 1 row per customer, ~80K rows)
  ├── id (PK)
  ├── email (PII)
  ├── country (ISO-2), city
  ├── signup_date         ← NULL for ~12K pre-2023 customers
  ├── plan_tier           ← NULL for ~12K pre-2023 customers
  └── last_order_date     ← denormalized, 1-day lag

orders (grain: 1 row per order, ~500K rows)
  ├── id (PK)
  ├── customer_id         → customers.id
  ├── status              (pending|processing|shipped|delivered|cancelled|returned)
  ├── total_amount        ← pre-computed, do not use for analytics revenue
  ├── currency            ← always EUR, ignore for filtering
  ├── created_at (UTC)
  ├── shipped_at          ← NULL for ~8% of delivered orders
  └── delivered_at

order_items (grain: 1 row per line item, ~1.5–2M rows est.)
  ├── id (PK)
  ├── order_id            → orders.id
  ├── product_id          → products.id
  ├── quantity
  ├── unit_price          ← historical price (not products.price)
  └── discount_pct        ← DECIMAL (0.15 = 15%), NOT percentage

returns (grain: 1 row per returned item)
  ├── id (PK)
  ├── order_id            → orders.id
  ├── order_item_id       → order_items.id
  ├── reason
  ├── refund_amount
  ├── created_at          ← DUPLICATES in Jan 2025, always filter
  └── processed_at

products (grain: 1 row per SKU, ~3,200 rows)
  ├── id (PK)
  ├── name, category, subcategory
  ├── price               ← CURRENT price only, historical not stored
  ├── cost                ← COGS, SENSITIVE
  └── is_active           ← false = discontinued, still in order history

---

5. Key Business Logic

Revenue domain

• Always join orders → order_items for revenue — orders.total_amount is a denormalized field and may not match the sum of line items (rounding, historical corrections).
• Status filter is mandatory. Only status IN ('shipped', 'delivered') should count as revenue. Exclude pending, processing, cancelled, returned.
• discount_pct is a decimal. The correct multiplier is (1 - discount_pct). Multiplying by (1 - discount_pct * 100) is 100x wrong — produces negative revenue for any discounted item.
• Net vs Gross: Gross = order_items sum. Net = Gross − SUM(returns.refund_amount). Refund amount may be less than unit_price (partial refunds are real and common).
• Currency: All amounts are EUR, FX-converted at load time. No multi-currency math needed.

Customers domain

• Pre-2023 cohort has NULL plan_tier (~12K / 15% of customers). Always use COALESCE(plan_tier, 'unknown') in GROUP BY. Bare GROUP BY plan_tier silently excludes this cohort — churn and LTV analyses are materially understated without it.
• signup_date is NULL for the same pre-2023 cohort. Exclude WHERE signup_date IS NOT NULL for acquisition cohort analysis. Do not exclude for total customer counts.
• last_order_date lags by 1 day. Acceptable for 90-day churn windows; do not use for "ordered today" checks. The nightly update job runs at ~02:00 UTC.
• email is PII. Do not include in query results shared externally or pasted into Slack. Use id for joins and country/plan_tier for segmentation.

Returns domain

• Jan 2025 duplicates are still in the table. Every query against returns must either:
  • Filter: WHERE created_at NOT BETWEEN '2025-01-01' AND '2025-01-31' (excludes the month), OR
  • Deduplicate: SELECT MIN(id), order_item_id FROM returns GROUP BY order_item_id, created_at::date The safest approach for return rate calculations is the filter — simpler and correct for most analyses.
• status = 'returned' on orders is order-level. It means the entire order was marked returned. For item-level return analysis, use the returns table directly joined on order_item_id.
• processed_at is NULL for pending refunds. Filter WHERE processed_at IS NOT NULL when measuring completed refund cycles.

Products domain

• products.price is current price only. For revenue calculations, always use order_items.unit_price (price at time of purchase). Joining products.price for revenue is a common mistake that introduces price-drift errors.
• Discontinued products (is_active = false) still appear in historical orders/returns. Do not filter WHERE is_active = true when analyzing historical data — you'll lose the history.
• cost (COGS) is sensitive. Do not include in any report shared with non-finance stakeholders.

Conversion Rate

• There is no session or event tracking data in this database. Conversion rate cannot be calculated reliably. The proxy completed orders / total signups is directionally useful but noisy — do not report it as a hard metric without the caveat.

---

6. Quick-Start SQL Patterns

All queries are written for PostgreSQL.

Pattern 1: Monthly gross revenue (correct)

-- Canonical gross revenue — the safe version both analysts should use
SELECT
    DATE_TRUNC('month', o.created_at)                      AS period,
    SUM(oi.unit_price * oi.quantity * (1 - oi.discount_pct)) AS gross_revenue,
    COUNT(DISTINCT o.id)                                   AS order_count,
    SUM(oi.unit_price * oi.quantity * (1 - oi.discount_pct))
        / NULLIF(COUNT(DISTINCT o.id), 0)                  AS aov
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('shipped', 'delivered')
GROUP BY 1
ORDER BY 1;

Pattern 2: Net revenue (after refunds)

-- Gross revenue minus actual refunds
SELECT
    DATE_TRUNC('month', o.created_at)                         AS period,
    SUM(oi.unit_price * oi.quantity * (1 - oi.discount_pct))  AS gross_revenue,
    COALESCE(SUM(r.refund_amount), 0)                         AS total_refunds,
    SUM(oi.unit_price * oi.quantity * (1 - oi.discount_pct))
        - COALESCE(SUM(r.refund_amount), 0)                   AS net_revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN returns r ON r.order_item_id = oi.id
    AND r.created_at NOT BETWEEN '2025-01-01' AND '2025-01-31'  -- exclude Jan 2025 bug
WHERE o.status IN ('shipped', 'delivered')
GROUP BY 1
ORDER BY 1;

Pattern 3: Return rate by product category

-- Item-level return rate per category (exclude Jan 2025 duplicates)
SELECT
    p.category,
    COUNT(DISTINCT r.order_item_id)::FLOAT
        / NULLIF(COUNT(DISTINCT oi.id), 0)  AS return_rate,
    COUNT(DISTINCT oi.id)                   AS items_sold,
    COUNT(DISTINCT r.order_item_id)         AS items_returned
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
LEFT JOIN returns r ON r.order_item_id = oi.id
    AND r.created_at NOT BETWEEN '2025-01-01' AND '2025-01-31'
WHERE o.status = 'delivered'
GROUP BY 1
ORDER BY 2 DESC;

Pattern 4: Customer count by plan tier (handle NULLs)

-- Always COALESCE plan_tier — bare GROUP BY silently excludes ~12K customers
SELECT
    COALESCE(plan_tier, 'unknown')  AS tier,
    COUNT(*)                        AS total_customers,
    COUNT(CASE WHEN last_order_date >= CURRENT_DATE - INTERVAL '90 days'
               THEN 1 END)          AS active_90d
FROM customers
GROUP BY 1
ORDER BY 2 DESC;

Pattern 5: AOV by plan tier (last 12 months)

-- Segment AOV by plan tier — reveals premium vs standard difference
SELECT
    COALESCE(c.plan_tier, 'unknown')                              AS tier,
    DATE_TRUNC('month', o.created_at)                            AS period,
    SUM(oi.unit_price * oi.quantity * (1 - oi.discount_pct))
        / NULLIF(COUNT(DISTINCT o.id), 0)                         AS aov,
    COUNT(DISTINCT o.id)                                          AS orders
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers c ON c.id = o.customer_id
WHERE o.status IN ('shipped', 'delivered')
  AND o.created_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1, 2
ORDER BY 2, 1;

Pattern 6: Return reasons breakdown

-- What drives returns? (safe: excludes Jan 2025 duplicates)
SELECT
    reason,
    COUNT(*)                                          AS return_count,
    SUM(refund_amount)                                AS total_refunded,
    COUNT(*)::FLOAT / SUM(COUNT(*)) OVER ()           AS share_of_returns
FROM returns
WHERE created_at NOT BETWEEN '2025-01-01' AND '2025-01-31'
  AND processed_at IS NOT NULL  -- completed refunds only
GROUP BY 1
ORDER BY 2 DESC;

Pattern 7: 90-day churn by plan tier

-- Customers who haven't ordered in 90 days (among those active in last 12 months)
SELECT
    COALESCE(plan_tier, 'unknown')                                   AS tier,
    COUNT(*)                                                         AS active_12m,
    COUNT(CASE WHEN last_order_date < CURRENT_DATE - INTERVAL '90 days'
               THEN 1 END)                                           AS churned_90d,
    COUNT(CASE WHEN last_order_date < CURRENT_DATE - INTERVAL '90 days'
               THEN 1 END)::FLOAT
        / NULLIF(COUNT(*), 0)                                        AS churn_rate
FROM customers
WHERE last_order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 4 DESC;

---

7. Common Pitfalls

• discount_pct is a decimal, not a percentage (order_items.discount_pct): The column stores 0.15 for a 15% discount. Writing (1 - discount_pct * 100) produces negative unit prices for any discounted item. Always (1 - discount_pct).

• Jan 2025 returns are duplicated (returns.created_at): A data pipeline bug created duplicate rows for all return events in January 2025. The bug was fixed in February 2025 but the rows remain in the table. Any return count or return rate query must filter created_at NOT BETWEEN '2025-01-01' AND '2025-01-31' or deduplicate on (order_item_id, created_at::date).

• orders.total_amount ≠ SUM(order_items) revenue (orders.total_amount): Use order_items.unit_price * quantity * (1 - discount_pct) for all revenue calculations. total_amount is a legacy column and may not match line-item math.

• Cancelled/pending orders inflate revenue (orders.status): Always filter WHERE status IN ('shipped', 'delivered'). Including pending or cancelled status inflates revenue by orders that never completed.

• NULL plan_tier excludes 15% of customers (customers.plan_tier): Approximately 12K customers migrated before 2023 have no plan_tier. GROUP BY plan_tier silently excludes them. Always COALESCE(plan_tier, 'unknown').

• products.price is current, not historical (products.price): The price column reflects today's price. For revenue analysis always use order_items.unit_price (recorded at purchase). Using products.price for historical revenue will be wrong for any repriced SKU.

• currency column is always 'EUR' (orders.currency): Despite selling in US/APAC markets, all amounts are FX-converted at load time and stored in EUR. Do not use currency as a filter or segment — it will always return only one value.

• shipped_at is NULL for 8% of delivered orders (orders.shipped_at): Legacy import gap. If you're calculating fulfilment time as shipped_at - created_at, you'll silently exclude 8% of delivered orders. Use delivered_at - created_at as a more reliable proxy.

• email is PII (customers.email): Do not include in any result shared externally, pasted into Slack, or exported to CSV. Use customers.id for joins.

• Discontinued products in history (products.is_active = false): Filtering WHERE is_active = true will exclude historical orders for discontinued SKUs. Only apply this filter when querying the current active catalog (e.g. for pricing reports).

---

8. Quick-Start Guide

Top 5 Tables to Know

Top 5 Metrics

KB freshness check

# No dbt — check when schema_notes.md was last updated:
git log --follow -p clients/demo-retail/schema_notes.md | head -20

Regenerate

# Full regeneration (static analysis):
/onboarding demo-retail

# Add new tables or refresh after schema changes:
/onboarding demo-retail --update

# With warehouse data (needs .env + API key):
source .venv/bin/activate
python -m agents.onboarding --client demo-retail

Contributing corrections

Add notes to ## Analyst Notes below. They are preserved on every regeneration. Format: - [YYYY-MM-DD] Your note here

---

9. Metabase Dashboards

Metabase API not queried in this session — static analysis only.

Known dashboard patterns from profile.yaml:

• Multiple saved questions use SELECT * → fragile, break on column additions
• Nightly refresh cadence — data is from the previous day by morning
• No freshness indicator — analysts cannot tell if the nightly job failed

Populate with actual workbook names after Metabase access is granted.

---

10. Planned Improvements

Once dbt is installed (P0 action from discovery report), the following will be added to this KB:

• Staging model inventory (stg_orders, stg_customers, etc. with type-cast columns)
• discount_pct renamed to discount_rate in staging layer
• Metric YAML files in clients/demo-retail/semantic_layer/metrics/
• dbt test results (PK/FK coverage, accepted_values for status/reason)
• Source freshness SLA configuration

Run /onboarding demo-retail --update after dbt is configured to regenerate this KB with model inventory.

---

Generated by Claude Code (Mode B — static file analysis) Discovery report: clients/demo-retail/reports/2026-04-10_discovery.md (Level 1/5 — Raw)

---

Analyst Notes

Add corrections and discoveries below. Preserved on every regeneration.