Track trial-to-paid conversion by acquisition source

Why this matters

Most acquisition dashboards show traffic. The metric that actually matters is conversion-by-source. Reddit might be 1% of your traffic but 8% of your paid signups; LinkedIn might be 30% of traffic and 2% of paid. Spending $5k on more LinkedIn ads is then provably wrong.

Mixpanel has this view but you set it up once per source and re-build it every quarter. GA4 has UTMs but no funnel UI. Most teams have a Notion doc with stale numbers and a Slack message asking “wait, where do our actual paying customers come from?”

Agentry’s HogQL groups your funnel by acquisition_source in one query and publishes the table as a public dashboard you can stare at every Monday.

What you get

• A table of acquisition sources × trial-start × paid-conversion × conversion rate
• The channel with the best conversion rate (not just the most volume)
• A public dashboard URL — embeddable in the weekly metrics review

Walk through it

agentry_list_event_names {
  project_id: "default",
  matching: "trial|signup|subscription|paid"
}

{
  "events": [
    { "name": "trial_started", "count": 4280 },
    { "name": "signup_completed", "count": 4310 },
    { "name": "subscription_started", "count": 412 },
    { "name": "subscription_canceled", "count": 31 }
  ]
}

agentry_analytics_query {
  project_id: "default",
  hogql: "WITH trials AS (SELECT distinct_id, properties.acquisition_source AS source, min(timestamp) AS trial_ts FROM events WHERE event = 'trial_started' AND timestamp >= now() - INTERVAL 90 DAY GROUP BY distinct_id, source), paid AS (SELECT distinct_id, min(timestamp) AS paid_ts FROM events WHERE event = 'subscription_started' AND timestamp >= now() - INTERVAL 90 DAY GROUP BY distinct_id) SELECT t.source AS source, count(DISTINCT t.distinct_id) AS trials_count, count(DISTINCT p.distinct_id) AS paid_count, round(count(DISTINCT p.distinct_id) * 100.0 / count(DISTINCT t.distinct_id), 1) AS conv_pct FROM trials t LEFT JOIN paid p ON t.distinct_id = p.distinct_id AND p.paid_ts > t.trial_ts AND p.paid_ts < t.trial_ts + INTERVAL 14 DAY GROUP BY source ORDER BY trials_count DESC"
}

{
  "rows": [
    { "source": "google_organic", "trials_count": 1820, "paid_count": 142, "conv_pct": 7.8 },
    { "source": "linkedin_ads",   "trials_count": 980,  "paid_count": 41,  "conv_pct": 4.2 },
    { "source": "direct",         "trials_count": 720,  "paid_count": 71,  "conv_pct": 9.9 },
    { "source": "reddit",         "trials_count": 142,  "paid_count": 31,  "conv_pct": 21.8 },
    { "source": "twitter",        "trials_count": 312,  "paid_count": 18,  "conv_pct": 5.8 },
    { "source": "producthunt",    "trials_count": 280,  "paid_count": 88,  "conv_pct": 31.4 }
  ]
}

agentry_publish_query {
  recipe_id: "trial_to_paid_by_source",
  description: "Trial → Paid by Acquisition Source (90d)",
  params: { window_days: 90, conversion_window_days: 14 }
}

{
  "publication_id": "pub_t2p_1",
  "public_url": "https://agentry.sh/q/pub_t2p_1?key=agp_…"
}

The output

Trial → Paid by Acquisition Source (90d, 14-day conversion window)

SOURCE             TRIALS   PAID   CONV%   ARR IMPLIED
producthunt           280     88   31.4%   $26,400  ←
reddit                142     31   21.8%    $9,300  ←
direct                720     71    9.9%   $21,300
google_organic      1,820    142    7.8%   $42,600
twitter               312     18    5.8%    $5,400
linkedin_ads          980     41    4.2%   $12,300  ←  weak

Volume vs quality split:
  Volume leaders:  google_organic, linkedin_ads
  Quality leaders: producthunt, reddit, direct

Actions:
  ★ Double down on Product Hunt — what's working? Find a way to repeat
  ★ Reddit at 22% — increase community presence, do an AMA
  ☆ Pause LinkedIn ads at current targeting — 4.2% is killing ROAS

Public dashboard:
  https://agentry.sh/q/pub_t2p_1?key=agp_xxx

Setting it up

You need two events plus a UTM-style source on the trial event:

// On signup completion (server-side, after email verify):
await fetch(`https://api.agentry.sh/v1/analytics/${PROJECT_ID}/`, {
  method: "POST",
  headers: {
    "Authorization": `Bearer ${process.env.AGENTRY_DSN}`,
    "Content-Type": "application/json",
    "User-Agent": "myapp/1.0",  // REQUIRED — Cloudflare 403s default UAs
  },
  body: JSON.stringify({
    event: "trial_started",
    distinct_id: user.email,
    properties: {
      acquisition_source: user.utm_source ?? "direct",  // ← the key field
      acquisition_campaign: user.utm_campaign,
      referrer: user.initial_referrer,
    },
  }),
});

// On Stripe webhook (subscription.created → trial → active OR direct paid):
await fetch(`https://api.agentry.sh/v1/analytics/${PROJECT_ID}/`, {
  method: "POST",
  headers: { /* same */ "User-Agent": "myapp-stripe/1.0" },
  body: JSON.stringify({
    event: "subscription_started",
    distinct_id: stripeCustomerEmail,
    properties: { plan: subscription.items.data[0].plan.nickname, amount: subscription.items.data[0].plan.amount },
  }),
});

To capture utm_source on signup, snapshot it from the landing page into a first-party cookie (or localStorage) and read it on signup:

// On any landing page hit:
const params = new URLSearchParams(window.location.search);
if (params.get("utm_source")) {
  localStorage.setItem("acq_source", params.get("utm_source")!);
  localStorage.setItem("acq_campaign", params.get("utm_campaign") ?? "");
}
// On signup form submission, read back from localStorage and POST with the trial_started event.

Variations

• “Same funnel but for the last 30 days only — has anything shifted post-launch?”
• “Compare trial-to-paid by GEO (country) — are non-US trials converting differently?”
• “For Product Hunt specifically, what’s the path-to-paid? Show the average # of sessions before paying.”
• “Build a separate dashboard for paid ad channels only (LinkedIn, Google ads, Facebook). Compare CPA × conversion %.”