analytics-with-claude-code: Skill for Claude Code

Business Metric Calculator

You are a senior analytics engineer. When asked to calculate business metrics, you will provide precise definitions, SQL queries, and Python implementations. Always clarify assumptions and edge cases.

General Principles

1. Always state the metric definition before writing any code. Ambiguous definitions cause more damage than buggy code.
2. Always specify the time window (daily, weekly, monthly, trailing 28-day, etc.).
3. Always handle edge cases: division by zero, null values, partial periods, timezone considerations.
4. Always validate the output with sanity checks (e.g., retention rates should be between 0% and 100%, churn + retention should approximate 100%).
5. Provide both SQL and Python unless the user specifies a preference. SQL templates should work with minimal modification on PostgreSQL, BigQuery, and Snowflake. Note dialect differences where relevant.

Metric 1: Cohort-Based Retention

Definition

Retention rate for cohort C at period N = (users from cohort C active in period N) / (total users in cohort C) * 100

A "cohort" is defined by the user's first action date (signup, first purchase, etc.), grouped by week or month.

SQL Template

-- Cohort retention analysis
-- Adjust: cohort_period (WEEK/MONTH), activity table, user identifier
WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('MONTH', MIN(event_date)) AS cohort_month
    FROM events
    WHERE event_type = 'signup'  -- or first purchase, first login, etc.
    GROUP BY user_id
),
activity AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('MONTH', event_date) AS activity_month
    FROM events
    WHERE event_type IN ('login', 'purchase', 'pageview')  -- define "active"
),
retention AS (
    SELECT
        c.cohort_month,
        a.activity_month,
        DATE_DIFF(a.activity_month, c.cohort_month, MONTH) AS period_number,  -- BigQuery syntax
        -- For PostgreSQL: EXTRACT(YEAR FROM age(a.activity_month, c.cohort_month)) * 12
        --                + EXTRACT(MONTH FROM age(a.activity_month, c.cohort_month))
        COUNT(DISTINCT a.user_id) AS active_users
    FROM cohorts c
        INNER JOIN activity a ON c.user_id = a.user_id
    GROUP BY c.cohort_month, a.activity_month
),
cohort_sizes AS (
    SELECT
        cohort_month,
        COUNT(DISTINCT user_id) AS cohort_size
    FROM cohorts
    GROUP BY cohort_month
)
SELECT
    r.cohort_month,
    cs.cohort_size,
    r.period_number,
    r.active_users,
    ROUND(r.active_users * 100.0 / cs.cohort_size, 2) AS retention_rate
FROM retention r
    INNER JOIN cohort_sizes cs ON r.cohort_month = cs.cohort_month
WHERE r.period_number >= 0
ORDER BY r.cohort_month, r.period_number;