CRM Analytics API

API endpoints that expose the analytics layer. All endpoints require JWT authentication.

Authentication

Admin endpoints require a valid admin JWT token:

Authorization: Bearer <token>

The cron endpoint requires the internal cron token (token_cron strategy), not the admin token.

Permission crm-menu is required for the analytics endpoint.

Endpoints

`GET /prospects/client-analytics`

Returns the full analytics payload for the CRM dashboard. Serves from a pre-computed snapshot when available; falls back to live computation over crm_analytics_leads when interactive filters are active or the snapshot is missing.

Auth: token_admin + permission crm-menu

Query Parameters

Parameter	Type	Required	Description
`startDate`	`string` (YYYY-MM-DD)	No	Start of the date range. If omitted, no date filter is applied.
`endDate`	`string` (YYYY-MM-DD)	No	End of the date range. Required when `startDate` is set.
`localeIds`	`string`	No	Comma-separated locale IDs (e.g., `"1,3"`). Filters results by country. Included in snapshot cache key.
`salesman`	`string`	No	Executive/salesman ID. Bypasses snapshot cache — always live.
`tier`	`string`	No	Comma-separated tier values `0`–`5` (e.g., `"1,2,3"`). Bypasses snapshot cache.
`stage`	`string`	No	Virtual stage name: `customer_lost`, `won`, `in_recharge`, or any pipeline stage name. Bypasses snapshot cache.
`product`	`string`	No	Package content translation tag (e.g., `"packageContent.electronics"`). Bypasses snapshot cache.

Caching rule: a snapshot is served only when no interactive filter (salesman, tier, stage, product) is set. The cache key is MD5(startDate|endDate|localeIds). Pre-built snapshots cover last30, 1m, 3m, and 6m with no localeIds.

Response Shape

json

{
  "kpis": {
    "total_leads":        { "current": 320, "previous": 290 },
    "total_shipments":    { "current": 47,  "previous": 39  },
    "avg_shipment_cost":  { "current": 125.40, "previous": 118.00 },
    "total_lost":         { "current": 18, "previous": 21 }
  },
  "extra_kpis": {
    "avg_closing_days":  42,
    "advancement_rate":  28.3,
    "in_recharge":       12
  },
  "sales_funnel": [
    { "stage_name": "contactado", "count": 85, "percentage": 26.6 },
    { "stage_name": "won",        "count": 47, "percentage": 14.7 },
    { "stage_name": "customer_lost", "count": 18, "percentage": 5.6 }
  ],
  "outcome_summary": {
    "won":        47,
    "lost":       18,
    "in_recharge": 12,
    "remaining":  243
  },
  "by_executive": [
    { "id": 5, "name": "Ana Gomez" },
    { "id": 8, "name": "Carlos Ruiz" }
  ],
  "by_executive_stage": [
    { "name": "Ana Gomez", "won": 12, "lost": 3, "remaining": 45, "total": 60 }
  ],
  "by_tier": [
    { "tier_label": "T1", "count": 130 },
    { "tier_label": "T2", "count": 87 }
  ],
  "by_product": [
    { "product_name": "packageContent.electronics", "lead_count": 95, "total_value": 48200.00 }
  ],
  "active_leads": [
    {
      "record_id": 1024,
      "record_type": "company",
      "name": "Acme Corp",
      "salesman": "Ana Gomez",
      "tier_label": "T2",
      "stage_name": "negociacion",
      "stage_color": "warning",
      "age_days": 312
    }
  ]
}

Response Fields

kpis — Main KPI cards. Each has current (selected period) and previous (equal-length period ending the day before startDate). The frontend renders a delta indicator.

Field	Description
`total_leads.current`	Count of leads created within the date range passing all filters
`total_leads.previous`	Same count for the immediately preceding equal-length period
`total_shipments.current`	Sum of `monthly_shipments` from `follow_up_data` for matching leads
`avg_shipment_cost.current`	`SUM(account_value / locale_rate) / SUM(monthly_shipments)` — normalized to USD
`total_lost.current`	Count of leads with `is_lost = 1` in the current period

extra_kpis — Supplementary metrics displayed as secondary KPI cards.

Field	Description
`avg_closing_days`	`AVG(closing_days)` for leads whose `won_at` falls within the date range (not `created_at`)
`advancement_rate`	`(won + in_recharge) / total * 100` — percentage of leads that converted or recharged
`in_recharge`	Count of leads with `has_recharge = 1 AND has_shipment = 0`

sales_funnel — Array of virtual-stage buckets. Sorted by count descending. Every lead appears in exactly one bucket (priority: customer_lost > won > in_recharge > stage_name).

outcome_summary — Aggregated counts of the four outcome buckets. Used for the outcome pie chart.

by_executive — Distinct list of executives present in the filtered dataset (locale filter only). Used to populate the salesman dropdown.

by_executive_stage — Per-executive breakdown of won, lost, remaining, and total. Used for the stacked bar chart.

by_tier — Lead counts per tier. By default excludes is_won = 1 and is_lost = 1 leads, but when the request filters by stage=won or stage=customer_lost the restriction is dropped so the chart reflects the active filter. Returned in tier order: blank, T0, T1, T2, T3, T4, T5.

by_product — Lead count and normalized total account value per product tag. Sorted by total_value descending.

active_leads — Top 100 leads ordered by age_days descending. By default returns only leads that are not won and not lost (intended for the aging table showing the oldest open leads), but when the request filters by stage=won or stage=customer_lost the table returns those leads instead so it stays consistent with the selected drilldown.

Example Requests

Preset range (served from snapshot):

http

GET /prospects/client-analytics?startDate=2026-03-18&endDate=2026-04-17
Authorization: Bearer <token>

With locale filter (separate snapshot key):

http

GET /prospects/client-analytics?startDate=2026-03-18&endDate=2026-04-17&localeIds=1
Authorization: Bearer <token>

With interactive filter (always live):

http

GET /prospects/client-analytics?startDate=2026-03-18&endDate=2026-04-17&salesman=5&stage=negociacion
Authorization: Bearer <token>

No date filter (all time):

http

GET /prospects/client-analytics
Authorization: Bearer <token>

`POST /crm/analytics/sync`

Triggers an incremental sync of crm_analytics_leads. Detects records changed since the last watermark, re-fetches their denormalized data, upserts into the materialized table, removes disqualified rows, and logs the run.

This endpoint is designed to be called by an external cron scheduler (e.g., EasyCron). It is safe to call repeatedly — the UPSERT and cleanup logic is idempotent.

Auth: token_cron (internal cron token — not the admin JWT)

Method: POST

Payload: none

Sync algorithm (what happens on each call)

Read the last watermark from crm_analytics_sync_log.
Find dirty company IDs: rows in companies, follow_up_comments, shipments, payment_history, company_monthly_tiers, follow_up_data with created_at > watermark.
Find dirty prospect IDs: rows in prospection_users, prospect_follow_up_comments, follow_up_data with created_at > watermark.
Pick the 10,000 oldest rows in crm_analytics_leads by synced_at (rolling refresh — catches changes invisible to timestamp detection such as salesman reassignment).
Union dirty + rolling, chunk into batches of 500, re-fetch via full denormalized JOIN, UPSERT.
Delete rows whose source record no longer qualifies (soft-deleted, disqualified, or re-linked to a company).
Write log entry with dirty_count, rolling_count, deleted_count, duration_ms. Watermark = job start time − 2 minutes (safety buffer).

Response

json

{
  "ok": true
}

On error (unhandled exception): standard Hapi 500 response.

Snapshot Pre-computation

The snapshot rebuild is triggered by the job rebuildCrmAnalyticsSnapshots in backend/jobs/crm-analytics.jobs.js. It is not exposed as a direct HTTP endpoint but is typically scheduled to run after each incremental sync to keep the preset snapshots current.

Preset key	Date range
`last30`	`[today - 30 days, today]`
`1m`	`[start of current month, today]`
`3m`	`[today - 3 months, today]`
`6m`	`[today - 6 months, today]`

These preset date boundaries must stay in sync with the quickPresets array in ClientAnalyticsView.vue.

Error Codes

Status	When
`401 Unauthorized`	Missing or invalid admin JWT token
`403 Forbidden`	Valid token but missing `crm-menu` permission
`422 Unprocessable Entity`	Invalid query parameter (e.g., non-string `localeIds`, invalid date format)
`500 Internal Server Error`	Unhandled exception in the analytics computation or sync job

Module Overview — Materialized table structure, ETL, query formulas, and key decisions
UI Screens & Flows — How the frontend consumes this API
CRM API — Other CRM endpoints (prospects, clients, notes, contacts)

UI Elements

Forms & Overlays

Tables

Analytics

Organization Settings

Payment Requests

Pricing & Costs

Additional Services & Charges

CRM Analytics API

Authentication

Endpoints

`GET /prospects/client-analytics`

Query Parameters

Response Shape

Response Fields

Example Requests

`POST /crm/analytics/sync`

Sync algorithm (what happens on each call)

Response

Snapshot Pre-computation

Error Codes

CRM Analytics API ​

Authentication ​

Endpoints ​

GET /prospects/client-analytics ​

Query Parameters ​

Response Shape ​

Response Fields ​

Example Requests ​

POST /crm/analytics/sync ​

Sync algorithm (what happens on each call) ​

Response ​

Snapshot Pre-computation ​

Error Codes ​

Related Documentation ​

CRM Analytics API

Authentication

Endpoints

`GET /prospects/client-analytics`

Query Parameters

Response Shape

Response Fields

Example Requests

`POST /crm/analytics/sync`

Sync algorithm (what happens on each call)

Response

Snapshot Pre-computation

Error Codes

Related Documentation