Use for: Trends over time
Example: Daily active users, revenue by month
When to use:
- Continuous data
- Show changes over time
- Compare multiple series
Comparison → Bar Chart
Use for: Comparing categories
Example: Revenue by product, sales by region
When to use:
- Categorical data
- Ranking (top 10)
- Part-to-whole (stacked bars)
Distribution → Histogram
Use for: Frequency distribution
Example: Order value distribution, user age ranges
When to use:
- Show data spread
- Identify outliers
- Normal vs skewed
Correlation → Scatter Plot
Use for: Relationship between variables
Example: Marketing spend vs revenue, price vs conversion
When to use:
- Two continuous variables
- Identify clusters
- Show outliers
Part-to-Whole → Pie Chart (Use Sparingly!)
Use for: Proportion of total (max 5 slices)
Example: Market share, traffic sources
Better alternative: Bar chart (easier to compare)
Geographic → Map
Use for: Location-based data
Example: Sales by state, user density
When to use:
- Spatial patterns
- Regional comparison
-- Metric: Monthly Recurring RevenueSELECT
DATE_TRUNC('month', subscription_start) ASmonth,
SUM(monthly_price) AS mrr,
COUNT(DISTINCT user_id) AS subscribers
FROM subscriptions
WHERE status ='active'AND subscription_start >= DATE_TRUNC('month', CURRENT_DATE-INTERVAL'12 months')
GROUPBY1ORDERBY1DESC-- Metric: Churn RateSELECT
DATE_TRUNC('month', cancelled_date) ASmonth,
COUNT(*) AS churned_customers,
ROUND(
COUNT(*)::NUMERIC/LAG(COUNT(*)) OVER (ORDERBY DATE_TRUNC('month', cancelled_date)) *100,
2
) AS churn_rate_pct
FROM subscriptions
WHERE status ='cancelled'AND cancelled_date >= DATE_TRUNC('month', CURRENT_DATE-INTERVAL'12 months')
GROUPBY1ORDERBY1DESC
Interactive Filters
Date Range Selector
-- Parameterized query in MetabaseSELECT
product_name,
SUM(revenue) AS total_revenue
FROM sales
WHERE sale_date BETWEEN {{start_date}} AND {{end_date}}
GROUPBY product_name
ORDERBY total_revenue DESC
LIMIT 10-- Parameters:-- start_date: Date field-- end_date: Date field
Multi-Select Filter
-- Filter by multiple regionsSELECT
region,
product_category,
SUM(revenue) AS revenue
FROM sales
WHERE region IN ({{regions}})
AND sale_date >=CURRENT_DATE-INTERVAL'30 days'GROUPBY region, product_category
-- Parameter:-- regions: Field filter on sales.region (multi-select)
Performance Optimization
Pre-Aggregation
-- Create materialized view for fast dashboard queriesCREATE MATERIALIZED VIEW daily_revenue_summary ASSELECTDATE(order_date) ASdate,
product_id,
region,
SUM(order_amount) AS revenue,
COUNT(*) AS order_count,
AVG(order_amount) AS avg_order_value
FROM orders
GROUPBY1, 2, 3;
-- Refresh nightlyCREATE INDEX ON daily_revenue_summary (date, region);
-- Query uses summary (fast)SELECT
region,
SUM(revenue) AS total_revenue
FROM daily_revenue_summary
WHEREdate>=CURRENT_DATE-INTERVAL'30 days'GROUPBY region;
Incremental Refresh
# Update only new dataimport pandas as pd
from datetime import datetime, timedelta
defincremental_refresh():
# Get last refresh timestamp
last_refresh = get_last_refresh_time()
# Query only new data
new_data = query_database(f"""
SELECT * FROM orders
WHERE updated_at > '{last_refresh}'
""")
# Append to existing data
append_to_dashboard_data(new_data)
# Update refresh timestamp
set_last_refresh_time(datetime.now())
Drill-Through & Drill-Down
Drill-Down (Hierarchy)
Revenue by Region
↓ (click region)
Revenue by Store
↓ (click store)
Revenue by Product
