User Lifecycle Analysis at Easypaisa
User lifecycle analysis to explore user behavior (with SQL)
Why do we want to do 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. 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.
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.
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 the same.
And when the user performs any activity from a 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 gives more autonomy on how to transform raw data according to the requirement.
At first, you have to 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 a daily basis. (let’s assume the activity is doing a transaction)