Six steps to prepare data for analytic CRM
One of the tasks that I ran into as a development director at a company that sells spare parts was a search for a “silver bullet” in sales organization. After the first contacts with key customers, I felt that they expected the company to anticipate their needs. And for this you need a comprehensive vision of the factors affecting customer relations. Having on hand a complete, clear information about the client and its segment can lead to more efficient cross-sales.
The very first working days allowed me to see that decision-making in the company was based on “intuitive” feelings, and not proven facts, since It was not possible to promptly present information from various sides. Emotional feelings and intuition influenced short-term decisions.
I had to find answers to two questions:
')
About customers. Why do customers leave, what affects their profitability, how do they make decisions on the procurement of a product / brand, than to attract them?
About the goods. How to optimize the product line, what products should be offered to a specific customer, how do consumers react to a certain product / brand?
This is what was done in this situation.
At the time of my entry to work, I had only standard 1C reports and expert opinions of my employees as initial data for decision making. Therefore, I needed a mechanism for collecting, processing and visualizing primary information.
As a tool for data processing, I initially considered 1C and Excel. However, I had to abandon this idea. To bring reports from 1C into a digestible view, you had to do a lot of gestures in Excel.
I needed the tools to merge tables and the ability to process data in stages. Setting up reports in 1C itself was not a trivial task, since the version used was heavily reworked 1C 8.1 “Sales Management”. To prepare the prototype of the 1C analytical report, I would have to spend too much time.
As a tool for business analysis, I chose the Deductor.
This program allows you to flexibly work with data. I already had a successful experience in this program, which left the most positive impression about the product.
As for the quality of the analysis, I needed a designer. It was necessary to obtain a minimally viable product with the possibility of its quick completion. The analysis itself was of no value to me.
In assessing the quality of the model, the starting point for me was the data I had, I “adjusted” it to the reality. To set up an adequate model, I assumed the need for a decent number of iterations.
The most time-consuming step in the analysis of sales data is their preparation and consolidation. I had structured data for analysis (sales data for the last 4 years) and categorical, such as: the industry of the client, data from his portrait, etc. It was also necessary to enrich the categorical data on clients of clients with additional information.
Data cleansing was a problem, as it was necessary to get rid of duplicate records (for example, the same client could appear under different LEs).
For the analysis of categorical data was created "customer rating". Customer characteristics were ranked as follows:
At this stage, employees were assigned the task of collecting the missing information.
I processed customer sales data using:
I started with abc-analysis, and spent it both in the entire history of sales, and in the context of months. With it, I divided the company's customers into three categories, according to the revenue of each of them:
It was also advisable to consider the dynamics of abc categories, because over 4 years the number of customers increased and, accordingly, the proportion of customers became blurred. ABC analysis is a very rough tool. But he allowed me to select customers with an average monthly purchase that does not pay for the maintenance costs. By a determined decision it was decided to set a barrage level for such clients. For large clients, this analysis did not provide clarity. After conducting a monthly abc-analysis and averaging the values for the previous year, it was decided to expertly distribute customers into groups of average monthly purchases.
XYZ analysis divides the company's customers into three categories, according to the stability of the procurement of each of them.
Description of xyz categories:
XYZ analysis did not allow to come to any conclusions - this was expected, because this type of analysis is more applicable to the analysis of the range.
The most interesting was the rfm-analysis.
RFM - customer segmentation by three parameters:
For rfm analysis, I used data on the backbone of the client base (with a lifespan of more than 3 months, because otherwise they could fall into the categories of new or occasional customers, purchase frequency more than 6, the amount of purchases exceeding the upper limit from the range).
The entire customer sample for each attribute was divided into 5 equal intervals, where the best value of the attribute was 5, the worst, respectively 1.
For the subsequent analysis, I organized a data warehouse (rfm analysis forms a picture with a cumulative result; to assess trends, it makes sense to compare the results for the current moment with the results of previous periods).
Also left customers were identified, I was interested in the 155-345 category. It was important to find among the departed "big fish" and understand the reasons for not cooperating.
After adding client data to the customer attribute table, I made a segmentation. Kohonen maps were used as a tool.
The customer selection was divided into 4 groups according to the types of customers (store, wholesale company, end user, service station):
After customer segmentation, assortment line profiles were compiled for six segments. The product line was grouped according to the principle of product applicability (for example, the data on shock absorbers for the VAZ-2108 of the Kraft and Hola brands were grouped into “VAZ-2108 Shock Absorbers”). After that, a table of deviations of the standard product range from the line for each customer was formed. The revealed deviations revealed tendencies - and, accordingly, an occasion for targeted presentations. The same steps were taken with respect to brands.
Based on the received information, decisions were made on targeted communications with customers. The corresponding tasks were set in CRM.
The task of forming a sales plan was partially solved on the basis of the sales forecast for regular customers. However, it was necessary to take into account the sales volume attributable to the rest of the customer base. For this model was built seasonality of sales. Then - a pool of sales models (regression models, moving average, neural networks). But their quality did not suit me - the model error was too great. Segment sales plans were set, based on its product line profile. The idea was to organize not “pushing”, but “pulling” sales to increase turnover. When building a sales plan, I proceeded from the premise that all the necessary products would either be in the company's warehouse, or delivered "off the wheels." Then the sales amounts were aggregated and compared with the customer's purchase history. An aggregate forecast was made for small customers. In such a semi-manual mode, a corridor of planned values was formed. The total amount of the sales plan was approved by bargaining with the owners. After that, sales plans were distributed to managers.
It must be said that at the first stage a lot of time had to be spent convincing employees in the reality of numbers. Then disputes smoothly turned into brainstorming from the series “how we will do it”. These actions paid off a hundredfold - employees from the reaction of "he will never take it" went over to stable work with the client's objections. They have become much more confident. The average deviation from the ruler profile by the end of the year decreased and the amount of the average monthly purchase naturally increased.
Unfortunately, I did not succeed in fully automating the planning / forecasting process - there were too many unformalized factors. The accuracy of the planned values was satisfactory, and she allowed to set the direction of work.
Some thoughts a year after the work done:
The very first working days allowed me to see that decision-making in the company was based on “intuitive” feelings, and not proven facts, since It was not possible to promptly present information from various sides. Emotional feelings and intuition influenced short-term decisions.
I had to find answers to two questions:
')
About customers. Why do customers leave, what affects their profitability, how do they make decisions on the procurement of a product / brand, than to attract them?
About the goods. How to optimize the product line, what products should be offered to a specific customer, how do consumers react to a certain product / brand?
This is what was done in this situation.
1. Defining the goal
At the time of my entry to work, I had only standard 1C reports and expert opinions of my employees as initial data for decision making. Therefore, I needed a mechanism for collecting, processing and visualizing primary information.
2. Tool selection
As a tool for data processing, I initially considered 1C and Excel. However, I had to abandon this idea. To bring reports from 1C into a digestible view, you had to do a lot of gestures in Excel.
I needed the tools to merge tables and the ability to process data in stages. Setting up reports in 1C itself was not a trivial task, since the version used was heavily reworked 1C 8.1 “Sales Management”. To prepare the prototype of the 1C analytical report, I would have to spend too much time.
As a tool for business analysis, I chose the Deductor.
Since there were no publications about this product on Habré, I hid the description from the official site under the spoiler:
“The Deductor platform is the basis for creating applied analytical solutions. The technologies implemented in it make it possible to go through all the stages of building an analytical system from creating a data warehouse to automatically selecting models and visualizing the results obtained using one system. Deductor is a complete analytical platform supporting technologies: Data Warehouse, ETL, OLAP, Knowledge Discovery in Databases and Data Mining. "
This program allows you to flexibly work with data. I already had a successful experience in this program, which left the most positive impression about the product.
As for the quality of the analysis, I needed a designer. It was necessary to obtain a minimally viable product with the possibility of its quick completion. The analysis itself was of no value to me.
In assessing the quality of the model, the starting point for me was the data I had, I “adjusted” it to the reality. To set up an adequate model, I assumed the need for a decent number of iterations.
3. Data preparation
The most time-consuming step in the analysis of sales data is their preparation and consolidation. I had structured data for analysis (sales data for the last 4 years) and categorical, such as: the industry of the client, data from his portrait, etc. It was also necessary to enrich the categorical data on clients of clients with additional information.
Data cleansing was a problem, as it was necessary to get rid of duplicate records (for example, the same client could appear under different LEs).
For the analysis of categorical data was created "customer rating". Customer characteristics were ranked as follows:
- type of customer;
- display area;
- passability;
- the breadth of the range;
- completeness of product groups;
- number of brands.
At this stage, employees were assigned the task of collecting the missing information.
4. Data analysis
I processed customer sales data using:
- abc analysis;
- xyz analysis;
- rfm analysis
I started with abc-analysis, and spent it both in the entire history of sales, and in the context of months. With it, I divided the company's customers into three categories, according to the revenue of each of them:
- A - customers who bring about 80% of revenue;
- b - customers who bring about 15% of revenue;
- c - customers who bring about 5% of revenue.
It was also advisable to consider the dynamics of abc categories, because over 4 years the number of customers increased and, accordingly, the proportion of customers became blurred. ABC analysis is a very rough tool. But he allowed me to select customers with an average monthly purchase that does not pay for the maintenance costs. By a determined decision it was decided to set a barrage level for such clients. For large clients, this analysis did not provide clarity. After conducting a monthly abc-analysis and averaging the values for the previous year, it was decided to expertly distribute customers into groups of average monthly purchases.
XYZ analysis divides the company's customers into three categories, according to the stability of the procurement of each of them.
Description of xyz categories:
- x - stable clients, minor fluctuations in the frequency of purchases, error from 0 to 10%;
- y - known procurement trends, minor fluctuations, error from 10 to 25%;
- z - purchases are irregular, there are no trends, the error is above 25%.
XYZ analysis did not allow to come to any conclusions - this was expected, because this type of analysis is more applicable to the analysis of the range.
The most interesting was the rfm-analysis.
RFM - customer segmentation by three parameters:
- recency - prescription of sale (the less time has passed since the last customer purchase, the more likely that a new one will occur);
- frequency - the frequency of purchases, the more purchases the customer makes, the more likely that he will repeat it in the future;
- monetary - the amount of sales for the entire history, the more money was spent, the greater the likelihood that it will not switch to another supplier.
For rfm analysis, I used data on the backbone of the client base (with a lifespan of more than 3 months, because otherwise they could fall into the categories of new or occasional customers, purchase frequency more than 6, the amount of purchases exceeding the upper limit from the range).
The entire customer sample for each attribute was divided into 5 equal intervals, where the best value of the attribute was 5, the worst, respectively 1.
For the subsequent analysis, I organized a data warehouse (rfm analysis forms a picture with a cumulative result; to assess trends, it makes sense to compare the results for the current moment with the results of previous periods).
Also left customers were identified, I was interested in the 155-345 category. It was important to find among the departed "big fish" and understand the reasons for not cooperating.
5. Customer segmentation
After adding client data to the customer attribute table, I made a segmentation. Kohonen maps were used as a tool.
The customer selection was divided into 4 groups according to the types of customers (store, wholesale company, end user, service station):
- for the group of shops 3 segments were allocated;
- for the group the wholesale company was allocated 2 segments;
- for the SRT group 1 segment was allocated;
- for the end-user group, it was decided not to select the segment (the cluster boundaries turned out to be very fuzzy).
6. Sales Planning
After customer segmentation, assortment line profiles were compiled for six segments. The product line was grouped according to the principle of product applicability (for example, the data on shock absorbers for the VAZ-2108 of the Kraft and Hola brands were grouped into “VAZ-2108 Shock Absorbers”). After that, a table of deviations of the standard product range from the line for each customer was formed. The revealed deviations revealed tendencies - and, accordingly, an occasion for targeted presentations. The same steps were taken with respect to brands.
Based on the received information, decisions were made on targeted communications with customers. The corresponding tasks were set in CRM.
The task of forming a sales plan was partially solved on the basis of the sales forecast for regular customers. However, it was necessary to take into account the sales volume attributable to the rest of the customer base. For this model was built seasonality of sales. Then - a pool of sales models (regression models, moving average, neural networks). But their quality did not suit me - the model error was too great. Segment sales plans were set, based on its product line profile. The idea was to organize not “pushing”, but “pulling” sales to increase turnover. When building a sales plan, I proceeded from the premise that all the necessary products would either be in the company's warehouse, or delivered "off the wheels." Then the sales amounts were aggregated and compared with the customer's purchase history. An aggregate forecast was made for small customers. In such a semi-manual mode, a corridor of planned values was formed. The total amount of the sales plan was approved by bargaining with the owners. After that, sales plans were distributed to managers.
It must be said that at the first stage a lot of time had to be spent convincing employees in the reality of numbers. Then disputes smoothly turned into brainstorming from the series “how we will do it”. These actions paid off a hundredfold - employees from the reaction of "he will never take it" went over to stable work with the client's objections. They have become much more confident. The average deviation from the ruler profile by the end of the year decreased and the amount of the average monthly purchase naturally increased.
Unfortunately, I did not succeed in fully automating the planning / forecasting process - there were too many unformalized factors. The accuracy of the planned values was satisfactory, and she allowed to set the direction of work.
Some thoughts a year after the work done:
- The sales planning approach, based solely on the overall customer procurement assessment, is a little close to reality.
- For the formation of a real plan, it is necessary to understand the structure of customer sales and the distribution of purchases among suppliers.
- Sampling data from wholesalers does not allow it to be called “BigData”, but by combining hard data with marketing information you can get “food for thought”.
- The process of building models is fascinating, but when building a business analyst you need to understand that it should be “binoculars” and not “a thing in itself”.
- Now for medium-sized companies, commercial directors need at least the initial competence of work in the field of business intelligence.
- When building analytics, the principle of kaizen or MVP works fine.
Source: https://habr.com/ru/post/237767/
All Articles