Weekly review of contract overages billed to spot suddent changes
Jump to Overview | Samples | Variables | Alert Functionality | OnDemand Report Filters | Best Practices & Tips | Related Alerts |
Overview
Overview
To alert your subscribers or sales rep whenever the rolling last 3 months overages are W% above the base for that same period. Also we check to see if the total actual contract clicks for the last 3 rolling months is X% of the previous 3 months. The idea is to look for any sudden shift in overages or volume for the last 3 rolling months.
Run Schedule: Weekly on Mondays
Type of Output: Email
* * *
Sample
Sample
* * *
Variables
Variables
Variable W: Target percentage to flag contracts where the overage clicks were W% over the base allowed (no decimals please)
Variable X: Quarterly target % to flag if last 3 months volume > or < X% of previous 3 months volume (no decimals please)
Variable Z: Set to YES to use Sales Rep on the Contract Record OR leave BLANK to consider Sales Rep per Customer Record or CRM.
Variable 1: Branch numbers alert fires on (list multiple branches separate by commas). Leave blank for ALL branches
* * *
Alert Functionality
Alert Functionality
-The purpose of this report is to help you spot potential upgrade opportunities where they have excessive overages that might make them vunerable to a competitor who might call on them and spot the overage charges as an oppty for new equipment. Also to spot any drastic change (+/-) in the last quarter to previous quarter volume that might indicate that they've either stoppped using your equipment OR an increase that might mean they are prospect for higher volume equipment.
-Sorts by highest to lowest Qtr over Qtr Variance.
-Alert is intended for Sales Reps, so separate email for each rep sent to Sales Rep's Manager and/or anyone listed in To/CC/Bcc:
**If Managers want a birds-eye view, then subscribe to ID918 which presents a simple overview in HTML format
Column Explanation
Column Explanation
Definitions:
RY01 – last completed 12-months
RQ01 – last completed 3-months
RQ02 – 3-months completed prior to RQ01
Section (1):
Overage Cycle: overage billing frequency per Contract Record
Total Overages Last 3 Months: actual overage portion of the total billed clicks
% over Base: represents the portion of the Total Meter (RQ01) contributed by Overage Billed (RQ01). Calculated as Overage Billed (RQ01) divide by Total Meter (RQ01) and then multiplied by 100
Variance Qtr over Qtr: the percentage by which the Total Meter (RQ01) exceeds (or is less than) Total Meter (RQ02). Calculated as (Total Meter (RQ01) - Total Meter (RQ02) divide by Total Meter (RQ02) and then multiplied by 100.
Section (2):
Base Billed (RY01): the base and overage total billed in Rolling Year 1 (i.e. the last completed 12 months)
Overage Billed (RY01): the overage portion of the total billed clicks
Actual Clicks (RY01): the number of actual clicks regardless of whether it is covered (base) clicks or additional clicks not covered (overage)
Overage Billed (RQ01): the overage portion of the total billed clicks
% over Base (RQ01): represents the portion of the Total Meter (RQ01) contributed by Overage Billed (RQ01). Calculated as Overage Billed (RQ01) divide by Total Meter (RQ01) and then multiplied by 100
* * *
On Demand Report Filters
On Demand Report Filters
Filter by Branch, Sales Manager and/or Sales Rep.
Exclude Zero Base: set to YES and will exclude from consideration any/all contracts with no base allowance
Overage Clicks Over Base Percentage RQ01: set filter for % over base
Variance % Actual Clicks RQ01 over RQ02: set filter for variance %
* * *
Best Practices & Tips
Best Practices & Tips
-Alert is intended for Sales Reps, so separate email for each rep sent to Sales Rep's Manager and/or anyone listed in To/CC/Bcc:
**If Managers want a birds-eye view, then subscribe to ID918 which presents a simple overview in HTML format
* * *
Related Alerts
Related Alerts
ID321 - Alert on last Contract invoice if Overage > W% of Base or Underage <X% of Base (requires ECi Digital Analytics)
ID330 - Customer not hitting minimum volumes
ID918 - Invoices billing Overage or Underage
* * *
0 Comments