Dears,
I want to know the recommended method of visualizing lifecycle analysis for a mobile application.
To decide active, returned, and churned users, I relied on performing the calculations in Athena using SQL similar to below (full article: SQL for calculating Churn, Retention & Re-Engagement).
with monthly_usage as (
select
who_identifier,
datediff(month, '1970-01-01', when_timestamp) as time_period
from events
where event = 'login' group by 1,2 order by 1,2),
lag_lead as (
select who_identifier, time_period,
lag(time_period,1) over (partition by who_identifier order by who_identifier, time_period),
lead(time_period,1) over (partition by who_identifier order by who_identifier, time_period)
from monthly_usage),
lag_lead_with_diffs as (
select who_identifier, time_period, uses_outlook, lag, lead,
time_period-lag lag_size,
lead-time_period lead_size
from lag_lead),
calculated as (select time_period,
case when lag is null then 'NEW'
when lag_size = 1 then 'ACTIVE'
when lag_size > 1 then 'RETURN'
end as this_month_value,
case when (lead_size > 1 OR lead_size IS NULL) then 'CHURN'
else NULL
end as next_month_churn,
count(distinct who_identifier)
from lag_lead_with_diffs
group by 1,2,3)
select time_period, this_month_value, sum(count)
from calculated group by 1,2
union
select time_period+1, 'CHURN', count
from calculated where next_month_churn is not null
order by 1
I actually married the query above with a date dimension generation field using unnest (sequence), since some time intervals might be missing.
Following this I visualized in Quicksight using : 1- pivot tables (retention rate cohort analyis), 2- stacked bar chart (check percentage of each segment across time).
Do you think this is a correct approach? Is there any simpler way to do such analysis in QuickSight?