How to use data to analyze whether an operation project is going well or not?

Recently, my friends often ask me the following questions: What should I do if I don’t have much data to analyze? I have some data but don’t know how to analyze it. What should I do? I have some data and I know what kind of analysis should be done, but I don’t have any advanced tools . What should I do?

For Xiao Miao, the above questions are all false propositions. Often, when we work with data, we always have more data than we need. After we have a deep understanding of the business, we always have more methods than problems. With the methods, the use of tools is always much simpler than imagined.

Okay, let’s take a look at an example first, and then you will understand why Xiao Miao said that.

1. Background

It is said that in a certain month and year, a foreign snack O2O brand wanted to expand into the Shanghai market. Based on the "lean entrepreneurship " principle of "steady and steady progress", the brand planned to conduct trial operations in several representative areas of Shanghai first.

Three areas were selected - Yangpu District, Changning District and Xuhui District. Three physical business outlets were set up in these three districts with large flow of people and dense office buildings. They are located near Wujiaochang Metro Station, Songhong Road Metro Station and Caohejing Metro Station respectively. These three metro stations are used as radiation promotion points. DM promotion is carried out near the subway entrances during rush hours, and door-to-door promotion is carried out in nearby office buildings during working hours (don't ask me how the promotion staff sneaked in). The trial operation began in late July and ended in late August, exactly one month after it began, marking the first phase. At this point, it is necessary to analyze the operational data during this period, revise and summarize the experience, and focus on the following aspects for discussion and research:

The overall operation situation, including the total order situation and the time distribution of orders; the user's consumption behavior analysis, mainly the order time distribution and purchasing power analysis; the overall and regional user value analysis, to guide and optimize the subsequent operation work. Compare the effects of ground promotion and user orders in these three places.

Due to the fact that there were too few technical personnel in the early stage and they were responsible for too many projects, the backend system was too simple, and the raw data collected by the backend was only the following:

Raw data table

The above table shows the order information placed by users during this period. Note that the same User ID may place an order more than once, and may make multiple purchases of different amounts on different dates and in different time periods. Understanding this is crucial for the subsequent user value analysis.

Okay, everyone. The table above will be the "ingredients" that Xiao Miao will use to cook next (it comes from a real case, and the data will be processed to a certain extent and is only used to demonstrate the data analysis method). There are no other seasonings (Excel is used to process data most of the time)~ These data may look ordinary, but if you use your brain to dig deeper, you will find a lot of mysteries.

Well, our data analysis journey is about to begin!

2. Order Time Distribution

Before conducting in-depth analysis, the raw data is first processed preliminarily - mainly in the time dimension. The hour and weekday functions are called respectively to extract the hour "time point" and "day of the week" information. As for the "time period", many friends have asked how this was "designed" before, and Baidu can't find it. Of course, this is my own creation. Now Xiao Miao posts a detailed picture:

After processing, a table of several time dimension data is obtained

How to use "Time Period"

In this way, the following information about the order time distribution can be obtained:

Order volume distribution during the trial operation period

So, what clues can we see from this chart showing the distribution of order time periods? In fact, it is obvious that there is only one "peak" for order placement, which is concentrated between 9:00 and 13:00. During this period, white-collar workers place orders during lunch time, and the rest of the time is "slacking off".

Let's analyze the overall order sales during the trial operation. Displaying the "date" information and "week" information on the horizontal axis at the same time will make it easier to find the order pattern over time. By making a line graph, we can see the following results.

Overall order distribution during the trial operation

From the above table, we can see that there are more orders on weekdays than on weekends. Order sales peaks occur during 7-27 to 7-31 and 8-3 to 8-6, and the order volume during these two periods increases sharply. The reason for this is largely due to the promotional activities held by these three physical outlets, which stimulated the purchasing behavior of target customers.

Then take out the "week" data separately and get the following picture:

Order distribution during the trial operation for one week

As can be seen from the above figure, the peak order volume of the week is concentrated on Wednesday and Thursday. The order volume on Monday and Tuesday is almost the same as these two days. However, on Friday, especially on weekends (Saturday and Sunday), the order volume drops sharply.

In general, the order volume is large during the week and small on weekends, which is consistent with the consumption behavior of people in the white-collar area and no abnormal situation occurs.

After knowing the above information, when carrying out the next stage of operations, you can prepare the following matters in the next stage of operations:

Arrange personnel and make deployment at business outlets before the peak period of user orders, so as to deliver goods to users in a timely and rapid manner;

Before the peak period of user orders, do a good job of system maintenance on the APP and website to avoid technical problems caused by a large number of orders, which would affect the user experience;

The next round of promotion can be carried out on Wednesdays and Thursdays, as users on these two days are more willing to place orders.

The above analysis of customer (time) behavior is relatively superficial, and the following analysis of customer value is the "highlight"!

3. Customer Value Analysis

For the customer value analysis here, Xiao Miao will use the RFM model, which involves three important dimensions, namely the most recent consumption (Recency), consumption frequency (Frequency) and consumption amount (Monetary).

The meaning of RFM three dimensions

Regarding the explanation of this model, Xiao Miao only quoted the key sentences from a certain encyclopedia. Students who want to learn more about this model can search it online by themselves~

An explanation of the principle of the RFM model on Baidu Encyclopedia

Among them, the "deep level" meaning of each dimension is:

The most recent consumption can show how likely a customer is to be influenced. The closer the date of the most recent consumption is to today, the deeper the consumer's impression of the product/brand will be, and the greater the possibility of being called back by advertising push;

The higher the customer's consumption frequency, the more loyal the customer is to the brand or product. Of course, such customers are more valuable to maintain. Even if the amount of consumption is not much, who knows whether they will spend a lot in the future.

The amount of consumption (here refers to the "cumulative amount of consumption") indicates the purchasing power of consumers. The larger the amount of consumption, the better the quality of this customer. Of course, we should treat them like treasures~

Although this customer value analysis model is very good, it has the following problems:

Each dimension can be divided into 5 levels, so the final result is 5*5*5=125 categories. The customer base is too detailed! Each customer group must have a set of targeted methods, but this requires money and manpower. Such an operation is too cumbersome and inhumane! There are multiple collinearity problems in the two dimensions F and M in this model. The consumption frequency and the cumulative consumption amount within a certain period of time are highly correlated. Generally speaking, the weight of the R value is the largest among the three indicators, but this judgment ignores factors such as the customer's consumption habits and total consumption, which makes the accuracy of the final result questionable to some extent.

In response to the above problems, Xiao Miao decided to make some improvements to the existing RFM model and use the "killer weapon" - cluster analysis to simplify our in-depth analysis work.

However, before cluster analysis, it is necessary to pre-process the original data. In addition to retaining the two basic information of "User ID" and "key blocks", it is also necessary to retain and deeply "extract" several indicators related to the three dimensions of R, F, and M:

From the indicator of "actual payment amount", through the calculation of related functions, we can get the four indicators of "minimum consumption amount", "maximum consumption amount", "average consumption amount" and "cumulative consumption amount";

By calculating the relevant functions from the "order date" indicator, we can get five indicators: "initial order date", "last order date", "number of days from the initial order date to today", "number of days from the last order to today" and "cumulative purchase frequency".

"Subordinate relationships" of important analytical indicators and related indicators

Among them, the calculation formulas/methods of the above derived indicators are:

The "maximum/minimum consumption amount" is obtained through the formula "=MAX/MIN(IF(original data!$A$1:$A$7028=Sheet1!A2,original data!$H$1:$H$7028))"; the "first/last order date" is obtained through the formula "=MAX/MIN(IF(original data!$A$1:$A$7028=Sheet1!A2,original data!$N$1:$N$7028))"; the "cumulative purchase frequency" is obtained from the pivot table. Under the same User Id, any indicator can be displayed as "count" to obtain the frequency. The “number of days from the last order to today” is obtained by the formula “DATEDIF(E2,TODAY(),”d”)”; the number of days from the initial order to today is obtained by the formula “=DATEDIF(D2,TODAY(),”d”)”, where column E represents the column of the last order date, and column D represents the column of the initial (first) order date.

It is worth noting that the above formula is constructed in a new sheet and refers to the data in the original sheet. The subsequent interval days can only be calculated after the initial/final order date is determined.

Minimum purchase amount calculation method

How to calculate purchase frequency

After calculating the above indicators, we get the following customer information value table, which can be used as the original data for the next step of analysis.

Processed customer value information form

Then enter the Excel form into the SPSS system. For detailed cluster analysis methods, please refer to Xiao Miao’s previous article: Data Operation Practice | How to use cluster analysis to optimize the content of corporate public accounts. After calculation, we can get the following new table:

SPSS output data after cluster analysis

It can be seen that there is an additional column of "classification" data in the above table. These are the four categories divided by SPSS software based on the homogeneity and heterogeneity of the user purchase information (purchase amount and purchase date, etc.) in several dimensions in the table (because the operation cat uses the K-means clustering method, it is necessary to manually set the number of categories. Therefore, before determining the four categories, it is necessary to repeatedly test the data of categories 2, 3, and 5 until there are obvious differences between the categories and a good degree of concentration)

Then use the pivot table to display the "value field" of each type of data as the "average value item" to obtain the "user value classification feature table".

User value classification feature table

Before analyzing the above table, Xiao Miao needs to point out that the importance of the above indicators is not at the same level. The weights of each indicator vary, and the importance varies. The weight coefficient needs to be allocated based on past experience and business conditions. Here is just Xiao Miao’s judgment:

The cumulative purchase frequency has the largest weight, because even if the average/cumulative consumption amount of multiple purchases is not large, repeated purchases represent the user's recognition of the brand/product and can reflect the user's loyalty.

Secondly, if the last order date is not too long ago, the success rate of using the customer recall strategy will be very high;

Next is the average spending amount. Neither a single high spending amount nor a single low spending amount can accurately reflect the customer's purchasing power for this product. Only by taking the historical average can we see his spending power for this product. However, we need to combine the minimum and maximum spending amounts to see whether the gap between the two is too large and how stable they are.

The lowest is the cumulative consumption amount, which reflects the customer’s cumulative consumption over a period of time and can also reflect the customer’s continued value of the product/brand.

According to the above judgment, the second and third categories are relatively high-quality customers. Their purchase frequency, the time of the most recent purchase, the cumulative consumption amount and the average consumption amount have balanced and good values. They are the key objects that need to be maintained. In the future, we can push high-value promotional activities/information to these two types of users to promote their subsequent purchasing behavior.

The first type of customers are "tycoons". Although their purchase frequency is low, their purchase amounts are large. Compared with other types of customers, their purchasing power is quite strong. They have money to spend as they please, but it is difficult to retain them. . .

In addition, the number of users in Category 4 is relatively large, and they are a customer base with potential to be tapped. The characteristics of this group are low average consumption amount and cumulative consumption amount, fewer purchases, and they have not bought products for a long time. The probability of being called back is very small. We can revisit these customers, find out the problems with products and services, make optimizations, and work hard on our internal skills so that we can "hold on" to them the next time we promote.

In summary, the second and third types of customers are our next key targets, which is "saving money"; based on the follow-up visits from the fourth type of customers, we will get suggestions for improving our products and services and recruit new customers in the subsequent operations, which is "increasing revenue".

Do you think it’s over after seeing this?

NO, you too naive~ Xiao Miao wants to squeeze out all the data and get as much information as possible that is useful for operational work!

The following is the proportion of the three types of users in each district after processing by the pivot table:

Distribution of three types of customers in each block

The above table shows the distribution of the three types of customers in each district. It can be seen that Songhong Road has the largest number of overall ordering customers, followed by Caohejing and then Wujiaochang.

In addition, the following general conclusions can be drawn from the above data:

Use the multiple judgment function formula in Excel to divide the customer's average single consumption amount into 7 levels. The function formula is too inhumane, so I won’t list it here. It’s enough for everyone to know the principle. It is recommended not to divide the levels too much. The maximum number of nested if levels in Excel seems to be 7. . .

Table of the proportion of customers by consumption amount range

This will provide us with information on the proportion of customers in each consumption amount range, and help us understand the overall consumption structure of customers during the trial operation period. The table does not look intuitive, so it is directly converted into the two figures below. The upper figure is a quantitative comparison, and the lower figure is a qualitative analysis of the proportion.

Distribution of number of customers by average spending amount

Percentage of customers in each spending amount range

Before analyzing the above two figures, it should be pointed out that the price of most single products of this O2O snack brand is between 3 and 15 yuan. From the above chart, we can see that most customers buy more than one item when placing an order, which means that the joint rate (the joint rate is an indicator of sales in the apparel industry, which describes how many items a customer buys at one time during a purchase, and reflects the effectiveness of the product combination. I've exposed my background. I majored in apparel) is quite good. For example, when a customer buys sausages, he also buys chicken wings, cola and French fries, which shows that the combination of such products is acceptable. Of course, there is still a lot of room for improvement~

Purchase frequency and number of customers distribution chart

Finally, there is a distribution chart of the number of customers by purchase frequency, which can reflect the user loyalty. Among them, users who have only purchased once account for the majority. Seeing such data, operators have to think about why so many customers only bought once. Is it because their own food is not delicious? Therefore, in the future, we must study the market, customers, competitors, and improve our internal skills.

IV. Conclusion

Okay, that’s the end of my sharing. Of course, you can also use data maps to make a heat map of the customer’s location distribution to reflect your professionalism, so as to understand the regional distribution of the entire customer base and conduct effective secondary key promotion. For specific operating methods, please refer to another article by Xiao Miao: Operation Practice | Learn data map analysis in 15 minutes.

From this example, what Xiao Miao wants to say is: when we have a piece of raw data, we should use our data analysis experience and accumulated theoretical knowledge in combination with the current business, and try our best to "squeeze" the data to extract valuable and nutritious information. In this way, the final data/analysis report can not only be used as the material for us to report to leaders or relevant departments for their reference, but more importantly, it can guide and optimize our subsequent operations and accumulate valuable operational experience for us.