Framework at a Glance
Six Systems. One Source of Truth.
25 certified KPIs governed across Salesforce, BQE Core, Smartsheet, HubSpot, Workday, and the CAO Bid Engine — unified through a Microsoft Fabric lakehouse semantic model with threshold-based decision triggers and a defined operating cadence.
RevPipeline360
Win Rate, Pipeline Velocity, Forecast Accuracy — governed in the Gold layer semantic model via Direct Lake
PMO EVM Architecture
CPI, SPI, EAC, ETC — cost performance metrics certified against BQE Core actuals
Contract-to-Cash
DSO, Collection Rate, AR Aging, Billing Leakage — certified against Salesforce + Smartsheet lifecycle data
Bonus Accrual & Workday
Realization Rate, Utilization — composite productivity metrics governed against Workday HCM actuals
CAO Bid Engine
Margin@Sign, Adjusted Margin, Pursuit ROI — pre-revenue metrics certified at bid grain
CRM Revenue Lifecycle
Deal-to-Cash lineage, 35 KPIs — governed via deal_id propagating through all revenue-bearing tables
When Every Team Has a Different Number
The Business Problem
Different teams report different numbers for the same KPI — leading to misalignment, lack of trust in reporting, and delayed or wrong decisions. Finance says DSO is 48 days. Operations says billing is current. Sales says pipeline is healthy. Leadership can't act because nobody agrees on the baseline.
The Governance Solution
A certified KPI dictionary anchored in a single semantic model — one definition, one formula, one owner per metric. Combined with threshold-based decision triggers that route alerts to the right person automatically, eliminating the "who owns this number" problem entirely.
Where Metric Chaos Lives
Source to Decision — Full Lineage
① Source Systems
Salesforce, BQE Core, Smartsheet, HubSpot, Workday, CAO Bid Engine — six systems, each with its own schema and metric definitions
② Integration Layer
Data Shuttle, Power Automate, Dataflow Gen2, and ODBC extracts normalize and route data into the lakehouse without loss of lineage
③ Fabric Lakehouse
Bronze → Silver → Gold medallion. Gold layer holds only certified, grain-declared, governance-approved metrics
④ Semantic Model
Direct Lake connection. Single certified definition per KPI. Ownership, formula, grain, and refresh cadence all documented
⑤ Decision Layer
Threshold-based triggers route automated alerts to the right owner. No manual monitoring. No missed breaches
⑥ Operating Cadence
Daily alerts, weekly business reviews, monthly health reports, quarterly governance reviews — all structured around certified metrics
25 Certified Metrics — One Source of Truth
| # | KPI | Domain | Definition | Formula | Grain | Source System | Owner |
|---|---|---|---|---|---|---|---|
| 01 | Win Rate | Revenue | Proportion of qualified opportunities closed as won | Closed-Won ÷ Total Qualified Pipeline | Deal | Salesforce / RevPipeline360 | Sales Ops |
| 02 | Pipeline Velocity | Revenue | Rate at which deals move through the pipeline | (# Deals × Win Rate × Avg Deal Size) ÷ Sales Cycle Days | Week | Salesforce / HubSpot | Sales Ops |
| 03 | Forecast Accuracy | Revenue | Variance between committed forecast and actual closed revenue | 1 − |Forecast − Actual| ÷ Actual | Month | RevPipeline360 | Finance |
| 04 | Deal Size Cohort | Revenue | Distribution of deals by contract value band | Count of deals per value tier | Deal | Salesforce / HubSpot | Sales Ops |
| 05 | DSO | Finance | Average days to collect payment from invoice date | AR Balance ÷ (Revenue ÷ Days in Period) | Month | C2C / CRM Lifecycle | Finance Director |
| 06 | Collection Rate | Finance | Proportion of invoiced revenue actually collected | Cash Collected ÷ Total Invoiced | Month | C2C Lifecycle | Finance |
| 07 | Billing Leakage | Finance | Revenue earned but not invoiced within SLA window | Earned Value − Invoiced Amount (per milestone) | Milestone | C2C / BQE Core | Finance |
| 08 | AR Aging | Finance | Receivables bucketed by days outstanding | Sum of AR by 0-30, 31-60, 61-90, 90+ buckets | Client / Month | C2C Lifecycle | Finance |
| 09 | Cash Realization | Finance | Cash collected vs. contracted value | Cash Received ÷ Contract Value | Contract | C2C / CRM Lifecycle | CFO |
| 10 | Margin@Sign | Commercial | Gross margin at contract signing before execution risk | (Contract Value − Estimated Cost) ÷ Contract Value | Contract / Bid | CAO Bid Engine | BD + Finance |
| 11 | Adjusted Margin | Commercial | Margin after commodity sensitivity and risk adjustment | Gross Margin − Risk Adj. − Commodity Sensitivity | Bid | CAO Bid Engine | BD |
| 12 | Pursuit ROI | Commercial | Return on bid pursuit cost relative to expected contract value | Expected Margin ÷ Pursuit Cost | Bid | CAO Bid Engine | BD |
| 13 | CPI | Operations | Cost efficiency relative to earned value on active projects | Earned Value ÷ Actual Cost | Project / Period | PMO EVM / BQE Core | PMO |
| 14 | SPI | Operations | Schedule efficiency relative to planned value | Earned Value ÷ Planned Value | Project / Period | PMO EVM / Smartsheet | PMO |
| 15 | EAC | Operations | Projected total cost at completion based on current performance | Budget at Completion ÷ CPI | Project | PMO EVM / BQE Core | PMO / Finance |
| 16 | On-Time Milestone Rate | Operations | Proportion of milestones completed on or before due date | On-Time Milestones ÷ Total Milestones | Project / Month | Smartsheet / C2C | Delivery Lead |
| 17 | Realization Rate | Delivery | Billable hours as proportion of total capacity hours | Billable Hours ÷ Total Available Hours | Staff / Month | Bonus Accrual / BQE Core | Delivery Lead |
| 18 | Utilization Rate | Delivery | Hours worked vs. contracted hours per resource | Actual Hours ÷ Contracted Hours | Staff / Month | Bonus Accrual / Workday | Delivery Lead |
| 19 | Composite Score | Delivery | Combined productivity score for incentive comp calculation | Utilization Rate × Realization Rate | Staff / Month | Bonus Accrual | Finance / HR |
| 20 | Accrual Accuracy | Finance | Variance between monthly accrual and actual payout | 1 − |Accrual − Actual Payout| ÷ Actual Payout | Month | Bonus Accrual / Workday | Finance |
| 21 | Contract Cycle Time | Commercial | Days from opportunity close to signed contract | Contract Sign Date − Opportunity Close Date | Contract | Salesforce / CRM Lifecycle | Sales Ops |
| 22 | SOW Risk Score | Commercial | Composite risk rating of contract clauses at signing | Weighted clause risk scoring model | Contract | CAO Bid Engine / CRM | BD |
| 23 | Revenue Lineage Integrity | Revenue | % of revenue transactions traceable from deal_id to cash receipt | Linked Transactions ÷ Total Revenue Transactions | Month | CRM Revenue Lifecycle | Finance / Data |
| 24 | Client LTV | Revenue | Projected lifetime revenue from a client based on historical pattern | Avg Annual Revenue × Expected Retention Years | Client | CRM Revenue Lifecycle | Sales Ops |
| 25 | Backlog Clearance Rate | Finance | Rate at which aged AR is being resolved | (Opening Backlog − Closing Backlog) ÷ Opening Backlog | Month | C2C Lifecycle | Finance Director |
From Threshold to Action — Automated
Every certified KPI has a defined threshold. When a threshold is breached, Power Automate routes an alert to the right owner with context, severity, and a required action — no manual monitoring, no missed signals.
| KPI | Threshold | Severity | Owner | Action Required | Cadence |
|---|---|---|---|---|---|
| DSO | > 45 days | 🔴 Critical | Finance Director | Client escalation + hold on new SOW issuance until cleared | Weekly |
| CPI | < 0.85 | 🔴 Critical | Project Manager | EAC reforecast + mandatory scope review with delivery lead | Per Milestone |
| Pipeline Drop | >15% WoW decline | 🟡 Warning | VP Sales | Forecast revision + deal acceleration review with sales team | Weekly |
| Margin@Sign | < 18% | 🔴 Critical | BD + Finance | Mandatory bid repricing or escalation to no-go decision | Per Bid |
| Realization Rate | < 75% | 🟡 Warning | Delivery Lead | Staffing reallocation review + project capacity audit | Monthly |
| DSO Trend | 3-month consecutive rise | 🔴 Critical | CFO | Collections strategy review + client payment terms audit | Monthly |
| Forecast Accuracy | < 85% | 🟡 Warning | Finance + Sales Ops | Forecast model recalibration + pipeline quality review | Monthly |
| Billing Leakage | > $10K per period | 🔴 Critical | Finance | Milestone audit + invoicing process review with delivery | Weekly |
How the Governance Runs Week to Week
- Threshold breach alerts via Power Automate
- Owner notification with KPI context and severity
- Acknowledgement tracking — unresponded alerts escalate
- One certified dashboard — no alternate versions
- 7-day delta vs. prior week for all 25 KPIs
- Open trigger log — unresolved actions highlighted
- Pipeline + DSO + CPI reviewed by leadership
- Metric drift analysis — KPI moving away from target
- Ownership audit — is the right person accountable?
- Formula validation — source data still mapping correctly
- New metric requests reviewed and queued
- Retire stale KPIs — no longer decision-relevant
- Add new metrics from business requests
- Threshold recalibration based on trailing performance
- Ownership reassignment for role changes
What Governance Actually Changes
Single Source of Truth
25 KPIs certified with one definition, one formula, one owner across six systems. No more parallel reports. No more "which number do we use?"
Decision Latency Eliminated
Threshold breaches trigger automated alerts to the right owner within the same business day — no manual monitoring, no reporting lag, no missed signals.
Executive Trust Restored
Leadership reviews one certified dashboard weekly. Every number is traceable to its source system, formula, and owner. No disputed figures in QBRs.
Scalable to Any New System
The governance model — dictionary + ownership + triggers + cadence — can onboard any new source system or KPI without rebuilding the framework.