Advanced Analytics with Metabase: Beyond the Basics

Introduction

Once you've mastered the basics of Metabase, it's time to explore advanced features that can transform your analytics capabilities.

Advanced SQL Techniques

Custom Metrics

WITH user_metrics AS (
  SELECT 
    user_id,
    COUNT(DISTINCT session_id) as session_count,
    SUM(time_spent) as total_time,
    MAX(last_activity) as last_seen
  FROM user_sessions
  GROUP BY user_id
)
SELECT 
  DATE_TRUNC('week', created_at) as week,
  COUNT(DISTINCT user_id) as active_users,
  AVG(session_count) as avg_sessions_per_user
FROM user_metrics
GROUP BY 1
ORDER BY 1 DESC

Custom Visualizations

Funnel Analysis

1. Create sequential queries:

WITH funnel AS (
  SELECT
    COUNT(DISTINCT user_id) as visitors,
    COUNT(DISTINCT CASE WHEN signup_date IS NOT NULL THEN user_id END) as signups,
    COUNT(DISTINCT CASE WHEN first_purchase_date IS NOT NULL THEN user_id END) as purchasers
  FROM user_journey
)
SELECT
  'Visitors' as stage,
  visitors as count,
  100.0 as conversion_rate
FROM funnel
UNION ALL
SELECT
  'Signups',
  signups,
  (signups::float / visitors * 100)
FROM funnel
UNION ALL
SELECT
  'Purchasers',
  purchasers,
  (purchasers::float / signups * 100)
FROM funnel

Automated Reporting

Pulse Configurations

{
  "name": "Weekly Performance Report",
  "cards": [
    {
      "id": 1,
      "include_csv": true,
      "include_xls": false
    }
  ],
  "channels": [
    {
      "enabled": true,
      "channel_type": "email",
      "schedule_type": "weekly",
      "schedule_day": "mon",
      "schedule_hour": 9,
      "schedule_frame": "first"
    }
  ]
}

Performance Optimization

Materialized Views

CREATE MATERIALIZED VIEW monthly_metrics AS
SELECT 
  date_trunc('month', created_at) as month,
  COUNT(*) as total_transactions,
  SUM(amount) as total_revenue,
  COUNT(DISTINCT user_id) as unique_users
FROM transactions
GROUP BY 1;

-- Refresh schedule
REFRESH MATERIALIZED VIEW monthly_metrics;

Dashboard Design Patterns

1. Executive Overview

• KPI Summary
• Trend Analysis
• Forecast Metrics

2. Operational Dashboard

• Real-time Metrics
• Alert Thresholds
• Action Items

3. Analysis Dashboard

• Detailed Breakdowns
• Cohort Analysis
• Custom Segments

Advanced Features

1. Custom Expressions

CASE 
  WHEN lifetime_value > 1000 THEN 'High Value'
  WHEN lifetime_value > 500 THEN 'Medium Value'
  ELSE 'Low Value'
END as customer_segment

2. Dynamic Filters

• Date Range Selectors
• Multiple Choice Filters
• Linked Filters

Conclusion

Advanced Metabase usage requires:

• Solid SQL foundation
• Understanding of business metrics
• Regular optimization
• Continuous learning

Remember to:

• Document complex queries
• Set up monitoring
• Train power users
• Review and refine regularly