Product qualified lead handoff list

The hard part of a PQL list is not the scoring, it is the join: usage lives under a product analytics identity, money lives under a billing customer id, and the rep lives in the CRM keyed on email and domain. Every row you cannot match across all three is a silent omission rather than an error, so the match rate is part of the deliverable, not a footnote.

Steps

1. Discover the CRM schema and the real event names before computing anything. Call hubspot.list_pipelines for deals to get stage ids and the closed and won flags, so "already in an open deal" is resolvable rather than guessed, and hubspot.list_properties for deals, contacts and companies to find whether the portal already carries a PQL property, a lifecycle stage convention or a product-usage field someone is syncing. Stage ids are opaque and per-pipeline, so never hardcode them. In parallel call posthog.list_events and posthog.list_properties, or mixpanel.list_events and mixpanel.list_event_properties, or amplitude.list_events and amplitude.list_event_properties, to find the events that actually express intent in this product: invite sent, limit reached, export run, pricing page viewed. Check posthog.list_cohorts for an existing power-user or trial cohort worth reusing rather than inventing one.

2. Aggregate usage to the account, not the user. posthog.query with HogQL grouped by the account or organisation property, or mixpanel.segmentation with a per-user breakdown rolled up, or amplitude.event_segmentation grouped by an account property, over both the last 14 and the last 28 days. Compute distinct active users, count of the core value event, count of seat invites sent, and whether a plan-limit event fired, with the date it fired. One enthusiastic user is not a buying committee, so require a seat or multi-user signal before an account qualifies on behaviour.

3. Get authoritative account identity from the application database. postgres.list_schemas, then postgres.get_schema, then postgres.query (or the bigquery, clickhouse, mysql or cloudflare_d1 equivalents) for the account-to-user mapping, seat counts, plan and workspace email domain. The application database is usually the only place account identity is clean, and it is what lets you translate a product analytics distinct_id into a domain that HubSpot and Apollo can both key on. Filter your own email domains, obvious test workspaces, QA accounts and partner accounts here, before scoring, or they will dominate every usage ranking.

4. Layer in billing state, because the time-sensitive signals live there. stripe.list_subscriptions and stripe.list_customers for current plan, trial end date, and whether the account is already paying at the relevant tier. Stripe amounts are in the smallest currency unit, so divide by 100 before showing MRR. Stripe cursors are object ids, so page by passing the last row's id as starting_after. A PQL list built only from analytics misses accounts whose trial ends on Friday, which are the most time-sensitive rows on the sheet.

5. Exclude accounts already in a sales motion, and report the join honestly. hubspot.search_companies on the workspace domains to find the existing owner, and hubspot.search_deals with dealstage NOT_IN the closed stage ids to find open deals. Two constraints bite here. First, enumeration filters are case-sensitive and string values under IN and NOT_IN must be lowercase, so a domain-exclusion filter built with mixed-case domains matches nothing and silently returns a list full of existing customers. Lowercase every domain before building the filter. Second, this connector returns only the first page of a HubSpot search and never hands back paging.next.after, so batch the domain lookups in groups small enough to come back under the 200-row page limit rather than issuing one broad query, and report the retrieved row count with a truncation caveat if any batch hits the limit exactly. Respect the 18-filter and 3,000-character body limits when batching, and pace at 5 requests per second or slower. Then state the join result as matched over attempted at every hop: product accounts attempted, resolved to a domain, matched in HubSpot, matched in Stripe.

6. Enrich for fit and attach support context, then write the list where the rep works. apollo.search_organizations on the corporate workspace domain for employee count, industry and revenue band, and apollo.search_people for a decision maker when the self-serve signup is not the buyer. Enrich only on corporate domains and mark enriched rows as enriched. Where an Intercom connection is available, intercom.search_conversations on the account attaches recent support context, which is what stops a rep walking into an open complaint. Then google_sheets.add_sheet to create a dated tab and google_sheets.append_rows to write into it, never google_sheets.update_cells over the rep's existing columns.

7. Report. One row per account: account, domain, signup date, plan, seats active 28 days, core event count 28 days, seats invited 14 days, limit-hit event and date, behaviour rank, fit (employees, industry), existing HubSpot owner, open deal yes or no, MRR today, trigger, suggested next step. Keep behaviour and fit as separate columns and never average them into one number. Above the table, the join audit: accounts attempted, matched and unmatched at each hop as matched over attempted, with the unmatched accounts listed rather than dropped. Then one sentence of judgement naming the single highest-value account whose trigger fired most recently, and the expected conversion range to compare against: roughly 15 to 30 percent for PQLs per Poyar and OpenView, against a self-serve freemium baseline of 3 to 8 percent.

Gotchas

• Identity resolution is the whole job and it fails quietly. Product analytics keys on distinct_id or user_id, Stripe keys on customer id, HubSpot keys on contact email and company domain. A user who signed up with a personal address and works at a target account will match on none of them. Always report matched over attempted at each hop and list the unmatched accounts separately rather than silently dropping them, because the drop is invisible in the final table.
• Free email domains cannot be rolled up to an account at all. Gmail, Outlook and similar addresses have no meaningful domain to join on, so they must be excluded from the account roll-up and counted as unmatched. Enriching them through apollo.search_organizations returns a plausible and wrong company.
• Enumeration and IN case sensitivity breaks the exclusion join. HubSpot enumeration filters are case-sensitive and string values under IN or NOT_IN must be lowercase. Get this wrong and the "exclude accounts with an open deal" filter matches nothing, so the list you hand sales includes accounts a rep is already working, which is the fastest way to lose their trust.
• The HubSpot connector caps at one page with no cursor. A single broad hubspot.search_companies or hubspot.search_deals call returns at most 200 rows and never tells you there were more, so an exclusion built on one call under-excludes in a way that looks fine. Batch narrowly and caveat the counts.
• Scoring a blended number destroys the handoff. A rep needs "hit the five-seat threshold on Tuesday", not "score 78". Keep the trigger as a named event with a date, and keep behaviour and fit in separate columns so the rep can see why the account is on the list.
• User-level enthusiasm is not account-level intent. Require a seat invitation, a second active user, or a plan-limit event before an account qualifies, otherwise the list ranks individual power users at accounts with no budget.
• Writing over the rep's existing sheet destroys their notes and formulas. Create a dated tab with google_sheets.add_sheet and append, so each week's list is additive and week-over-week movement stays visible.