-- events table
-- event_type: 'lesson_completed', 'streak_updated'
-- event_time: timestamp

WITH first_lesson AS (
    SELECT 
        user_id,
        MIN(event_time) AS first_lesson_time
    FROM events 
    WHERE event_type = 'lesson_completed'
    GROUP BY user_id
),
streaks AS (
    SELECT 
        e.user_id,
        e.event_time::date AS activity_date,
        ROW_NUMBER() OVER (PARTITION BY e.user_id ORDER BY e.event_time::date) AS day_number
    FROM events e
    JOIN first_lesson fl ON e.user_id = fl.user_id
    WHERE e.event_type = 'lesson_completed'
      AND e.event_time::date <= fl.first_lesson_time::date + INTERVAL '30 days'
),
cohorts AS (
    SELECT 
        user_id,
        CASE 
            WHEN MAX(day_number) >= 7 THEN '7-day_streak'
            WHEN MAX(day_number) >= 3 THEN '3-day_streak'
            ELSE 'low_streak'
        END AS cohort
    FROM streaks
    GROUP BY user_id
),
daily_active AS (
    SELECT 
        c.cohort,
        DATE_TRUNC('day', e.event_time) AS activity_day,
        COUNT(DISTINCT e.user_id) AS dau
    FROM events e
    JOIN cohorts c ON e.user_id = c.user_id
    WHERE e.event_type = 'lesson_completed'
    GROUP BY c.cohort, activity_day
),
cohort_size AS (
    SELECT cohort, COUNT(*) AS size
    FROM cohorts
    GROUP BY cohort
)
SELECT 
    da.cohort,
    da.activity_day,
    da.dau,
    cs.size,
    ROUND(100.0 * da.dau / cs.size, 2) AS retention_pct
FROM daily_active da
JOIN cohort_size cs ON da.cohort = cs.cohort
WHERE da.activity_day <= '2025-05-01'
ORDER BY da.cohort, da.activity_day;

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

# events: user_id, event_time, event_type
data = pd.read_csv('spotify_events.csv')
data['event_time'] = pd.to_datetime(data['event_time'])

# Step 1: First playlist creation
first_playlist = (
    data[data['event_type'] == 'playlist_created']
    .groupby('user_id')['event_time'].min()
    .reset_index()
    .rename(columns={'event_time': 'first_playlist_time'})
)

data = data.merge(first_playlist, on='user_id', how='left')

# Step 2: Define cohort
data['cohort'] = data['first_playlist_time'].apply(
    lambda x: 'creator' if pd.notna(x) else 'non_creator'
)

# Step 3: Daily active users
data['activity_day'] = data['event_time'].dt.date
daily = (
    data[data['event_type'].isin(['play_song', 'skip_track'])]
    .groupby(['cohort', 'activity_day'])['user_id']
    .nunique().reset_index(name='dau')
)

# Step 4: Cohort size
cohort_size = data.groupby('cohort')['user_id'].nunique().reset_index()
cohort_size = cohort_size.rename(columns={'user_id': 'size'})

daily = daily.merge(cohort_size, on='cohort')
daily['retention'] = daily['dau'] / daily['size']

# Plot
plt.figure(figsize=(10, 6))
sns.lineplot(data=daily, x='activity_day', y='retention', hue='cohort')
plt.title('Retention: Playlist Creators vs Non-Creators')
plt.ylabel('Retention Rate')
plt.xlabel('Days Since First Activity')
plt.show()

WITH first_signup AS (
    SELECT user_id, MIN(event_time) AS signup_time
    FROM events WHERE event_type = 'signup'
    GROUP BY user_id
),
first_share AS (
    SELECT 
        e.user_id,
        MIN(e.event_time) AS first_share_time
    FROM events e
    WHERE e.event_type = 'page_shared'
    GROUP BY e.user_id
),
behavioral_cohort AS (
    SELECT 
        fs.user_id,
        CASE 
            WHEN fsh.first_share_time IS NOT NULL 
             AND fsh.first_share_time <= fs.signup_time + INTERVAL '24 hours'
            THEN 'fast_sharer'
            ELSE 'standard_user'
        END AS cohort
    FROM first_signup fs
    LEFT JOIN first_share fsh ON fs.user_id = fsh.user_id
),
monthly_active AS (
    SELECT 
        bc.cohort,
        DATE_TRUNC('month', e.event_time) AS activity_month,
        COUNT(DISTINCT e.user_id) AS mau
    FROM events e
    JOIN behavioral_cohort bc ON e.user_id = bc.user_id
    WHERE e.event_type IN ('page_view', 'page_edit')
    GROUP BY bc.cohort, activity_month
)
SELECT 
    cohort,
    activity_month,
    mau,
    FIRST_VALUE(mau) OVER (PARTITION BY cohort ORDER BY activity_month) AS cohort_size,
    ROUND(100.0 * mau / FIRST_VALUE(mau) OVER (PARTITION BY cohort ORDER BY activity_month), 2) AS retention
FROM monthly_active
ORDER BY cohort, activity_month;

class BehavioralCohort:
    def __init__(self, events_df, behavior_event, time_window='24h'):
        self.events = events_df
        self.behavior = behavior_event
        self.window = time_window

    def define_cohort(self, first_event='signup'):
        first = (
            self.events[self.events['event_type'] == first_event]
            .groupby('user_id')['event_time'].min()
            .reset_index()
            .rename(columns={'event_time': 'first_time'})
        )

        behavior = (
            self.events[self.events['event_type'] == self.behavior]
            .groupby('user_id')['event_time'].min()
            .reset_index()
            .rename(columns={'event_time': 'behavior_time'})
        )

        merged = first.merge(behavior, on='user_id', how='left')
        merged['cohort'] = np.where(
            merged['behavior_time'] <= merged['first_time'] + pd.Timedelta(self.window),
            f'{self.behavior}_within_{self.window}',
            'control'
        )
        return merged[['user_id', 'cohort']]

    def retention_heatmap(self, activity_events=['page_view']):
        # ... same as before
        pass

behavioral-cohorts/
├── sql/
│   ├── duolingo_streak.sql
│   ├── spotify_creator.sql
│   └── notion_sharer.sql
├── python/
│   ├── cohort_framework.py
│   └── dashboards/
│       ├── streak_retention.ipynb
│       └── creator_ltv.ipynb
├── data/
│   └── sample_events.csv
└── README.md

# Behavioral Cohort Analysis

## Cohorts Included
- **Duolingo**: 7-day streak starters
- **Spotify**: First playlist creators
- **Notion**: Fast sharers (within 24h)

## Key Finding
> Users who take a **high-intent action early** retain **3–5x better**

## Run
```bash
python python/cohort_framework.py --product duolingo