<summary>Query outreach prospects for campaign progress. Two modes:
mode="search" (default): individual prospect rows.
mode="stats": aggregate counts, with optional group_by for analytics.
Columns: id, person_id, display_name, email, mail_host, linkedin_url,
linkedin_provider_id, title, headline, company, location, email_stage, linkedin_stage,
priority (True = this prospect's LinkedIn sends jump the queue), current_node_id,
entered_at, enact_blocked_at, enact_blocked_reason, data (JSONB), agent_id, created_at,
updated_at, linkedin_messages_sent, last_outbound_at.
There's no rollup `stage` column; filter on `email_stage` or `linkedin_stage`
to isolate a funnel (both non-null — not-started is `''`; gate absence with
`= ''`). JSONB queries: `data->>'title' ILIKE '%founder%'`. For
monitored companies, call query_monitored_companies.
`id` is the prospect's `outreach_prospects` primary key — returned on every
search row and queryable in `where_clause`. When an event hands you a roster
of prospect primary keys (a flow node's `on_enter` enactment carries them in
`event['prospect_ids']`), resolve the whole roster with `id IN (...)` and `order_by="id"`,
so it queues in upload order.
`person_id` is the canonical-person FK — the `query_people` entity this outreach row
resolves to. Each row also carries that person's canonical identity line — `title`,
`headline`, `company`, `location` — joined from that `query_people` row: the deduped,
typed, filterable values (the prospect's own `data` blob may carry a separate
per-enrollment copy under `data->>'title'`/`'company'`). For the rest of the profile —
summary, work history, education — call query_people with `id = <person_id>`.
`person_id = <id from query_people>` lists every prospect row tracking that person. Use it
to cross between the two: query_people returns one deduped row per real person,
query_prospects returns the per-owner tracking rows under it. These are your own prospects
only — a teammate's rows for the same person need `as_teammate`.
Each search row also carries `lead_bucket`, the derived lead-funnel bucket
(one of not_started/queued/contacted/engaged/replied/interested/meeting_booked/
not_interested/finished/not_reached/already_connected) — a read-only returned field, not a
queryable column. To filter by funnel position, gate on
`email_stage`/`linkedin_stage`, never on `lead_bucket`. Each row also carries a read-only
`removal_reason`: for a removed prospect (a 'skipped' stage), why they were taken out of
the campaign; '' otherwise or when no reason was recorded.
`current_node_id` / `entered_at` are the prospect's Campaign Flow position — the
sequence-DAG node they currently rest on and when they arrived. `current_node_id` is
NULL for a prospect on the start node / not yet routed onto any node. The node id is
opaque; resolve it to a label and kind — and read the per-node occupancy counts — with
`get_campaign_flow`. Filter on it to list one node's occupants
(`current_node_id = '<id from get_campaign_flow>'`, or `current_node_id IS NULL` for the
start node) or to find stalled prospects (`entered_at <= '<7 days ago>'`).
`enact_blocked_at` / `enact_blocked_reason` answer "why did this prospect stop?" —
non-null means their current node's step could not be performed and won't retry
until unblocked (`enact_blocked_at IS NOT NULL` lists a campaign's stuck
prospects). Fix the cause, then clear the block with `retry_blocked_enactment`.
`mail_host` is the email provider hosting the person's address (Google Workspace,
Microsoft 365, …), read from their domain's MX record when the address was saved:
`google`, `microsoft`, `gateway` (a security gateway such as Mimecast or Proofpoint
receives the mail first, which hides the provider behind it), `other` (any other host),
`unknown` (no mail record, or the domain's DNS is broken), or `''` when there's no email
or no lookup of it has succeeded yet. To filter or route on who hosts a prospect's mail
("Google-hosted only"), read this column, since a domain name alone doesn't show it.
`linkedin_messages_sent` / `last_outbound_at` are the count and latest timestamp
of outbound LinkedIn messages sent to the prospect (hand-sent ones included; a
connection-request note isn't a message, even though LinkedIn replays it into the
chat when they accept).
**For any follow-up check, gate on `linkedin_messages_sent`, never on
`linkedin_stage` alone.** `linkedin_stage = 'messaged'` is a flat bucket — it can't
tell a prospect who's had only the first message from one who's already had several,
so selecting on the stage (even with a `last_outbound_at` cutoff) re-sends a
follow-up that already went out. Gate the exact step: `linkedin_stage = 'messaged'
AND linkedin_messages_sent = 1 AND last_outbound_at <= '<5 days ago>'` is the first
follow-up; `= 2` with a 7-day cutoff is the second. Both fields reflect synced
LinkedIn history, which usually syncs within minutes: reliable for day-grained
follow-up checks.
**Stage meanings** — translate these for the user (say "still being looked up", not
"resolving"). `email_stage` and `linkedin_stage` share one vocabulary; a rung marked
"(LinkedIn)" never appears on email. `not_started` (blank stage, never enrolled) is
reported separately in the stats return, not a stored stage.
- resolving (LinkedIn) — looking up their profile before anything can send; NOT yet contacted.
- warming (LinkedIn) — a warm-up flow is engaging their content (comments, reactions) to build
familiarity, before the first outreach or between steps; not a send in itself.
- graduated (LinkedIn) — the warm-up finished; what it triggers next (a connection request, an
intro message, or nothing) depends on the flow.
- pending — scheduled to go out (show the scheduled time if present).
- sent — the connection request (LinkedIn) or the email has been delivered.
- connected (LinkedIn) — they accepted the connection request.
- messaged (LinkedIn) — a follow-up message went out after they connected.
- replied — they replied (a neutral/unclassified reply). Either channel.
- interested — they replied with clear interest (a positive reply). Ranks above replied. Either channel.
- meeting_booked — they agreed to or booked a meeting in their reply. The top rung. Either channel.
- not_interested — they replied with an explicit decline. Ranks above `replied`, below the
positives — still a replier: include it in any "everyone who replied" count. Either channel.
- skipped — deliberately not pursued: a manual/agent cancel, or a cross-agent duplicate. Either channel.
- unreachable (LinkedIn) — a permanent failure: invalid/locked/not-found profile, or they don't accept invites.
- already_connected (LinkedIn) — already a 1st-degree connection, so the request was a no-op; not
pursued by default, but you can message them directly.
- withdrawn (LinkedIn) — the connection request sat unaccepted past the withdrawal window and was auto-retracted.
**Acceptance** (LinkedIn connection-request accept rate) does NOT come from the `connected`
count — accepters advance to `messaged`/`replied`, so `connected` undercounts. Read it from
`get_agent_details` (`tracking_summary.funnel` carries per-channel funnels with the connection
`acceptance_rate`), also surfaced on the per-agent **LinkedIn CR acceptance** line in your Active Agents context; it matures over ~a week
(an accept lands up to ~8h after the click, requests sit `sent` for days), so a campaign's first week
reads low. **Who/when** — who accepted or replied and when, a week-over-week trend — comes from
`list_prospect_events`, not stats: stats are current positions, the event feed is the history.
**Analytics** — `mode="stats"` with `group_by` a JSONB `data` key (the per-enrollment
`data->>'...'`, not the canonical top-level identity columns) surfaces patterns:
`group_by="title"` (which job titles reply/engage), `"company"`, `"source"` (which lead sources
perform). Each group carries the same per-channel stage maps; everyone who replied is the sum
of every reply rung — `replied`, `interested`, `meeting_booked`, `not_interested` (the
classified rungs rank above a neutral reply) — the `replied` count alone undercounts. Report
per-channel — don't mix the email and LinkedIn maps.
Acceptance has no per-group breakdown — it reads only off the per-agent CR line above. Surface a
pattern proactively when one emerges (e.g. "seed-stage companies reply at ~3× the Series-B rate"),
not only when the user asks.
**Dedup before acting** — to check whether you've already tracked or acted on someone, query by
whichever identifier you have (omit `agent_id` to look across all your agents):
`where_clause="email='...' OR linkedin_url='...' OR linkedin_provider_id='...'"`.</summary>
<returns>
<description>In search mode, {count, truncated, items array}.
In stats mode, {total, by_email_stage: {...}, by_linkedin_stage: {...},
not_started, groups?: {<group>: {total, by_email_stage, by_linkedin_stage}}}.
`not_started` is the count of prospects with neither channel stage
populated (never enrolled) — in `total` but in neither stage map.</description>
</returns>