Total Net Profit
$42.1M
β² 12.4% vs Q1
Contract Revenue
$68.7M
β² 8.2% vs Q1
Total Support Cost
$26.6M
β² 3.1% vs Q1
At-Risk CSPs
34
β² 4 new this quarter
Avg Margin / CSP
61.3%
β² 2.1 pts vs Q1
Profit vs. Cost Trend
Monthly revenue, support cost, and net profit across active CSP portfolio
CSP Segment Distribution
By risk classification
π’ High Efficiency87 CSPs
π‘ Legit Demand54 CSPs
π΄ Inefficient34 CSPs
β« Outlier8 CSPs
Profit Optimization Pipeline
IntelligenceIQ four-stage model flow for CSP profit maximization
1
Cost Prediction
XGBoost / LightGBM
per CSP monthly forecast
per CSP monthly forecast
2
Root Cause Split
Inefficiency Score
vs Demand Complexity
vs Demand Complexity
3
Churn Risk Model
Price sensitivity
& retention probability
& retention probability
4
Tier Optimization
Max expected profit
per CSP assignment
per CSP assignment
AI-Driven Insights
Highest-impact actions to increase partner margins this quarter
12 CSPs Generating Negative Margin
These partners are consuming $4.2M in support costs against $3.1M in contract revenue. Immediate upsell or contract restructuring recommended. Combined opportunity: +$1.4M net profit.
Escalation Rate Drives 63% of Cost Variance
Inefficient CSPs average 4.7Γ the escalation rate of high-efficiency peers. Training investment of ~$180K could deflect $2.1M in support costs β a 12Γ ROI.
Legitimate Demand CSPs Being Under-Priced
54 CSPs with complex Azure/hybrid portfolios are paying Standard tier despite consuming Premium-level resources. Repositioned pricing could unlock $3.8M in additional revenue.
Incident Prediction Accuracy: 91.4%
Model trained on 18 months of incident history, severity mix, and resolution quality. Gradient Boosting outperforms linear baseline by 34% RMSE reduction.
Top Profit Opportunity: Tier Migration
Moving 34 low-efficiency CSPs to Premium+ contracts increases revenue by $6.2M. With 14% estimated churn, expected net gain is $5.3M β highest single lever identified.
Self-Service Deflection Underutilized
Analysis shows 38% of Tier-1 incidents match documented KB articles. Targeted self-serve nudges for 22 CSPs could cut support costs by $890K with zero contract changes.
Total Active CSPs
183
β² 11 new Q2
Profitable CSPs
149
81.4% of portfolio
Unprofitable CSPs
34
18.6% β action needed
Avg Monthly Incidents
148
β² 12 vs Q1
Avg Cost / Incident
$312
βΌ $18 vs Q1
CSP Portfolio β Risk Segmentation View
Sorted by profit margin. Click a row to simulate contract tier change.
| CSP Name | Segment | Contract Tier | Annual Revenue | Annual Support Cost | Net Profit (Annual) | Efficiency Score | Churn Risk | Recommended Action |
|---|
Incident Volume by Severity
Monthly P1βP4 distribution across portfolio
Cost vs. Revenue Scatter
Profit zones by CSP β bubble = customer count
Contract Tier Simulator
Model expected profit across pricing scenarios before making contract changes
Expected Profit Equation
Full objective function derived from
fact_pricing_simulation β maximize over all candidate tiers T// fact_pricing_simulation.expected_profit_usd
ExpectedProfit(CSP, T) =
(candidate_annual_price_usd[T]
β predicted_annual_support_cost_usd)
β predicted_annual_support_cost_usd)
Γ predicted_retention_probability(CSP, ΞPrice)
// predicted_annual_support_cost_usd β ML model output
f(support_burden_score) where:
support_burden_score =
0.50 Γ normalized_support_cost_usd
+ 0.30 Γ inefficiency_score
+ 0.20 Γ portfolio_complexity_score
+ 0.30 Γ inefficiency_score
+ 0.20 Γ portfolio_complexity_score
inefficiency_score =
0.30 Γ escalation_rate
+ 0.25 Γ reopen_rate
+ 0.20 Γ repeat_issue_rate
+ 0.15 Γ (1 β l1_resolution_rate)
+ 0.10 Γ (1 β knowledge_base_usage_rate)
+ 0.25 Γ reopen_rate
+ 0.20 Γ repeat_issue_rate
+ 0.15 Γ (1 β l1_resolution_rate)
+ 0.10 Γ (1 β knowledge_base_usage_rate)
portfolio_complexity_score =
0.25 Γ enterprise_customer_ratio
+ 0.20 Γ regulated_industry_ratio
+ 0.20 Γ hybrid_infra_ratio
+ 0.20 Γ advanced_security_ratio
+ 0.15 Γ multi_geo_ratio
+ 0.20 Γ regulated_industry_ratio
+ 0.20 Γ hybrid_infra_ratio
+ 0.20 Γ advanced_security_ratio
+ 0.15 Γ multi_geo_ratio
// predicted_retention_probability β churn model output
g(ΞPrice, csp_segment, growth_rate_cost_3m,
renewal_within_6m_flag, avg_partner_touchpoints)
renewal_within_6m_flag, avg_partner_touchpoints)
// Sources: fact_csp_month + fact_support_operations_month
// Training label: churn_flag (historical outcome)
Single-CSP Optimizer
Simulate optimal pricing for any partner
π’ High Efficiency
87
Avg margin: 79%
π‘ Legit Demand
54
Avg margin: 58% β underprice risk
π΄ Low Efficiency
34
Avg margin: β4% β primary target
β« Extreme Outliers
8
Requires restructuring
Upsell Opportunity
$5.3M
Across 34 inefficient CSPs
Quadrant Matrix β Inefficiency vs. Portfolio Complexity
Axes derived from
inefficiency_score (fact_support_operations_month) vs portfolio_complexity_score (fact_customer_portfolio_month)Action Playbook by Segment
Recommended interventions to optimize profit per segment
π’ High Efficiency / Low Cost β RETAIN
- Keep contract pricing stable β don't over-index
- Offer co-sell incentives and MDF to reward performance
- Use as benchmarks for training inefficient peers
π‘ Legit Demand / Complex β UPSELL WITH VALUE
- Move to Premium tier β frame as capability access, not penalty
- Bundle proactive support + TAM as premium value
- Target renewal window for tier discussion
π΄ Low Efficiency / High Cost β UPSELL OR TRAIN
- Primary profit lever β aggressive contract migration
- Offer training investment as alternative to price increase
- Set SLA improvement gates tied to contract renewal
β« Extreme Outliers β RESTRUCTURE
- Usage caps or dedicated support pricing
- Contract restructuring or transition off Unified
- Evaluate business case for continued relationship
Segment Profit Comparison
Average revenue, support cost, and net profit by CSP segment classification
Data Schema β CSP Profit Model
Star schema with 8 tables. Designed for Azure Data Lake / Synapse + Python ML pipelines + Power BI.
2 Dimension Tables
5 Fact Tables
3 Derived Scores
1 Optional Dim
π
Minimum Viable Schema β Start Here
Begin with dim_csp + dim_contract + fact_incident + fact_csp_month to immediately unlock cost prediction, profitability views, and contract tier recommendations. Add the remaining tables once you need to separate inefficiency from legitimate complexity.
DIMENSION TABLES
A. dim_csp β Partner Master
One row per CSP. Your master partner table.
| Column | Type | Description |
|---|---|---|
| csp_id | string | Unique CSP identifier |
| csp_name | string | CSP legal/business name |
| region | string | Geography |
| country | string | Country |
| segment | string | SMB / Mid-market / Enterprise-focused |
| partner_tier | string | Partner tier classification |
| managed_services_flag | bool | Whether CSP runs managed services |
| internal_support_headcount | int | Estimated support team size |
| internal_support_maturity_score | decimal | Optional operational maturity score |
| created_date | date | When CSP relationship started |
| status | string | Active / Inactive / Probation |
B. dim_contract β Contract Periods
One row per contract or contract period. Supports pricing optimization over time.
| Column | Type | Description |
|---|---|---|
| contract_id | string | Unique contract id |
| csp_id | FK | FK to dim_csp |
| contract_start_date | date | Start date |
| contract_end_date | date | End date |
| contract_tier | string | Current tier |
| annual_contract_value | decimal | Revenue from CSP |
| included_support_units | int | Included support entitlement if applicable |
| overage_pricing_rule | string | Overuse pricing logic |
| pricing_model | string | Fixed / Tiered / Hybrid |
| currency | string | Contract currency |
| renewal_flag | bool | Whether renewal is upcoming |
| renewal_date | date | Next renewal date |
FACT TABLES
C. fact_incident β Individual Support Incidents
GOLD TABLE
One row per support incident. Your gold table for detailed support burden analysis.
| Column | Type | Description |
|---|---|---|
| incident_id | string | Unique incident id |
| csp_id | FK | FK to dim_csp |
| contract_id | FK | FK to contract active at incident time |
| incident_open_date | datetime | When opened |
| incident_close_date | datetime | When closed |
| month_key | date | First day of incident month |
| product_family | string | Azure / M365 / Dynamics / Security etc. |
| workload | string | More specific workload |
| severity | string | Sev A/B/C or equivalent |
| issue_category | string | Billing / technical / identity / networking |
| issue_subcategory | string | Finer taxonomy |
| channel | string | Portal / phone / partner center / TAM |
| Column | Type | Description |
|---|---|---|
| escalated_flag | bool | Whether escalated |
| escalation_count | int | Number of escalations |
| reopen_flag | bool | Whether reopened |
| time_to_first_response_hours | decimal | Response metric |
| time_to_resolution_hours | decimal | Resolution metric |
| transferred_count | int | Number of team transfers |
| resolved_by_msft_flag | bool | Final ownership by support team |
| duplicate_issue_flag | bool | Repeat/known problem |
| support_cost_usd | decimal | Estimated internal support cost |
| csat_score | decimal | Optional satisfaction score |
| sla_breach_flag | bool | Whether SLA breached |
D. fact_csp_month β Monthly CSP Rollup
PRIMARY ML INPUT
One row per CSP per month. The most important modeling table β main input to forecasting and optimization.
| Column | Type | Description |
|---|---|---|
| csp_id | string | CSP id |
| month_key | date | Month |
| active_contract_id | FK | Contract during month |
| active_contract_tier | string | Tier during month |
| monthly_recognized_revenue_usd | decimal | Revenue allocated to month |
| incident_count | int | Total incidents |
| sev_a_count | int | High severity incidents |
| sev_b_count | int | Medium severity incidents |
| sev_c_count | int | Low severity incidents |
| escalated_incident_count | int | Count escalated |
| repeat_incident_count | int | Repeated issue count |
| avg_time_to_resolution_hours | decimal | Avg resolution time |
| avg_time_to_first_response_hours | decimal | Avg response time |
| avg_transfers_per_incident | decimal | Handoff friction |
| Column | Type | Description |
|---|---|---|
| total_support_cost_usd | decimal | Sum of support cost |
| cost_per_incident_usd | decimal | Total cost / incident count |
| gross_margin_usd | decimal | Revenue β support cost |
| gross_margin_pct | decimal | Margin % |
| customer_count_supported | int | Approx downstream customers |
| seat_count_supported | int | Total supported seats/users |
| azure_consumption_usd | decimal | Optional portfolio proxy |
| m365_seat_count | int | Optional workload proxy |
| complex_workload_ratio | decimal | Share of advanced workloads |
| growth_rate_incidents_3m | decimal | Rolling trend |
| growth_rate_cost_3m | decimal | Rolling cost trend |
| renewal_within_6m_flag | bool | Renewal window |
| churn_flag | bool | Whether CSP later churned |
| upsell_flag | bool | Whether moved to higher tier later |
E. fact_customer_portfolio_month
One row per CSP per month β downstream customer base. Distinguishes legitimate demand from weak support operations.
| Column | Type | Description |
|---|---|---|
| csp_id | string | CSP id |
| month_key | date | Month |
| downstream_customer_count | int | Number of end customers |
| small_customer_count | int | Small customers |
| mid_customer_count | int | Mid-market customers |
| enterprise_customer_count | int | Enterprise customers |
| regulated_industry_count | int | Healthcare / finance / public sector |
| multi_geo_customer_count | int | Complex geography customers |
| advanced_security_customer_count | int | High-support environments |
| hybrid_infra_customer_count | int | Hybrid/on-prem integration |
| portfolio_complexity_score | decimal | Derived complexity score (see below) |
F. fact_support_operations_month
One row per CSP per month β internal team performance. Best place to capture "they lean on us because their team is weak."
| Column | Type | Description |
|---|---|---|
| csp_id | string | CSP id |
| month_key | date | Month |
| l1_resolution_rate | decimal | Resolved without escalation |
| escalation_rate | decimal | Escalated / total incidents |
| reopen_rate | decimal | Reopened / total incidents |
| repeat_issue_rate | decimal | Duplicate/repeat issue rate |
| avg_partner_touchpoints | decimal | Partner interaction count |
| knowledge_base_usage_rate | decimal | Self-service usage |
| certified_agent_count | int | Certified support staff |
| support_staff_turnover_rate | decimal | Optional |
| after_hours_incident_ratio | decimal | Operational pressure indicator |
| inefficiency_score | decimal | Derived score (see below) |
H. fact_pricing_simulation
One row per CSP per candidate pricing scenario. The decision layer for contract tier optimization.
| Column | Type | Description |
|---|---|---|
| simulation_id | string | Unique scenario id |
| csp_id | FK | CSP id |
| month_key | date | Evaluation month |
| candidate_contract_tier | string | Proposed tier |
| candidate_annual_price_usd | decimal | Simulated price |
| predicted_annual_support_cost_usd | decimal | ML model output |
| predicted_retention_probability | decimal | Churn model output |
| expected_profit_usd | decimal | (price β cost) Γ retention_prob |
| recommended_flag | bool | Best scenario for this CSP |
G. dim_product (optional)
Useful for weighted support-cost modeling across workloads.
| Column | Type | Description |
|---|---|---|
| product_id | string | Product/workload id |
| product_family | string | Azure / M365 / Dynamics |
| workload | string | Workload name |
| complexity_weight | decimal | Relative support burden |
| strategic_flag | bool | Important workload flag |
Entity Relationships
dim_csp βββ¬ββ dim_contractβββ fact_incident
βββ fact_csp_month
βββ fact_customer_portfolio_month
βββ fact_support_operations_month
βββ fact_pricing_simulation
dim_product ββ fact_incident (via workload)
DERIVED SCORES β ENGINEERED FEATURES
portfolio_complexity_score
Measures whether higher support demand is justified by portfolio composition.
= weighted sum of:
0.25 Γ enterprise_customer_ratio
0.20 Γ regulated_industry_ratio
0.20 Γ hybrid_infra_ratio
0.20 Γ advanced_security_ratio
0.15 Γ multi_geo_ratio
0.25 Γ enterprise_customer_ratio
0.20 Γ regulated_industry_ratio
0.20 Γ hybrid_infra_ratio
0.20 Γ advanced_security_ratio
0.15 Γ multi_geo_ratio
inefficiency_score
Measures whether the CSP's own support function is weak β not just that demand is high.
= weighted sum of:
0.30 Γ escalation_rate
0.25 Γ reopen_rate
0.20 Γ repeat_issue_rate
0.15 Γ (1 β l1_resolution_rate)
0.10 Γ (1 β kb_usage_rate)
0.30 Γ escalation_rate
0.25 Γ reopen_rate
0.20 Γ repeat_issue_rate
0.15 Γ (1 β l1_resolution_rate)
0.10 Γ (1 β kb_usage_rate)
support_burden_score
Blended metric β "how expensive is this CSP to serve?" Tune weights with real data.
= weighted sum of:
0.50 Γ normalized_support_cost
0.30 Γ inefficiency_score
0.20 Γ portfolio_complexity_score
β feeds pricing simulation
0.50 Γ normalized_support_cost
0.30 Γ inefficiency_score
0.20 Γ portfolio_complexity_score
β feeds pricing simulation
Implementation Stack
End-to-end technical architecture for the CSP Profit Optimization System
π¦ Data Layer
Azure Data Lake Gen2
Azure Synapse Analytics
Purview Data Catalog
Event Hub (real-time incidents)
Azure Synapse Analytics
Purview Data Catalog
Event Hub (real-time incidents)
π€ ML Layer
Python + LightGBM / XGBoost
Azure ML β training pipelines
MLflow β experiment tracking
Batch scoring (monthly runs)
Azure ML β training pipelines
MLflow β experiment tracking
Batch scoring (monthly runs)
π Reporting Layer
Power BI Premium
DirectQuery to Synapse
Row-level security (per CSP)
Automated alerts + notifications
DirectQuery to Synapse
Row-level security (per CSP)
Automated alerts + notifications
Global Portfolio Optimizer
Select a region on the map to configure optimization levers and generate an AI-powered portfolio suggestion.
Partner Manager Dashboard
Select a manager to view their CSP portfolio, quota attainment, and prioritized action queue
Priority Action Queue β Sarah Chen
AI-ranked actions by expected profit impact. Complete before renewal window closes.
Quota Attainment
Team Efficiency Breakdown
Manager Leaderboard
Ranked by net profit delivered this quarter
| # | Manager | Region | CSPs | Net Profit | vs Target | Margin |
|---|
Incident Cost Heatmap β By Manager Γ Month
Support cost concentration; darker = higher burden