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.
If you are on the original architecture (i.e. if you do not have the “Data Warehouse Views” option under the “Manage Data” menu), you will want to reach out to our support team to build out the below columns. On the new architecture, these columns can be created from the “Manage Data -> Data Warehouse” page. Detailed instructions are given below.
A further distinction is made if your business allows guest orders. If so, you can ignore all steps for the `customer_entity` table. If guest orders are not allowed, ignore all steps for the `sales_flat_order` table.
Columns to create
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.
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.