User Retention Charts
1. Read the data
select
sessions.user_id,
date_trunc('week',users.signed_up_at) as signed_up_at_week,
floor(extract('day' from session_started_at - signed_up_at)/7) as week -- The number of weeks that passed since the user signed up
from
sessions
left join users on sessions.user_id = users.user_id
2. Select the time range
We have data for the last 365 days, but we don't usually need to look so far back. Let's restrict our analysis to a specific time range.
StartDate
EndDate
import pandas as pd
# Round these time ranges to the nearest week
week_start = pd.to_datetime(StartDate).to_period('W').to_timestamp().tz_localize('UTC')
week_end = pd.to_datetime(EndDate).to_period('W').to_timestamp().tz_localize('UTC')
# Filter out any cohorts outside our time range
sessions_weekly = sessions_weekly.loc[
(sessions_weekly.signed_up_at_week >= week_start) & (sessions_weekly.signed_up_at_week <= week_end)
].copy()
Execution Error
NameError: name 'StartDate' is not defined
df = sessions_weekly.copy()
df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 190335 entries, 0 to 190334
Data columns (total 3 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 user_id 190335 non-null object
1 signed_up_at_week 190335 non-null datetime64[ns, UTC]
2 week 190335 non-null float64
dtypes: datetime64[ns, UTC](1), float64(1), object(1)
memory usage: 4.4+ MB
3. Calculate retention
retention = sessions_weekly.copy()
# Save the cohort size before we start calculating retention
cohort_size = retention.groupby(['signed_up_at_week']).user_id.nunique().reset_index(name='cohort_size')
# For each cohort-week, calculate the number of users who visited
retention = retention.groupby(['signed_up_at_week','week']).user_id.nunique().reset_index(name='n_users')
# Pivot and melt the table. This is a little trick that allows us to add rows during weeks where a cohort was not active.
retention = retention.pivot(index=['signed_up_at_week'],columns='week',values='n_users').fillna(0)
retention = retention.melt(value_name='n_users',ignore_index=False).reset_index()
# If part of the cohort is still not finished the week, then exclude that cohort-week from the data
# To do this we add 6 days to the sign up date. This gives us the last sign ups of that cohort.
retention = retention.loc[
~(retention.signed_up_at_week + pd.to_timedelta(retention.week + 1,'W') + pd.Timedelta(6,'D') >
pd.Timestamp.now(tz='UTC').floor('D'))
]
# Divide by the cohort size to get a percentage
retention = retention.merge(cohort_size,on=['signed_up_at_week'])
retention['prop'] = retention.n_users / retention.cohort_size
4. Visualize
# Rename the columns so they are more pretty
retention = retention.rename(columns = dict(
prop='Retention',
week='Week',
signed_up_at_week='Cohort',
n_users='Number of users',
cohort_size='Cohort size'
))
Let's start by plotting a time series of each cohort's retention rate. This is useful for identifying changes over time. For example, we can see that our cohorts in September are performing much better than the ones in May through August.
Sometimes it's helpful to look at the number of users, not just the retention rate. It's also useful to visualize retention as a matrix, to find any outliers.
import altair as alt
base=alt.Chart().encode(
x=alt.X('Week:N', title='Week', axis=alt.Axis(labelAngle=0)),
y=alt.Y('monthdate(Cohort):N', title='Cohort')
)
rect=base.mark_rect(tooltip=True).encode(
alt.Color('Retention', scale=alt.Scale(scheme='greenblue'))
)
text=base.mark_text(tooltip=True, color='white').encode(
alt.Text('Number of users', format=''),
)
(rect+text).properties(data=retention, width=800, height=500)