Email Funnel Metrics

Tracks the full email outreach funnel from send to meeting booked. All metrics are computed at query time via SQL aggregations over synced SmartLead data.

Data Sources

Table	Purpose
`jeff_sl_lead_messages`	Email events (SENT, REPLY) with timestamps
`jeff_sl_leads`	Lead records with status and category
`jeff_sl_campaigns`	Campaign metadata (series, sub_series, client)
`jeff_sl_categories`	SmartLead category labels (Positive Reply, Meeting Booked, etc.)
`jeff_sl_email_accounts`	Sender email account info
`jeff_meetings`	Meeting CRM records with stage tracking and `agency_id` (credit owner)
`orange_schema.agencies`	Internal agency table; bridges Smartlead `client_id` to CRM credit via `smartlead_client_id`
`jeff_posthog_events`	Audit page view and click events
`jeff_job_rows`	Links audits to jobs and emails
`jeff_jobs`	Links jobs to SmartLead campaigns

Date Modes

The date mode controls how events are bucketed into time periods and which leads are included in the date range. Everything else (metric columns, joins, filters) stays the same.

Activity Mode (default)

Each event is bucketed by its own timestamp. The date range filters on m.time (the event timestamp).

Use when: "What happened in this time window?" — operational dashboards, daily monitoring.

date_trunc('{granularity}', m.time)::date AS date

WHERE m.time >= :date_from
  AND m.time < :date_to + INTERVAL '1 day'

Cohort Mode

ALL events for a lead are bucketed by that lead's email_1 send date. The date range filters on COALESCE(sent_e1.time, l.created_at) — so it selects leads whose first email was sent in the date range, then pulls in all their events regardless of when those events occurred.

Use when: "For leads we emailed in this window, what were the outcomes?" — campaign effectiveness, conversion analysis.

-- Extra self-join to look up each lead's email_1 send time
LEFT JOIN jeff_sl_lead_messages sent_e1
    ON sent_e1.lead_id = m.lead_id
    AND sent_e1.campaign_id = m.campaign_id
    AND sent_e1.sequence_number = 1
    AND sent_e1.type = 'SENT'

date_trunc('{granularity}', COALESCE(sent_e1.time, l.created_at))::date AS date

WHERE COALESCE(sent_e1.time, l.created_at) >= :date_from
  AND COALESCE(sent_e1.time, l.created_at) < :date_to + INTERVAL '1 day'

What changes between the two modes

Component	Activity	Cohort
Date expression (SELECT + GROUP BY)	`m.time`	`COALESCE(sent_e1.time, l.created_at)`
WHERE date filter	Filters on event timestamp	Filters on email_1 send date
Extra JOIN	None	Self-join to `jeff_sl_lead_messages` to find email_1 timestamp
Metric columns	Same	Same
Other JOINs	Same	Same
Filters	Same	Same

Example

A lead receives email_1 on Jan 3, then replies on Jan 15, then books a meeting on Jan 22. Querying January with weekly granularity:

Event	Activity Mode Bucket	Cohort Mode Bucket
email_1 sent	Week of Jan 1	Week of Jan 1
reply	Week of Jan 15	Week of Jan 1
meeting booked	Week of Jan 22	Week of Jan 1

In activity mode, the events spread across 3 weeks. In cohort mode, all 3 events collapse into the week the lead was first emailed.

Dimensions (group by)

Dimension	Source	Description
`series`	`campaigns.series`	Campaign series number
`sub_series`	`campaigns.sub_series`	Campaign sub-series string
`client_id`	`campaigns.client_id`	SmartLead client
`agency_id`	`agencies.id`	Agency that owns the credit. SL-side metrics resolve via `agencies.smartlead_client_id = campaigns.client_id`; CRM metrics read `jeff_meetings.agency_id` directly. Agencies with NULL `smartlead_client_id` show zero SL-side counts and real CRM counts.
`domain`	`email_accounts.domain_name`	Sending email domain
`provider`	`email_accounts.type`	SMTP, GMAIL, or OUTLOOK
`sequence_number`	`lead_messages.sequence_number`	Position in email sequence
`reply_class`	`lead_messages.reply_class`	Reply taxonomy: genuine / auto_reply / bounce. Non-reply (SENT) rows → `(none)`. Splits the reply metrics; incompatible with category/blocked/CRM/PostHog metrics.
`agency_name`	`agencies.name` (via PostHog join)	Agency name (PostHog-only queries)
`marketplace`	PostHog event properties	Marketplace code, e.g., US

CRM metrics (crm_positive_replies, crm_meetings_booked) only decompose along client_id and agency_id. When grouped by series, sub_series, domain, provider, or sequence_number, the CRM columns are NULL — those dimensions describe the source message, which is not the credit unit.

Filters

Filter	Description
`client_id`	Restrict to specific Smartlead client IDs. For CRM metrics, resolves via `agencies.smartlead_client_id` — the count includes meetings credited to any agency linked to the requested client(s).
`campaign_id`	Restrict to specific campaign IDs
`series`	Restrict to specific series numbers
`sub_series`	Restrict to specific sub-series strings
`agency_id`	Restrict to specific agency IDs. For SL-side metrics, joins `agencies.smartlead_client_id = campaigns.client_id`. For CRM metrics, filters `jeff_meetings.agency_id` directly.
`reply_class`	Restrict to reply classes (genuine, auto_reply, bounce). Filters to matching REPLY rows, so reach/send metrics read 0. Incompatible with category/blocked/CRM/PostHog metrics.
`agency_name`	Agency name filter (PostHog-only queries)

Count Metrics (22)

All SQL snippets below show the metric column logic. The date bucketing (activity vs cohort) and WHERE clause are determined by the date mode described above.

Delivery Metrics

Metric	SQL Logic	Description
`prospects_reached`	`COUNT(DISTINCT campaign_lead_map_id) WHERE type='SENT' AND sequence_number=1`	Lead-campaign participations that received email 1. Same lead in two campaigns counts twice. Denominator for all rate metrics.
`unique_prospects_reached`	`COUNT(DISTINCT lead_id) WHERE type='SENT' AND sequence_number=1`	Distinct humans that received email 1, deduped on `lead_id` only. Same lead in two campaigns counts once. Non-additive across `group_by` dimensions.
`total_emails_sent`	`COUNT(*) WHERE type='SENT'`	Total emails across all sequence steps
`email_1_sent`	`COUNT(*) WHERE type='SENT' AND sequence_number=1`	First email sends
`email_2_sent`	`COUNT(*) WHERE type='SENT' AND sequence_number=2`	Second email sends
`email_3_sent`	`COUNT(*) WHERE type='SENT' AND sequence_number=3`	Third email sends

Reply Metrics

Replies (type='REPLY') are classified by sender-address pattern into a reply_class: genuine (human), auto_reply (automated/helpdesk), or bounce (delivery-failure NDR). Bounces are not replies here — they live in the SMTP bounce family. Split any of these with group_by=reply_class.

Metric	SQL Logic	Description
`genuine_replies`	`COUNT(DISTINCT campaign_lead_map_id) WHERE type='REPLY' AND reply_class='genuine'`	Real human replies (any sentiment, incl. out-of-office from a real mailbox). The engagement signal.
`auto_replies`	`COUNT(DISTINCT campaign_lead_map_id) WHERE type='REPLY' AND reply_class='auto_reply'`	Automated/helpdesk auto-replies, detected by sender address only (noreply-style senders, helpdesk domains). Does NOT catch real-mailbox OOO (that reads as genuine) — see `slcat_ooo_replies`.
`total_replies`	`COUNT(DISTINCT campaign_lead_map_id) WHERE type='REPLY' AND reply_class IN ('genuine','auto_reply')`	Any reply = genuine + auto. Excludes bounce NDRs. `total_replies <= genuine_replies + auto_replies` (a lead-campaign replying in both classes is counted once here, once per slice).
`genuine_replies_email_1`	`… reply_class='genuine' AND sequence_number=1`	Genuine replies to email 1
`genuine_replies_email_2`	`… reply_class='genuine' AND sequence_number=2`	Genuine replies to email 2
`genuine_replies_email_3`	`… reply_class='genuine' AND sequence_number=3`	Genuine replies to email 3

SmartLead Category Metrics (rep-validated)

These come from SmartLead's lead categorization system. A sales rep manually assigns these categories after reviewing lead responses.

Metric	Category Match	Description
`sl_meetings_booked`	`category.name = 'Meeting Booked'`	Prospect confirmed a meeting slot
`total_bounces`	`category.name = 'Sender Originated Bounce'`	Delivery failures
`ooo_replies`	`category.name = 'Out Of Office'`	Auto-replies, not real engagement

sl_positive_replies (and its rates sl_positive_reply_rate, sl_slot_to_booking_rate) were removed in EQU-587. The Positive Reply category count is message-dated and over-counts — a lead is counted in every week it has any message, not once at the reply (e.g. 354 weekly-sum vs 272 true distinct over May–Jun 2026). Pulled until reworked. Use crm_positive_replies for a rep-confirmed positive signal.

CRM Metrics (from Meeting CRM)

CRM metrics attribute meetings by agency ownership (jeff_meetings.agency_id), not by the source campaign's Smartlead client. When the rep publishes a meeting via POST /meetings/{id}/publish, the chosen agency owns the credit — even if it differs from the agency that owns the campaign that produced the lead. Meetings with NULL agency_id (unpublished) are excluded.

When filtered by client_id, the lookup resolves agencies.smartlead_client_id → mtg.agency_id. This makes the count match what the CRM UI shows for that client/week (whereas the older campaign-source attribution would undercount whenever a meeting was credited to a different agency than the campaign owner).

Bucketing in both activity and cohort date modes is by the meeting event date (creation date for crm_positive_replies, first BOOKED+ event date for crm_meetings_booked) — the source email's cohort window does not apply because the credited agency may have nothing to do with that source campaign.

Metric	SQL Logic	Description
`crm_positive_replies`	`COUNT(DISTINCT mtg.id)` joined `jeff_meetings → orange_schema.agencies ON agencies.id = mtg.agency_id`	Meetings credited to an agency in the date window. Bucketed by `mtg.created_at`.
`crm_meetings_booked`	`COUNT(DISTINCT mtg.id)` over the same join, restricted to meetings whose current `jeff_meetings.stage` is in `BOOKED+` (`BOOKED, NO_SHOW, PITCHED, DID_NOT_PITCH, DID_NOT_CLOSE, CLOSED`) and that have a stage event reaching BOOKED+ in the window	Meetings currently in a BOOKED+ stage. Bucketed by the first BOOKED+ transition's date. Meetings reverted back out of BOOKED+ are excluded so the count is a true subset of `crm_positive_replies` for the same scope.

Other

Metric	SQL Logic	Description
`blocked_leads`	`COUNT(DISTINCT lead_id) WHERE lead.status = 'BLOCKED'`	SmartLead prevented sending

PostHog Engagement Metrics

Track audit page interactions. Joined via: jeff_posthog_events → jeff_job_rows (audit_id) → jeff_jobs (job_id) → jeff_sl_leads (campaign_id + email).

Metric	Event Filter	Description
`audit_views`	`event = 'audit_viewed'`	Distinct leads who viewed audit page
`unique_audit_visitors`	`DISTINCT distinct_id WHERE event = 'audit_viewed'`	Unique browser sessions on audit pages
`cta_clicks`	`event = '$autocapture' AND el_text ILIKE '%get started%' OR '%başlayın%'`	CTA button clicks on audit page
`amazon_clicks`	`event = '$autocapture' AND el_text ILIKE '%view on amazon%'`	"View on Amazon" link clicks

Rate Metrics

All rates use prospects_reached as denominator unless noted otherwise. Rates are computed in Python after the SQL query returns (not in SQL).

Caller note: rate metrics depend on the underlying counts being selected. When requesting a rate via smartlead_query_funnel, also pass its numerator and denominator count metrics — missing counts silently produce 0.0.

Metric	Formula	Description
`reply_rate`	`total_replies / prospects_reached`	Raw reply rate over ANY reply (genuine + auto). Deliverability signal — see `genuine_reply_rate` for engagement. `reply_rate ≈ genuine_reply_rate + auto_reply_rate`.
`genuine_reply_rate`	`genuine_replies / prospects_reached`	True engagement rate — human replies only.
`auto_reply_rate`	`auto_replies / prospects_reached`	Share of reached prospects returning an automated auto-reply.
`crm_positive_reply_rate`	`crm_positive_replies / prospects_reached`	Overall CRM-positive conversion from reach. Gated: rejected when grouped/filtered by `agency_id`/`client_id` (see paragraph below); fine globally, over time, and by seller/email or age-at-send dims.
`crm_booking_rate`	`crm_meetings_booked / prospects_reached`	Overall booked-meeting conversion from reach — the end-to-end booking rate (replaces the removed `slcat_booking_rate`). Same `agency_id`/`client_id` gate as `crm_positive_reply_rate`.
`reply_to_crm_positive_rate`	`crm_positive_replies / genuine_replies`	Reply-quality rate — fraction of GENUINE (human) replies that become CRM-positive. Gated like the other CRM rates: rejected when grouped/filtered by `agency_id`/`client_id` (agency-attributed numerator vs campaign-attributed denominator; see paragraph below).
`bounce_rate`	`total_bounces / prospects_reached`	Delivery failure rate
`blocked_rate`	`blocked_leads / prospects_reached`	Rate SmartLead refused to send
`audit_view_rate`	`audit_views / prospects_reached`	Email-to-page conversion
`cta_click_rate`	`cta_clicks / prospects_reached`	End-to-end email to CTA click
`audit_cta_rate`	`cta_clicks / audit_views`	On-page CTA conversion rate

crm_positive_reply_rate and crm_booking_rate put an agency-credited CRM numerator over a campaign-side reach denominator. CRM counts attribute by agency credit while prospects_reached attributes by campaign source — a meeting credited to agency X may have come from agency Y's campaign, so X's denominator doesn't include the email that produced it, making the ratio per-agency-meaningless. Rather than drop the rates, the MCP hard-rejects grouping or filtering them by agency_id or client_id (a structured error) while allowing every coherent slice — globally, over time, and by seller/email or age-at-send dimensions. Older/all-history windows are mildly inflated by legacy manual-entry meetings that carry no reach, so they read best on recent windows. For a per-wave marginal (campaign-scoped numerator, so it is per-client-safe) see open_positive_rate / open_meeting_rate.

reply_to_crm_positive_rate inherits the same numerator-vs-denominator attribution split (agency-attributed CRM positives over campaign-attributed genuine replies), so it carries the same agency_id/client_id gate as crm_positive_reply_rate and is tagged meeting_crm — all three CRM-numerator rates are treated identically.

Rate computation code

def safe_div(num, den):
    return round(num / den, 4) if den else 0.0

reached = row["prospects_reached"]

row["reply_rate"]              = safe_div(row["total_replies"], reached)
row["genuine_reply_rate"]      = safe_div(row["genuine_replies"], reached)
row["auto_reply_rate"]         = safe_div(row["auto_replies"], reached)
row["crm_positive_reply_rate"] = safe_div(row["crm_positive_replies"], reached)
row["crm_booking_rate"]        = safe_div(row["crm_meetings_booked"], reached)
row["reply_to_crm_positive_rate"] = safe_div(row["crm_positive_replies"], row["genuine_replies"])
row["bounce_rate"]             = safe_div(row["total_bounces"], reached)
row["blocked_rate"]            = safe_div(row["blocked_leads"], reached)
row["audit_view_rate"]         = safe_div(row["audit_views"], reached)
row["cta_click_rate"]          = safe_div(row["cta_clicks"], reached)
row["audit_cta_rate"]          = safe_div(row["cta_clicks"], row["audit_views"])

Full SQL Query

The complete query with all metric columns. The date expression and WHERE clause shown here are for activity mode — see Date Modes above for how these change in cohort mode.

Full funnel query (activity mode)

SELECT
    date_trunc('{granularity}', m.time)::date AS date,

    -- Delivery
    COUNT(DISTINCT CASE WHEN m.type = 'SENT' AND m.sequence_number = 1
          THEN l.id END) AS prospects_reached,
    COUNT(*) FILTER (WHERE m.type = 'SENT') AS total_emails_sent,
    COUNT(*) FILTER (WHERE m.type = 'SENT' AND m.sequence_number = 1) AS email_1_sent,
    COUNT(*) FILTER (WHERE m.type = 'SENT' AND m.sequence_number = 2) AS email_2_sent,
    COUNT(*) FILTER (WHERE m.type = 'SENT' AND m.sequence_number = 3) AS email_3_sent,

    -- Replies (split by reply_class; bounces excluded — they are not replies)
    COUNT(DISTINCT CASE WHEN m.type = 'REPLY' AND m.reply_class = 'genuine'
          THEN m.campaign_lead_map_id END) AS genuine_replies,
    COUNT(DISTINCT CASE WHEN m.type = 'REPLY' AND m.reply_class = 'auto_reply'
          THEN m.campaign_lead_map_id END) AS auto_replies,
    COUNT(DISTINCT CASE WHEN m.type = 'REPLY' AND m.reply_class IN ('genuine','auto_reply')
          THEN m.campaign_lead_map_id END) AS total_replies,
    COUNT(DISTINCT CASE WHEN m.type = 'REPLY' AND m.reply_class = 'genuine' AND m.sequence_number = 1
          THEN m.campaign_lead_map_id END) AS genuine_replies_email_1,
    COUNT(DISTINCT CASE WHEN m.type = 'REPLY' AND m.reply_class = 'genuine' AND m.sequence_number = 2
          THEN m.campaign_lead_map_id END) AS genuine_replies_email_2,
    COUNT(DISTINCT CASE WHEN m.type = 'REPLY' AND m.reply_class = 'genuine' AND m.sequence_number = 3
          THEN m.campaign_lead_map_id END) AS genuine_replies_email_3,

    -- SmartLead categories (rep-validated)
    COUNT(DISTINCT CASE WHEN LOWER(cat.name) = LOWER('Meeting Booked')
          THEN l.id END) AS sl_meetings_booked,
    COUNT(DISTINCT CASE WHEN LOWER(cat.name) = LOWER('Sender Originated Bounce')
          THEN l.id END) AS total_bounces,
    COUNT(DISTINCT CASE WHEN LOWER(cat.name) = LOWER('Out Of Office')
          THEN l.id END) AS ooo_replies,

    -- (CRM metrics are computed in a separate query — see "CRM CTE" below)

    -- Blocked
    COUNT(DISTINCT CASE WHEN l.status = 'BLOCKED' THEN l.id END) AS blocked_leads,

    -- PostHog engagement
    COUNT(DISTINCT CASE WHEN ph.event = 'audit_viewed' THEN l.id END) AS audit_views,
    COUNT(DISTINCT CASE WHEN ph.event = 'audit_viewed'
          THEN ph.distinct_id END) AS unique_audit_visitors,
    COUNT(DISTINCT CASE WHEN ph.event = '$autocapture' AND (
          ph.properties->>'$el_text' ILIKE '%get started%' OR
          ph.properties->>'$el_text' ILIKE '%başlayın%')
          THEN l.id END) AS cta_clicks,
    COUNT(DISTINCT CASE WHEN ph.event = '$autocapture' AND
          ph.properties->>'$el_text' ILIKE '%view on amazon%'
          THEN l.id END) AS amazon_clicks

FROM {schema}.jeff_sl_lead_messages m
JOIN {schema}.jeff_sl_leads l
    ON l.id = m.lead_id AND l.campaign_id = m.campaign_id
JOIN {schema}.jeff_sl_campaigns c
    ON c.id = m.campaign_id
LEFT JOIN {schema}.jeff_sl_categories cat
    ON cat.id = l.lead_category_id
LEFT JOIN {schema}.jeff_job_rows ph_jr
    ON ph_jr.prospect_email = l.email
LEFT JOIN {schema}.jeff_jobs ph_j
    ON ph_j.id = ph_jr.job_id AND ph_j.sl_campaign_id = c.id
LEFT JOIN {schema}.jeff_posthog_events ph
    ON ph.audit_id = ph_jr.audit_id AND ph_j.id IS NOT NULL
WHERE m.time >= :date_from
    AND m.time < :date_to + INTERVAL '1 day'
    AND c.parent_campaign_id IS NULL  -- exclude sub-campaigns
GROUP BY 1
ORDER BY 1

CRM CTE (agency-attributed, runs in a separate SQL pass)

CRM metrics are computed in their own queries because (a) attribution is by jeff_meetings.agency_id, not by the source campaign's client_id, and (b) bucketing is by the meeting event date in both activity and cohort modes. Results are merged into the main funnel rows by (date, client_id|agency_id) keys after both queries return.

-- crm_positive_replies (bucketed by mtg.created_at)
SELECT
    date_trunc('{granularity}', mtg.created_at)::date AS date,
    a.smartlead_client_id AS client_id,        -- present when group_by includes client_id
    a.id AS agency_id,                         -- present when group_by includes agency_id
    COUNT(DISTINCT mtg.id) AS crm_positive_replies
FROM {schema}.jeff_meetings mtg
JOIN {schema}.agencies a ON a.id = mtg.agency_id     -- excludes NULL agency_id
WHERE mtg.created_at >= :date_from
  AND mtg.created_at <  :date_to + INTERVAL '1 day'
  -- client_id filter resolves through the agency mapping:
  AND a.smartlead_client_id = ANY(:filter_client_ids)   -- when client_id filter set
  AND a.id                  = ANY(:filter_agency_ids)   -- when agency_id filter set
GROUP BY 1, 2, 3;

-- crm_meetings_booked (bucketed by first stage event reaching BOOKED+,
-- restricted to meetings whose current stage is still BOOKED+)
WITH first_booked AS (
    SELECT DISTINCT ON (meeting_id)
           meeting_id, created_at AS booked_at
    FROM {schema}.jeff_meeting_stage_events
    WHERE new_stage IN ('BOOKED','NO_SHOW','PITCHED','DID_NOT_PITCH','DID_NOT_CLOSE','CLOSED')
    ORDER BY meeting_id, created_at
)
SELECT
    date_trunc('{granularity}', fb.booked_at)::date AS date,
    a.smartlead_client_id AS client_id,
    a.id AS agency_id,
    COUNT(DISTINCT mtg.id) AS crm_meetings_booked
FROM first_booked fb
JOIN {schema}.jeff_meetings mtg ON mtg.id = fb.meeting_id
JOIN {schema}.agencies     a   ON a.id   = mtg.agency_id
WHERE mtg.stage IN ('BOOKED','NO_SHOW','PITCHED','DID_NOT_PITCH','DID_NOT_CLOSE','CLOSED')
  AND fb.booked_at >= :date_from
  AND fb.booked_at <  :date_to + INTERVAL '1 day'
GROUP BY 1, 2, 3;

If group_by contains a CRM-incompatible dimension (series, sub_series, domain, provider, sequence_number), the CRM CTE is skipped entirely and the CRM columns remain NULL on every row.

PostHog-only query (no SmartLead join)

When only PostHog metrics are requested (audit_views, unique_audit_visitors, cta_clicks, amazon_clicks, and their rates), a simpler query runs directly against PostHog events without the SmartLead join chain:

SELECT
    date_trunc('{granularity}', pe.timestamp)::date AS date,
    COUNT(*) FILTER (WHERE pe.event = 'audit_viewed') AS audit_views,
    COUNT(DISTINCT pe.distinct_id) FILTER (WHERE pe.event = 'audit_viewed')
        AS unique_audit_visitors,
    COUNT(*) FILTER (WHERE pe.event = '$autocapture' AND (
        pe.properties->>'$el_text' ILIKE '%get started%' OR
        pe.properties->>'$el_text' ILIKE '%başlayın%'))
        AS cta_clicks,
    COUNT(*) FILTER (WHERE pe.event = '$autocapture' AND
        pe.properties->>'$el_text' ILIKE '%view on amazon%')
        AS amazon_clicks
FROM {schema}.jeff_posthog_events pe
JOIN {schema}.jeff_audits ja ON ja.id = pe.audit_id
WHERE pe.timestamp >= :date_from
    AND pe.timestamp < :date_to + INTERVAL '1 day'
GROUP BY 1
ORDER BY 1

This shows ALL audit page activity regardless of campaign linkage.

Data Sources​

Date Modes​

Activity Mode (default)​

Cohort Mode​

What changes between the two modes​

Example​

Dimensions (group by)​

Filters​

Count Metrics (22)​

Delivery Metrics​

Reply Metrics​

SmartLead Category Metrics (rep-validated)​

CRM Metrics (from Meeting CRM)​

Other​

PostHog Engagement Metrics​

Rate Metrics​

Full SQL Query​