Build Interactive Dashboards with AI Assistants

# DTC Skincare Sales Dashboard Specification

## 1. Recommended Tool
**Streamlit + Plotly** is the best fit. It connects natively to Shopify, Meta, and Google Ads APIs via Python connectors, supports fast iteration with AI copilots (Cursor, Claude, ChatGPT), handles parameterized filters and drill-downs cleanly with `st.session_state`, and deploys free on Streamlit Community Cloud. Power BI is overkill for a small ops team, and Tableau licensing is costly. Streamlit gives engineers and non-technical managers a shared, interactive web app without BI overhead.

## 2. Data Model & Required Fields

| Table | Key Fields |
|---|---|
| `orders` | order_id, order_date, customer_id, channel, subtotal, discount, shipping, total, is_repeat |
| `order_items` | order_id, sku, product_name, category, qty, unit_price, cogs |
| `customers` | customer_id, first_order_date, lifetime_orders, lifetime_value |
| `ad_spend` | date, platform (Meta/Google), campaign, spend, impressions, clicks, attributed_revenue |

Join keys: `order_id`, `customer_id`, `date`. Refresh nightly via Shopify GraphQL, Meta Marketing API, Google Ads API.

## 3. Layout (12-column grid)

| Row | Columns | Section |
|---|---|---|
| 1 | 1–12 | Global Filters (date range, channel, product category) |
| 2 | 1–12 | KPI Strip (6 cards) |
| 3 | 1–8 / 9–12 | Revenue Trend / Channel Mix |
| 4 | 1–6 / 7–12 | Top SKUs / Underperformers |
| 5 | 1–6 / 7–12 | Ad Spend vs Revenue / ROAS by Platform |
| 6 | 1–12 | Repeat Customer Cohort Heatmap |

## 4. Charts & Widgets

- **Revenue Trend** — Line chart; metric: daily revenue; dimension: order_date; interactions: hover tooltip (orders, AOV), brush-to-zoom, click day → filters rows 4–6.
- **Channel Mix** — Donut; metric: revenue share; dimension: channel (Direct, Meta, Google, Organic); click slice → cross-filters dashboard.
- **Top SKUs** — Horizontal bar (top 10); metric: revenue; dimension: sku; drill-down: click bar → opens modal with daily units, return rate, margin.
- **Underperformers** — Table with conditional formatting; metrics: revenue WoW %, sell-through %, days of inventory; filter: <20th percentile; row click → SKU detail.
- **Ad Spend vs Revenue** — Dual-axis combo (bars=spend, line=attributed revenue); dimension: date; toggle parameter: Meta / Google / Both.
- **ROAS by Platform** — Grouped bar; metric: ROAS; dimension: campaign × platform; tooltip: spend, revenue, CPA.
- **Repeat Customer Heatmap** — Cohort grid; rows: acquisition month; cols: months since; cell: repeat purchase rate %.

## 5. KPI Cards (with formulas)

| KPI | Formula |
|---|---|
| Daily Revenue | `SUM(orders.total) WHERE date = today` |
| AOV | `SUM(total) / COUNT(DISTINCT order_id)` |
| Repeat Customer Rate | `COUNT(customers WHERE lifetime_orders >= 2) / COUNT(customers)` |
| Blended ROAS | `SUM(attributed_revenue) / SUM(ad_spend)` |
| CAC | `SUM(ad_spend) / COUNT(new customers)` |
| Orders Today | `COUNT(DISTINCT order_id) WHERE date = today` |

Each card shows value + Δ vs previous period (color-coded).

## 6. Color & Theme

Clean, skincare-inspired palette:
- Background: `#FAF7F2` (warm cream)
- Primary: `#2E3B2C` (deep sage)
- Accent: `#D4A574` (muted gold)
- Positive: `#7BA885` · Negative: `#C97B6A`
- Font: Inter or DM Sans, 14px base
- Rounded corners (8px), subtle shadows, generous whitespace

## 7. AI Builder Prompt

```
Build a Streamlit dashboard called "Skincare Ops Dashboard" using Plotly and pandas.

DATA: Load four CSVs (orders, order_items, customers, ad_spend) from /data. 
Join orders↔order_items on order_id, orders↔customers on customer_id.

GLOBAL FILTERS (sidebar): date range picker (default last 30 days), 
multiselect channel (Direct/Meta/Google/Organic), multiselect product category. 
Store in st.session_state and apply to all charts.

LAYOUT:
- Row 1: 6 KPI cards using st.metric — Daily Revenue, AOV, Repeat Customer Rate, 
  Blended ROAS, CAC, Orders Today. Each shows delta vs prior period.
- Row 2: col(8,4) — Plotly line chart of daily revenue with range slider; 
  donut of revenue by channel (click cross-filters other charts).
- Row 3: col(6,6) — horizontal bar top 10 SKUs by revenue (click opens st.expander 
  with SKU detail: daily units, margin, return rate); 
  st.dataframe of underperforming SKUs (WoW revenue <-20%) with red/green styling.
- Row 4: col(6,6) — dual-axis bar+line of ad spend vs attributed revenue with 
  Meta/Google/Both radio toggle; grouped bar of ROAS by campaign×platform.
- Row 

# DTC Skincare Sales Dashboard Specification

## 1. Recommended Tool: Streamlit with Plotly

**Justification:** Streamlit offers rapid prototyping with minimal code, native Plotly integration for interactive charts, and seamless Shopify API connectivity via community libraries. It supports multi-select filters, cross-filtering, and drill-down navigation without backend complexity. For a lean ops team, Streamlit's Python-based architecture enables quick iteration and custom logic (e.g., repeat customer calculations). Alternative: Tableau Pulse for enterprise polish, but Streamlit wins on speed and cost for DTC.

---

## 2. Data Model & Required Fields

| **Table** | **Key Fields** |
|-----------|----------------|
| **Orders** | order_id, order_date, customer_id, total_revenue, channel (Shopify, Facebook, Google), device, utm_source, utm_medium, repeat_customer (Y/N) |
| **Order Line Items** | sku, product_name, quantity, price, product_category, cogs |
| **Ad Spend** | date, platform (Meta/Google), campaign_id, campaign_name, spend, impressions, clicks |
| **Customers** | customer_id, first_purchase_date, email, repeat_flag, ltv |

**Data Source:** Shopify GraphQL API → Orders + Line Items; Meta Ads Manager API & Google Ads API → Ad spend; joined on date and UTM fields.

---

## 3. Dashboard Layout (Grid: 12 columns × Auto rows)

| **Section** | **Grid Position** | **Purpose** |
|---|---|---|
| **Filters Bar** | Full width (cols 1-12, row 1) | Date range, channel multi-select, product category filter |
| **KPI Cards** | Cols 1-3, row 2 | Daily Revenue, AOV, Repeat Rate |
| **KPI Cards** | Cols 4-6, row 2 | Total Ad Spend, ROAS, Conversion Rate |
| **Revenue Trend** | Cols 1-8, row 3 | Primary chart; time-series with channel breakdown |
| **Channel Attribution** | Cols 9-12, row 3 | Pie/donut; revenue by channel |
| **Top SKUs Table** | Cols 1-6, row 4 | Sortable; qty sold, revenue, margin |
| **Ad Spend vs. Revenue** | Cols 7-12, row 4 | Scatter or bar; platform-level performance |
| **Repeat Customer Rate Trend** | Cols 1-6, row 5 | Line chart; repeat % over time |
| **Product Performance Drill** | Cols 7-12, row 5 | Heatmap or bar; SKU vs. channel |

---

## 4. Charts & Interactive Features

**Revenue Trend Line Chart**
- Metric: Daily Revenue (sum)
- Dimension: Date (x-axis), Channel (color)
- Filters: Date range picker, channel multi-select
- Interactions: Hover tooltip (revenue, customer count), click legend to toggle channels, zoom on selection

**Top SKUs Table**
- Metrics: Units sold, revenue, AOV per SKU, margin %
- Dimensions: SKU, product_name, category
- Filters: Sortable columns, search box for SKU name
- Interactions: Click SKU row → drill into **SKU Detail Modal** (revenue by channel, repeat vs. new customer split, daily trend)

**Channel Attribution Donut**
- Metric: Revenue (size)
- Dimension: Channel (segment)
- Filters: Date range, repeat/new customer toggle
- Interactions: Hover for revenue %; click segment to filter all other charts to that channel

**Ad Spend vs. Revenue (Bubble/Bar Chart)**
- Metric: Ad spend (x), revenue (y), ROAS (size/color)
- Dimension: Platform (Meta, Google), date
- Filters: Date range, platform toggle
- Interactions: Hover for ROAS and CPA; click platform to isolate

**Repeat Customer Rate Trend (Line)**
- Metric: % repeat customers
- Dimension: Date (x), cohort (optional: color by acquisition channel)
- Filters: Date range
- Interactions: Hover for absolute repeat customer count; click cohort to drill

**Product Performance Heatmap**
- Metric: Revenue (cell color intensity)
- Dimensions: SKU (rows), Channel (columns)
- Filters: Top N SKUs selector, date range
- Interactions: Hover for revenue & AOV; click cell to navigate to SKU detail

---

## 5. KPI Cards with Formulas

| **KPI** | **Formula** | **Benchmark/Target** |
|--------|-----------|----------------------|
| **Daily Revenue** | SUM(order total) filtered by date range | Visualize vs. prior period |
| **AOV** | SUM(revenue) / COUNT(orders) | Target: +5% MoM |
| **Repeat Customer Rate** | COUNT(orders where repeat_flag=1) / COUNT(unique customers) | Target: >25% |
| **Total Ad Spend** | SUM(spend from Meta + Google) | Show spend pacing |
| **ROAS** | Revenue attributed to ads / Total ad spend | Target: >3.0 |
| **Conversion Rate** | COUNT(orders) / SUM(clicks from ads) | Channel-specific targets |

---

## 6. Color Scheme & Theme

- **Primary:** Teal (#20B2AA) for revenue/positive metrics
- **Secondary:** Coral (#FF6B6B) for ad spend/costs
- **Accent:** Gold (#FFD93D) for repeat customer/loyalty metrics
- **Neutral:** Light gray (#F5F5F5) backgrounds, dark gray (#2C3E50) text
- **Theme:** Clean, minimal; large readable fonts for ops team; high contrast

---

## 7. Ready-to-Paste AI Builder Prompt

```
You are building an interactive DTC sales dashboard in Streamlit for a skincare brand's ops team.

REQUIREMENTS:
1. Data Sources: Shopify Orders API (revenue, SKU, customer, channel), Meta & Google Ads APIs (daily spend), merged on date & UTM.
2. Filters (top of page, full width): Date range picker (default: last 30 days), multi-select channel filter (Shopify Direct, Facebook, Google), product category dropdown.
3. KPI Cards (row 2): Daily Revenue (sum), AOV (revenue/orders), Repeat Customer Rate (%), Ad Spend (sum), ROAS (revenue/spend), Conversion Rate (%).
4. Charts:
   - Revenue Trend (line, date x-axis, channel color-coded, legend toggle, hover tooltip)
   - Channel Attribution (donut, revenue by channel, click to cross-filter)
   - Top 10 SKUs (interactive table, sortable, clickable rows drill to detail modal showing SKU revenue by channel & repeat vs. new split)
   - Ad Spend vs. Revenue (bar chart, platform labels, ROAS as bar color)
   - Repeat Customer Rate Trend (line chart, date x-axis, %)
   - Product Performance Heatmap (SKU rows, channel columns, revenue as cell color)
5. Interactions: All filters apply to all charts (cross-filtering). Clicking SKU opens a modal with drill-down detail. Hover tooltips on all charts.
6. Color Palette: Teal primary, coral for costs, gold for repeat metrics, light gray backgrounds.
7. Mobile responsive layout; readable on tablet.
Output clean, commented Streamlit code with data loading, filtering logic, and Plotly chart definitions.
```

---

**Next Steps:** Export your Shopify, Meta, and Google Ads data to CSV or connect via APIs. Load into Streamlit, run the generated code, and iterate based on ops team feedback. Expected build time: 4–6 hours with AI assistance.

# 1) Recommended AI Dashboard Builder

**Recommended tool: Power BI with Copilot.** It’s the best fit because you need a multi-source ops dashboard combining **Shopify orders/customers/products** with **Meta + Google ad spend**, plus strong **date/channel filters, drill-downs, cross-filtering, KPI formulas, and underperforming SKU analysis**. Power BI handles relational modeling well, supports reusable measures like repeat rate and AOV, and Copilot can accelerate dashboard generation from a structured prompt.

# 2) Data Model and Required Fields

Use a **star schema**.

| Table | Grain | Required Fields |
|---|---|---|
| `fact_orders` | 1 row per order | order_id, order_date, customer_id, sales_channel, revenue_gross, discounts, refunds, revenue_net, shipping, tax, total_units, first_order_flag |
| `fact_order_items` | 1 row per order line | order_id, order_date, sku_id, product_name, variant_name, category, units_sold, line_revenue_net, line_discount |
| `fact_ad_spend` | 1 row per date-platform-campaign | date, platform (Meta/Google), campaign_name, ad_channel, spend, impressions, clicks, conversions |
| `dim_customer` | 1 row per customer | customer_id, first_order_date, total_orders_lifetime, acquisition_channel, country |
| `dim_product` | 1 row per SKU | sku_id, sku, product_name, category, collection, launch_date, status |
| `dim_date` | 1 row per date | date, day, week, month, quarter, year, day_of_week |

**Relationships**
- `fact_orders[customer_id] -> dim_customer[customer_id]`
- `fact_order_items[sku_id] -> dim_product[sku_id]`
- `fact_orders[order_id] -> fact_order_items[order_id]`
- `fact_orders[order_date]`, `fact_order_items[order_date]`, `fact_ad_spend[date] -> dim_date[date]`

# 3) Layout and Grid Placement

Use a **12-column grid**.

## A. Header / Controls
- **Row 1, cols 1–12:** Dashboard title, last refresh timestamp
- **Row 2, cols 1–3:** Date range slicer
- **Row 2, cols 4–6:** Channel filter
- **Row 2, cols 7–9:** Product category / SKU search
- **Row 2, cols 10–12:** Platform filter (All / Meta / Google)

## B. KPI Summary
- **Row 3, cols 1–2:** Daily Revenue
- **Row 3, cols 3–4:** AOV
- **Row 3, cols 5–6:** Repeat Customer Rate
- **Row 3, cols 7–8:** Ad Spend
- **Row 3, cols 9–10:** ROAS
- **Row 3, cols 11–12:** Orders

## C. Performance Trends
- **Row 4, cols 1–8:** Revenue / AOV / Ad Spend trend
- **Row 4, cols 9–12:** Channel mix

## D. Product Drill-down
- **Row 5, cols 1–7:** Top / underperforming SKU table
- **Row 5, cols 8–12:** SKU performance scatter or bar

## E. Customer Insights
- **Row 6, cols 1–6:** New vs repeat customers trend
- **Row 6, cols 7–12:** Repeat rate by channel

# 4) Charts and Widgets

- **Line chart** — *Metrics:* daily revenue, ad spend, AOV (toggle parameter) / *Dimension:* date / *Interactions:* date drill-down day→week→month, cross-filter all visuals, tooltips with revenue, orders, AOV, spend, ROAS  
- **Stacked bar or donut** — *Metrics:* revenue, orders / *Dimension:* sales channel / *Interactions:* click channel to filter page, tooltip with share %  
- **Matrix/table** — *Metrics:* SKU revenue, units sold, AOV, discount %, refund %, repeat customer orders / *Dimension:* SKU → product → category / *Interactions:* sortable, conditional formatting for underperformance, search, click SKU to filter all visuals  
- **Horizontal bar chart** — *Metrics:* top 10 SKU revenue or units / *Dimension:* SKU / *Interactions:* parameter toggle Top/Bottom performers, cross-filter dashboard  
- **Scatter plot** — *Metrics:* revenue vs ad-attributed spend or units, bubble size = margin proxy or orders / *Dimension:* SKU / *Interactions:* highlight low-revenue/high-spend SKUs, lasso select for drill-in  
- **Line/area chart** — *Metrics:* new customers, repeat customers / *Dimension:* date / *Interactions:* drill-down and synced date filter  
- **Bar chart** — *Metrics:* repeat customer rate / *Dimension:* channel / *Interactions:* click channel to isolate underperformance  
- **Slicers** — *Fields:* date range, channel, platform, category, SKU, campaign

# 5) KPI Cards and Formulas

| KPI | Formula |
|---|---|
| Daily Revenue | `SUM(fact_orders[revenue_net])` |
| AOV | `SUM(revenue_net) / DISTINCTCOUNT(order_id)` |
| Orders | `DISTINCTCOUNT(order_id)` |
| Repeat Customer Rate | `DISTINCTCOUNT(customers with total_orders > 1 in selected period) / DISTINCTCOUNT(customers in selected period)` |
| Ad Spend | `SUM(fact_ad_spend[spend])` |
| ROAS | `SUM(revenue_net) / SUM(ad_spend)` |

Include WoW or vs prior period delta on each KPI.

# 6) Color Scheme and Theme

Use a **clean modern ops theme**:
- Revenue: **#2E8B57**
- AOV: **#1F77B4**
- Ad Spend: **#D62728**
- Repeat/Retention: **#9467BD**
- Neutral text/background: **#F8FAFC, #334155, #CBD5E1**
- Underperforming SKUs conditional color: **amber/red**
Use white cards, subtle shadows, compact typography, and consistent date formatting.

# 7) Ready-to-Paste AI Builder Prompt

```text
Build an interactive operations dashboard for a DTC skincare Shopify brand in Power BI.

Data model:
- fact_orders: order_id, order_date, customer_id, sales_channel, revenue_gross, discounts, refunds, revenue_net, shipping, tax, total_units, first_order_flag
- fact_order_items: order_id, order_date, sku_id, product_name, variant_name, category, units_sold, line_revenue_net, line_discount
- fact_ad_spend: date, platform, campaign_name, ad_channel, spend, impressions, clicks, conversions
- dim_customer: customer_id, first_order_date, total_orders_lifetime, acquisition_channel, country
- dim_product: sku_id, sku, product_name, category, collection, launch_date, status
- dim_date: date, day, week, month, quarter, year, day_of_week

Create relationships by date, customer_id, sku_id, and order_id.

Add filters/slicers:
- date range
- sales channel
- ad platform
- category
- SKU search

Create KPI cards:
- Daily Revenue = sum revenue_net
- AOV = sum revenue_net / distinct orders
- Orders = distinct order_id
- Repeat Customer Rate = repeat customers / total customers in selected period
- Ad Spend = sum spend
- ROAS = revenue_net / ad_spend
Show prior-period delta on each KPI.

Layout on a 12-column grid:
- Header and filters at top
- KPI row beneath
- Trend chart for revenue/AOV/ad spend with metric toggle
- Channel mix chart
- SKU performance table with conditional formatting
- Top/bottom SKU bar chart with toggle
- New vs repeat customer trend
- Repeat rate by channel

Interactivity:
- all visuals cross-filter
- drill-down date hierarchy day > week > month
- click SKU or channel to filter the page
- rich tooltips with revenue, units, orders, AOV, spend, ROAS
- highlight underperforming products with low revenue and high spend

Style:
- modern clean ops dashboard
- colors: revenue #2E8B57, AOV #1F77B4, ad spend #D62728, repeat #9467BD
- white cards, light gray background, compact typography
```

### 1. Recommended AI Dashboard Builder Tool
Streamlit is recommended for its seamless Shopify API integration via Python libraries like shopify-python-api, quick AI-assisted code generation (e.g., via GitHub Copilot or Cursor AI), and native Plotly support for highly interactive charts with cross-filters, drill-downs, and date/channel selectors. It's free, deployable in minutes, and excels for ops teams needing custom DTC analytics without vendor lock-in.

### 2. Data Model and Required Fields
Use a star schema with these tables (import from Shopify API, Google Analytics 4, Meta Ads API, Google Ads API via CSV/ BigQuery exports):

| Table       | Key Fields                          | Notes |
|-------------|-------------------------------------|-------|
| Orders     | order_date, revenue, aov, customer_id, channel (Shopify/Meta/Google), product_sku |
| Customers  | customer_id, is_repeat (binary), first_order_date |
| Products   | sku, product_name, category, units_sold |
| Ads        | date, platform (Meta/Google), spend, revenue_attributed |

Join on date/customer_id/sku/channel.

### 3. Layout with Named Sections and Grid Placement
2-column responsive grid (Streamlit columns):
- **Row 1 (Full Width)**: Global Filters (date range picker, channel multi-select dropdown).
- **Row 2**: Left: KPI Cards (1x4 grid); Right: Daily Revenue Line Chart.
- **Row 3**: Left: Top SKUs Bar Chart; Right: AOV Trend + Repeat Rate Cards.
- **Row 4 (Full Width)**: Ad Spend vs Revenue Stacked Bar + Product Drill-Down Table.

### 4. Charts and Widgets
- **Daily Revenue Line**: Line chart; Metric: sum(revenue); Dimension: order_date; Filters: date range, channel; Interactivity: zoom, cross-filter to table/SKUs, hover tooltips (AOV/revenue).
- **Top SKUs Bar**: Horizontal bar; Metric: sum(revenue), count(units_sold); Dimension: sku/product_name; Filters: date, channel; Interactivity: top-10 sort, click drill-down to product table (shows underperformers < avg revenue).
- **Ad Spend vs Revenue Stacked Bar**: Stacked bar; Metric: sum(spend), sum(revenue_attributed); Dimension: date/platform; Filters: date, channel; Interactivity: cross-filter to revenue line, ROAS tooltip (revenue/spend).
- **Product Drill-Down Table**: Data table; Metrics: revenue, units, AOV; Dimensions: sku, category; Filters: all global + revenue threshold slider; Interactivity: sort/search, row-click expands to customer details.

### 5. KPI Cards with Formulas
Four cards (update on filter change):
- **Total Revenue**: `sum(revenue)` over filtered period.
- **AOV**: `sum(revenue) / count(orders)`.
- **Repeat Customer Rate**: `count(distinct repeat_customers) / count(distinct customers) * 100`.
- **ROAS**: `sum(revenue_attributed) / sum(spend)`.

### 6. Suggested Color Scheme and Theme
Skincare-inspired pastel theme: Primary (#E8F5E8 mint green), Accent (#F0C0A8 peach), Warning (#FFD1DC pink for underperformers), Dark text (#2C5530). Use Streamlit's light theme with rounded corners.

### 7. Ready-to-Paste Streamlit AI Builder Prompt
```python
Build a Streamlit dashboard for Shopify DTC skincare sales ops. Use Pandas for data (assume df_orders, df_customers, df_products, df_ads loaded). Add st.sidebar date_range_slider and multiselect channel filter ['Shopify','Meta','Google']. KPIs in st.columns: total_revenue = df['revenue'].sum(), aov = revenue/orders, repeat_rate = repeats/customers*100, roas=attr_rev/spend. Charts with plotly.express: px.line revenue by date, px.bar top10 sku revenue, px.bar stacked ad spend vs rev by platform/date, aggrid table for sku drill-down with filters. Enable cross-filtering, drill-downs, tooltips. Pastel colors: {'green':'#E8F5E8', 'peach':'#F0C0A8'}. Make fully interactive, responsive grid layout as specified.
```
(Word count: 548)