ID587 - Contracts Lost & Won (SSRS) Overview & Sample:

This report will allow you to analyze which teams/reps are losing the most, who you are losing to and we can even track how you are winning deals allowing you to put plans in place. ID281 is our Power BI dashboard version.

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

Click to Subscribe 



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

When Gary completed his certification on Net Promoter Score, the key take away quote was "a 5% increase in retention can result in a 50 to 100% increase in profit". This all tied into the cost of acquiring a new customer VS keeping the ones you have.  Understanding why you are losing customers is the 1st step.

Click Here for best best practices on Termination Codes.

Run Schedule: 1st of Month

Type of Output: Email & On Demand SSRS Report


*  *  *



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


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


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




New Revenue by Rep



Lost Revenue by Sales Rep


New Revenue by Win Code:

**Customers who have had a contract for more than 12-months (or months back on your date filter) will not be considered new even if you put WinCode on a new contract as it is not considered net new.


Lost Revenue by Term Code - Sales Rep



*  *  *





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. **If this variable is set to Consolidated the report will not send to branch roles.**

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:


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



*  *  *


OnDemand Report Filters

OnDemand Report Filters


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:


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

-This alert does have the option to send to branch roles if you are using those. Be sure to set Variable X to ALL to ensure the report sends to branch roles you have set up. 

-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



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


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


Create eView to see Contract Records with ZCJWinCode:

From System eView Service Contracts, create New:


Name your eView, then click OK:


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


Select Import new columns:


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



Select OK:


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


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


eView shown here filtered by specified WinCode:



*  *  *

Related Alerts

Related Alerts

ID923 - Contract MIF Analysis


*  *  * 



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


Please sign in to leave a comment.
Powered by Zendesk