Re-engagement reporting

None of the reports on this page are built. This is a proposed reporting surface for the segments described in Re-engagement segments, written to decide what is worth building and in what order.

Three of them can be built against data that exists today. The rest depend on an enrolment log that does not yet exist — see What blocks most of this.

Why this page exists

The five segments enrol contacts onto call lists every few hours, and there is currently no measurement of what happens next. Each handler logs its own counters (updated, assigned, skipped_no_hs_id) to CloudWatch and nothing aggregates them; nothing at all tracks whether an enrolled contact was ever dialled, or whether a segment’s priority score predicts anything.

The unit of analysis is the enrolment: one contact, stamped by one segment, on one day. Every report below is a different question asked of that unit.

flowchart LR
  A["Segment enrols\ncall_list_date stamped"] --> B["Rep dials\nCALL engagement"]
  B --> C["Connect\nhs_call_status / disposition"]
  C --> D["Positive outcome\nwarm lead, meeting, deal"]
  A -.->|"never dialled"| E["Leakage"]
  A -.->|"suppression missed"| F["Compliance risk"]

Classification used throughout

Several reports below depend on a per-call outcome classification. The working definition, implemented as a HubSpot custom report formula field:

Category Rule

Category	Rule
`Connected (Status)`	`hs_call_status = COMPLETED`, owner is not the AI fallback owner `86817215`, and the call occurred on or after the contact’s `call_list_date`
`Connected (Disposition)`	`hs_call_disposition` is one of the six positive outcomes, same owner and date conditions
`Blacklisted`	`hs_call_disposition` is one of the suppression outcomes. Deliberately not date-bound or owner-bound — a disqualifying outcome counts whenever and by whomever it was recorded
`Not Connected`	Everything else

Connected (Status)

hs_call_status = COMPLETED, owner is not the AI fallback owner 86817215, and the call occurred on or after the contact’s call_list_date

Connected (Disposition)

hs_call_disposition is one of the six positive outcomes, same owner and date conditions

Blacklisted

hs_call_disposition is one of the suppression outcomes. Deliberately not date-bound or owner-bound — a disqualifying outcome counts whenever and by whomever it was recorded

Not Connected

Everything else

The date condition matters: without it, a call that predates the enrolment is credited to the segment that enrolled the contact afterwards.

Set	Dispositions
Positive	Account created, API Connected Confirmed, Connected, Follow up scheduled, Left live message, Warm Lead (AE Follow Up)
Suppression	Blacklisted, Do not contact, Not a seller, No longer selling/shut down, Marketplace not supported, Gatekeeper

Set

Dispositions

Positive

Account created, API Connected Confirmed, Connected, Follow up scheduled, Left live message, Warm Lead (AE Follow Up)

Suppression

Blacklisted, Do not contact, Not a seller, No longer selling/shut down, Marketplace not supported, Gatekeeper

Both sets are judgement calls, not facts drawn from the portal. Two are worth revisiting: Left live message counts as positive without a live conversation having happened, and Gatekeeper sits in the suppression set although it describes a reached-the-wrong-person outcome rather than a disqualification. The authoritative GUID list is FIVE9_DISPOSITION_TO_HUBSPOT_GUID in lambdas/morpheus_hubspot/utils/constants.py and the blocked_emails CTE in bigquery/v_email_clicked_re_engagement_summary.sql, which currently disagree with each other on membership.

A. Effectiveness

Does re-engagement produce anything?

Report Grain Metrics Answers

Report	Grain	Metrics	Answers
Outcome by enrolment source	contact × `outreach_list_reason`	Share of each outcome category	Which of the five segments is worth keeping
Enrolment-to-outcome funnel	enrolment cohort by week	enrolled → dialled → connected → positive → meeting/deal	Where the drop-off is, per segment
Positive-disposition mix	call	Distribution across the six positive GUIDs	Whether a segment yields warm leads or only "connected" noise
Priority tier performance	`call_priority` 1–4	Connect rate, positive rate	Whether the priority formulas predict anything

Outcome by enrolment source

contact × outreach_list_reason

Share of each outcome category

Which of the five segments is worth keeping

Enrolment-to-outcome funnel

enrolment cohort by week

enrolled → dialled → connected → positive → meeting/deal

Where the drop-off is, per segment

Positive-disposition mix

call

Distribution across the six positive GUIDs

Whether a segment yields warm leads or only "connected" noise

Priority tier performance

call_priority 1–4

Connect rate, positive rate

Whether the priority formulas predict anything

The funnel deliberately mirrors the structure of the existing v_call_campaign_funnel view so the two can be read side by side, but it is keyed on enrolment rather than on an AI call.

Priority tier performance is currently unmeasurable for one segment and skewed for all of them. The lost-deal handler does not write call_priority at all — the line and _score_to_call_priority() are commented out — so its contacts carry whatever priority a previous segment left behind. Any tier analysis silently attributes those rows to the wrong tier.

B. Execution hygiene

Are reps working the list they are given?

Report Grain Metrics Answers

Report	Grain	Metrics	Answers
Time-to-first-call SLA	contact	Days from `call_list_date` to first non-AI call, bucketed same-day / 1–2 / 3–7 / never	How quickly enrolment turns into a dial
Never-called leakage	contact	Count enrolled with zero call engagements, split by rep, segment, enrolment age	How much enrolment volume is pure waste
Per-rep throughput	rep × day	Assigned, dialled, connected, untouched carry-over	Whether the list size matches rep capacity
Stale enrolment aging	contact	Days since `call_list_date` with no call	Which contacts are rotting on the list

Time-to-first-call SLA

contact

Days from call_list_date to first non-AI call, bucketed same-day / 1–2 / 3–7 / never

How quickly enrolment turns into a dial

Never-called leakage

contact

Count enrolled with zero call engagements, split by rep, segment, enrolment age

How much enrolment volume is pure waste

Per-rep throughput

rep × day

Assigned, dialled, connected, untouched carry-over

Whether the list size matches rep capacity

Stale enrolment aging

contact

Days since call_list_date with no call

Which contacts are rotting on the list

Never-called leakage is the report with the largest known number behind it already: contacts with both call_list_date and call_priority populated and no associated CALL engagement of any kind currently number in the tens of thousands.

This report also validates a design assumption in the campaign segment. Its ordering was changed to last_call_engagement_timestamp ASC NULLS FIRST specifically so the queue would drain itself — but as the handler’s own note concedes, that property only holds if reps actually dial. Never-called leakage is how you find out whether it does.

C. Assignment integrity

Report Grain Metrics Answers

Report	Grain	Metrics	Answers
Round-robin fairness	rep	`call_assigned_to` volume against an even split	Whether `select_next_sales_rep_round_robin` skews
Fallback-owner residue	contact	Count still owned by `86817215` after enrolment	How often reassignment silently fails
Assignment vs. actual caller	call	Rows where `call_assigned_to` ≠ the call’s `hubspot_owner_id`	Whether routing is respected in practice

Round-robin fairness

rep

call_assigned_to volume against an even split

Whether select_next_sales_rep_round_robin skews

Fallback-owner residue

contact

Count still owned by 86817215 after enrolment

How often reassignment silently fails

Assignment vs. actual caller

call

Rows where call_assigned_to ≠ the call’s hubspot_owner_id

Whether routing is respected in practice

Fallback-owner residue is a direct check on a known failure path: when the round-robin returns no rep, four of the five handlers log a warning and continue without an owner, leaving the contact enrolled but unrouted. Only the campaign segment aborts the batch instead.

D. Compliance and suppression

Report	Grain	Metrics	Answers
Blacklist leakage	contact	Contacts with a suppression disposition that were subsequently re-enrolled, split by segment	Whether suppression is actually holding
Re-enrolment churn	contact	Enrolment count, distinct segments, total attempts, no progression	Who is being over-contacted
Cross-segment collision	contact × day	Contacts claimed by two or more segments in the same window	How often segments overwrite each other

Report

Grain

Metrics

Answers

Blacklist leakage

contact

Contacts with a suppression disposition that were subsequently re-enrolled, split by segment

Whether suppression is actually holding

Re-enrolment churn

contact

Enrolment count, distinct segments, total attempts, no progression

Who is being over-contacted

Cross-segment collision

contact × day

Contacts claimed by two or more segments in the same window

How often segments overwrite each other

Blacklist leakage is the highest-priority report on this page, because the suppression rules are not uniform across segments and the gap is structural rather than hypothetical. The supplier, no-show, and lost-deal views all exclude contacts carrying a bad disposition. The email-click view suppresses on a blocked-email set, but the handler’s own Python filter only checks for a human call within two days — and the query wrapper filters on deal_id IS NULL alone. A contact marked Do not contact or Not a seller can be re-enrolled if the blocked-email join misses.

Cross-segment collision is not just a reporting curiosity. call_list_date is a single scalar property, so when two segments enrol the same contact on the same day the second write wins and the first segment’s outreach_list_reason and call_priority are overwritten. Attribution in section A is wrong by exactly the collision rate until this is measured.

E. Pipeline health

Report Grain Metrics Answers

Report	Grain	Metrics	Answers
Handler run summary	handler × run	Rows returned, qualifying, updated, assigned, `skipped_no_hs_id`	Whether a segment has silently stopped working
Skip-reason breakdown	handler × reason	Called today, human call within 2 days, no HubSpot id, invalid `contactid`, no active rep	Whether a segment produces volume that is almost entirely filtered out
View yield against cap	BigQuery view	Rows returned against the query `LIMIT`	Whether real volume is being truncated

Handler run summary

handler × run

Rows returned, qualifying, updated, assigned, skipped_no_hs_id

Whether a segment has silently stopped working

Skip-reason breakdown

handler × reason

Called today, human call within 2 days, no HubSpot id, invalid contactid, no active rep

Whether a segment produces volume that is almost entirely filtered out

View yield against cap

BigQuery view

Rows returned against the query LIMIT

Whether real volume is being truncated

The handler run summary addresses a specific blind spot. Every segment returns early — with a warning and no work — when BIGQUERY_CREDENTIALS is empty, when the query raises, or when the view returns nothing. All three look identical from outside: no enrolments. A total credential outage is indistinguishable from a quiet day.

View caps to watch: 5000 for the email-click, supplier, and no-show views; 2000 for lost-deal.

What blocks most of this

Three gaps, in increasing order of how much they block.

outreach_list_reason is not written by every segment: The lost-deal handler has the line commented out, so its enrolments carry no attribution at all. Every report keyed on segment silently drops or misattributes them. Fixing this is one uncommented line.
call_priority is not written by every segment: Same handler, same cause. Priority tier analysis is unreliable until this is restored, because stale values from earlier enrolments are indistinguishable from real ones.
There is no enrolment history: The blocker that matters. call_list_date is a single date property that every segment overwrites. Only the most recent enrolment survives, which means cohort funnels cannot be built, re-enrolment counts cannot be computed, collisions are invisible after the fact, and time-to-first-call can only be measured against the latest stamp rather than the one the rep was actually given.

Two ways to close the third gap:

Option Shape

Option	Shape
HubSpot custom event	An append-only `ReEngagementEnrolled` event per enrolment carrying segment, priority, assigned rep, and source view. Follows the existing custom-event naming pattern for portal `9298797` and lands in the warehouse with the rest of the CRM export.
Append-only table	An enrolment row written through `morpheus_data_layer` at the same time as the HubSpot PATCH. Requires a Prisma migration in the web application first, since the schema is not owned here — see Data.

HubSpot custom event

An append-only ReEngagementEnrolled event per enrolment carrying segment, priority, assigned rep, and source view. Follows the existing custom-event naming pattern for portal 9298797 and lands in the warehouse with the rest of the CRM export.

Append-only table

An enrolment row written through morpheus_data_layer at the same time as the HubSpot PATCH. Requires a Prisma migration in the web application first, since the schema is not owned here — see Data.

The custom-event route needs no schema change outside HubSpot and keeps the record next to the engagement data every report already joins against. The table route gives stronger guarantees and is queryable without waiting for an export interval.

Build spec: enrolment-to-outcome funnel

The first report built from this catalogue (section A, row 2). It lives in HubSpot’s custom report builder, not in BigQuery: every property it reads is written to the portal by the segment handlers, so the report sits directly on the source of truth and needs no export interval.

Formula fields

Four custom formula fields, each formatted as Number and aggregated as Sum. All share two guards — the call was made by a human, and it happened on or after the enrolment date.

Field Meaning

Field	Meaning
`stage_dialled`	A non-AI owner dialled on or after `call_list_date`
`stage_connected`	That call reached someone — `COMPLETED` status or a positive disposition
`stage_positive`	Positive disposition only, no status fallback
`stage_blacklisted`	Carries a suppression disposition. No date or owner guard, by design

stage_dialled

A non-AI owner dialled on or after call_list_date

stage_connected

That call reached someone — COMPLETED status or a positive disposition

stage_positive

Positive disposition only, no status fallback

stage_blacklisted

Carries a suppression disposition. No date or owner guard, by design

stage_dialled:

IF([CALL.hubspot_owner_id] != '86817215' AND DATEDIFF("DAY", [CONTACT.call_list_date], [CALL.hs_timestamp]) >= 0, 1, 0)

stage_connected:

IF([CALL.hubspot_owner_id] != '86817215' AND DATEDIFF("DAY", [CONTACT.call_list_date], [CALL.hs_timestamp]) >= 0 AND ([CALL.hs_call_status] = "COMPLETED" OR [CALL.hs_call_disposition] = 'f240bbac-87c9-4f6e-bf70-924b57d47db7' OR [CALL.hs_call_disposition] = 'cb091f0c-6c11-43e5-8351-b9464bd07080' OR [CALL.hs_call_disposition] = 'a4c4c377-d246-4b32-a13b-75a56a4cd0ff' OR [CALL.hs_call_disposition] = '2a86928b-2094-462e-a033-84ea14d77261' OR [CALL.hs_call_disposition] = '85db30fb-aa93-4f61-b9ba-28e6c181ae6d' OR [CALL.hs_call_disposition] = 'fb19cf15-0308-40cb-8b9d-7e432311eb09'), 1, 0)

stage_positive:

IF([CALL.hubspot_owner_id] != '86817215' AND DATEDIFF("DAY", [CONTACT.call_list_date], [CALL.hs_timestamp]) >= 0 AND ([CALL.hs_call_disposition] = 'f240bbac-87c9-4f6e-bf70-924b57d47db7' OR [CALL.hs_call_disposition] = 'cb091f0c-6c11-43e5-8351-b9464bd07080' OR [CALL.hs_call_disposition] = 'a4c4c377-d246-4b32-a13b-75a56a4cd0ff' OR [CALL.hs_call_disposition] = '2a86928b-2094-462e-a033-84ea14d77261' OR [CALL.hs_call_disposition] = '85db30fb-aa93-4f61-b9ba-28e6c181ae6d' OR [CALL.hs_call_disposition] = 'fb19cf15-0308-40cb-8b9d-7e432311eb09'), 1, 0)

stage_blacklisted:

IF([CALL.hs_call_disposition] = 'b4544009-ea7a-4d3a-af90-84d10f16fb08' OR [CALL.hs_call_disposition] = '4d34d77a-64e1-45bf-8e27-679ef35ce634' OR [CALL.hs_call_disposition] = 'c70b6205-7185-4066-a78a-304205e764ca' OR [CALL.hs_call_disposition] = '9ae74d67-7118-4fa3-a715-4f32aab43bf3' OR [CALL.hs_call_disposition] = '685e7a0d-7335-4d2f-9e6a-fe8122d89bc2' OR [CALL.hs_call_disposition] = '2d471822-8c57-4071-a3b4-a461e4f92cc3', 1, 0)

Write these on a single line. The formula editor has no IN() function — an attempt to use one fails with "Multiple disconnected expressions found where one was expected", because the parser reads the comma-separated GUIDs as separate expressions. Chained OR equality is the only form that validates, and line breaks inside the expression are also rejected.

The date guard is DATEDIFF("DAY", …) >= 0 rather than a plain comparison because call_list_date is a date property and hs_timestamp is a datetime. Truncating both to day granularity is what makes a 3 PM call on the enrolment date evaluate to 0 instead of a negative number.

Chart configuration

Horizontal bar. Two configurations work, and they answer slightly different questions.

Option A — grouped bars, three measures

Setting Value

Setting	Value
Data sources	Contacts + Calls (cross-object)
Y-axis (categories)	`outreach_list_reason`
X-axis (measures)	Sum of `stage_dialled`, `stage_connected`, `stage_positive`
Breakdown	empty
Display	Grouped
Sort	Descending by `stage_dialled`

Data sources

Contacts + Calls (cross-object)

Y-axis (categories)

outreach_list_reason

X-axis (measures)

Sum of stage_dialled, stage_connected, stage_positive

Breakdown

empty

Display

Grouped

Sort

Descending by stage_dialled

The breakdown slot must stay empty: HubSpot does not allow multiple measures and a breakdown simultaneously. Reads as drop-off left to right within each segment.

Option B — stacked bars, outcome as breakdown (preferred)

Setting Value

Setting	Value
Data sources	Contacts + Calls (cross-object)
Y-axis (categories)	`outreach_list_reason`
X-axis (measure)	Count of Contacts, unique
Breakdown	The four-output outcome label formula
Display	Stacked, or 100% stacked

Data sources

Contacts + Calls (cross-object)

Y-axis (categories)

outreach_list_reason

X-axis (measure)

Count of Contacts, unique

Breakdown

The four-output outcome label formula

Display

Stacked, or 100% stacked

Preferred because it counts unique contacts rather than summing per-call values — see the double-count warning below — and because 100% stacked makes segments with very different volumes directly comparable.

Filters

call_list_date is known
call_list_date in the last 90 days
Optionally exclude owner 86817215 at report level rather than repeating it in each formula

Companion report: Enrolled

The Enrolled stage cannot appear in the chart above. A cross-object Contacts + Calls report drops contacts with no associated call — which is precisely the never-dialled population, and the largest bar in the funnel.

Build it separately as a single-object Contacts report: count of unique Contacts, filtered to call_list_date is known, broken down by outreach_list_reason. Place the two side by side on one dashboard. Enrolled minus Dialled, per segment, is the leakage figure that section B asks for.

Known limitations

Summing formula fields counts calls, not contacts: A contact dialled four times contributes 4 to every stage it qualifies for, so funnel widths stop being comparable. Either use Option B, or treat the formulas as filters on a count-of-unique-Contacts metric rather than as summed measures.
No native funnel visualisation: HubSpot’s funnel chart type accepts only lifecycle stages and deal stages, not arbitrary formula fields. A horizontal bar or a table is the available shape.
Snapshot, not cohort: The same overwrite problem described in the blockers section. A contact enrolled twice is attributed entirely to whichever segment wrote call_list_date last, and the earlier enrolment leaves no trace.
Unattributed lost-deal rows: Because the lost-deal handler does not write outreach_list_reason, its enrolments group under a blank category. Worth an explicit "Unknown" label in the report rather than letting them read as a segment.

Build order

Order	Report	Rationale
1	Blacklist leakage	Compliance exposure, buildable today, and the suppression asymmetry is already known
2	Never-called leakage	Largest single quantified waste, buildable today
3	Outcome by enrolment source	Decides which segments to keep; buildable today with the collision caveat
4	—	Close the enrolment-log gap
5	Everything else	Unblocked by step 4

Steps 1–3 need only the outcome classification and the properties that already exist. Nothing past step 3 should be started before the enrolment log lands, because those reports would be built against a property that overwrites itself.