Six steps to preparing data for analytic CRM
One of the tasks that I encountered as a development director in a company engaged in the wholesale of spare parts was to search for a “silver bullet” in organizing sales. After the first contacts with key customers, I felt that they expected the company to anticipate their needs. And this requires a comprehensive vision of the factors affecting customer relationships. Having at hand the full clear information about the client and his segment can lead to more effective cross-sales.
The very first working days allowed me to see that decision-making in the company was based on “intuitive” feelings, rather than verified facts, because It was not possible to provide information quickly from different angles. Short-term decisions were influenced by emotional sensations and intuition.
I had to find answers to two questions
about the client. Why do customers leave, which affects their profitability, how do they make decisions about the purchase of a particular product / brand, how to attract them?
About the goods. How to optimize the product line, what products should be offered to a specific buyer, how do consumers react to a particular product / brand?
Here is what was done in this situation.
At the time I went to work, I only had 1C standard 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 processing data, I initially considered 1C and Excel. However, I had to abandon this idea. To bring reports from 1C to a digestible form, I had to do a lot of gestures in Excel.
I needed tools for merging 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 redesigned 1C 8.1 “Sales Management”. To prepare a prototype of the 1C analytical report, I would have to spend too much time.
As a tool for business analysis, I chose Deductor.
This program allows you to flexibly work with data. I already had successful experience in this program, which left the most positive impression on the product.
As for the quality of analysis, I needed a constructor. It was necessary to get a minimally viable product with the possibility of its quick development. The analysis itself was of no value to me.
In assessing the quality of the model, the starting point for me was the data that I had available, I “tuned” it to 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 ones, such as: industry affiliation of the client, data from his portrait, etc. It was also necessary to enrich categorical customer data with additional information.
Clearing the data was a certain problem, since it was necessary to get rid of duplicate records (for example, the same client could appear under different legal entities).
To analyze categorical data, a “customer rating” was created. Customer features were ranked as follows:
At this stage, employees were tasked with collecting the missing information.
I processed the sales data for customers using:
I started with an 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 the abc categories, since over 4 years the number of customers has increased and, accordingly, customer shares have eroded. ABC analysis is a very crude tool. But he allowed me to single out clients with an average monthly purchase that did not pay back the maintenance costs. By a strong-willed decision, it was decided to establish a barrage level of prices for such customers. For large customers, this analysis did not give clarity. After conducting a monthly abc analysis and averaging the values for the previous year, it was decided to expertly distribute customers among the 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 categories xyz:
XYZ analysis did not allow to come to any conclusions - this was expected, because This type of analysis is more likely to be used for assortment analysis.
The most interesting was the rfm analysis.
RFM - customer segmentation in three ways:
For the rfm analysis, I used the data on the backbone of the client base (with a lifetime of more than 3 months, because otherwise they could fall into the category of new or casual customers, with a purchase frequency of more than 6, the amount of purchases exceeding the upper limit of the range).
The entire sample of customers for each characteristic was divided into 5 uniform 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 at the moment with past results).
Also, the departed customers were identified, I was interested in 155-345 categories. It was important to find among the departed "large fish" and to understand the reasons for refusing to cooperate.
After adding customer data to the customer characteristics table, I segmented. Kohonen maps were used as an instrument.
The customer sample was divided into 4 groups by customer type (store, wholesale company, end user, service station):
After customer segmentation, profiles of the product line for six segments were compiled. The assortment line was grouped according to the principle of product applicability (for example, data on shock absorbers for VAZ-2108 brands Kraft and Hola were grouped in “VAZ-2108 shock absorbers”). After that, a table of deviations of a typical assortment line from the line for each customer was formed. The revealed deviations revealed trends - and, accordingly, an occasion for targeted presentations. The same steps have been taken for brands.
Based on the information received, decisions were made on targeted communications with customers. Corresponding tasks were set in CRM.
The task of forming a sales plan was partially solved on the basis of the forecast of sales by regular customers. However, it was necessary to take into account the volume of sales attributable to the rest of the customer base. For this, a model of seasonality of sales was built. Then - a pool of sales models (regression models, moving average, neural networks). But their quality did not suit me - the error of the models was too great. Segment sales plans were set based on his profile in the product line. 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 will either be in the company's warehouse or delivered “off the wheels”. Then, the sums of sales were aggregated and compared with the purchase history of customers. For small customers, an aggregate forecast was built. In this semi-manual mode, a corridor of planned values was formed. The total amount of the sales plan was approved by bidding with the owners. After that, sales plans were distributed among managers.
I must say that at the first stage I had to spend a lot of time convincing employees of the reality of numbers. Then the controversy smoothly turned into a brainstorming session from the series “How We Will Do It”. These actions paid off handsomely - the employees from the reaction "he will never take it" switched to sustainable work with the client’s objections. They became noticeably more confident. The average deviation from the line profile by the end of the year decreased and the amount of the average monthly purchase naturally increased.
Unfortunately, I did not manage to fully automate the planning / forecasting process - there were too many unformalized factors. The accuracy of the planned values was satisfactory, and it allowed us 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, rather than verified facts, because It was not possible to provide information quickly from different angles. Short-term decisions were influenced by emotional sensations and intuition.
I had to find answers to two questions
about the client. Why do customers leave, which affects their profitability, how do they make decisions about the purchase of a particular product / brand, how to attract them?
About the goods. How to optimize the product line, what products should be offered to a specific buyer, how do consumers react to a particular product / brand?
Here is what was done in this situation.
1. Definition of purpose
At the time I went to work, I only had 1C standard 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 processing data, I initially considered 1C and Excel. However, I had to abandon this idea. To bring reports from 1C to a digestible form, I had to do a lot of gestures in Excel.
I needed tools for merging 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 redesigned 1C 8.1 “Sales Management”. To prepare a prototype of the 1C analytical report, I would have to spend too much time.
As a tool for business analysis, I chose Deductor.
Since there were no publications on this product on Habré, I hid a description from the official site under the spoiler:
“The Deductor platform is the foundation for creating applied analytic solutions. The technologies implemented in it allow you to go through all the stages of building an analytical system from creating a data warehouse to automatically selecting models and visualizing the results using one system. Deductor is a complete analytics platform that supports technologies: Data Warehouse, ETL, OLAP, Knowledge Discovery in Databases and Data Mining. "
This program allows you to flexibly work with data. I already had successful experience in this program, which left the most positive impression on the product.
As for the quality of analysis, I needed a constructor. It was necessary to get a minimally viable product with the possibility of its quick development. The analysis itself was of no value to me.
In assessing the quality of the model, the starting point for me was the data that I had available, I “tuned” it to 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 ones, such as: industry affiliation of the client, data from his portrait, etc. It was also necessary to enrich categorical customer data with additional information.
Clearing the data was a certain problem, since it was necessary to get rid of duplicate records (for example, the same client could appear under different legal entities).
To analyze categorical data, a “customer rating” was created. Customer features were ranked as follows:
- type of client;
- display area;
- patency;
- breadth of assortment;
- completeness of product groups;
- number of brands.
At this stage, employees were tasked with collecting the missing information.
4. Data Analysis
I processed the sales data for customers using:
- abc analysis;
- xyz analysis;
- rfm analysis.
I started with an 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 the 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 the abc categories, since over 4 years the number of customers has increased and, accordingly, customer shares have eroded. ABC analysis is a very crude tool. But he allowed me to single out clients with an average monthly purchase that did not pay back the maintenance costs. By a strong-willed decision, it was decided to establish a barrage level of prices for such customers. For large customers, this analysis did not give clarity. After conducting a monthly abc analysis and averaging the values for the previous year, it was decided to expertly distribute customers among the 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 categories xyz:
- x - stable customers, slight fluctuations in the frequency of purchases, error from 0 to 10%;
- y — procurement trends, minor fluctuations, error from 10 to 25% are known;
- 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 likely to be used for assortment analysis.
The most interesting was the rfm analysis.
RFM - customer segmentation in three ways:
- recency - prescription of the sale (the less time has passed since the last purchase of the client, the more likely that a new one will happen);
- frequency - frequency of purchases, the more purchases a client makes, the more likely he will repeat it in the future;
- monetary - the amount of sales in the history, the more money was spent, the more likely it is that he will not switch to another supplier.
For the rfm analysis, I used the data on the backbone of the client base (with a lifetime of more than 3 months, because otherwise they could fall into the category of new or casual customers, with a purchase frequency of more than 6, the amount of purchases exceeding the upper limit of the range).
The entire sample of customers for each characteristic was divided into 5 uniform 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 at the moment with past results).
Also, the departed customers were identified, I was interested in 155-345 categories. It was important to find among the departed "large fish" and to understand the reasons for refusing to cooperate.
5. Customer segmentation
After adding customer data to the customer characteristics table, I segmented. Kohonen maps were used as an instrument.
The customer sample was divided into 4 groups by customer type (store, wholesale company, end user, service station):
- 3 segments were allocated for the group of stores;
- 2 segments were allocated for the wholesale company group;
- for the service group 1 segment was allocated;
- for the group, the end user decided not to allocate a segment (the boundaries of the cluster were very fuzzy).
6. Sales Planning
After customer segmentation, profiles of the product line for six segments were compiled. The assortment line was grouped according to the principle of product applicability (for example, data on shock absorbers for VAZ-2108 brands Kraft and Hola were grouped in “VAZ-2108 shock absorbers”). After that, a table of deviations of a typical assortment line from the line for each customer was formed. The revealed deviations revealed trends - and, accordingly, an occasion for targeted presentations. The same steps have been taken for brands.
Based on the information received, decisions were made on targeted communications with customers. Corresponding tasks were set in CRM.
The task of forming a sales plan was partially solved on the basis of the forecast of sales by regular customers. However, it was necessary to take into account the volume of sales attributable to the rest of the customer base. For this, a model of seasonality of sales was built. Then - a pool of sales models (regression models, moving average, neural networks). But their quality did not suit me - the error of the models was too great. Segment sales plans were set based on his profile in the product line. 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 will either be in the company's warehouse or delivered “off the wheels”. Then, the sums of sales were aggregated and compared with the purchase history of customers. For small customers, an aggregate forecast was built. In this semi-manual mode, a corridor of planned values was formed. The total amount of the sales plan was approved by bidding with the owners. After that, sales plans were distributed among managers.
I must say that at the first stage I had to spend a lot of time convincing employees of the reality of numbers. Then the controversy smoothly turned into a brainstorming session from the series “How We Will Do It”. These actions paid off handsomely - the employees from the reaction "he will never take it" switched to sustainable work with the client’s objections. They became noticeably more confident. The average deviation from the line profile by the end of the year decreased and the amount of the average monthly purchase naturally increased.
Unfortunately, I did not manage to fully automate the planning / forecasting process - there were too many unformalized factors. The accuracy of the planned values was satisfactory, and it allowed us to set the direction of work.
Some thoughts a year after the work done:
- The sales planning approach, based only on a general assessment of customer purchases, is not very close to reality.
- To form a real plan, you need to understand the structure of customer sales and the distribution of purchases among suppliers.
- The selection of data from wholesale companies does not allow it to be called “BigData”, however, by joining hard data with marketing information you can get “food for thought”.
- The process of building models is a fascinating thing, but when building business analytics, 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 initial competencies in the field of business analytics.
- When building analytics, the kaizen or MVP principle works great.