Follow

ID150 Contract Profitability SSRS Report Overview & Sample

It is a customer profitability report not just contract.  It sums up all of a customers contracts(using your applicable report filters) to give you a more complete service picture. We found that a straight up contract profitability report could lead to some inaccurate conclusions when you factor in renewals, terminations, multiple contracts for different services across one account..etc. **Please note ID150 Custom Report does not pick up info from already terminated contracts. It is only able to pull profitability info on active contracts. 

Still using the Crystal Custom Reports version? Please see THIS LINK for an overview.

New to SSRS Reports? Please see THIS LINK for access and security settings needed.

We have built the customer contract profit report to accommodate a process whereby your service managers could look at a small/manageable slice of their worst performing contract customers, sit with sales and decide what (if any) actions need to be taken to bring the account into the black, and create an action plan note on the contract and set a ‘next review’ snooze date (See https://support.ceojuice.com/entries/23501038-ID150-Contract-Profit-Snoozing-Contracts) so that the contracts will not show up on the worst performing again until after that ‘snooze’ date, and then only if it’s still in an unprofitable state.

It’s really designed to be a ‘Contract Customer’ profitability so that your always evaluating the customers overall performance even if you filter down to only selected contract types etc.  So the ‘Period’ numbers are always the ‘filtered contracts’…but the Customer Lifetime is the total of ALL contracts billed thru eAutomate regardless of any ‘ContractTypes’ you filter the report on.  Reasoning is if we showed only Totals for the contracts selected..then client is not evaluating the complete picture and will not have enough information to make an informed decision…

We use analytics de-normalized  mode, (distributed details) which basically allocates the contract revenue (base & overage + any additional revenue) into the months covered on the contract invoices (typically next month on base...last month for overage) so that trending at the period (YYYYMM) can be accurate based upon when the revenue is earned vs. when it's billed. 

Filter Options (right click on image to enlarge):

 

**Of special note in screen shot above, highlighted is the "Analytics Last Update". If this is NOT last night, then info contained on this report will not contain accurate up to date information**

From Rolling Month AND Back to Previous Rolling Month - RM01 is the last full month that has past (so if it is September 2016 now, then RM01 is August 2016.) and RM12 goes back to 12 months. Please see THIS LINK for details on what these data points mean.

Customer Search (contains) - this search allows you to pull up Customer Name(s) contain whatever text you enter in this field. If you would like exact customer, then you will need to put exact customer name as it is in eAuto.

Contract Number - this requires the EXACT Contract Number in eAuto, with revision number included (i.e. -01 extension)

The following filters should be used when running report to find grouping of contracts or group of "bottom worst". If you're using this report to run on one contract, use defaults on filter.

Include Only Contracts Where Avg Monthly Rev > - indicate greater than revenue amount as perhaps you don't want to consider super low revenue contracts. 

Show Only If Customer Margin < X% - indicate less than margin % as perhaps you want to filter down to specific margin level customers/contracts. 

Show Only Bottom (X) Worst Customers - maximize the amount of customers/contracts included in this report so you keep to a manageable size to effectively work on. 

Include Only Customers With Current Active Contract - Yes/No, you may not want, for example, ABC Realty location #1 info if there's no active contract on it anymore, but do want to see their location #2 info.

Include Snooze Contract Dates Thru - can filter out by date any contracts "snoozed" for review, see THIS LINK on "snoozing" contracts.

Show Equipment Detail - by selecting YES, the report will automatically provided equipment location, contract dates, etc. on report. You can also use drill down in report if you select NO.

Min # Of Active Machines On Any Contract - this allows you to limit contracts shown to minimum number of active machines currently under the contract

Branch - you can use this filter to run for specified branch

Show Notes - report can show you notes recorded on Contract Record

**Please also see save as icon, you can export the report to PDF, Excel or Word**

 

Top of report shows your summarized selected filter options as well as special notes that greatly impact the data in the report results:

 

SSRS Report Sample:

1) This is the Sales Rep assigned to this Customer.

2) This is the number of surveys customers replied to in the reporting period and their NPS score for you.

3) Total count of destinct active equipment for ALL active contracts.

4) Finds the first contract start date for the customers entire history.

5) The Lifetime numbers here in the Customer Level represent ALL contract related revenue/costs on ALL contracts for the Customer, not just the filtered contracts that appear on this report.

6) Color and BW Supply costs are connected via the Meter Usage Type listed in Toner Item Record. If one of these costs is missing, then first check your Meter Usage fields.

**Underage column above is clicks paid for, but not used (i.e. 10,000 clicks but you only recorded 6,000 for the period. You paid for 4,000 clicks you did not use.)

1) This is the customers margin for the selected period and for the contracts that meet the report criteria you selected.

2) This is the Customer's Lifetime margin for ALL contracts billed in eAutomate. (Please see THIS LINK for more details on Lifetime margins.)

 

The contract major level of the report represents the totals (for the selected period and the lifetime) for all contracts that have the same contract number (prefix to the revision #) and we show the count of Active contract revisions for this Contract Major# and the Contract Major period are the total for all revisions and the selected reporting period...and the Lifetime are the total for all the revisions for all revenue/cost billed in eAutomate. (Please see THIS LINK for more details on Lifetime margins.)

**Please note, if the Cost Supplies column does not equal the total of Cost Supplies Color + Cost Supplies BW, then you have items coded with a supplies service code and no usage limit meter type set.**

 

**If you're subscribed to alert ID219/Closed Call Data Entry Issues, then this section will show Service Invoices that are suspect and could be effecting your costs. 

 

 

 

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

2 Comments

  • 0
    Avatar
    Michael Hippler

    #4 If the old contract was terminated and they have a new contract it pulls information from the old contract also. Creates a large negative in my case.

  • 0
    Avatar
    Juice Support

    Michael,

    It's meant to be more of a customer profitability report rather than a contract profitability report. See this post. https://support.ceojuice.com/entries/23478611-ID150-Output-explained

Please sign in to leave a comment.
Powered by Zendesk