Consumption Cycle

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.

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.

2. Add a calculation indicator to calculate the latest consumption time, as shown in the following figure.

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.

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".