"""
Multi-Market Analytics Routes (P8).
Blueprint: /api/analytics/multi-market
Endpoints:
GET /overview – Market-level KPIs
GET /comparison – Side-by-side market comparison
GET /market/<market_id> – Single market drill-down
GET /underperforming – Markets needing attention
"""
from datetime import datetime, timedelta, timezone
from typing import Any
from flask import Blueprint, jsonify
from sqlalchemy import and_, func
from app import db
from app.models import AdCampaign, AdMetric, CrmContact, CrmDeal
multi_market_bp = Blueprint('multi_market', __name__, url_prefix='/api/analytics/multi-market')
# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------
_STAGE_OPTIONS = [
'lead', 'qualified', 'proposal', 'negotiation',
'closed_won', 'closed_lost', 'new', 'discovery',
]
def _safe_pct(num: float, den: float) -> float:
return round((num / den) * 100, 2) if den else 0.0
def _aware(dt):
"""Normalize a possibly-naive datetime to timezone-aware UTC."""
if dt is not None and dt.tzinfo is None:
return dt.replace(tzinfo=timezone.utc)
return dt
def _market_deals_query(market: str):
"""All deals for contacts belonging to *market*."""
return (
db.session.query(CrmDeal)
.join(CrmContact, CrmContact.id == CrmDeal.contact_id)
.filter(CrmContact.market == market)
.all()
)
# ---------------------------------------------------------------------------
# 1. GET /overview
# ---------------------------------------------------------------------------
@multi_market_bp.route('/overview', methods=['GET'])
def market_overview():
"""Market-level KPIs grouped by market, sorted by total_revenue desc."""
markets: list[str] = [
row[0]
for row in db.session.query(CrmContact.market)
.filter(CrmContact.market.isnot(None), CrmContact.market != '')
.distinct()
.all()
]
results: list[dict[str, Any]] = []
for market in markets:
deals = _market_deals_query(market)
won = [d for d in deals if d.stage == 'closed_won']
total_deals = len(deals)
won_deals = len(won)
close_rate = _safe_pct(won_deals, total_deals)
total_revenue = sum(d.amount or 0 for d in won)
avg_deal_size = total_revenue / won_deals if won_deals else 0.0
active_contacts = CrmContact.query.filter(
CrmContact.market == market,
CrmContact.status == 'active',
).count()
results.append({
'market': market,
'total_deals': total_deals,
'won_deals': won_deals,
'close_rate': close_rate,
'total_revenue': round(total_revenue, 2),
'avg_deal_size': round(avg_deal_size, 2),
'active_contacts': active_contacts,
})
results.sort(key=lambda r: r['total_revenue'], reverse=True)
return jsonify({'markets': results, 'total_markets': len(results)})
# ---------------------------------------------------------------------------
# 2. GET /comparison
# ---------------------------------------------------------------------------
@multi_market_bp.route('/comparison', methods=['GET'])
def market_comparison():
"""Side-by-side comparison with top/bottom performers and market share."""
markets: list[str] = [
row[0]
for row in db.session.query(CrmContact.market)
.filter(CrmContact.market.isnot(None), CrmContact.market != '')
.distinct()
.all()
]
row_data: list[dict[str, Any]] = []
for market in markets:
deals = _market_deals_query(market)
won = [d for d in deals if d.stage == 'closed_won']
revenue = sum(d.amount or 0 for d in won)
close_rate = _safe_pct(len(won), len(deals))
avg_deal_size = revenue / len(won) if won else 0.0
owners = {d.deal_owner_id for d in deals if d.deal_owner_id}
deals_per_owner = len(deals) / len(owners) if owners else 0.0
# CPC from ad campaigns (aggregate across all campaigns; per-market CPC
# would require tagging campaigns by market — we fall back to global avg)
row_data.append({
'market': market,
'revenue': round(revenue, 2),
'close_rate': close_rate,
'avg_deal_size': round(avg_deal_size, 2),
'deals_per_owner': round(deals_per_owner, 2),
'total_deals': len(deals),
'won_deals': len(won),
'unique_owners': len(owners),
})
grand_total = sum(r['revenue'] for r in row_data)
for r in row_data:
r['market_share'] = round(r['revenue'] / grand_total * 100, 2) if grand_total else 0.0
# Global CPC from AdMetric
metrics = AdMetric.query.filter(
and_(AdMetric.clicks > 0, AdMetric.spend > 0)
).all()
total_spend = sum(m.spend or 0 for m in metrics)
total_clicks = sum(m.clicks or 0 for m in metrics)
avg_cpc = round(total_spend / total_clicks, 2) if total_clicks else 0.0
for r in row_data:
r['cpc'] = avg_cpc
# Averages
n = len(row_data)
averages = {}
if n:
averages = {
'revenue': round(sum(r['revenue'] for r in row_data) / n, 2),
'close_rate': round(sum(r['close_rate'] for r in row_data) / n, 2),
'avg_deal_size': round(sum(r['avg_deal_size'] for r in row_data) / n, 2),
}
# Top / bottom performers (by close_rate)
by_cr = sorted(row_data, key=lambda r: r['close_rate'], reverse=True)
top_k = max(1, n // 3) if n else 0
return jsonify({
'markets': row_data,
'averages': averages,
'top_performers': by_cr[:top_k],
'bottom_performers': by_cr[-top_k:] if n > 1 else [],
'total_markets': n,
})
# ---------------------------------------------------------------------------
# 3. GET /market/<market_id>
# ---------------------------------------------------------------------------
@multi_market_bp.route('/market/<market_id>', methods=['GET'])
def market_drilldown(market_id: str):
"""Single market drill-down: pipeline, top deals, recent activity, revenue trend."""
deals = _market_deals_query(market_id)
won = [d for d in deals if d.stage == 'closed_won']
# Pipeline by stage
stage_map: dict[str, dict[str, Any]] = {}
for d in deals:
st = d.stage or 'unknown'
if st not in stage_map:
stage_map[st] = {'stage': st, 'count': 0, 'value': 0.0}
stage_map[st]['count'] += 1
stage_map[st]['value'] += d.amount or 0
pipeline_by_stage = [
{**s, 'value': round(s['value'], 2)} for s in stage_map.values()
]
# Top deals (active + won, sorted by amount desc)
candidates = sorted(
[d for d in deals if d.stage != 'closed_lost'],
key=lambda d: d.amount or 0,
reverse=True,
)[:10]
top_deals = [
{
'id': d.id,
'amount': round(d.amount or 0, 2),
'stage': d.stage,
'probability': d.probability,
'expected_close_date': d.expected_close_date.isoformat() if d.expected_close_date else None,
'pipeline': d.pipeline,
'contact_id': d.contact_id,
}
for d in candidates
]
# Recent activity (last 30 days)
thirty_ago = datetime.now(timezone.utc) - timedelta(days=30)
recent = [
{
'id': d.id,
'stage': d.stage,
'amount': round(d.amount or 0, 2),
'created_at': d.created_at.isoformat() if d.created_at else None,
}
for d in deals
if d.created_at and _aware(d.created_at) >= thirty_ago
]
recent.sort(key=lambda r: r['created_at'] or '', reverse=True)
# Revenue trend (last 6 months from won deals)
now = datetime.now(timezone.utc)
trend: list[dict[str, Any]] = []
for i in range(6):
month_start = now - timedelta(days=180) + timedelta(days=30 * i)
month_end = month_start + timedelta(days=30)
rev = sum(
d.amount or 0
for d in won
if d.created_at and month_start <= _aware(d.created_at) < month_end
)
trend.append({
'month': month_start.strftime('%b %Y'),
'revenue': round(rev, 2),
})
# Contacts
contacts = CrmContact.query.filter(CrmContact.market == market_id).all()
active_contacts = [c for c in contacts if c.status == 'active']
return jsonify({
'market': market_id,
'summary': {
'total_deals': len(deals),
'won_deals': len(won),
'lost_deals': len([d for d in deals if d.stage == 'closed_lost']),
'active_deals': len([d for d in deals if d.stage not in ('closed_won', 'closed_lost')]),
'close_rate': _safe_pct(len(won), len(deals)),
'total_revenue': round(sum(d.amount or 0 for d in won), 2),
'avg_deal_size': round(sum(d.amount or 0 for d in won) / len(won), 2) if won else 0.0,
'total_contacts': len(contacts),
'active_contacts': len(active_contacts),
},
'pipeline_by_stage': pipeline_by_stage,
'top_deals': top_deals,
'recent_activity': recent[:20],
'revenue_trend': trend,
})
# ---------------------------------------------------------------------------
# 4. GET /underperforming
# ---------------------------------------------------------------------------
@multi_market_bp.route('/underperforming', methods=['GET'])
def underperforming_markets():
"""Detect markets with issues and return actionable recommendations."""
markets: list[str] = [
row[0]
for row in db.session.query(CrmContact.market)
.filter(CrmContact.market.isnot(None), CrmContact.market != '')
.distinct()
.all()
]
if not markets:
return jsonify({'underperforming': [], 'total_analyzed': 0, 'benchmarks': {}})
metrics: list[dict[str, Any]] = []
for market in markets:
deals = _market_deals_query(market)
won = [d for d in deals if d.stage == 'closed_won']
revenue = sum(d.amount or 0 for d in won)
close_rate = _safe_pct(len(won), len(deals))
avg_deal_size = revenue / len(won) if won else 0.0
# Revenue trend: recent (90d) vs older (90-180d)
now = datetime.now(timezone.utc)
recent_won = [d for d in won if d.created_at and _aware(d.created_at) >= now - timedelta(days=90)]
older_won = [
d for d in won
if d.created_at and (now - timedelta(days=180)) <= _aware(d.created_at) < (now - timedelta(days=90))
]
recent_rev = sum(d.amount or 0 for d in recent_won)
older_rev = sum(d.amount or 0 for d in older_won)
declining = older_rev > 0 and recent_rev < older_rev * 0.8
metrics.append({
'market': market,
'close_rate': close_rate,
'total_revenue': round(revenue, 2),
'avg_deal_size': round(avg_deal_size, 2),
'total_deals': len(deals),
'won_deals': len(won),
'recent_revenue': round(recent_rev, 2),
'older_revenue': round(older_rev, 2),
'declining': declining,
})
n = len(metrics)
avg_close_rate = sum(m['close_rate'] for m in metrics) / n
avg_revenue = sum(m['total_revenue'] for m in metrics) / n
underperforming: list[dict[str, Any]] = []
for m in metrics:
issues: list[str] = []
recommendations: list[str] = []
if m['close_rate'] < avg_close_rate:
issues.append('close_rate_below_average')
recommendations.append(
f"Close rate ({m['close_rate']}%) is below the average ({avg_close_rate:.1f}%). "
f"Review sales playbooks and consider targeted coaching for reps in this market."
)
if m['declining']:
issues.append('declining_revenue')
recommendations.append(
f"Revenue declined from ${m['older_revenue']:,.0f} to ${m['recent_revenue']:,.0f} over the "
f"last quarter. Investigate market-specific challenges and renew outreach."
)
if avg_revenue > 0 and m['total_revenue'] < avg_revenue * 0.5:
issues.append('low_revenue')
recommendations.append(
f"Total revenue (${m['total_revenue']:,.0f}) is well below the market average "
f"(${avg_revenue:,.0f}). Consider increasing lead generation and pipeline velocity."
)
if m['avg_deal_size'] > 0 and m['avg_deal_size'] < 5000:
issues.append('high_cac_risk')
recommendations.append(
f"Average deal size (${m['avg_deal_size']:,.0f}) is small, raising CAC concerns. "
f"Focus on upselling and bundling to increase deal size."
)
if issues:
underperforming.append({
'market': m['market'],
'issues': issues,
'recommendations': recommendations,
'metrics': m,
'severity': 'high' if len(issues) >= 3 else ('medium' if len(issues) >= 2 else 'low'),
})
severity_order = {'high': 0, 'medium': 1, 'low': 2}
underperforming.sort(key=lambda x: (severity_order.get(x['severity'], 3), -x['metrics']['total_revenue']))
return jsonify({
'underperforming': underperforming,
'total_analyzed': n,
'benchmarks': {
'avg_close_rate': round(avg_close_rate, 2),
'avg_revenue': round(avg_revenue, 2),
},
})