In today’s competitive landscape, understanding user behavior and engagement is crucial for businesses to drive growth, retain customers, and optimize their offerings. User lifecycle analysis provides a powerful framework for categorizing users into distinct stages based on their actions, behaviors, and engagement patterns. In this blog, we’ll explore the key stages of user lifecycle analysis and how businesses can leverage this approach to make informed decisions and enhance their overall success.
By breaking up the active user base into groups, it helps to reveal what exactly is contributing to the growth (or shrinkage!) of your user base, and helps teams build more sustainable user growth.
It can also shed insight on the success of your efforts to retain and engage users. For example, you could use it to measure whether or not the campaign you ran this week to target users who churned last week was successful or not.
When analyzing User Lifecycle we group users according to their state. The state describes the users’ current and past behavior.
| State | Description |
| New user | A user who is engaging for the first time. |
| Old user | A user who is part of active base. |
| Churn user | A user who was engaged in previous time period but did not engage in this period |
| Rejoiner | A user who was a Churner and engaged (now) to become a rejoiner. |
Every state in the above table is calculated by ‘day since last active’ DSLA. So for example, A New User will have DSLA=0, but on the very next day (if he didn’t perform any activity) his DSLA will become 1 and his state will be old. From now onwards, his state will be old (DSLA between 0 and 29) until his DSLA=30 and then his state will be Churn User. And if he doesn’t perform any activity, his DSLA will increase and his state will be same.
And when the user perform any activity from churned state, his DSLA will become 0 and his state will be ‘Rejoiner‘. A rejoiner can keep this state for only 1 day and from the next he will again become ‘Old User‘.
A bit confused 😕 about this. Let me explain this in a diagram.
I hope it’s clear now.
You can create your own definition of the states depending upon the needs and nature of your product. In the above example, we defined the activity period for different states as 30 day (DSLA =29)
Lifecycle management can help you develop targeted strategies for each stage of the user journey. By understanding the specific needs, behaviors, and pain points of users at different stages, businesses can tailor their marketing campaigns, communication, and product enhancements to address those needs effectively.
Now let’s jump into how this user lifecycle can be developed via SQL (you can escape here if you are not into SQL).
At first, you have create a table where you will insert the user activities data. Below is the query that can be used for insertion. And it should be scheduled on daily basis. (let’s assume the activity is doing a transaction)
Create table user_activity as Select user_id, transaction_date from transaction_table where transaction_date= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) --to get previous day data group by 1,2
The output will be user_id and transaction_date. Used group by function to remove any duplicates incase of multiple transactions by a single user.
Next step is to use the above table in another query for creating the user lifecycle. The below should also be scheduled on daily basis.
create table user_lifecycle as
Select user_id,
last_active_date,
DSLA,
case when status in ('DC','C') and
DSLA=0 then 'R'
else status end as status from
(Select coalesce(B.user_id,A.user_id) as user_id,
COALESCE(B.transaction_date,A.last_active_date) as last_active_date,
DATEDIFF(to_date(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),'yyyyMMdd'),
COALESCE(B.transaction_date,A.last_active_date)) as DSLA,
case when A.user_id is null then 'N'
when A.user_id is not null and status in ('N','O','R')
and DATEDIFF(to_date(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),'yyyyMMdd'),
COALESCE(B.transaction_date,A.last_active_date))
between 0 and 29
then 'O'
when A.user_id is not null and DATEDIFF(to_date(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),'yyyyMMdd'),
COALESCE(B.transaction_date,A.last_active_date)) =30
then 'DC'
when A.user_id is not null and DATEDIFF(to_date(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),'yyyyMMdd'),
COALESCE(B.transaction_date,A.last_active_date)) >30
then 'C'
else status end as status
from
(select * from user_lifecycle
where dt = DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
and user_id is not null
)as A
full outer join
(select * from user_activity
where transaction_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
)as B
on a.user_id=b.user_id
where coalesce(B.user_id,A.user_id) is not null)
So after executing the above two queries, output of user_lifecycle will be as follows:
| user_id | last_active_date | DSLA | status |
|---|---|---|---|
| 123a | 2023-06-21 | 3 | O |
| 431c | 2023-06-24 | 0 | N |
| 689j | 2023-04-24 | 61 | C |
| 543u | 2023-06-24 | 0 | R |
This is how to create a user lifecycle analysis via SQL. Now you can target churners or someone who is about to churn (DSLA between 25 and 29). It will help you to create more targeted campaigns for your whole user base.
In conclusion, user lifecycle analysis is an invaluable tool for product managers and data analysts alike. By understanding the stages and behaviors of users throughout their journey, businesses can make data-driven decisions to enhance user experiences, optimize conversion rates, and boost customer retention.