ustomer relation marketing : system and computations


Computational techniques and tools for the task:MS Excel modules (pivoting, visualisation graphs)

The initial data file is the same for everyone : CRM_coded_EN2022.xlsxs


Data mining procedures:
 1. Exploration (data cleansing, e.g. dealing with negative values, transformation of the provided
transaction data set by creating new derived variables for building customer data set.
 2. Model building by applying ELT principle (Extract, transform, load) for creating variables and
applying suggested techniques for their analysis
 3. Deployment: using the elaborated models for generating insights


Creating CRM variables (Pivot ,MS Excel)


Pivot analysis functions are used for creating variables for analysis, making analytical reports
and for visualisation.

1.      The analysis is perfomed by using all variables computed during the data exploration
(transformation) phase. Among these variables are:

1.1.   The M is equal to the total value of purchases during all the history of communication.

1.2.   Average purchase during one visit

1.3.   Standard deviation of the selected variables

1.4.   Variable F indicator is equal to the number of visits of the customer

1.5.    Tenure (duration of life cycle in days)

1.6.   Variable R (Recency) show the number of days since the last visit till the date set for
analysis (you can use the „Today“ or any other date (e.g. end of the year).

1.7.   Average number of days between visits

1.8.    Indicators of „good“, „bad“ or other types of customers

1.9.   Categorical variables for entitling meaningful subsets, frequency of routes, etc.


Marketing management models for analysis (MS Excel)


2.      Customer analysis by comparing two segments: individuals and firms. Please use various
diagrams for graphical visualisation

3.      „Whale curve“ analysis: in order to plot it you have to sort customers by descending order
of their turnover values,  to compute thier cumulative percent values and to plot to Y axis. In X
axis you plot the cumulative percent of the number of customers (e.g. if the enterprise has 10
customers, each of them makes 10% of the enterprise customers, second line will show cumulative of
2 customers which make 20 cumulative percent, etc. Till last line showing 100%).

4.      Pareto analysis (testing of the statement if the turnover generated by 20% of best
customers equals to 80% of total turnover of the enteprise).

5.      RFM analysis (Recency, Frequency, Monetary value). The three indicators of R, F and M are
computed for each customer.

6.      Loyalty and Churn analysis – create variables and define the formulas for their values
suitable for analysis of the level of loyalty (defined by yourself) of the customers. Select two
variables for defining loyalty (frequency of visits or other variables). and to suggets formula for
computing level of risk for churn.