Repurchase rate analysis

1. Overview

1.1 Concept

The repurchase rate refers to the number of purchases in the recent period of time, which is used to indicate the loyalty of users, and the reverse indicates the stickiness of users of goods or services.

1.2 Problems solved

What is the monthly repurchase rate of old users?

What impact do different promotion channels have on repurchase?

Which products have a high repurchase rate, and which products need to focus on increasing the repurchase rate?

1.3 Expected effect

1.4 Implementation ideas

User repurchase rate calculation formula: the number of users who have purchased twice or more in a certain period of time/the total number of users who have purchased 

2. Example

Sample data: 

371.Repurchase rate analysis.xlsx

2.1 Calculate the number of users who have purchased twice or more

Create a new self-service dataset under the data preparation, and select all the fields under the "Repurchase rate analysis" data table, as shown in the following figure:

Since it is necessary to calculate the number of customers who have purchased more than twice in a certain period of time, we need to filter out the same users corresponding to different purchase times, that is, customers who have purchased more than twice.

Click "+" to add a group summary step, and drag the "Customer ID" and "Purchase date" fields into the "Grouping Column", as shown in the figure below:

Add a new row of records to record the number of purchases made by all customers at different times, and add the "New column" function. After naming it, enter "1", and click "OK", as shown in the figure below:

Add a grouping summary step, drag "customer ID" into the grouping column, drag the "1" count into the summary column, you can calculate the number of purchases for each customer, as shown in the following figure:

Filter out data greater than 1, that is, filter out customers who have purchased more than twice, as shown in the following figure:

Add a new column, name it "Count", enter "1", and click "OK" to count customers who have purchased more than once, as shown in the following figure:

Name and save the self-service dataset.

2.2 Data consolidation

Create a new self-service dataset and select all the fields under the "Repurchase data analysis" data table, as shown in the figure below:

Add the left and right merge steps, use all the fields under the self-service dataset created in section 2.1 as merge fields, click "OK". The merge method is "left merge", and the merge basis is "Customer ID", as shown in the following figure:

Name and save the self-service dataset.

2.3 Calculate the repurchase rate

Enter the dashboard, create a new dashboard, name and select the storage location, click "OK", add components, select the "Repurchase data analysis" created in section 2.2, and click "OK", as shown in the following figure:

Copy the "Customer ID" field to "Customer ID1", click the drop-down of the "Customer ID1" field, and select "Convert to indicator" to convert the customer ID1 into an indicator for recounting, as shown in the following figure:

Click to add a calculation indicator, name it "Repurchase rate", and enter the formula COUNTD_AGG(IF(Count!=NULL, Customer ID, NULL))/COUNTD_AGG(Customer ID). Click "OK", as shown in the figure below:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

Formula description:

Select "Custom Chart", drag "Purchase date" into the horizontal axis and set the format to "Year Month", drag the "Customer ID" and "Repurchase rate" converted into indicators into the vertical axis, and set the graphic properties to "Column chart" and "Line Chart", as shown in the following figure:

Click "Repurchase rate", set the value axis, and select the right value axis, as shown in the figure below:

2.4 Effect display

See section 1.3 of this article for details.

3. Conclusion analysis

According to the time trend graph of the number of purchased users and the repurchase rate, the health of user stickiness can be seen by superimposing the repurchase rate with the total number of users. The best state is that the repurchase rate does not change with the number of users. Maintaining an upward trend. Because with the development of the company, the users who create long-term value for the company must be these old users.