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.
Why do we want this?
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.
For example, you are working on a multi-product app. And you want to do a marketing campaign for users who are active on Product A and Product B but churned on Product C. By using user lifecycle, it is very easier to extract such user bases and perform post-campaign analysis to gauge the success.
More specific example (fintech): Let’s say Money Transfer (Product) has a monthly active base of 1M in January, and in Feb the base has increased to 1.2M. Now we can see what group has contributed to the overall growth. By digging into the data, we found out that in Feb, fewer users churned out and there were almost 800K users who started using Money Transfer for the first time in Feb. So, the most important factor in the growth was new users addition.
Let’s jump on what exactly is user lifecycle analysis.
While creating 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 achurn user 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)
How to create user lifecycle for a multi product app?
Being a data analyst in my previous jobs, I am very comfortable in writing SQL queries. So instead of working on mixpanel etc tools to create a user lifecycle, I started working on SQL. This also give me more autonomy on how to transform raw data according to the requirement.
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,
product from
transaction_table
where transaction_date= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) --to get previous day data group by 1,2,3
The output will be user_id ,transaction_date and product. Let’s say you are working on fintech app, Product could be money transfer, bill payment , savings etc
Example of how transaction table looked like:
| user_id | Transaction_date | Product | Amount |
| 123a | 2023-06-21 | Bill payment | 105 |
| 431c | 2023-06-24 | savings | 21 |
| 689j | 2023-06-24 | Bill payment | 23 |
| 543u | 2023-01-04 | Money transfer | 42 |
| 543u | 2023-02-07 | Bill payment | 52 |
| 543u | 2023-06-03 | Money transfer | 10 |
| 543u | 2023-06-10 | Savings | 19 |
| 431c | 2023-05-15 | Money transfer | 09 |
| 123a | 2023-02-21 | savings | 18 |
| 123a | 2023-06-24 | savings | 18 |
User_activity table:
| user_id | Transaction_date | Product |
| 123a | 2023-06-21 | Bill payment |
| 431c | 2023-06-24 | savings |
| 689j | 2023-06-24 | Bill payment |
| 543u | 2023-01-04 | Money transfer |
| 543u | 2023-02-07 | Bill payment |
| 543u | 2023-06-03 | Money transfer |
| 543u | 2023-06-10 | savings |
| 431c | 2023-05-15 | Money transfer |
| 123a | 2023-02-21 | savings |
| 123a | 2023-06-24 | savings |
Next step is to use the above table in another query for creating the user lifecycle. The below can also be scheduled on daily basis.
create table user_lifecycle as Select user_id, product,
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.product,a.product) as product,
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),'yyyyM 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),'yyyyM 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
and a.product=b.product
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 | Product | last_active_date | DSLA | status |
| 123a | Bill payment | 2023-06-21 | 3 | O |
| 123a | Savings | 2023-06-24 | 0 | R |
| 689j | Bill payment | 2023-06-24 | 0 | N |
| 543u | Money transfer | 2023-06-03 | 21 | O |
| 543u | Bill payment | 2023-02-03 | 113 | C |
| 543u | savings | 2023-06-10 | 14 | O |
| 431c | Money transfer | 2023-05-15 | 40 | C |
Now let’s recall the example shared earlier: . you want to do a marketing campaign for users who are active on Product A(Money transfer) and Product B(savings) but churned on Product C(Bill payment). Now with the above output, you can simply extract by using this query:
Select A.user_id from
(Select user_id from user_lifecycle where product =’Money transfer’ and DSLA <30)as A
inner join
(Select user_id from user_lifecycle where product =’savings’ and DSLA <30)as B
On A.user_id=B.user_id
(Select user_id from user_lifecycle where product =’Bill payment’ and DSLA >=30)as C
On B.user_id=C.user_id
This will help you extract users who are active on Product A(Money transfer) and Product B(savings) but churned on
Product C(Bill payment). And as per the above output table, we will get 543u (user).
Now we can do targeted campaigns, send push notifications/emails or SMS to hook them back at our app. And
at easypaisa, we have been successfully targeting our users to increase usage on our app.
We recently did a campaign for the churned bill payment users who were active on other products. And by doing this targeted campaign ,we effectively managed to get 15% incremental user on our bill payment product.
You can tailor this above query or churn user cycle as per your requirement and product specifications. Exploring the lifecycle journey will not help you do campaigns, but it will also help you discover how users are engaging with your product and at what stage they are coming back to your product.
This work is also published on Reforge