Analytics P3: Bona Lluna Cervesa -- Brewery Analytics Dashboard

Client

Carmen Reyes, Co-founder and Commercial Director at Bona Lluna Cervesa. A craft brewery outside Barcelona with 25 employees, 12 beer styles, three sales channels.

What you're building

A unified analytics dashboard in Metabase that consolidates data from three sales channels (taproom POS, distribution invoicing, WooCommerce online shop) into one source of truth. Carmen checks this dashboard every Monday morning to see what sold, where, and what's trending.

Tech stack

Python 3.11+ (Miniconda "analytics" environment)
DuckDB (data loading, profiling, analysis)
Jupyter Notebook (exploration, analysis)
pandas (data manipulation)
matplotlib / seaborn (exploratory charts during analysis)
Metabase (BI dashboard platform, running via Docker at localhost:3000)
Docker (runs Metabase)
Git / GitHub

File structure

materials/
  taproom_pos.csv          -- 12 months of taproom POS transactions
  distribution_invoices.csv -- 12 months of wholesale invoices
  online_orders.csv        -- 12 months of WooCommerce orders
  product_catalog.csv      -- Canonical product names, categories, costs
  docker-compose.yml       -- Docker Compose for Metabase
  analysis-template.md     -- Template for analysis deliverable
  dashboard-layout-template.md -- Dashboard layout guidance
  CLAUDE.md                -- This file

Key materials

product_catalog.csv -- authoritative product list with canonical names. All other systems map to these names.
analysis-template.md -- fill in progressively: data quality assessment, metric definitions, segmentation findings, cleaning decisions
dashboard-layout-template.md -- layout guidance for the Metabase dashboard (KPIs at top, trends in middle, breakdowns below)

Work breakdown

T1: Profile and reconcile data sources (load all three into DuckDB, profile each, identify inconsistencies)
T2: Build product name mapping (map taproom/distribution/online names to catalog canonical names)
T3: Define governed metrics (revenue, volume, growth -- plain language + SQL, with documented trade-offs)
T4: Cross-check metric definitions (second AI reviews definitions and SQL for interpretation gaps)
T5: Segmentation analysis (revenue and volume by channel and beer style, sorted by business impact)
T6: Set up Metabase and connect DuckDB (Docker, database connection, verify)
T7: Build dashboard panels (KPI summary, channel breakdown, beer style performance, trend line)
T8: Add filters and verify consistency (date range, channel, beer style -- test all combinations)
T9: Present to Carmen and iterate (handle YoY request, defer production data request)
T10: Data quality assessment deliverable (completeness, consistency, accuracy, timeliness)
T11: Final push to GitHub

Verification targets

All metric definitions have both plain-language and SQL expressions
Revenue totals per channel sum to overall total (or difference is explained)
Dashboard panels use identical SQL definitions for the same metric
All filter combinations produce correct results (no edge case breakdowns)
Product name mapping covers all 12 active styles across all three sources
Cleaning decisions documented with trade-offs
Cross-check findings documented

Rules

Use SQL mode in Metabase, not the visual query builder
Profile each data source independently before combining
Document every cleaning decision and its trade-off
Every metric definition needs plain language first, SQL second
Commit after each ticket with a descriptive message