CRM Prediction — Technical Reference

Overview

The Prediction subsection of the CRM Analytics module provides a 6-month historical baseline and a 6-month forward forecast for shipment volume and revenue. It is computed live on each request (no snapshot caching) by backend/libraries/crmPrediction.util.js.

---

Endpoint

GET /prospects/client-prediction

Auth: token_admin

Permission: crm-menu

Query Parameters

Parameter	Type	Description
executives	string (optional)	Comma-separated administrator IDs. Omit to include all.
localeIds	string (optional)	Comma-separated locale IDs. Omit to include all countries.

Response Payload

{
  summary: {
    total_accounts:          number,  // companies with a follow-up stage
    total_prospects:         number,  // prospection_users in the pipeline
    avg_closing_days:        number,  // global average days from first activity to won
    conversion_rate:         number,  // percentage 0-100
    last_6m_shipments:       number,  // sum of historical actuals last 6 months
    projected_6m_shipments:  number,  // sum of forecast months 1-6
    projected_revenue:       number,  // USD
  },
  historical: [
    { month: 'YYYY-MM', actual_shipments: number, actual_revenue: number },
    // …up to 6 entries
  ],
  forecast: [
    {
      month:               'YYYY-MM',
      projected_shipments: number,
      projected_revenue:   number,
      low_band:            number,  // projected × 0.8
      high_band:           number,  // projected × 1.2
    },
    // …6 entries (next 6 calendar months)
  ],
  by_executive: [
    {
      id:                  number,
      name:                string,
      role:                string | null,
      accounts:            number,
      prospects:           number,
      conversion_rate:     number,   // percentage 0-100
      avg_closing_days:    number,
      historical_shipments: number,  // avg monthly over last 6m × 6
      forecast_shipments:  number,   // total contribution across horizon
    },
    // …sorted by forecast_shipments DESC
  ],
}

---

Library: backend/libraries/crmPrediction.util.js

Exported Function

async function computeCrmPrediction({ executives, localeIds } = {}): Promise<object>

SQL Queries

All six queries run in parallel via Promise.all.

Query 1 — Historical actuals (last 6 months)

Joins company_monthly_tiers (one snapshot per company per month) with crm_analytics_leads to scope by executive and locale.

SELECT DATE_FORMAT(cmt.created_at, '%Y-%m-01') AS month_bucket,
       SUM(cmt.total_shipments)                AS actual_shipments,
       SUM(cmt.total_revenue)                  AS actual_revenue
FROM company_monthly_tiers cmt
JOIN crm_analytics_leads cal
     ON cal.record_type = 'company'
     AND cal.record_id = cmt.company_id
WHERE cmt.created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
  [AND cal.executive_id IN (...)]
  [AND cal.locale_id IN (...)]
GROUP BY month_bucket
ORDER BY month_bucket ASC

Query 2 — Active pipeline leads

Leads not yet won or lost, with declared monthly shipment volume > 0.

SELECT cal.record_id, cal.record_type, cal.executive_id,
       cal.monthly_shipments, cal.account_value, cal.locale_rate, cal.closing_days
FROM crm_analytics_leads cal
WHERE cal.is_won  = 0
  AND cal.is_lost = 0
  AND cal.monthly_shipments > 0
  [AND cal.executive_id IN (...)]
  [AND cal.locale_id    IN (...)]

Query 3 — Conversion rate per executive

All-time, scoped to filters. has_shipment = 1 AND is_lost = 0 maps to "won"; is_lost = 1 maps to "lost".

SELECT cal.executive_id,
       SUM(CASE WHEN cal.is_lost = 0 AND cal.has_shipment = 1 THEN 1 ELSE 0 END) AS won,
       SUM(CASE WHEN cal.is_lost = 1 THEN 1 ELSE 0 END)                          AS lost
FROM crm_analytics_leads cal
WHERE cal.executive_id IS NOT NULL [filters]
GROUP BY cal.executive_id

Query 4 — Average closing days per executive

Only closed/won leads that have a recorded closing_days value.

SELECT cal.executive_id,
       AVG(cal.closing_days) AS avg_closing_days
FROM crm_analytics_leads cal
WHERE cal.closing_days IS NOT NULL
  AND cal.is_lost  = 0
  AND cal.has_shipment = 1
  [filters]
GROUP BY cal.executive_id

Query 5 — Executive metadata

Distinct executives in scope, with their role label.

SELECT DISTINCT
    cal.executive_id AS id,
    cal.executive_name AS name,
    car.description AS role
FROM crm_analytics_leads cal
LEFT JOIN administrators a ON a.id = cal.executive_id
LEFT JOIN catalog_administrator_roles car ON car.id = a.role_id
WHERE cal.executive_id IS NOT NULL [filters]
ORDER BY car.description, cal.executive_name

Query 6 — Executive portfolio stats

Account/prospect counts and average historical shipments per executive.

SELECT cal.executive_id,
       COUNT(CASE WHEN cal.record_type = 'company'  THEN 1 END) AS accounts,
       COUNT(CASE WHEN cal.record_type = 'prospect' THEN 1 END) AS prospects,
       COALESCE(SUM(cmt_avg.avg_shipments), 0)                  AS historical_shipments
FROM crm_analytics_leads cal
LEFT JOIN (
    SELECT cmt.company_id, AVG(cmt.total_shipments) AS avg_shipments
    FROM company_monthly_tiers cmt
    WHERE cmt.created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
    GROUP BY cmt.company_id
) cmt_avg ON cmt_avg.company_id = cal.record_id AND cal.record_type = 'company'
WHERE cal.executive_id IS NOT NULL [filters]
GROUP BY cal.executive_id

---

Forecast Computation (JS)

After the six SQL queries complete, the forecast is assembled entirely in JavaScript.

1. Build lookup maps

// conversionMap: executive_id → rate (0..1)
conversionMap[id] = won / (won + lost);   // 0 if no closed deals

// closingDaysMap: executive_id → avg days (integer)
// globalAvgClosingDays: fallback when executive has no history

2. Compute recurring baseline

const last3 = historical.slice(-3);
const baselineRecurring = last3.length > 0
    ? Math.round(last3.reduce((s, r) => s + r.actual_shipments, 0) / last3.length)
    : 0;

baselineRecurring captures stable shipment volume from already-won accounts that continue to ship month over month.

3. Generate forecast month labels

// forecastMonths[0] = next calendar month, forecastMonths[5] = 6 months from now
const forecastMonths = Array.from({ length: 6 }, (_, i) => {
    const d = new Date(today.getFullYear(), today.getMonth() + 1 + i, 1);
    return `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}`;
});

4. Distribute pipeline contribution

For each active lead:

const convRate      = conversionMap[execId] ?? 0;
const closingDays   = closingDaysMap[execId] ?? globalAvgClosingDays;
const closeMonthIdx = clamp(ceil(closingDays / 30) - 1, 0, 5);
                      // 0-based; 0 = next month, 5 = 6th month

const expectedShipments = monthly_shipments * convRate;

for (let m = closeMonthIdx; m < 6; m++) {
    pipelineContribution[m] += expectedShipments;
}

The "from closing month onward" logic models that once an account converts, it ships every subsequent month within the horizon.

5. Assemble per-month forecast

forecast[m] = {
    projected_shipments: Math.round(baselineRecurring + pipelineContribution[m]),
    low_band:  Math.round(projected * 0.8),
    high_band: Math.round(projected * 1.2),
};

The ±20% confidence band mirrors the "accurate" bucket threshold already defined in the Forecast Security module (80%–120%).

---

Design Decisions

No caching

The prediction payload is computed live on every request. Unlike the main CRM Analytics endpoint (which uses a pre-computed crm_analytics_leads snapshot and a Redis cache for repeated date/locale combinations), the prediction query set is small (six focused queries) and the result changes meaningfully whenever the pipeline changes. Adding a cache layer would be premature optimization; it can be introduced later if latency becomes a concern.

No date filter

The forecast is always anchored to today. Allowing arbitrary date ranges would make the "next 6 months" window ambiguous (relative to what date?) and would complicate the interpretation of the confidence band. If historical analysis by date range is needed, the Analytics module already covers that use case.

company_monthly_tiers over live shipments

Querying the shipments table directly for historical totals would be expensive (millions of rows, join-heavy). company_monthly_tiers is a pre-aggregated snapshot table that is updated by a scheduled job, making it orders of magnitude faster. The trade-off is that the historical data may lag by up to one snapshot cycle, which is acceptable for a planning/projection tool.

Conversion rate scope

Conversion rate is computed all-time (no date window) to maximize the statistical sample. Using only a short recent window would produce noisy or null rates for executives with low closing volume in the selected period.

Confidence band at ±20%

The 20% margin was chosen for internal consistency with the Forecast Security module, which classifies a forecast as "accurate" when the actual result falls between 80% and 120% of the estimate. The band communicates that the projection is the midpoint of a plausible range, not a guaranteed outcome.