Magento Business Intelligence Help Center

Defining customer concentration

In this article, we demonstrate how to set up a dashboard that will help you measure how total revenue is distributed among your customer base. Understand what percent of customers contribute what percent of revenue and create segmented lists to best market to and retain your high contributing customers.

This analysis contains advanced calculated columns that can only be created by a Magento BI analyst, so file a support request with the subject [CUSTOMER CONCENTRATION ANALYSIS], before getting started with this analysis.

You will need to first upload a file containing just a primary key with the value of one. This will allow the creation of some necessary calculated columns for the analysis.

You can leverage this help center article as well as the image below to format your file.

Image

Columns to create

      • Customer concentration table (this is the file you just uploaded with the number "1")
        • Number of customers (RFM > 0) will be created by an analyst as part of your [CUSTOMER CONCENTRATION] ticket
      • customer_entity table (this is the file you just uploaded with the number "1")
        • [INPUT] reference will be created by an analyst as part of your [CUSTOMER CONCENTRATION] ticket
        • Number of customers (RFM > 0) will be created by an analyst as part of your [CUSTOMER CONCENTRATION] ticket
        • Customer's revenue percentile will be created by an analyst as part of your [CUSTOMER CONCENTRATION] ticket

NOTE: the percentiles used are even splits of customers, representing the Xth percentile of your customer base. Each customer will be associated with an integer from 1 to 100, which can be thought of as their lifetime revenue rank. For example, if the Customer's revenue percentile for a specific customer is 5, this customer is in the 5th percentile of all customers in terms of lifetime revenue.

  • Total customer lifetime value
    • In the customer_entity table
    • This metric performs a Sum
    • On the Customer's lifetime revenue column
    • Ordered by the Customer's first order date timestamp
  • Customer concentration
    • Metric A: Total customer lifetime revenue by percentile
      • Metric: Total customer lifetime value
      • Filter:
        • Customer's revenue percentile IS NOT NULL
    • Metric B: Total customer lifetime revenue (ungrouped)
      • Metric: Total customer lifetime value
      • Filter:
        • Customer's revenue percentile IS NOT NULL
      • Group by: Independent
    • Time period: All time
    • Interval: None
    • Group by: Customer's revenue percentile
    • Show top/bottom: 100% of Customer's revenue percentile Name
    • Chart type: Line
  • Top 10% concentration
    • Metric A: Total customer lifetime revenue
      • Filter:
        • Customer's revenue percentile <= 10
    • Time period: All time
    • Interval: None
    • Hide chart
    • Group by: Email
    • Chart type: Table
  • Bottom 50% concentration with only one purchase
    • Metric A: Total customer lifetime revenue
      • Filter:
        • Customer's revenue percentile <= 50
        • Customer's lifetime number of orders = 1
    • Time period: All time
    • Interval: None
    • Hide chart
    • Group by: Email
    • Chart type: Table
  • Bottom 10% concentration
    • Metric A: Total customer lifetime revenue
      • Filter:
        • Customer's revenue percentile > 90
    • Time period: All time
    • Interval: None
    • Hide chart
    • Group by: Email
    • Chart type: Table

After compiling all the reports, you can organize them on the dashboard as you desire. The end result may look like the above sample dashboard.

If you run into any questions while building this analysis, or simply want to engage our professional services team to build the analysis for you, simply open a support ticket here.

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk