Archive

Posts Tagged ‘Microsoft Excel Pivot Table’

Find Your Most Valuable Customers Using PowerPivot and DAX for Microsoft Excel 2013

June 11th, 2013 Abi Shende No comments

RFM is a method used for analyzing customer behavior and defining market segments. It is commonly used in database marketing and direct marketing and has received particular attention in retail.

RFM stands for:
Recency: How recently did the customer purchase?
Frequency: How often did they purchase?
Monetary Value: How much did they spend?

In this article we will use PowerPivot and DAX (Data Analysis Expression Language) for Microsoft Excel 2013 to rank customers by their monetary value, based on the sales amount in the last 12 months.

We can start this process by populating the PowerPivot data model from the ERP and/or CRM solutions. Our PowerPivot Data Model consists of the following four tables:

PowerPivot Data Model consists of these four tables: Calendar, DimCustomer, FactInternetSales, DimProduct

PowerPivot Data Model consists of these four tables: Calendar, DimCustomer, FactInternetSales, DimProduct.

Next, we will create the following four calculated columns in the DimCustomer table:

Calculated Column DAX Formula Explanation
L12MSales
=CALCULATE(SUM(FactInternetSales [SalesAmount]),
                       DATESBETWEEN (
                           Calendar [FullDate],
                           DATE (2012,6,1),
                           DATE (2013,5,31)
                        )
                      )
This column holds the total sales amount for each customer for the last 12 months.
RunningL12MSales
=SUMX (
     FILTER (
         DimCustomer,
         DimCustomer [L12MSales] >= EARLIER(
     DimCustomer [L12MSales])
                ),
          DimCustomer [L12MSales]
            )
This column calculates the running total of customer sales, which includes the sales for the customer on the current row and all other customers having sales greater than the current customer. This is accomplished using the FILTER function to filter the customers and the EARLIER function, which ignores the filter context and uses the previous row context to compare all other customers’ sales with the current customer’s sales.
RunningPct
=DimCustomer [RunningL12MSales]/
SUM(DimCustomer[L12MSales])
This column calculates the percentage of running total to the total of all customer sales.
SalesABCRank
=IF(DimCustomer[RunningPct]  < 0.7, "A",
    IF(DimCustomer[RunningPct] <  0.9, "B", "C"))
Finally, this column assigns the rank to each customer based on the Running Percentage as follows:
A = Top 70% of Total Sales
B = Middle 20% of Total Sales
C = Bottom 10% of Total Sales

The following screen shot shows the result of these calculations in the PowerPivot data view of DimCustomer table. The DimCustomer table is sorted by L12MSales in the descending order (largest to smallest) to see that top customers are ranked “A”.

After completing calculations in the PowerPivot data view of DimCustomer table, the DimCustomer table is sorted to see the top customers (ranked “A”)

After completing calculations in the PowerPivot data view of DimCustomer table, the DimCustomer table is sorted to see the top customers (ranked “A”).

Now, we can use this data in Microsoft Excel PivotTable for analysis and reporting purposes. The following screenshot shows the list of customers ranked “A” – i.e., top customers that contribute to 70% of sales.

Data can be put in Microsoft Excel Pivot Table for further analysis and reporting

Data can be put in Microsoft Excel Pivot Table for further analysis and reporting.  

The following PivotTable using the same data shows that the top 3,595 customers generate 70% of sales while the bottom 12,465 customers generate only 10% of sales.

This PivotTable shows the percentage of sales generated by customers in different sales ranks

This PivotTable shows the percentage of sales generated by customers in different sales ranks. 

We can use a similar technique to rank customers by recency and frequency and calculate a combined customer rank.

Also, the PowerPivot tables can be refreshed periodically to recalculate the ranks.

This is just one example that illustrates how PowerPivot for Microsoft Excel 2013 provides powerful Business Intelligence capabilities to analyze operational, financial and sales data from ERP and CRM solutions.

If you have any questions, or if you would like more information, please email us at dynamics@ignify.com.

Abi Shende is a Team Lead in Microsoft Dynamics CRM at Ignify. Ignify is a technology provider of ERP, CRM, and eCommerce software solutions to businesses and public sector organizations. Ignify has been included as the fastest growing business in North America for 5 years in a row by Deloitte, Inc Magazine and Entrepreneur Magazine and ranked as one of 100 most innovative companies in the world in the Red Herring Global 100 in 2011.