A Dynamic ICP Model That Catches Its Own Drift

Build a dynamic ICP model against my existing accounts, conversion_events,
and revenue tables in Postgres:

1. Write a migration for an `icp` table storing authored ICP hypotheses:
   segment name, size band (min/max employees), industry, region, one row
   per tier.
2. Write a view `icp_fit_score` that joins accounts to icp and scores each
   account 0-1 against every ICP row's criteria.
3. Add a materialized view that blends icp_fit_score with a weighted
   moving average of conversion_events and revenue per account, weighting
   the last N days more heavily than older data.
4. Set up two pg_cron jobs against that materialized view: hourly for the
   conversion numbers, daily for revenue.
5. Write a query that ranks accounts by conversion and revenue alone, no
   ICP tier, and diffs it against the authored tiers to surface accounts
   winning without being tagged tier 1.

Use my actual table and column names; ask me for them if you can't see the
schema.

Problem#

Most ICP definitions are authored once and trusted forever. Someone writes down “tier 1 = mid-market, 200–2000 employees, EMEA” (HubSpot even ships a field for it, hs_ideal_customer_profile), and that tag drives targeting, content, and spend for the next year regardless of whether accounts matching it convert or pay. An ICP is a hypothesis, not a fact. Nothing in a typical CRM checks it against outcomes, so drift is invisible until a quarter’s pipeline comes in soft and nobody can say which segment caused it.

Pattern#

Treat the ICP as data with a feedback loop:

1. Store the hypothesis as a row. An icp table holds the authored criteria (segment, size band, industry, region) as structured, queryable data.
2. Score every account against it. A derived view scores each account 0–1 against each ICP’s criteria, turning “does this account fit tier 1” into a number that recalculates continuously as behavior changes.
3. Blend the score with what’s happening now. A weighted moving average pulls in live conversion and revenue data for the accounts matching each ICP, so recent performance counts for more than the original assumption. A segment that converted well a year ago but is slipping now gets re-weighted down even as its label stays the same.
4. Surface the gap between the score and reality. For each authored ICP, show percent converting, percent generating revenue, and revenue itself, refreshed on a schedule (funnel-sensitive numbers hourly, revenue daily) so the “actual” side never goes stale.
5. Rank the emergent segments too, independent of what was authored. The same conversion and revenue data, cut without regard to ICP tier, surfaces whichever accounts are winning right now, even ones nobody tagged as tier 1. When that ranking disagrees with the authored tiers, the definition is wrong and should move.

This is also why the model couldn’t live as a HubSpot workflow: hs_ideal_customer_profile is a static text field with no path back to conversion or revenue. Building it properly meant making ICP fitment a first-class part of the CRM’s own data model, one of the reasons the CRM itself got replaced with a homegrown, event-sourced system built for that requirement.

Output#

Run the same loop against your own tiers and a segment quietly underperforming its label surfaces within weeks, in time to act before the quarter closes — not a quarter later, once the topline number already shows it.

Variations#

• The match threshold for “does this account count toward this ICP’s score” is a tuning knob: tighter thresholds cut false positives but shrink the sample the scorecard is built on.
• Swap in whatever conversion and revenue tables your own CRM already has: this is a re-weighting loop that works on top of any CRM, including HubSpot’s own objects, just without the ability to make the ICP field itself derived.
• The same shape catches churn early: filter the scorecard to one segment and alert on week-over-week conversion drop, weeks before a quarterly review would surface it.
• Next in this line. The same feedback loop applies one level down, to individual buyer personas, and the whole model becomes queryable by a non-engineer once it sits behind a roster-intelligence MCP server.