ID535 - Stack Rank Your Customers Overview & Sample:

This report stack ranks your customers using data from your contracts, revenue, cost and clicks and presents the data as the last complete 12 months rolling versus the previous 12 months rolling to that.

Jump to a specific section by clicking a link

Overview | Samples | Variables | Alert Functionality | Best Practices & Tips | Related Alerts


Click to Subscribe



This report stack ranks your customers using data from your contracts, revenue, cost and clicks and presents the data as the last complete 12 months rolling versus the previous 12 months rolling to that. The Columns reported are Revenue (total customer revenue, not just contract revenue), Cost, Margin, CostPerCopy, ActualClicks, BaseAndOverageClicks (billed totals), color & BW clicks (both actual and billed)). The report is emailed to you in a .txt file that you import into excel as a tab delimited file (see Best Practices section below for how to import the file)

Note that this reports on total customer revenue and not just contract revenue. Additionally, the report does not include data for the current month as it is not a completed month.


Run Schedule:  Monthly on the 1st

Type of Output: Email


*  *  *



Below is a sample of the Column headers, right click on images and open in new tab to enlarge. See the best practice section below for understanding each column.







*  *  *



Variable W: Enter 'Active' to have the report run based on customers who have active contracts or enter 'All' to have the report run on all customers who had contract billing in the last 12 months even if contracts are expired now.



*  *  *

Alert Functionality

Alert Functionality

The report reviews billed customer revenue (for all invoice types; sales, service, contracts, etc.), cost to your company for all transactions, and total clicks on equipment for the last complete rolling 12 months and the rolling 12 months prior to that.



*  *  *


Best Practices & Tips

Best Practices & Tips

Understanding the report columns:

(RY01) = last rolling 12 months

(RY02) = last rolling 12 months prior to RY01

Rank = the number represents where this customer ranks as compared to all customers on the report

Revenue = this is revenue for the customer for all transactions, not just contract revenue

Cost = this is the cost to your company for this customer for all transactions, not just contract revenue

Actual Meter Clicks = this represents all billed clicks

Actual BW Clicks = this represents b/w billed clicks

Margin = this is the margin for the customer

CPC Total = this is the amount billed per copy

Revenue Supplies = this is the revenue for supplies


Importing the .txt Report File to Excel:

This file is a tab delimited file containing the stack ranking columns for you Customers.

To use this file you will need to save it as .txt file and then open in Excel:

1) save it to a folder you can access from Excel

2) Open Excel and select "File...Open" from the menu

Set the "Files of type" to All Files(*.*)..and then select the CustomerStackRank.txt file from the folder you saved it in from the email:



3) Excel will recognize that it is a delimited file and present you with the text import wizard as below:

Select "Delimited" and click on the "Finish" button


4) Your data should appear in rows and columns, with Column A being the "ParentCustomer"...Column B the LocationCustomer...Column C indicates if you selected only customers that have a currently active contract at the time the report ran..then the remaining columns are your metrics. 


Other Things to Note:

1) Your Click metrics will always be lagging based upon the meter billing cycle for each contract. If you bill a lot of contract meters Quarterly, then you are going to be missing the most recent 3 months of meters until you bill that contract (contracts are billed in arrears usually).  If you bill annually, then your RY01 is going to be off in regard to the Contract metrics (base & overage revenue, meter clicks). 

2) The contract invoices distribute the revenue & clicks for that invoice across the Overage Dates for that invoice evenly. To reconcile a contract invoice will typically require you have the previous and next invoice so you can see how the revenue & clicks are prorated into the number of days in the cycle. This then gets distributed per diem into the appropriate Rolling Months so that the clicks/revenue are spread evenly, and you have "data" for each month.  But the clicks can't distribute until they are billed, and since they are billed in arrears then your click distributions will only be current thru the most recent meter billing cycle for each contract.  

You can verify the data by using the eView CEOJuice Contract and Transactional Analysis, CLICK HERE for an overview of eView.


*  *  *

Related Alerts

Related Alerts

ID773 - Worst Profitable Contract Customers Overview & Sample

ID150 - Contract Profitability SSRS Report Overview & Sample

ID771 - Profit Analysis (Power BI) Overview & Sample

ID560 - Low Profit Contract Up for Renewal / Escalation - Overview & Sample



*  *  *

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.
Powered by Zendesk