# Metric Calculator

> >

- **Type:** Skill
- **Install:** `agentstack add skill-adityawrk-analytics-with-claude-code-metric-calculator`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [adityawrk](https://agentstack.voostack.com/s/adityawrk)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [adityawrk](https://github.com/adityawrk)
- **Source:** https://github.com/adityawrk/analytics-with-claude-code/tree/main/.claude/skills/metric-calculator

## Install

```sh
agentstack add skill-adityawrk-analytics-with-claude-code-metric-calculator
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# 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

```sql
-- 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;
```

### Python Implementation

```python
def cohort_retention(df, user_col, date_col, activity_col=None, period='M'):
    """
    Calculate cohort retention.

    Parameters:
        df: DataFrame with user activity data
        user_col: column name for user identifier
        date_col: column name for event date
        activity_col: optional column to filter activity types
        period: 'M' for monthly, 'W' for weekly
    """
    df[date_col] = pd.to_datetime(df[date_col])

    # Determine cohort for each user
    cohorts = df.groupby(user_col)[date_col].min().dt.to_period(period).rename('cohort')

    # Determine activity period for each event
    df = df.merge(cohorts, on=user_col)
    df['activity_period'] = df[date_col].dt.to_period(period)
    df['period_number'] = (df['activity_period'] - df['cohort']).apply(lambda x: x.n)

    # Build retention table
    retention = (
        df.groupby(['cohort', 'period_number'])[user_col]
        .nunique()
        .reset_index()
        .rename(columns={user_col: 'active_users'})
    )

    cohort_sizes = retention[retention['period_number'] == 0][['cohort', 'active_users']].rename(
        columns={'active_users': 'cohort_size'}
    )
    retention = retention.merge(cohort_sizes, on='cohort')
    retention['retention_rate'] = (retention['active_users'] / retention['cohort_size'] * 100).round(2)

    # Pivot to triangle format
    triangle = retention.pivot_table(
        index='cohort', columns='period_number', values='retention_rate'
    )
    return triangle, retention
```

### Visualization

```python
def plot_retention_heatmap(triangle):
    plt.figure(figsize=(14, 8))
    sns.heatmap(
        triangle, annot=True, fmt='.1f', cmap='YlOrRd_r',
        vmin=0, vmax=100, cbar_kws={'label': 'Retention %'}
    )
    plt.title('Cohort Retention Heatmap')
    plt.xlabel('Period Number')
    plt.ylabel('Cohort')
    plt.tight_layout()
    plt.savefig('retention_heatmap.png', dpi=150, bbox_inches='tight')
    plt.close()
```

## Metric 2: Customer Lifetime Value (LTV)

### Definition
LTV = Average Revenue Per User (ARPU) * Average Customer Lifespan

Or more precisely: LTV = Sum of all future discounted cash flows from a customer.

### Simplified LTV (Historical)

```sql
-- Historical LTV by cohort
SELECT
    cohort_month,
    COUNT(DISTINCT user_id) AS users,
    SUM(revenue) AS total_revenue,
    SUM(revenue) / COUNT(DISTINCT user_id) AS ltv_to_date
FROM (
    SELECT
        t.user_id,
        DATE_TRUNC('MONTH', u.signup_date) AS cohort_month,
        t.revenue
    FROM transactions t
        INNER JOIN users u ON t.user_id = u.user_id
) sub
GROUP BY cohort_month
ORDER BY cohort_month;
```

### Projected LTV (using retention curves)

```python
def projected_ltv(retention_rates, arpu_per_period, discount_rate=0.10, periods=36):
    """
    Project LTV using retention curve and ARPU.

    Parameters:
        retention_rates: list of retention rates [1.0, 0.65, 0.45, ...]
        arpu_per_period: average revenue per user per period
        discount_rate: annual discount rate (converted to per-period)
        periods: number of periods to project
    """
    # Extrapolate retention if needed (exponential decay fit)
    if len(retention_rates)  3:1
- **Break-even risk**: LTV:CAC between 1:1 and 3:1
- **Unsustainable**: LTV:CAC  2 -> 3 -> 4).
- **Loose funnel**: user must complete all steps but order does not matter.
- Report which type you are calculating. If the user does not specify, use loose.

### Time-Bounded Funnel
Add a time constraint: user must complete the funnel within N hours/days of step 1.

```sql
-- Strict time-bounded funnel (within 7 days)
WITH step1 AS (
    SELECT user_id, MIN(event_time) AS step1_time
    FROM events WHERE event = 'page_view'
    GROUP BY user_id
),
step2 AS (
    SELECT e.user_id, MIN(e.event_time) AS step2_time
    FROM events e
        INNER JOIN step1 s1 ON e.user_id = s1.user_id
    WHERE e.event = 'add_to_cart'
        AND e.event_time > s1.step1_time
        AND e.event_time  120%
- **Good**: 100-120%
- **Concerning**: = start) & (df[date_col] <= as_of_date)]
        user_days = active.groupby(user_col)[date_col].nunique()
        results[f'L{w}_mean'] = user_days.mean()
        results[f'L{w}_median'] = user_days.median()
        results[f'L{w}_distribution'] = user_days.describe()
    return results
```

## Output Format

For every metric calculation, output:

```
## [Metric Name]

**Definition**: [precise definition]
**Time Period**: [period analyzed]
**Filters Applied**: [any filters]

**Result**:
| Period | Value | Change |
|--------|-------|--------|
| ...    | ...   | ...    |

**Sanity Checks**:
- [check 1]: PASSED / FAILED
- [check 2]: PASSED / FAILED

**Interpretation**: [1-2 sentences explaining what the number means in context]
**Caveats**: [any data quality issues, assumptions, or limitations]
```

## Edge Cases

- **Division by zero**: always use NULLIF(denominator, 0) in SQL. In Python, handle with np.where or explicit checks. Never let a division by zero produce an error or infinity in output.
- **Partial periods**: the current month is incomplete. Either exclude it or clearly label it as partial. Never compare a partial month to a full month without noting it.
- **Timezone mismatches**: ask the user what timezone their data is in. Event timestamps in UTC vs local time can shift daily metrics by up to 2 days.
- **Reactivated users**: decide whether reactivated users count as "new" or "returning" for retention and churn. Document the choice.
- **Free trials**: decide whether free trial users are included in revenue metrics. They should be excluded from ARPU/MRR by default unless the user specifies otherwise.
- **Refunds**: decide whether to use gross or net revenue. Default to net (after refunds).
- **Bot/test accounts**: always ask if there are known test accounts to exclude. Filter them out before calculating any metric.

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [adityawrk](https://github.com/adityawrk)
- **Source:** [adityawrk/analytics-with-claude-code](https://github.com/adityawrk/analytics-with-claude-code)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-adityawrk-analytics-with-claude-code-metric-calculator
- Seller: https://agentstack.voostack.com/s/adityawrk
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.