User inflow and loss analysis

1. Overview

1.1 Background

Xiao Guo is the person in charge of a department store. He wants to analyze the competitiveness of various brands in the department store. This time he considers starting from the inflow and loss of users. While helping department stores understand the competitiveness of each brand, it can also help the person in charge of each brand see how their brands flow in and out.

He analyzed the behavior of inbound and outbound clients, and divided the inflow reasons into "other brand inflows, channel inflows, and category inflows", and loss reasons into "brand loss, channel loss, and category loss." After the fine division, the competitiveness of each brand is more clear.

1.2 Concept

Number of inflows: users who have had transactions in the past 6 months, and those who have not had transactions for 6 months are pushed forward.

The number of inflow: users who had transactions in the previous 6 months and no transactions in the past 6 months were pushed forward.

Take user A as an example. He has purchased Nokia's electronic products in the past six months, and he has not purchased Nokia's electronic products for six months. For Nokia's electronic product category, this user is an inflow client. But where did this user come from:

• Other brand inflow: If user A purchases electronic products from other brands in the department store six months ago, for the Nokia electronic product category, the client belongs to other brand inflow.

• Category inflow: If user A has only purchased products of other categories in the department store for six months before the push, he has not purchased electronic equipment. Then the reason for his inflow is because he needs products in the electronic category, so it belongs to category inflow.

• Channel inflow: If user A has not purchased goods in the department store for 6 months, it means that he is a new client of the department store, so he belongs to channel inflow.

Similarly, it can correspond to "brand loss, category loss, and channel loss". For the specific introduction of the three types of loss, please refer to section 2.2 of this article.

1.3 Expected effect

Many brands have multiple categories of products, but when these separate categories are market-oriented, they are independent individuals and need to be compared with other brand products in the same category.

When Xiao Guo analyzed the number of inflows and losses, he used "categories" to classify and analyze each brand.

1) After completing the dashboard, Xiao Guo sees in the table that the net inflow of the brand "Acme" in office supplies is negative, so click on "Acme", the right side shows the user inflow and loss of the Acme brand in the office supplies category: Brand The attrition is serious and needs to be focused on.

2) Xiao Guo also screened out the TOP10 net loss of brands, and analyzed which brands have declined in competitiveness, hoping to be used as a reference for adjusting the brands in the future.

3) Xiao Guo finally screened out the TOP 10 channels for inflows, analyzed which brands and certain product types have made outstanding contributions to attracting new department stores, and prepared to give these brand types of products some benefits.

Click to view Xiao Guo's dashboard: user inflow and loss analysis.

1.4 Get data

The sample data used in this article: department store data.xlsx (Because the sample data only counts to 2016-12-29, this article calculates the inflow and loss situation of 2016-12-29)

The user can click the user inflow and loss analysis edit page and save it as, and can view the operation mode in the dashboard and the data table used by the dashboard to learn.

2. Operation steps

2.1 Filter lost clients

1) Upload "department store data".

2) Create a self-service dataset, select the field of "department store data", as shown in the figure below:

3) Add "Group Summary" and drag into the fields. Select "drop down>Latest Time" for the "OrderDate", as shown in the figure below:

In this way, the time when the user last bought a certain brand in a certain category is calculated. As shown in the figure above, it can be seen that the last time "A" bought Hoover in the "stationery" category was: 2013-02-08.

4) Calculate the time with 2016-12-29 as the target, filter out the time of the last purchase, and the data where the last purchase was [6 months ago; within 12 months], as shown below:

This part of users purchased 6 months ago, but did not purchase it 6 months later. For a certain brand under a certain category, these users are lost users.

For example, Xing Ning, in the "electronic equipment" category, is Nokia's lost users.

5) Save and update the self-service dataset "loss users".    

2.2 Filter out clients with purchase records in the past six months

We have screened out the lost users. For "a certain brand in a certain category," they purchased it six months ago, but they have not purchased it in the past six months. What is the reason for this?

We can analyze their behavior in the past six months.

Take Xing Ning as an example. He bought Nokia's electronic equipment in the first six months:

• If he did not have a purchase record in the department store in thepast six months, it means that the user has lost the purchase channel.

• If he has a purchase record in a department store for the past six months, but he has not purchased electronic equipment. It shows that he does not need this category in the near future. For Nokia, he is a category of lost users.

• If he bought electronic equipment in a department store in the past six months, but bought other brands. For Nokia, this is a loss of brand users.

1) Create a new self-service dataset, name it "user purchase details in the past six months", and check the field, as shown below:

2) The calculation time is 2016-12-29. The purchase record for the past six months is obtained, as shown in the figure below:

3) Because users may have repeated purchases for a certain brand and certain category. Therefore, perform "Group Summary" once, and only keep one piece of data for the same, as shown below:

4) Save and update the self-service dataset.

2.3 Analysis of causes of loss

In section 2.2, we enumerated the behavior of all users in the past six months, but what we need is the behavior of losing users in the past six months.

1) Go back to the first self-service dataset "lost users" and merge it with the "user purchase details in the past six months". The merge method is "left merge", and the merge basis only retains the "ClientName", as shown below:

The combined effect is shown in the figure above. For example: O bought stationery in the past six months.

2) Add a mark column to mark the behaviors after six months ago and six months later.

If the categories purchased before and after June are the same: mark 2, which means brand loss.

If the purchase category after six months is empty, it means that the user has no purchase record in the department store in the past six months: mark 0, which means that the channel is lost.

Other situations: mark 1 to indicate category loss.

As shown in the picture above, the user "Feng Li" who lost stiletto office supplies has four purchase actions in the past six months. But no matter how many actions, there is only one final cause of loss. He has purchased office supplies in the past six months, indicating that he is a brand lost user.

3) For grouping and summarizing, only one mark of the same cause of loss needs to be kept, as shown below:

4) After confirming that the "causes of loss" will not be repeated, perform the "Group Summary" calculation, as shown below:

After grouping and summing the distinct values of 0, 1, and 2, the results obtained are nothing more than four: 0, 1, 2, 3.

4) Obtain the reasons for the loss of users based on the summed marks. Add a column as "reason", as shown in the figure below.

When "causes of loss = 2 or 3", it is brand loss; when "causes of loss=1", it is category loss; when "causes of loss=0", it is channel loss.

5) The last "Group Summary" is carried out to count the number of customers lost in each channel, as shown below:

6) Save and update the self-service dataset

2.4 Combine the reasons for loss and inflow into a table

1) Above we have successfully counted the reasons for inflow. In a similar way, we will make another analysis of the reasons for the inflow, which will not be repeated here, as shown below:

2) Create a self-service dataset "Summary of Inflow and Outflow Reasons". Check all the data in the "lost users" and conduct "Left and right merge" with the "inflow customers", as shown below:

3) Save and update the self-service dataset.

2.5 Make the component

Use "Summary of Inflow and Outflow Reasons" to make the component. As shown in the figure below, a group table is made to show the number of inflow customers, the number of loss customers, and the net inflow of each brand under various categories.

Net inflow = number of inflow customers-number of loss customers.

Then add components based on analysis requirements.

2.6 Effect display

Please refer to section 1.2 of this article.