Follow

ID587 - 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. 

Overview | Samples | Variables | Alert Functionality | OnDemand Report Filters | Best Practices & Tips |  Related Alerts | Webinar

Click to Subscribe 

Overview

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. 

Run Schedule: 1st of Month

Type of Output: Email & On Demand SSRS Report

 

*  *  *

Sample

Sample

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

 

New Revenue by Rep

Page_2_New_Revenue_By_Rep.png

 

Lost Revenue by Sales Rep

Page_3_Lost_Customers_by_Rep.png

New Revenue by Win Code:

SampleWinCode.PNG

Lost Revenue by Term Code - Sales Rep

Page_4_By_Term_Code__then_Rep.png

 

*  *  *

Variables

Variables

 

ID587Variables.PNG

Variable W: How many months to look back for matching lost contract to new added contract (max is 12 months)

Variable X: BranchNumbers to report for (separated by comma) or 'All' for all branches, or enter 'Consolidated' for one company-wide report

Variable Y: Enter 'Contract' to reference the Sales Rep assigned to the Contract, or 'Customer' to reference rep assigned to the customer per CRM else the customer per e-automate.

Variable Z: Enter Contract Type(s) to EXCLUDE (leave blank to include all), separate multiple with comma

Variable 1: Custom Property on Contract Record populated with your Win Code 

Variable 3: Only include metered contracts (Yes) or include all contracts (No).

  

*  *  *

 Alert Functionality

Alert Functionality

-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.

-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.)  

-If you see 0's (zero's) on report, this is indication of non-metered contract:

ID587zero.PNG

-If you use Custom Property ZCJWinCode on Contract Record to record standardized text to record a won deal/how won, report will show by Revenue by Win Code:

SampleWinCode.PNG

 

*  *  *

 

OnDemand Report Filters

OnDemand Report Filters

mceclip0.png

Months Back: Max lookback is 12-months 

Cust Rep Contract Rep: Consider Sales Rep assignment based on Customer or Contract Record

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. 

Win Code: can filter on specified Win Codes set on Custom Property ZCJWinCode:

**DO NOT use SELECT ALL as this is broken, rather select INCLUDE ALL or check each needed:

WinCodeFileter.PNG

Include Only Meter Contracts: Can select to only consider metered contracts or select ALL contracts (metered & non-metered, i.e. IT Contracts)

 

*  *  *

 

Best Practices & Tips

Best Practices & Tips

-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.)

Create Custom Property ZCJWinCode

Create new Attribute via Tools / Lists & Codes:

Attribute name: ZCJWinCode

Description: Deal Won Code ID587

*Data type: Text Box OR User-Defind Lookup

*Use Text Box to allow for free add of Win Code Types / Entry

ID587a.PNG

OR 

*Use User-Defind Lookup to provide fixed drop down list of choices (you can always add more if needed):

ID587WinCode.PNG

Add Attribute to your Contract Configuration via Tools / Lists & Codes / Configurations (Custom Properties:

ID587aa.PNG

Create eView to see Contract Records with ZCJWinCode:

From System eView Service Contracts, create New:

1.PNG

Name your eView, then click OK:

2.PNG

You will be taken directly to your new eView, now select Edit (to further customize/add columns):

3.PNG

Select Import new columns:

4.PNG

Change Source to Custom Properties, then you will see all your Contract Custom Properties in drop down, select ZCJWinCode:

5.PNG

 

Select OK:

6.PNG

Now this Custom Property is listed in your Unselect Columns (usually at bottom of listing), use arrow to move to your Selected columns:

7.PNG

Go to Filters tab on eView, then select Sync with columns (so you can filter by ZCJWinCode):

8.PNG

eView shown here filtered by specified WinCode:

9.PNG

 

*  *  *

Related Alerts

Related Alerts

ID923 - Contract MIF Analysis

 

*  *  * 

Webinar

WEBINAR

Webinar from May 2019:

Webinar from April 2020 - Updates with version 20200424:

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