Measures
This table shows the opportunity fact measures:
| Measure | Mapping | Note |
|---|---|---|
| Opportunity Canceled Amount | Opportunity Expected Revenue for status Canceled, else 0.0 | |
| Opportunity Canceled Amount Ratio | Opportunity Canceled Amount / Opportunity Expected Revenue | |
| Opportunity Canceled Count | 1 for status Canceled, else 0 | |
| Opportunity Canceled Count Ratio | Opportunity Canceled Count / Opportunity Count | |
| Opportunity Closed Amount | Opportunity Expected Revenue for status Canceled or Lost or Won, else 0.0 | |
| Opportunity Closed Amount Ratio | Opportunity Closed Amount/ Opportunity Expected Revenue | |
| Opportunity Closed Count | 1 for status Canceled, Lost or Won, else 0 | |
| Opportunity Closed Count Ratio | Opportunity Closed Count / Opportunity Count | |
| Opportunity Count | Always 1 but 0 for null records | |
| Opportunity Customer Count Distinct | Distinct count of Sold-to BPs | |
| Opportunity Deal Size | Opportunity Expected Revenue / Opportunity Count | |
| Opportunity Duration | End Date - Date of First Contact | |
| Opportunity Expected Revenue | tdsmi110.xpam | |
| Opportunity External Sales Rep Count Distinct | Distinct count of External Sales Reps | |
| Opportunity Forecast Cancel Amount | Opportunity Canceled Amount Ratio * Opportunity Open Amount | (1) |
| Opportunity Forecast Cancel Count | Opportunity Canceled Count Ratio * Opportunity Open Count | (1) |
| Opportunity Forecast Lose Amount | Opportunity Lost Amount Ratio * Opportunity Open Amount | (1) |
| Opportunity Forecast Lose Count | Opportunity Lost Count Ratio * Opportunity Open Count | (1) |
| Opportunity Forecast Velocity | Opportunity Forecasted Win Amount / Opportunity Duration Indicates the daily amount that is forecasted to come through the sales funnel. |
|
| Opportunity Forecast Win Amount | Opportunity Won Amount Ratio * Opportunity Open Amount | (1) |
| Opportunity Forecast Win Count | Opportunity Won Count Ratio * Opportunity Open Count | (1) |
| Opportunity Lost Amount | Opportunity Expected Revenue for status Lost, else 0.0 | |
| Opportunity Lost Amount Ratio | Opportunity Lost Amount / Opportunity Expected Revenue | |
| Opportunity Lost Count | 1 for status Lost, else 0 | |
| Opportunity Lost Count Ratio | Opportunity Lost Count / Opportunity Count | |
| Opportunity Open Amount | Opportunity Expected Revenue for status Preliminary or In Process or Dead, else 0.0 | |
| Opportunity Open Amount Ratio | Opportunity Open Amount / Opportunity Expected Revenue | |
| Opportunity Open Count | 1 for status Preliminary or In Process or Dead, else 0 | |
| Opportunity Open Count Ratio | Opportunity Open Count / Opportunity Count | |
| Opportunity Open Weighted Revenue | Opportunity Weighted Revenue for status Preliminary or In Process (or Dead), else 0.0 | |
| Opportunity Velocity | Opportunity Won Amount / Opportunity Duration Indicates the daily amount that comes through the sales funnel. |
(2) |
| Opportunity Weighted Probability | Opportunity Weighted Revenue / Opportunity Expected Revenue | |
| Opportunity Weighted Revenue | Probability Percentage * Opportunity Expected Revenue. Measure can be used for both open and closed opportunities since Probability Percentage is automatically set to 100% for status Won and 0% for status Dead, Canceled and Lost. |
|
| Opportunity Won Amount | Opportunity Expected Revenue for status Won else 0.0 | |
| Opportunity Won Amount Factor | Opportunity Expected Revenue / Opportunity Won Amount. Won Amount Ratio 20% equals Won Amount Factor 5: you need opportunities with an expected revenue of 50$ to win opportunities with revenue 10$. |
|
| Opportunity Won Amount Ratio | Opportunity Won Amount / Opportunity Expected Revenue. Use filter Closed = 1 to exclude Open opportunities. |
|
| Opportunity Won Count | 1 for status Won, else 0.0. | |
| Opportunity Won Count Factor | Opportunity Count / Opportunity Won Count = number of
opportunities needed per won opportunity. Won Count Ratio 20% equals Won Count Factor 5: you need 10 opportunities to win 2. |
|
| Opportunity Won Count Ratio | Opportunity Won Count / Opportunity Count. Use filter Closed = 1 to exclude open opportunities. |
- (1) Dimensional expression to get the Won Count Ratio or Won Revenue Ratio of the previous year.
-
(2) Velocity = (Number of opportunities * Average Win Rate * Average Deal Size) / Average Duration.
This generic formula uses historic averages for win rate, deal size, and duration to calculate velocity for both closed and open opportunities.
The formula that LN uses is:
- Weighted Revenue = Probability Percentage * Expected Revenue
- Duration = Actual or Expected Close Date – First Contact Date
- Velocity = Weighted Revenue / Average Duration
For closed opportunities, the LN formula matches the generic formula. Won opportunities have a Probability Percentage of 100%, lost or canceled opportunities have a Probability Percentage of 0%, and Duration is based on the Actual Close Date.
For open opportunities, however, the LN formula does more. The input of sales reps is used instead of historic averages. In LN, sales reps specify the Probability Percentage, Expected Revenue, and Expected Close Date.
In both formulas, velocity combines multiple aspects in a single KPI. The velocity increases if you increase the number of opportunities, win more opportunities, increase the revenue per opportunity, or shorten the opportunity duration.