Interactive plotly charts

Step 1: The dashboard design guide

Open materials/dashboard-design-guide.md. Five sections. Two matter most right now.

Interaction paths. Every drill-down answers a follow-up question. The path is: Amina sees the store overview, clicks Oyster Bay, sees category breakdown, clicks Fiction, sees subcategory detail. Each step answers a natural next question. A drill-down that leads to a confusing view is worse than no drill-down -- it is a broken promise.

Self-service patterns. Two types. Exploratory: the user controls all filters, chooses what to drill into. Guided: the questions are pre-set, the filters pre-applied. Amina gets exploratory. Her managers get guided. The guide has a dual-audience checklist -- for each dashboard element, ask whether the least data-literate user could understand it without help.

Read the minimum-data-point thresholds section. If a filter produces fewer than 20 transactions, a chart drawn from that data is misleading. The threshold depends on context, but the principle is fixed: show a warning, not a confident trend from sparse data.

Step 2: Plan the decomposition

Before building, plan the sequence. This is the first time you will use plan mode explicitly for analytical decomposition.

I need to build interactive plotly charts for Soma Books. Here's the sequence:
(a) a store-level overview chart with click-to-drill-down to categories
(b) a category trend chart with time filters
(c) a store comparison chart that accounts for Kariakoo's demographics.

For each chart, I need: the metric definitions from our SQL queries, human-readable hover labels, and minimum-data-point awareness. Plan the build order and what context each chart needs.

Planning before starting produces better results than decomposing in-flight. The plan tells Claude what context each piece of work needs -- the metric definitions for the overview, the school order separation for the trend chart, the Kariakoo demographic notes for the comparison. This is context curation: deciding what to include in each piece of the session.

Step 3: Store revenue overview

Build the first chart. A bar chart showing revenue per store. Interactive -- clicking a store bar drills down to category breakdown.

Build a plotly bar chart showing total revenue per store using the POS data. Use the retail revenue metric (excluding school bulk orders with quantity >= 10). Make it interactive: clicking a store bar should show the category breakdown for that store. Use human-readable hover labels -- "Store: Oyster Bay | Retail Revenue: TZS 12,450,000" not "store: Oyster Bay, revenue: 12450000". Save as a Jupyter notebook.

Check the output. AI commonly generates plotly charts with raw column names in the hover text. If the hover shows price_tzs instead of "Revenue (TZS)", specify the correction.

The difference between raw column names and human-readable labels is the difference between a tool that communicates and one that does not. Every label on the chart is a promise: "I will help you understand this number."

Step 4: Category trend chart

The second chart shows category revenue trends over time. School bulk orders need to be visually distinguishable so Amina can see the seasonal pattern without it distorting the trend.

Build a plotly line chart showing monthly revenue by category over the full 18-month period. Use the retail revenue metric (excluding bulk orders). Add a separate series or annotation showing the school bulk order months (January and September) so the seasonal pattern is visible but does not distort the underlying trends. Include time range filters. Human-readable hover labels throughout.

When the chart shows trends with limited data, showing only the point estimate is dishonest. If a category has only a handful of transactions in a given month, a trend line through those points suggests confidence the data does not support. The minimum-data-point threshold applies here -- months with sparse data for a category should be flagged.

Step 5: Store comparison chart

The third chart compares stores. Kariakoo's higher foot traffic and lower average transaction value make raw comparison misleading. The chart needs to account for this.

Build a plotly chart comparing the three stores. Show both total transactions and average transaction value side by side. Add an annotation or note for Kariakoo explaining that the neighborhood demographics drive higher foot traffic and lower average values. Test edge case: filter to a store + category + month with very few transactions (e.g., last 30 days, children's stationery). If fewer than 20 transactions back the view, display a warning instead of a misleading trend.

Test the edge case yourself. Filter to a narrow combination -- a single store, a single subcategory, a recent month. Count the transactions backing the view. If there are only 8, the chart should say so. AI does not implement minimum-data-point warnings by default. You will need to specify this explicitly.

Step 6: Verify against metric definitions

Each plotly chart uses a metric. Those metrics have SQL definitions from Unit 2. The numbers must match.

Pick a store, a category, a month. Run the SQL metric definition directly in DuckDB. Then check the same number on the plotly chart. They should be identical. If they differ, one of the tools is using a different aggregation or a different filter boundary.

For Oyster Bay, Fiction, January 2025: run the retail revenue SQL query directly in DuckDB. Then check the same number in the plotly store overview chart. Do they match? If not, what's different about how each tool is computing the number?

This is where plotly and SQL serve different purposes. plotly is for interactive exploration in a notebook -- the analyst's workspace. Metabase is for persistent dashboards that stakeholders visit daily. The choice between them is about who sees the output and how. But the metric definitions must be the same across both.