Feature Adoption and Product Engagement

Product analytics answers: which features drive retention, who are the power users, and which accounts are zombies (subscribed but not using the product)?

1. Feature Adoption Rate

Adoption = accounts using a feature at least once / total active accounts:

WITH active_accounts AS (
    SELECT COUNT(*) AS total FROM saas_accounts
    WHERE status = 'active'
),
feature_adopters AS (
    SELECT
        feature_name,
        COUNT(DISTINCT account_id) AS adopters,
        SUM(usage_count)           AS total_uses,
        ROUND(AVG(usage_count), 1) AS avg_uses_per_account
    FROM saas_feature_usage
    WHERE usage_count > 0
    GROUP BY feature_name
)
SELECT
    fa.feature_name,
    fa.adopters,
    fa.total_uses,
    fa.avg_uses_per_account,
    ROUND(100.0 * fa.adopters / aa.total, 1) AS adoption_rate_pct
FROM feature_adopters fa
CROSS JOIN active_accounts aa
ORDER BY adoption_rate_pct DESC;

What This Returns

feature_name  | adopters | total_uses | adoption_rate_pct
--------------+----------+------------+------------------
Dashboard     |      275 |      14800 |              79.7
Reports       |      268 |      14300 |              77.7
Exports       |      260 |      13900 |              75.4
...

2. Zero-Usage Accounts (Zombie Accounts — Churn Risk)

SELECT
    a.id,
    a.name,
    a.acquisition_channel,
    p.name    AS plan,
    a.mrr_cents / 100 AS mrr_usd,
    a.converted_at::DATE,
    COALESCE(SUM(fu.usage_count), 0) AS total_usage_last_90d
FROM saas_accounts a
JOIN saas_plans p ON p.id = a.plan_id
LEFT JOIN saas_feature_usage fu
    ON  fu.account_id = a.id
    AND fu.period_date >= CURRENT_DATE - INTERVAL '90 days'
WHERE a.status = 'active'
GROUP BY a.id, a.name, a.acquisition_channel, p.name, a.mrr_cents, a.converted_at
HAVING COALESCE(SUM(fu.usage_count), 0) = 0
ORDER BY a.mrr_cents DESC;

3. Power User Identification

Power users are in the top decile of feature usage:

WITH account_usage AS (
    SELECT
        account_id,
        SUM(usage_count) AS total_usage
    FROM saas_feature_usage
    GROUP BY account_id
),
deciles AS (
    SELECT
        account_id,
        total_usage,
        NTILE(10) OVER (ORDER BY total_usage DESC) AS usage_decile
    FROM account_usage
)
SELECT
    d.account_id,
    a.name,
    a.plan_id,
    p.name AS plan,
    d.total_usage,
    d.usage_decile
FROM deciles d
JOIN saas_accounts a ON a.id = d.account_id
JOIN saas_plans p    ON p.id = a.plan_id
WHERE d.usage_decile = 1   -- top 10%
ORDER BY d.total_usage DESC;

4. Feature Co-Usage — Which Features Are Used Together?

SELECT
    f1.feature_name AS feature_a,
    f2.feature_name AS feature_b,
    COUNT(DISTINCT f1.account_id) AS co_usage_accounts
FROM saas_feature_usage f1
JOIN saas_feature_usage f2
    ON  f2.account_id = f1.account_id
    AND f2.feature_name > f1.feature_name
    AND f2.usage_count > 0
WHERE f1.usage_count > 0
GROUP BY f1.feature_name, f2.feature_name
ORDER BY co_usage_accounts DESC;

5. Feature Usage Trend Over Time

SELECT
    DATE_TRUNC('month', period_date)::DATE AS month,
    feature_name,
    SUM(usage_count)                        AS total_uses,
    COUNT(DISTINCT account_id)              AS active_accounts
FROM saas_feature_usage
GROUP BY 1, 2
ORDER BY 1, total_uses DESC;

Key Takeaway

HAVING SUM(usage) = 0 combined with a LEFT JOIN to usage data is the cleanest way to find "accounts with no activity" — the LEFT JOIN preserves accounts with no usage rows, and HAVING filters to those where the sum is zero. NTILE(10) divides accounts into deciles without needing to know the threshold values in advance — it adapts to any data distribution automatically.

← Previous Next →