115 lines
4.0 KiB
Python
115 lines
4.0 KiB
Python
"""
|
|
app/admin/analytics/routes.py
|
|
Platform analytics dashboard: active tenants, MRR, trial conversions,
|
|
churn rate, tenant health signals.
|
|
"""
|
|
|
|
import logging
|
|
from datetime import datetime, timezone, timedelta
|
|
|
|
from flask import Blueprint, render_template
|
|
from flask_login import login_required
|
|
from sqlalchemy import func
|
|
|
|
from app.extensions import db
|
|
from app.models.platform import Tenant, Plan, TenantBillingHistory
|
|
from app.admin.utils import superadmin_required
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
analytics_bp = Blueprint("analytics", __name__, url_prefix="/analytics")
|
|
|
|
|
|
@analytics_bp.route("/")
|
|
@login_required
|
|
@superadmin_required
|
|
def index():
|
|
now = datetime.now(timezone.utc)
|
|
thirty_days_ago = now - timedelta(days=30)
|
|
|
|
# ── Tenant counts by status ───────────────────────────────
|
|
counts = (
|
|
db.session.query(Tenant.status, func.count(Tenant.id))
|
|
.group_by(Tenant.status)
|
|
.all()
|
|
)
|
|
status_counts = {s: c for s, c in counts}
|
|
total_tenants = sum(status_counts.values())
|
|
active_count = status_counts.get("active", 0)
|
|
trial_count = status_counts.get("trial", 0)
|
|
suspended_count = status_counts.get("suspended", 0)
|
|
cancelled_count = status_counts.get("cancelled", 0)
|
|
|
|
# ── MRR — sum of monthly prices for active non-demo tenants ──
|
|
mrr_rows = (
|
|
db.session.query(Plan.price_monthly, func.count(Tenant.id))
|
|
.join(Tenant, Tenant.plan_id == Plan.id)
|
|
.filter(Tenant.status == "active", Tenant.is_demo == False) # noqa: E712
|
|
.group_by(Plan.price_monthly)
|
|
.all()
|
|
)
|
|
mrr = sum(price * count for price, count in mrr_rows)
|
|
|
|
# ── Trial conversions last 30 days ───────────────────────
|
|
converted = Tenant.query.filter(
|
|
Tenant.status == "active",
|
|
Tenant.trial_ends_at >= thirty_days_ago,
|
|
Tenant.trial_ends_at <= now,
|
|
).count()
|
|
|
|
# ── Churn last 30 days ────────────────────────────────────
|
|
churned = Tenant.query.filter(
|
|
Tenant.status.in_(["suspended", "cancelled"]),
|
|
Tenant.updated_at >= thirty_days_ago,
|
|
).count()
|
|
|
|
# ── New tenants last 30 days ─────────────────────────────
|
|
new_tenants = Tenant.query.filter(
|
|
Tenant.created_at >= thirty_days_ago
|
|
).count()
|
|
|
|
# ── Tenant health table (trials expiring soon) ────────────
|
|
expiring_soon = Tenant.query.filter(
|
|
Tenant.status == "trial",
|
|
Tenant.trial_ends_at <= now + timedelta(days=3),
|
|
Tenant.trial_ends_at >= now,
|
|
).order_by(Tenant.trial_ends_at.asc()).all()
|
|
|
|
# ── Revenue last 30 days (from billing history) ───────────
|
|
revenue_30d_row = db.session.query(
|
|
func.sum(TenantBillingHistory.amount)
|
|
).filter(
|
|
TenantBillingHistory.created_at >= thirty_days_ago
|
|
).scalar()
|
|
revenue_30d = float(revenue_30d_row or 0)
|
|
|
|
# ── Tenants by plan ───────────────────────────────────────
|
|
plan_counts = (
|
|
db.session.query(Plan.name, func.count(Tenant.id))
|
|
.join(Tenant, Tenant.plan_id == Plan.id)
|
|
.filter(Tenant.status.in_(["active", "trial"]))
|
|
.group_by(Plan.name)
|
|
.all()
|
|
)
|
|
|
|
stats = {
|
|
"total_tenants": total_tenants,
|
|
"active_count": active_count,
|
|
"trial_count": trial_count,
|
|
"suspended_count": suspended_count,
|
|
"cancelled_count": cancelled_count,
|
|
"mrr": mrr,
|
|
"converted_30d": converted,
|
|
"churned_30d": churned,
|
|
"new_tenants_30d": new_tenants,
|
|
"revenue_30d": revenue_30d,
|
|
"plan_counts": plan_counts,
|
|
}
|
|
|
|
return render_template(
|
|
"admin/analytics/index.html",
|
|
stats=stats,
|
|
expiring_soon=expiring_soon,
|
|
now=now,
|
|
)
|