Overview
Background
To know how many users consume once a month, every two months, every three months, or else, you can calculate users' consumption cycles to figure out their stickiness.
Preview
As can be seen from the figure below, users who consume once every 6 months are the most.
For online preview, click Consumption Cycle.
You can click Save as subject to make this chart by yourself or view the editing steps.
Procedures
Adding Data
Sample data: Consumption Timetable.xlsx
Create a subject and upload the sample data, as shown in the following figure.
Creating a Component
To calculate the consumption cycle, you need to use the formula: Consumption cycle = Consumption time span / Consumption times.
Calculating the Consumption Time Span
Formula: Consumption time span = Latest consumption time - First consumption time
1. Add a calculation indicator to calculate the first consumption time, as shown in the following figure.
| Formula | Description | Function Reference |
|---|---|---|
DEF(COUNTD_AGG(Purchase Time), [User ID]) | Calculate the first consumption time of each user. |
2. Add a calculation indicator to calculate the latest consumption time, as shown in the following figure.
| Formula | Description | Function Reference |
|---|---|---|
DEF(COUNTD_AGG(Purchase Time), [User ID]) | Calculate the latest consumption time of each user. |
3. Add a calculation indicator to calculate the consumption time span of each user, as shown in the following figure.
The DATEDIF function is used here to calculate the time span by month between the first consumption time and the latest consumption time. For details about this function, see DATEDIF.
Calculating the Consumption Times of Each User
Add a calculation indicator to calculate the consumption times of each user, as shown in the following figure.
| Formula | Description | Function Reference |
|---|---|---|
COUNTD_AGG(Purchase Time) | Perform a distinct count on the consumption times. | |
DEF(COUNTD_AGG(Purchase Time), [User ID]) | Perform a distinct count on the consumption times of each user. (Multiple purchases within a day are counted as once only.) | / |
Calculating the Consumption Cycle
Formula: Consumption cycle = Consumption time span / Consumption times
For easier analysis, the ROUND function can be used to round the results to the nearest integer, as shown in the following figure.
To calculate the number of users with different consumption cycles, convert Consumption Cycle to dimension, as shown in the following figure.
Calculating the Number of Users
Add a calculation indicator to perform a distinct count on the number of users, as shown in the following figure.
Creating a Chart Component
Set the chart type as Multi-Series Column Chart and set the group way of Consumption Cycle as Merge same items as a group, as shown in the following figure.
Through the previous steps, the component showcasing the consumption cycle of each user is created.
Demonstration
For the demonstration of this component, see section "Preview".
