Time to Apply by Source Group

Definition

The number of days between a person’s first recorded engagement touch and their application date, grouped by first-touch source channel. Shows which channels produce fast converters and which require a longer nurture window.

Summary

Conversion speed tracks closely with channel intent level. Paid search converts fastest (median 20 days) — these are bottom-funnel searchers actively looking for the institution. Organic search and direct/unknown follow at ~38–39 days. Paid display is the slowest reliable group at 57 days, consistent with its role as an awareness channel. The mean/median gap is largest for paid/carnegie (88.7 avg vs. 29 median), indicating a long right tail of slow converters pulling the average up significantly.

Source Group	Applicants	Avg Days	Median Days	Min	Max
paid social	9	26.4	13	1	60
paid search	169	42.9	20	1	581
ai referral	28	36.1	20.5	1	245
paid / carnegie	141	88.7	29	1	1452
organic social	126	84.1	37	1	872
email	182	106.7	37	1	964
organic search	7,330	74.6	38	1	1581
direct / unknown	9,403	93.3	39	1	1732
paid display	172	105.1	57	1	1300
other	98	58.9	18	1	1061
vendor / purchased list	3	25.0	32	1	42

Key Insight

Conversion speed reflects channel intent. Paid search applicants convert in a median 20 days — they arrived with intent already formed. Organic search and direct/unknown follow at ~38–39 days. Paid display is the slowest at 57 days, validating its role as an awareness channel. The large mean/median gaps in paid/carnegie and email suggest both channels produce a long tail of slow-converting prospects who may need more deliberate nurture to close.

Why This Is Important

First-touch application rate tells you which channels attract applicants. Time to apply tells you how quickly those channels convert. A channel can have a high application rate but a long conversion window — or a low rate but fast conversion among the subset who do apply. These are meaningfully different channel behaviors that inform campaign timing and nurture strategy.

Specifically this helps:

Set realistic expectations for how long after first contact a channel should be evaluated
Identify channels where nurture investment is most likely to accelerate conversion
Validate the awareness vs. conversion channel framing — paid channels should show longer windows than high-intent channels like organic search

Methodology

Source group classification uses the same CASE logic as the first-touch application rate page. Days to apply is measured from first engagement touch to application date, limited to applicants where the application came after the first touch.

Average and median are both reported — the gap between them indicates a long right tail (a subset taking much longer to convert than typical).

Query

WITH first_touch AS (
  SELECT
    person_id,
    touched_at AS first_touch_at,
    utm_source,
    utm_medium,
    ROW_NUMBER() OVER (
      PARTITION BY person_id
      ORDER BY touched_at ASC
    ) AS rn
  FROM person_touches
  WHERE touch_category = 'engagement'
),

source_groups AS (
  SELECT
    person_id,
    first_touch_at,
    CASE
      WHEN lower(utm_source) IN ('google', 'bing', 'yahoo', 'yahoo!', 'duckduckgo', 'ecosia', 'yandex', '360.cn', 'baidu')
        AND lower(utm_medium) IN ('organic', 'organic_search', 'search')
        THEN 'organic search'
      WHEN lower(utm_source) IN ('google', 'bing')
        AND lower(utm_medium) IN ('cpc', 'paid', 'paid_search', 'ppc')
        THEN 'paid search'
      WHEN lower(utm_source) IN ('googlead', 'mediately')
        OR lower(utm_medium) IN ('mediately', 'display', 'lightbox', 'banner')
        THEN 'paid display'
      WHEN lower(utm_source) IN ('carnegie-digital', 'carnegie', 'carnegie_digital')
        AND lower(utm_medium) IN ('ppc', 'paid-social', 'paid_social', 'cpc', 'display', 'lightbox', 'discovery', 'custom-content', 'video')
        THEN 'paid / carnegie'
      WHEN lower(utm_source) IN ('facebook', 'instagram', 'linkedin', 'youtube', 'tiktok', 'snapchat', 'twitter', 'reddit', 'meta')
        AND lower(utm_medium) IN ('paid', 'paid-social', 'paid_social', 'cpc')
        THEN 'paid social'
      WHEN lower(utm_source) IN ('facebook', 'instagram', 'linkedin', 'youtube', 'tiktok', 'snapchat', 'twitter', 'reddit')
        OR lower(utm_medium) IN ('social')
        THEN 'organic social'
      WHEN lower(utm_source) IN ('slate', 'gmail', 'miami-matters', 'mass_mailer', 'inbound-email', 'notifier', 'email', 'newsletter')
        OR lower(utm_medium) IN ('email')
        THEN 'email'
      WHEN lower(utm_source) IN ('nrccua', 'encoura', 'collegeboard', 'cappex', 'niche', 'niche.com', 'qs', 'usnews_studentconnect', 'hotcoursesinternational', 'idp', 'ziprecruiter')
        THEN 'vendor / purchased list'
      WHEN lower(utm_source) IN ('chatgpt.com', 'perplexity', 'claude', 'gemini', 'copilot.com')
        THEN 'ai referral'
      WHEN utm_source IS NULL OR utm_source = '' OR utm_source = '(none)'
        THEN 'direct / unknown'
      ELSE 'other'
    END AS source_group
  FROM first_touch
  WHERE rn = 1
),

applied AS (
  SELECT
    person_id,
    MIN(tag_date) AS applied_at
  FROM person_tags
  WHERE tag_type = 'applied'
    AND tag_value = 'Yes'
  GROUP BY person_id
)

SELECT
  sg.source_group,
  COUNT(*) AS applicants,
  ROUND(AVG(datediff('day', CAST(sg.first_touch_at AS DATE), CAST(a.applied_at AS DATE))), 1) AS avg_days_to_apply,
  ROUND(MEDIAN(datediff('day', CAST(sg.first_touch_at AS DATE), CAST(a.applied_at AS DATE))), 1) AS median_days_to_apply,
  MIN(datediff('day', CAST(sg.first_touch_at AS DATE), CAST(a.applied_at AS DATE))) AS min_days,
  MAX(datediff('day', CAST(sg.first_touch_at AS DATE), CAST(a.applied_at AS DATE))) AS max_days
FROM source_groups sg
JOIN applied a ON sg.person_id = a.person_id
WHERE a.applied_at >= sg.first_touch_at
GROUP BY sg.source_group
ORDER BY median_days_to_apply ASC;