Report and activity endpoints

Volume fields: unique_calls, total_duration_m, billed_minutes, calls_picked_up.

Disposition fields: ivr, gatekeeper, dial_failed, qualified_lead, call_back, not_qualified, not_a_seller, not_interested, wrong_num, voice_mail, ai_screening, busy, hangup_early, ai_issue, do_not_call, phone_provider_unavailable, dial_no_answer, user_declined, invalid_destination, dial_busy, user_hangup, error_retell, tel_provider_perm_denied, no_disposition.

totals is computed by the database, so the UI never re-sums the page.

source and target are indices into nodes. Node colours come from SANKEY_NODE_COLOR_MAP in lib/constants.ts, so a disposition is the same colour in every chart.

Per date and AI agent: ai_agent_id, ai_agent_name, total_calls, total_analysis, avg_score, total_score, correct_dispositions, correct_disposition_pct, avg_qa_ai_agent_score, total_qa_ai_agent_score, total_critical_issues, hangup_calls, hangup_disposition_rate_pct.

Per-number pickup rate. The query joins "PhoneNumbers" — quoted, because that table keeps the PascalCase default name.

Recipient status distribution over a date range, from vm_email_campaign_target_summary. Each day is the America/New_York date of sentAt. total_opened and total_clicked count targets that are not bounced. The dashboard open and click rates divide those counts by delivered mail (total_sent minus total_bounced). Parameters:: dateFrom, dateTo

Auth via resolveSubscription(). These views have no tenant column, so results are not filtered by subscriptionId.

A deal counts only when created_date is on or after the contact’s first AI call in the range. Deal won is stage closedwon (Active (Won)) or 1045206663 (Closed Won). lost_reasons counts people on a lost best deal, grouped by HubSpot lost reason. Funded is first_purchase_date on or after that first AI call. deal_amount_total is HubSpot deal amount on won deals, not funded dollars. v_deals_latest must exist before deal columns return data.

Each warm_leads row has engagement_since (first Warm Lead call in the range, or first AI call for won-only contacts), human_calls_since (HubSpot calls by an owner other than 86817215), and emails_since (from hubspot4.v_emails_latest). Both counts run from engagement_since to now. v_emails_latest must exist in BigQuery, otherwise the whole report request fails.

Auth via resolveSubscription(). The view has no tenant column, so results are not filtered by subscriptionId.

by_segment and funnel are summed entry counts in lib/services/reengagement-daily-call-report-service.ts. contact_funnel counts each HubSpot contact once for the same range. Dates are bound as STRING and compared with DATE(@dateFrom) / DATE(@dateTo).

daily and by_segment include per-disposition call counts from the view (calls_connected, calls_account_created, calls_api_connected, calls_hang_up, and the rest of the mapped HubSpot IDs). The segment chart stacks those counts. connected_contacts uses the view’s conversation-disposition IN list. positive_calls_contacts is Warm Lead, Follow up scheduled, API Connected Confirmed, or Account Created. uncalled_contacts is contacts on that list date with no human call on the list date or the next day. never_called_contacts is contacts on that row with no human call on or after their first list date. The view still supplies the date-window counts; the query adds never_called_contacts.

Contact-level rows for list dates in the requested range. dialled is that row’s own window (human call on the list date or the next day; owner 86817215 excluded). ever_dialled is true when any of the contact’s list dates, on or after their first list date, has such a call. dialStatus filters on ever_dialled. Parameters:: dateFrom, dateTo, dialStatus (all, dialled, not_dialled), segment, search, page, pageSize.

GET /api/reports/reengagement-contacts/history?hubspotContactId= returns up to 100 list appearances for one HubSpot contact, newest first. Each history row’s dialled flag is that date’s own window.

Auth via resolveSubscription(). The view has no tenant column, so results are not filtered by subscriptionId. The query returns one aggregated row of COUNTIF buckets — it does not select contact fields.

Status matching is LOWER(TRIM(…​)). API needed counts needed and required. Remainder buckets are other (non-null unmatched) and null. with_first_purchase counts contacts whose supplier has a non-null first_purchase_date. attributed_wins counts contacts whose first_purchase_date is on or after first_listdate.

The unified feed: Activity rows merged with Call rows.

Normalisation is done by lib/utils/map-activity-feed.ts; campaign names are resolved in bulk by lib/utils/resolve-activity-campaign-names.ts rather than per row. Hook: useActivity(params?).