Follow

ID587 - Customers on Metered Contracts Lost & Won (SSRS) Overview & Sample

Wondering if your customer base is growing or not? Tracking your contracts won and lost can give you great insight on your trends. This SSRS Report will send to you on 1st of each month and you can run manually via your SSRS Reports. 

Watch our webinar overview below:

You must be terminating contracts and using Termination CodesClick Here for best practice on terminating contracts and suggested codes to use. We highly recommend using Termination Codes to reflect details of who deal was lost to  (competitor, lease upgrade, etc.)

Overview:

This report determines a "Lost" customer when a contract is terminated for a customer and that customer (nor the parent account) has any other active metered contracts at the time this report runs. An "Added" contract customer is determined by looking back one month and if there were no active metered contracts for that Customer (or it's parent), then this report considers that to be an Added Customer Contract. Another caveat is that this reports on "live" data. So it's possible that a contract customer "Lost" 6 months ago has since opened a new contract..in which case that "Lost" contract will disappear from this report and the new contract will show up as an "Add". However, if a contract drop/add crosses, then it's possible for the "Lost" to disappear and the "Add" to not show up since the report looks back one month for an active contract. 

The intention is to give you a trend as to the net impact on your customer base.

**METERED CONTRACTS ONLY! Not for connectivity contracts etc.

**Please also note the report will only show the termination code for dropped contracts that were terminated. So if you have no "dropped contracts", then you won't see termination details.

Please understand this report looks at your data at the time the report is run, so due to changing contract coverage etc, the "historical" data on this report might change and differ from the previous month(s) reports. It is a snapshot point in time that is accurate as of "Today"..but it cannot recreate contract conditions historically. So please use it as a temperature gauge metric only that uses contract termination dates / start dates etc. as of today and it's accurate for today. It is not something you can compare this months report to another from five months ago because data conditions could have changed. And please note: previous months could vary slightly if you make a practice of backdating contract start dates and/or terminations.

Click HERE for instructions on running an eView based on Termination Codes.

******************************************************************************

Report Filters:

Filter by Sales Rep Manager, Sales Reps, Termination Codes 

Include New Revenue: By selecting YES, the report will consider a new contract if the base has been billed (even if no clicks billed yet). By selecting NO, the report will not consider new contract revenue.

Filters.png

Sample:

Page 1:

Graph showing New and Lost Revenue over period filter you've selected:

Graph.png

Shows by Rolling Month, then by branch, listed by Termination Code the Average Monthly Revenue & Cost, as well as Monthly Base, Overage and Underage values:

The Cost is the actual costs allocated to the contracts. The average revenue for the period is the total revenue divided by the months back (per ‘Months Back’ filter on the report).

By_Rolling_Month_-_alpha_list_by_termination_code__2_.png

Details showing Sales Rep, Customer, Termination Code showing the Average Monthly Revenue & Cost, as well as Monthly Base, Overage and Underage values:

Term_Code_header_for_detail.png

detail_.png

Page 2:

New Revenue by Rep

Page_2_New_Revenue_By_Rep.png

Page 3:

Lost Revenue by Sales Rep

Page_3_Lost_Customers_by_Rep.png

Page 4:

Lost Revenue by Term Code - Sales Rep

Page_4_By_Term_Code__then_Rep.png

 

To note:

The only potential flaw in our logic is when contract administrator might 'inactivate' a contract where the customer is way past due. We see that as inactive of course and that contract customer might show up as 'lost'.  But then next month customer has service call, we tell them it's billable, they ask if they can just pay up in full and be under contract, so we re-activate the contract now all of a sudden it's not lost 2 months ago. So if that is your process, the AR process will alter our report.  We suggest the best way to handle past due customers on contracts, is to create a new billable bill code called 'PastDue' on the contract. Then no need to terminate the contract if we think the customer is going to pay later. Instead just update the contract's BillCode to 'PastDue'. When the next service call comes up billable, tech can pick up the check for contract payment, then tech can do the service call, change the service call to non-billable. (**But be sure to wait until the check clears to flip the contract back to a nonbillable BillCode.)  

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

0 Comments

Please sign in to leave a comment.
Powered by Zendesk