Campaign ROI
Loading…
—
Full-funnel ROI from lead capture through funded investment, per campaign. Every contact ever created by SCI is the universe; their investments are followed forward with no date cap, so a lead from 2024 whose investment funded in 2026 appears in the 2024 cohort.
Contacts, investments, and meetings are read from the nightly R2 cache (rebuilt at 2 AM ET). Spend data is fetched live from the Meta Marketing API and LinkedIn Marketing API on each load — this accounts for most of the load time. Google Ads spend is pending integration.
The Cohort dropdown selects which contacts form the cohort based on their createdate in HubSpot. "All Time" includes every contact since 2023. Other ranges filter by creation date. Investments are always followed forward regardless of cohort range.
Filters the table by how each contact was acquired. Classification starts with HubSpot's hs_analytics_source but is overridden by event-level paid signals where available.
Event-level paid classification (v16): HubSpot's hs_analytics_source is set by session-level tracking at the moment a contact converts (fills out a form). If a person clicks a Meta ad, leaves the site, and returns days later via an organic link or direct visit to fill out the form, HubSpot tags them as "Organic Social" or "Direct Traffic" — not "Paid Social." Similarly, contacts migrated from a previous CRM (Infusionsoft) had their source overwritten to IMPORT/INTEGRATION during bulk import.
To fix this, the nightly cache rebuild processes every form submission event and checks multiple signals to determine if the contact came from a paid ad: hsa_cam (Meta campaign ID from URL params), hsa_net (ad network identifier), hs_visit_source starting with "paid", utm_medium containing "paid"/"cpc"/"ppc"/"retargeting", and lead ad URL patterns (Meta/LinkedIn form-hosted ads). If any of these signals is present, the contact is classified as Paid regardless of what hs_analytics_source says — including imported contacts.
Bucket definitions after override: Paid = any contact with a paid event signal, OR whose hs_analytics_source contains "Paid". Imported = INTEGRATION, IMPORT, OFFLINE, or API sources without any paid event signal. Direct = "Direct Traffic" (with no paid event signal). Organic = everything else that isn't Paid, Direct, or Imported.
All shows Paid + Organic + Direct but excludes Imported. This prevents CRM migration contacts from polluting campaign attribution. To see the imported population, select the Imported bucket explicitly.
When Paid is selected, a platform dropdown appears allowing you to filter by ad platform: Meta (Facebook/Instagram), LinkedIn, or Google. Platform classification always uses hs_analytics_source_data_1 (DD1) — values containing "facebook", "fb", "ig", or "instagram" map to Meta; "linkedin" or "lnkd" to LinkedIn; "google" or "adwords" to Google. A LinkedIn contact who also has a Meta engagement event still shows under LinkedIn.
Campaign (default) groups rows by ad campaign. For contacts with a Meta campaign ID (hsa_cam from engagement events), the ID is used as the grouping key — this consolidates fragmented DD2 variants into their true campaign. Contacts without an hsa_cam fall back to DD2 → DD1 → Original Source. First Conversion groups by the contact's first_conversion_event_name. Recent Conversion groups by recent_conversion_event_name. In conversion mode, booking and spend columns are hidden.
Click any column header to sort. Click again to reverse. The active sort column shows ▾ (descending) or ▴ (ascending). Default sort: Capital Raised descending.
Leads: Total contacts in the cohort attributed to this campaign.
AI Leads: Accredited investor contacts (are_you_accredited_ = "Yes").
Spend: Total ad spend for this campaign from Meta and/or LinkedIn. Shows "—" when no spend data is available.
CPAIL: Cost Per AI Lead = Spend ÷ AI Leads.
Intros / Cost per Intro: Intro meetings booked by contacts in this campaign's cohort.
Discoveries / Cost per Discovery: Discovery meetings booked by contacts in this campaign's cohort.
Investments: Count of funded investments by contacts in this campaign's cohort (all-time, follow-forward).
Capital Raised: Total dollar amount of those investments.
Cost of Capital: Spend ÷ Investments.
Cost/$100k: How much ad spend it takes to produce $100,000 in invested capital. Formula: (Spend ÷ Capital Raised) × 100,000. Lower is better.
Filters the funded columns only — Investments, Capital Raised, Cost of Capital, Cost/$100k (and the matching KPI tiles, top cards, and drill drawer). Leads, AI Leads, Spend, Intros, and Discoveries are pre-funding and are never filtered by this toggle.
Each investor's investments are ordered by Funds Received date; the earliest is that investor's New investment, every later one is Returning. "First ever" is computed across all of an investor's investments regardless of the cohort/bucket shown, so a returning investment still reads as Returning even when the first one is outside the current view.
Investor resolution (v19.6): an investment is tied to a contact by HubSpot association first, falling back to matching the investment's email_address to a contact's primary email. The association pass recovers funded capital — and its campaign attribution — for investments whose email differs from the contact's primary email. This also defines the investor key for New/Returning, so investments under two emails that resolve to the same contact are treated as one investor.
Not-attributed note: the line under the spend note sizes investments that resolve to no cached contact (no association to a known contact and no primary-email match). They can't be tied to a campaign and are excluded from every row. This is a contact-identity gap to resolve in HubSpot (merge the investor's records / add the secondary email), not a dashboard calculation — once merged, attribution and New/Returning self-correct on the next cache rebuild.
Campaign-level spend is matched by Meta campaign ID (primary) or campaign name (fallback, case-insensitive). Meta and LinkedIn spend are fetched for the last 37 months (Meta's API maximum — earlier spend is unavailable). Google Ads spend is pending API integration. Campaigns without platform spend show "—" in cost columns.
Ad-level spend (v18): Spend per ad set and per ad is also fetched at load time from Meta's Insights API (level=ad). This data populates the Spend and cost columns in the drill drawer and provides ad set and ad names without requiring per-ID API calls.
A note below the table header shows how many Meta and LinkedIn campaigns returned spend data and the date range covered.
Four summary cards appear between the KPI tiles and the table in Campaign mode. The cards adapt to the current bucket:
When spend data is available (Paid / All): cards show the top 5 most cost-efficient items: Lowest CPAIL, Lowest Cost/Intro, Lowest Cost/Discovery, and Lowest Cost/$100K. Lower is better — these highlight where ad dollars produce the most results.
When no spend data (Organic / Direct / Imported): cards switch to volume leaders: Most AI Leads, Most Intros, Most Discoveries, and Most Capital Raised. Higher is better.
Minimum thresholds filter out noise and are shown in a note below the cards. For cost cards: 10+ AI leads for CPAIL, 5+ intros for Cost/Intro, 3+ discoveries for Cost/Discovery, 1+ investment for Cost/$100K. Items below these thresholds are excluded from rankings.
Level toggle: Each card has a Campaign / Ad Set / Ad toggle. Switch any card independently to see top performers at that granularity. Ad Set and Ad levels aggregate all contacts across all campaigns by their hsa_grp or hsa_ad, with spend from the pre-fetched ad-level data. The toggle is hidden for Organic/Direct/Imported since those contacts don't have ad set or ad attribution.
Click-through: Click any row in a card to open the drill drawer scoped to that item. Campaign rows open the full campaign drill. Ad Set rows open a drill showing that ad set's ads and contacts. Ad rows open directly to the contacts level. When drilling into an ad set or ad, a breadcrumb appears above the title showing the parent campaign (and ad set for ads) as clickable links for upward navigation.
The search box above the table filters campaign rows by keyword as you type. Matching is case-insensitive on the campaign name. KPI tiles always reflect the full (unfiltered) bucket. The totals row at the bottom sums only the visible rows when a search is active.
Purely numeric values, GUIDs (e.g., c1d25565-f664-4089-...), and userId:-prefixed values in DD2 are treated as malformed — these are artifacts from defunct integrations. When DD2 matches any of these patterns, the dashboard falls back to DD1, then to Original Source.
When a specific paid platform is selected (e.g., Google only), contacts whose DD1 doesn't match any known platform are excluded rather than included as "other."
Case-insensitive campaign grouping (v17): DD2 fallback values are matched against known Meta campaign names case-insensitively. If a contact's DD2 matches a Meta campaign name (e.g., "lm – charlotte download" matching "LM – Charlotte Download"), the contact groups under the Meta campaign ID. Remaining DD2 values without a Meta match are lowercased for grouping to prevent case-split duplicate rows.
Click any campaign row to open a three-level drill-down drawer. All levels are sortable — click any column header to sort that level independently (sorting one level does not affect the others). Sort arrows (▾/▴) show the active column at each level. Expanded accordion sections persist through sort operations.
Level 1 — Ad Sets: Rows grouped by Meta ad set ID (hsa_grp). Columns: Ad Set name, Leads, AI Leads, Spend, CPAIL, Intros, Cost/Intro, Discos, Cost/Disco, Investments, Capital Raised, Cost of Capital, Cost/$100K. Spend data comes from the pre-fetched ad-level spend (Meta level=ad Insights API). Ad set names are resolved from the spend data first, with a per-ID API fallback for any missing names.
Level 2 — Ads: Within each ad set, rows grouped by individual ad ID (hsa_ad). Same 13 columns as the ad set level. Spend data comes from per-ad spend. Ad names resolved from spend data with lazy per-ID fallback.
Level 3 — Contacts: Individual contacts with name (linked to HubSpot), created date, accreditation status (Yes/No), meeting tags (blue "Intro" and purple "Disco" pills with ×N suffix for multiples), investment count, and capital raised. Investments expanded inline below each contact showing fund, type, and date.
Contacts without ad set or ad attribution (no hsa_grp/hsa_ad in their form submission) appear under "(no ad set)" / "(no ad)". These rows show "—" for spend and cost columns since there is no ad-level spend to attribute. This primarily affects older contacts (pre-late-2022) who came through Lead Ads before Meta's ad-level tracking parameters were being captured in HubSpot form submission events. Only ~176 contacts are affected — all newer Lead Ad contacts have full tracking.
A sticky totals row at the bottom of the main table shows the sum of all visible rows. When a campaign search is active, the totals reflect only the filtered rows. KPI tiles above the table always show the full bucket totals regardless of search.
Campaign grouping uses the contact's hsa_cam (Meta campaign ID from engagement events) as the primary key when available — this consolidates fragmented DD2 variants into their true campaign. Contacts without hsa_cam fall back to DD2 → DD1 → Original Source, with case-insensitive matching against known Meta campaign names.
Spend matching uses a two-tier approach. Primary: the contact's hsa_cam value is matched directly to Meta's campaign ID → spend data, or Google Ads campaign ID. Fallback: campaign name case-insensitive match against Meta, LinkedIn, and Google campaign names. The ID-based path is reliable; the name-based fallback catches LinkedIn spend and edge cases.
Platform-aware spend (v19.9): When a single platform is selected (e.g. Google only), spend from other platforms is hidden even if the campaign row has it. This prevents misleading totals — e.g. contacts HubSpot classified as PAID_SEARCH whose hsa_cam points to a Meta campaign would otherwise show Meta's full campaign spend under the Google filter. The spend column shows "—" for those rows; switch to All Platforms or Meta to see their spend.