Re-engagement segments

Re-engagement is the mechanism that decides who deserves a call today out of a CRM containing far more contacts than any team can work. Each segment watches for one kind of fresh signal, scores urgency, and stamps the contact onto today’s call list.

Every segment follows the same shape:

flowchart LR
  A["BigQuery view\nmembership + priority_score"] --> B["Scheduled handler\nPython suppression"]
  B --> C["Ownership decision\nkeep owner or round-robin"]
  C --> D["HubSpot contact properties\ncall_list_date, call_priority,\ncall_assigned_to, outreach_list_reason"]

The split is deliberate: set logic and scoring live in SQL (a view can be changed and inspected without a deploy), identity, ownership, and the same-day guard live in Python (they need the operational database).

The five segments

Segment Routing key Lambda Signal

Email click

Hubspot.EmailClickReEngagement

morpheus-hubspot

Clicked a link in a campaign email

Supplier account created

Hubspot.SupplierAccountReEngagement

morpheus-hubspot

Created a Payability account but stalled at "appointment scheduled"

Unattended meeting

Hubspot.UnattendedMeetingReEngagement

morpheus-hubspot

Booked a meeting, did not attend, then went quiet

Lost or stalled deal

Hubspot.LostDealReEngagement

morpheus-hubspot

Deal is outside an active stage and was not hard-disqualified

Amazon seller campaign

Phone.CampaignTargetReEngagement

phone-agent

AI dialer reached an Amazon seller campaign target

The four morpheus-hubspot segments PATCH HubSpot directly. The phone-agent segment publishes a Contact.Update event to CrmSyncQueue instead — same end result, one hop later.

Properties written

HubSpot contact property Value

call_list_date

Today’s date in US Eastern, YYYY-MM-DD. Stamping it is what puts the contact on today’s list.

call_priority

"1"–"4"; 4 is most urgent. Either a direct value from the view or a rounded priority_score.

call_assigned_to

The owner id the call is routed to.

hubspot_owner_id

Written only when the contact was reassigned, so an existing real owner is never overwritten.

outreach_list_reason

Which segment enrolled the contact, so reps and reporting can see the source.

Reason strings (from each function’s utils/constants.py):

Segment outreach_list_reason

Email click

Email click re-engagement

Supplier account

Supplier account re-engagement

Unattended meeting

Unattended meeting re-engagement

Lost deal

Lost deal re-engagement (defined but commented out in the handler)

Amazon seller campaign

Amazon seller campaign re-engagement

Score-to-priority conversion

Three segments return a float priority_score (roughly 0–4) that the handler rounds and clamps:

def _score_to_call_priority(value) -> str:
    score = float(value)                       # unparseable → "1"
    rounded = math.floor(score + 0.5)          # half-up: 2.5 → 3
    return str(max(1, min(4, rounded)))

math.floor(x + 0.5) is used rather than round() because Python’s round() is banker’s rounding, which would send 2.5 down to 2.

Email click

Handler: lambdas/morpheus_hubspot/events/scheduled/email_click_reengagement.py View: bigquery/v_email_clicked_re_engagement_summary.sql

Membership (SQL)

An email_campaign_targets row where:

  • clickedat IS NOT NULL — they clicked

  • unsubscribedat IS NULL — they have not opted out

  • the resolved HubSpot contact has property_deal_id IS NULL — no existing deal

  • the email is not in blocked_emails

  • the query wrapper adds WHERE deal_id IS NULL and LIMIT 5000

One row per email address, keeping the most recent click (QUALIFY ROW_NUMBER() … PARTITION BY ect.email ORDER BY ect.clickedat DESC).

HubSpot contact resolution is by email, not by outreach_app.contacts.hubspotcontactid. That mirror column is frequently null or stale at query time, and keying the joins on it let rows through unlinked — the call, task, and blocked-disposition joins all missed, and Python then resolved the id downstream and stamped contacts that should have been suppressed. The join is an INNER JOIN on email: a target with no matching HubSpot contact cannot be enrolled or checked, so it is excluded.

blocked_emails drops any email that has ever had a call with a blocked disposition, across all HubSpot contacts sharing that address — robust to duplicate or misaligned contact ids.

The blocked set is seven disposition GUIDs. Four are labelled in the view — Wrong Number, Do Not Call, Not Qualified, Not a Seller — and three carry a ⚠ confirm label comment because their portal labels have not been verified. The authoritative list is the blocked_emails CTE in the view; the same seven GUIDs appear in the bad_disposition_calls CTE of the no-show and lost-deal views.

Priority (SQL)

Freshness of the click, in US Eastern days:

Days since click priority

0–1 (today or yesterday)

4

2–6

3

7+

2

The handler reads this column directly — no score conversion — defaulting to "2" when absent.

Suppression (Python)

A row is dropped when the last call engagement was made by a human owner within EMAIL_CLICK_REENGAGEMENT_STALE_DAYS (2 days):

owner = row["engagement_owner_id"]
if not owner or owner == DIALER_FALLBACK_HUBSPOT_OWNER_ID:   # "86817215" = AI
    return False                                            # AI calls don't suppress
return (now - last_call_timestamp) < timedelta(days=2)

An AI call does not suppress a human follow-up; that is the entire point of the segment.

Identity

This is the only segment that needs a database lookup. The view returns the internal contactid (a UUID), so the handler loads the contacts row and uses contact.hubspotContactId. A missing contact or a blank id is counted in skipped_no_hs_id and the row is dropped.

Ownership

Reassign when the current owner is blank, is the AI fallback owner, or is not an active sales_reps row for the subscription. Otherwise the existing owner is left untouched and no call_assigned_to is set by this segment.

Supplier account created

Handler: lambdas/morpheus_hubspot/events/scheduled/supplier_account_reengagement.py View: bigquery/v_supplier_account_created_reeng.sql

Someone signed up for Payability and then stopped moving.

Membership (SQL)

A HubSpot Deal where:

  • created_date > CURRENT_TIMESTAMP() - 90 days (the query wrapper narrows this further to 14 days on account_created_date)

  • deal_stage = 'appointmentscheduled'

  • pipeline = 'default'

  • at least one associated contact vid

  • no call with a bad disposition (bad_disposition_calls = 0)

Deals are deduplicated to their latest exported version first, so stage and lost-reason filters apply to the current deal. The first associated contact vid is used. Ordered by priority_score DESC, last_call_date ASC, LIMIT 5000.

The view returns the HubSpot contact_id directly, so no database lookup is needed.

Priority (SQL)

Never called goes straight to the top; otherwise a disposition weight is scaled by staleness and damped by attempt count:

LEAST(4.0,
  CASE
    WHEN last_call_date IS NULL THEN 4.0
    ELSE disposition_weight
       * LEAST(1.3, 1.0 + days_since_last_call / 30.0)     -- staleness boost, capped
       * (1.0 / (1.0 + 0.25 * total_calls))                -- attempt damping
  END
)

Disposition weights:

Last disposition Weight Reading

Never called

4.0

Untouched — highest value

Left Live Message

3.2

Reached a human

Connected

3.0

Real conversation happened

No Answer

2.6

Neutral

Left Voicemail

2.4

Neutral

Busy

2.2

Neutral

Anything else

1.6

Default

Failed

0.8

Technical dead end

Wrong Number

0.4

Almost certainly unreachable

The three factors encode three intuitions: a good last conversation is worth resuming, the longer it has been the more a call is due (capped at +30%), and each additional attempt reduces the value of one more (four prior calls halve the score).

Suppression (Python)

Dropped when the contact was already called today in US Eastern, by any owner.

Ownership

The row’s hubspot_owner_id is respected when present. When it is blank, a round-robin rep is assigned and both hubspot_owner_id and call_assigned_to are written.

Unattended meeting (no-show)

Handler: lambdas/morpheus_hubspot/events/scheduled/unattended_meeting_reengagement.py View: bigquery/v_unattended_meeting_reeng.sql

They booked a meeting, did not show up, and have since gone cold.

Membership (SQL)

  • bdr_rep is set and non-blank on the deal

  • at least one associated contact vid

  • a MEETING engagement exists (last_meeting_date IS NOT NULL) — you cannot no-show a meeting that never existed

  • the meeting outcome is null or not in ('COMPLETED')

  • no contact activity of any type in the last 30 days

  • no call with an excluded disposition

All deal stages are included, open and closed. One row per contact, keeping the most recent meeting. Ordered priority_score DESC, last_meeting_date ASC, LIMIT 5000.

The view carries two ⚠ markers to confirm against production data: which metadata.* column holds the meeting outcome, and which value means "attended" (currently assumed to be COMPLETED). Both are isolated in the last_meeting CTE and the WHERE clause. If the assumption is wrong, this segment mis-selects.

Priority (SQL)

Base 2.5, scaled by how recently they went cold and by how much history exists:

LEAST(4.0,
  2.5
  * recency_factor        -- NULL last activity → 1.2
                          -- else GREATEST(0.5, 1.3 - (days_since_activity - 30) / 120.0)
  * engagement_factor     -- 1.0 + 0.06 * LEAST(total_activities, 10)   → max 1.6
)

Recency reference points: 30 days → 1.3, 90 days → 0.8, clamped to [0.5, 1.3]. Effective range is roughly 1.3 (cold and barely touched) to 4.0 (recently cold and heavily engaged).

This is the inverse intuition to the supplier segment: here just gone cold is the best time to call, because the relationship is still warm.

Suppression (Python)

Dropped when already called today (US Eastern, any owner).

Ownership

Route to the deal’s BDR owner — but only when that owner is a real active sales rep. Blank, the AI fallback owner, or a non-active rep triggers round-robin reassignment.

Lost or stalled deal

Handler: lambdas/morpheus_hubspot/events/scheduled/reengagement/lost_deal_reengagement.py View: bigquery/v_lost_deal_reeng.sql

Membership (SQL)

All three must hold:

  1. The contact’s latest deal is not in an active pipeline stage — stage is null or outside the active-stage list.

  2. The deal was not closed-lost for a hard-disqualifying reason (not a seller, hard underwriting decline, do-not-contact, and similar).

  3. The contact has no call carrying an excluded disposition.

Deals are deduplicated first. One row per contact: with multiple qualifying deals the most recently modified wins. The query wrapper additionally requires a non-blank phone, orders by priority_score DESC, last_call_date ASC, and caps at LIMIT 2000.

Priority (SQL)

Same shape as the supplier segment — never called → 4.0, otherwise disposition weight × staleness boost (capped at 1.3) × attempt damping — with a slightly different weight table (3.2 for Wrong Number here rather than 0.4, 0.8 for Failed, 1.6 default).

The handler does not currently write call_priority or outreach_list_reason for this segment. Both lines are commented out, along with _score_to_call_priority(), so a lost-deal contact receives only call_list_date (and call_assigned_to / hubspot_owner_id when reassigned). The view still computes and orders by priority_score; the handler ignores it.

Suppression (Python)

Dropped when already called today (US Eastern, any owner).

Ownership

Route to the deal’s owner when they are an active rep; otherwise round-robin.

Amazon seller campaign

Handler: lambdas/phone_agent/events/campaign_reengagement.py View: bigquery/v_amazon_seller_contact_engagement_summary.sql

The one segment that runs in phone-agent, and the one that publishes an event rather than calling HubSpot.

Membership (SQL)

A CampaignTarget row where the last call engagement is null or older than one day. The view also joins the latest CALL engagement, the latest non-completed TASK engagement, and the latest outreach Call row, and exposes the HubSpot lifecycle stage and deal fields. Ordered last_call_engagement_timestamp ASC NULLS FIRST — never-called contacts first, then the longest-neglected — LIMIT BQ_REENGAGEMENT_BATCH_LIMIT (5000).

This previously ordered on last_active_task_id ASC NULLS FIRST, task_created_date ASC NULLS FIRST. That is a static key: a contact’s position never changed no matter how often the segment ran, so whichever contacts sorted last were starved indefinitely. Ordering on last engagement is self-draining instead — once a contact is actually called, the timestamp advances and the row moves to the back.

The property only holds if reps work the list. Contacts enrolled but never dialled stay pinned at the front.

The view computes a priority column (4 / 3 / 2 from outreach-call recency), but the handler ignores it and computes its own.

Priority (Python)

is_ai_owner = row["engagement_owner_id"] == AI_OWNER_ID     # "86817215"
if not is_ai_owner:
    return 1
if last_engagement and (now - last_engagement) < timedelta(days=3):   # REENGAGEMENT_STALE_DAYS
    return 4
return 3
Condition Priority Reading

AI-owned last engagement within 3 days

4

The AI just spoke to them — strike while it is hot

AI-owned last engagement older than 3 days, or none

3

AI touched them at some point

Last engagement owner is not the AI (or unknown)

1

A human already owns this conversation

Ownership

Same three-way test as elsewhere, with one difference: the current owner is read from the local contacts row via the view’s internal contactid, because the view returns a UUID rather than a HubSpot owner id. Owners for the whole batch are resolved in a single get_many_by_ids() query, alongside one query for the active rep pool.

Reps are then reserved in one reserve_sales_rep_round_robin_batch() call for exactly the number of rows needing reassignment. If that returns no rep, the handler logs at ERROR and aborts the whole batch rather than continuing — the only segment that does this. Because the reservation happens before any publish, the abort is now all-or-nothing rather than part-way through.

Output

Publishes a Contact.Update event per row to CrmSyncQueue via publish_events(), keyed MessageGroupId = hubspot_contact_id, with the four properties (plus hubspot_owner_id when reassigned). Messages go out in SendMessageBatch calls of ten; rejected entries are reported by group id rather than raised. The completion line reports published=N/total reassigned=N failed=N.

The whole handler is three phases — one batched read, in-memory planning, one batched publish — so its cost is flat in the row count. It deliberately avoids per-row database and SQS round trips: at ~2500 rows those consumed the entire 120-second Lambda timeout before reaching the end of the batch, and because EventBridge retries an async invocation twice, each retry re-processed the same head rows and never reached the tail.

Shared preconditions

Every segment returns early, with a warning and no work, when:

  • BIGQUERY_CREDENTIALS is empty in the secret.

  • The BigQuery query raises — logged at ERROR, handler returns.

  • The view returns no rows.

  • CRM_SYNC_QUEUE_URL is empty (campaign segment only).

Per-row failures — a failed update_contact_properties() or a failed publish — are logged and counted; the batch continues. In the campaign segment, publish failures surface as the group ids returned by publish_events().

Cadence

All five rules are currently rate(6 hours) or rate(3 hours), values set during development rather than the "every 30 minutes" their descriptions claim. See EventBridge schedules for the live values.

Because every segment recomputes membership and suppression from scratch on each run, cadence affects freshness and cost but not correctness.

Comparison

Segment Priority source Same-day guard Identity Owner override

Email click

View column 4/3/2

Human call within 2 days

DB lookup by UUID

Existing owner kept

Supplier

priority_score → 1–4

Called today

Direct from view

Existing owner kept

No-show

priority_score → 1–4

Called today

Direct from view

Deal BDR owner

Lost deal

not written

Called today

Direct from view

Deal owner

Campaign

Python 4/3/1

none (view: >1 day since call)

DB lookup by UUID

Existing owner kept

The sales-facing summary of the same rules is Daily call lists.