Data Analyst Plugin

claude plugins add knowledge-work-plugins/data

/analyze What was our monthly revenue trend for the past 12 months, broken down by product line?

/explore-data users table

## Dataset Profile: users

### Overview
- **Rows:** 2,347,891
- **Columns:** 47
- **Last updated:** 2024-02-27 14:32:00

### Data Quality
| Column | Type | Nulls | Cardinality | Notes |
|--------|------|-------|-------------|-------|
| user_id | bigint | 0% | 100% (2.3M unique) | Primary key |
| email | varchar | 0.2% | 99.8% | 4,234 duplicates |
| created_at | timestamp | 0% | - | Range: 2019-01-01 to 2024-02-27 |
| status | varchar | 0% | 4 distinct | active, inactive, suspended, UNKNOWN |

### Flags
- ⚠️ **status column** has unexpected value "UNKNOWN" in 340 rows
- ⚠️ **email** has 0.2% null values
- ✓ **user_id** has no duplicates or nulls

### Suggested Explorations
High-value dimensions to explore:
- **plan_type**: 5 distinct values, drives revenue
- **signup_source**: 12 distinct values, affects retention
- **country**: 87 distinct values, geographical analysis

/write-query I need a cohort retention analysis -- users grouped by signup month,
showing what % are still active 1, 3, 6, and 12 months later. We use Snowflake.

-- Cohort Retention Analysis
-- Users grouped by signup month with retention at 1, 3, 6, and 12 months

WITH cohorts AS (
    -- Define user cohorts by signup month
    SELECT
        user_id,
        DATE_TRUNC('month', signup_date) as cohort_month
    FROM users
    WHERE signup_date >= '2023-01-01'
),

activity AS (
    -- Get user activity by month
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', activity_date) as activity_month
    FROM user_events
    WHERE event_type IN ('login', 'action', 'purchase')
),

retention AS (
    -- Calculate retention for each cohort month
    SELECT
        c.cohort_month,
        COUNT(DISTINCT c.user_id) as cohort_size,
        
        -- Active in month 0 (signup month)
        COUNT(DISTINCT CASE 
            WHEN a.activity_month = c.cohort_month 
            THEN c.user_id 
        END) as month_0_active,
        
        -- Active in month 1
        COUNT(DISTINCT CASE 
            WHEN a.activity_month = DATEADD(month, 1, c.cohort_month)
            THEN c.user_id 
        END) as month_1_active,
        
        -- Active in month 3
        COUNT(DISTINCT CASE 
            WHEN a.activity_month = DATEADD(month, 3, c.cohort_month)
            THEN c.user_id 
        END) as month_3_active,
        
        -- Active in month 6
        COUNT(DISTINCT CASE 
            WHEN a.activity_month = DATEADD(month, 6, c.cohort_month)
            THEN c.user_id 
        END) as month_6_active,
        
        -- Active in month 12
        COUNT(DISTINCT CASE 
            WHEN a.activity_month = DATEADD(month, 12, c.cohort_month)
            THEN c.user_id 
        END) as month_12_active
        
    FROM cohorts c
    LEFT JOIN activity a ON c.user_id = a.user_id
    GROUP BY c.cohort_month
)

SELECT
    cohort_month,
    cohort_size,
    
    -- Retention percentages
    ROUND(100.0 * month_0_active / cohort_size, 1) as month_0_retention_pct,
    ROUND(100.0 * month_1_active / cohort_size, 1) as month_1_retention_pct,
    ROUND(100.0 * month_3_active / cohort_size, 1) as month_3_retention_pct,
    ROUND(100.0 * month_6_active / cohort_size, 1) as month_6_retention_pct,
    ROUND(100.0 * month_12_active / cohort_size, 1) as month_12_retention_pct
    
FROM retention
WHERE cohort_size >= 100  -- Filter out small cohorts
ORDER BY cohort_month;

/create-viz Create a bar chart comparing monthly revenue across product lines

/build-dashboard Create a sales dashboard with monthly revenue, top products,
and regional breakdown

/validate [shares analysis document]

## Analysis Validation: Churn Analysis Q1 2024

### Methodology Review
✓ **Sample size**: 45,000 users — statistically significant
⚠️ **Survivorship bias**: Analysis excludes users who churned before Jan 1
   - Could understate true churn rate by ~5pp
✓ **Time period**: 90 days — appropriate for monthly product

### Accuracy Checks
✓ **Aggregation logic**: Correctly calculates churn as (churned / starting_active)
⚠️ **Denominator**: Excludes trial users which could overstate conversion rate
✓ **Date filters**: Properly handles timezone and end-of-month boundaries

### Bias Detection
⚠️ **Selection bias**: Power users over-represented in sample
✓ **Confounding variables**: Properly controls for plan type and tenure

### Recommendations
1. Add caveat about trial user exclusion in executive summary
2. Include sensitivity analysis with trial users included
3. Add confidence intervals to churn rate estimates

### Verdict
**Ready to share with noted caveats**

Confidence: Medium-High

/analyze What was our monthly revenue trend for the past 12 months, broken down by product line?

/write-query We have a 500M row events table partitioned by date. 
Find the top 100 users by event count in the last 7 days with their most recent event type.
We use BigQuery.

-- Top 100 users by event count (last 7 days)
-- Optimized for BigQuery with partition pruning

WITH recent_events AS (
    SELECT
        user_id,
        event_type,
        event_timestamp,
        -- Rank events per user by recency
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY event_timestamp DESC
        ) as recency_rank
    FROM events
    WHERE 
        -- Partition filter for performance
        DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
),

user_event_counts AS (
    SELECT
        user_id,
        COUNT(*) as event_count,
        -- Get most recent event type
        MAX(CASE WHEN recency_rank = 1 THEN event_type END) as latest_event_type
    FROM recent_events
    GROUP BY user_id
)

SELECT
    user_id,
    event_count,
    latest_event_type
FROM user_event_counts
ORDER BY event_count DESC
LIMIT 100;

/build-dashboard Create a sales dashboard