How to screen out high-value users from tens of millions or even millions of users?

When you have accumulated tens of millions or even millions of users, how can you screen out high-value users for subsequent conversions?

Hello everyone, I am PM Daming . I will share some fragments of life and work with you here in the future.

Case Study:

The company has accumulated some old users in its original products. Due to the product ecology, user stickiness is caused and the usage time fluctuates according to the market heat, sometimes high and sometimes low. In order to make up for the shortcomings of the user ecology and open up new business lines at the same time, a complementary product has been developed. New products need to be promoted in the early stage, such as early closed testing, data recovery, user opinions, BUG feedback, etc.

Ask a question here:

“With limited initial launch and promotion budget, how can we select high-value users from tens of millions or even millions of users?”

Give everyone 3 seconds to think, you can type your answer in the comment section.

3,2,1.

alright.

But what I want to say is that the answer is not important. As for why, I will share a model in the following content.

The RFM model can be implemented.

So what is the RFM model?

The RFM model is an important tool and means to measure user value and user profitability.

It is often used in user value research and user-oriented operations, and can accurately identify user needs and maximize the effectiveness of strategies.

It can be applied to multiple industries and scenarios, such as the Internet, retail, e-commerce, banking, communications, etc.

RFM model definition:

Recently : The most recent consumption refers to the time of the user's last consumption. In theory, users with more recent consumption are more likely to respond to immediate goods or services, which helps to attract users to continue purchasing and win their loyalty.

Frequency : Consumption frequency refers to the number of times a user purchases within a specific period of time. The key is to encourage consumers to increase their purchase frequency and convert low-frequency buyers into high-frequency buyers. Frequent buyers usually have the highest satisfaction and loyalty.

Monetary : Consumption amount is the core of the database report. In theory, the M value and F value are the same, both with a time range, referring to the consumption amount within a period of time. Therefore, for general products, the M value has a relatively weak effect on customer segmentation.

Then compare it with the average (median) to get a qualitative description (high or low), and finally divide the users into 8 different categories. User stratification can be defined by applying the product's own user portrait.

Combine the above cases and models with the specific operation steps.

Preliminary data preparation:

User classification table: user recharge transaction flow, including user ID, recharge time, and recharge amount.

Sit tight, hold on, and get ready to go on the highway~~

Data processing:

first step:

Select some data samples to confirm the time difference between the recharge time and the data withdrawal time. The data samples used here are false data, which are mainly used to illustrate the operation steps.

Add a new "Distance Days" column as shown in the figure. Subtract the recharge time "C2" from the data retrieval time "D2" to calculate the distance days for the first user. Double-click the left button of the mouse to fill the "E2" column and calculate the distance days for the entire column.

Step 2:

Select all data to create a pivot table.

Drag the "user_id" column into the row area below, and then drag "user_id", "amount", and "days left" into the value area.

Calculation dimensions: user_id count, amount sum, and minimum number of days.

Click the "user_id" numerical calculation bar button with the left mouse button, select "Value Field Settings", select "Count" in "Calculation Type", and click "OK" to get the purchase frequency of each user.

Click the "Amount" numerical calculation bar button with the left mouse button, select "Value Field Settings", select "Sum" in "Calculation Type", and click "OK" to get the total recharge amount of each user.

Click the "Days from" numerical calculation bar button with the left mouse button, select "Value Field Settings", select "Minimum Value" in "Calculation Type", and click "OK" to get the time difference between the user's most recent purchase and data collection.

Copy the data obtained from the completed pivot table into a new worksheet, paste it as a numeric type, rename each new field in the column, add "R", "F", and "M" fields, and delete the data in the total row.

Step 3:

Calculate the R, F, and M values.

Enter "=IF(B2<AVERAGE(B:B),"Low","High")" in cell E2, set the R value of users whose last purchase was made less than the average number of days to "Low", otherwise it is "High", and get the R value of the entire column.

Fill the "R" column, and then fill the "F" and "M" values ​​to the right in the same way.

Create a new "RFM" field and enter "=E2&F2&G2" in cell H2 to get the RFM value.

The user VLOOKUP function substitutes the prepared user types into the new table, creates a new "User Type" field, and enters "=VLOOKUP(H2,User Type!E:F,2,0))" in cell I2 to obtain the type of each user.

The RFM model made with EXCEL spreadsheet has been created. The next thing to do is to screen out high-quality users in the list for promotion or other forms of promotional activities based on actual needs.