wbr-metric-source-map

[Actual] WBR — metric → source map (living document)

Purpose: Catalog every [Actual] WBR metric the agent may help compute, with HubSpot logic aligned to monthly [Actual] MBR semantics where the same concepts appear (a dedicated MBR metric map may land in a follow-up PR).

Sheet: GTM Review → [Actual] WBR.
Week window: wbr-actual-weekly-spec.md — resolve column from row 5 (Monday label). Unless RevOps specifies otherwise, week W = [start, end) timestamps aligned to that column (document timezone in Gate 1 — default UTC for API filters).

Gate 1 evidence (deals): § Gate 1 evidence standard — dealname + deal ID for every deal in any cohort; aggregates plus full lists (or RevOps-capped sample with explicit note).

---

V1 product scope (2026)

Rule	Detail
Output	Dry run only: auditable markdown in chat (tables + deal lists). No modify_sheet_values in V1.
Marketing block	Not attempted in V1 (all rows in § Marketing (V1 — skip)).
Sales + funnel + partner SQL (HubSpot)	In scope for Gate 1 computation where rules are defined below.
Delivery block	In scope only if record source → Delivery bucket mapping is filled in § Lead source mapping; else flag TBD in dry run.
Target rows	Human / sheet — agent may echo targets from a read of the prior column or leave blank; do not overwrite targets via automation in V1.

V1 dry-run output format (auditable)

For each weekly run, produce in order:

1. [Actual] WBR-style summary table (read this first): Two value columns — prior Monday | current Monday (same headers as row 5 on the sheet) — and row labels = column B text for the Sales / Inputs block (see tab export). Automation: from repo root, npm run hubspot:wbr:dry-run:op (1Password op-run.env — see tools/hubspot-api-service/op-run.env.example) or npm run hubspot:wbr:dry-run with HUBSPOT_ACCESS_TOKEN set. Optional --monday=YYYY-MM-DD (default 2026-04-06). Output: tools/hubspot-api-service/output/wbr-dry-run-latest.md.
2. Gate 1 detail table (optional in chat): | Metric key | Proposed value | Definition (1 line) | Source | Caveats |
3. HubSpot query log: Week bounds (ISO strings), filters (rollup, pipeline, Record source → Source Name), snapshot timestamp.
4. Deal appendices: dealname + id for cohorts (per Gate 1 evidence standard).
5. Explicit skips: Marketing V1, Active MQL to SQL Lead Source Count, TBD bucket mapping, Calendar-only rows, MCP limits.

---

Conventions (shared with [Actual] MBR)

• Primary sales pipeline(s): Brainforge Sales (or agreed set); exclude partner/promo/archive unless the row says otherwise.
• Rollup ladder: bf_sales_review_rollup values mql, sql_early, sql_mid, proposal, won, plus dormant, other. Map from dealstage via DEALSTAGE_TO_ROLLUP in tools/hubspot-api-service/scripts/dealstage-to-rollup.ts (keep in sync with the rollup backfill script when stages change).
• Active deal (open): Not closed won and not closed lost — use HubSpot semantics (hs_is_closed_won, hs_is_closed, dealstage lost stages) consistent with monthly MBR.
• “Active SQL” (WBR): Open deals only, bf_sales_review_rollup ∈ {sql_early, sql_mid, proposal} — does not include won or any closed deal. Primary pipeline unless the row says otherwise.
• “SQL” rollup set (broader, for transitions / flow): When a row needs “entered SQL” or pipeline snapshot, use sql_early | sql_mid | proposal | won as stated on that row.

Week-boundary conversion percentages

Same counting structure as monthly [Actual] MBR; substitute week W for calendar month M.

For week W and step A → B (rollup):

• Numerator: Count deals whose first A → B transition has timestamp in W (from dealstage history → rollup).
• Denominator: Distinct deals in union of: (1) in rollup A at instant start of W, (2) first entered A during W.

Narrative (month M → week W): Stock in stage at month/week start, plus entries into the prior stage during the period, form the eligible pool; conversions are first transitions A → B with end timestamp in the period. Apply the same idea here with week W instead of calendar month M.

Gate 1 evidence standard (deal-level)

For every metric built from a set of deals (counts, sums, conversion %, medians):

1. State the aggregate (count, $, %, median).
2. List dealname + HubSpot deal id for each deal in the cohort, unless RevOps caps list length — if capped, state the cap and that the list is partial.
3. For conversion % rows, list both numerator and denominator deal sets (or clearly labeled samples if capped).

Lead source mapping (record source)

HubSpot property: Record source (deal). The value WBR buckets use is Source Name — the single primary channel/relationship assigned on the deal. In the CRM API, this is typically the deal property source_name (confirm internal name in Settings → Properties → Deals; see also hubspot-crm-property-audit-2026-04-01.md). Internal tools or a data agent may surface the same concept under other labels (e.g. a recalled/readout of Source Name); align queries to Source Name / source_name, not a different field.

How Source Name should be set (RevOps / reps):

Using any available information about how this deal was first initiated or where it originated from, determine the primary source channel or relationship that best describes where the deal came from and assign the appropriate value to the Source Name field.

Map Source Name values (exact picklist strings from HubSpot) into:

Bucket	Used by rows
Partner	Partner SQL block, Partner % Active SQL
Referral	Referral % Active SQL, referral counts
Delivery	Delivery-sourced rows
Sales Outbound	Sales Outbound % Active SQL

TODO (RevOps): Paste exact Source Name strings per bucket in footnotes. Until then, dry run must list unmapped values seen in cohorts.

---

Calendar-backed metrics (Sales — top of sheet)

Metric key	Source	Calculation / rule	V1 dry run	Write sheet (post-V1)
Meetings Booked	Google Calendar	§ Conventions + wbr-actual-weekly-spec; two calendars (primary + subscribed uttam@brainforge.ai); dedupe event id; ≥1 external (non-@brainforge.ai).	Y	Y
Meetings Taken	TBD	Not defined.	N	N
Meetings Rescheduled	TBD	Not defined.	N	N
Discovery Calls	Calendar + HubSpot	Subset of booked-with-external where any external email matches HubSpot contact.	Y	Y
$ New MQL Pipeline Added	HubSpot	Marketing-adjacent — optional in V1; if run: created in W or entered MQL in W; bf_sales_review_rollup = mql.	Optional	Y
$ New Partner SQL Pipeline Added	HubSpot	New deals (createdate ∈ W), rollup ∈ {sql_early…won}, Partner cohort per Partner attribution.	Y	Y
$ New SQL Pipeline Added	HubSpot	Snapshot: sum amount for deals with rollup ∈ {sql_early, sql_mid, proposal, won} at query time; primary pipeline filter when applied. State snapshot time in dry run.	Y	Y

Growth % rows next to the above (e.g. MQL % growth): Prior week column from sheet read_sheet_values or prior dry run — include in dry-run table.

---

Sales — inputs (HubSpot, V1)

Metric key	Calculation / rule (WBR week W)	Notes	V1 dry run
Active Sales Qualified Leads (SQLs)	Count of open deals with bf_sales_review_rollup ∈ {sql_early, sql_mid, proposal} — excludes won. Primary pipeline.	Active SQL cohort	Y
SQL % Growth	(Active SQL count this week − prior week) ÷ prior week; prior from sheet col O or previous Gate 1.	—	Y (if prior available)
Deals Advanced (Stage Change)	Count of deals with ≥1 forward rollup transition (any of mql→sql_early, sql_early→sql_mid, sql_mid→proposal, proposal→won) with transition timestamp in W; dedupe once per deal per week.	Forward ladder edges	Y
Median Days in Current Deal Stage	Among active SQL deals, median of days since current stage entered (hs_v2_date_entered_* or history).	—	Y (if data accessible)
# Deals Stalled	Count active SQL deals with days in current stage ≥ 14 (see footnotes).	—	Y
# New SQLs Added	Deals whose first entry into **sql_early	sql_mid	proposal
# SQLs Lost	Count deals that entered the HubSpot Lost deal stage (dealstage = Lost in the primary sales pipeline) with entry timestamp in W; include only deals that were in bf_sales_review_rollup ∈ {sql_early, sql_mid, proposal} immediately before Loss (per stage history).	—	Y

---

Funnel conversion % (HubSpot, V1)

Same numerator / denominator pattern as § Week-boundary conversion percentages, with week W instead of month M.

Metric key	Transition	V1 dry run
MQL → SQL-Early Conversion %	mql → sql_early	Y
SQL-Early → SQL-Mid Conversion %	sql_early → sql_mid	Y
SQL-Mid → Proposal %	sql_mid → proposal	Y
Proposal → Won %	proposal → won	Y

Gate 1: Show denominator count, numerator count, %, and dealname + id for both cohorts (per Gate 1 evidence standard).

---

Median days between stages (HubSpot, V1)

Cohort: deals that completed the leg; window for which completions count: default = transition end timestamp in W (alternative: trailing 90 days ending end of W — state which in dry run). List dealname + id for deals in the median cohort when feasible.

Metric key	Leg	V1 dry run
Median Days MQL → SQL-Early	First MQL entry → first sql_early	Y
Median Days SQL-Early → SQL-Mid	First sql_early → first sql_mid	Y
Median SQL-Mid → Proposal	First sql_mid → first proposal	Y
Median Days Proposal → Won	First proposal → first won	Y

---

Lead source — counts & mix (HubSpot, V1)

Prerequisite: Lead source mapping (record source).

Metric key	Rule	V1 dry run
Active MQL to SQL Lead Source Count	Out of scope — do not compute; list under “Skipped — RevOps” in dry run.	N
Active Partner SQL Lead Source Count	Active SQL cohort with Source Name (Record source) in Partner bucket.	Y with mapping
Active Referral SQL Lead Source Count	Same, Referral bucket.	Y with mapping
Active Delivery SQL Lead Source Count	Same, Delivery bucket.	Y with mapping
Active Sales Outbound SQL Lead Source Count	Same, Sales Outbound bucket.	Y with mapping
Partner % Active SQL	Partner count ÷ total active SQL (same denominator as Active SQLs row).	Y
Referral % Active SQL	Referral ÷ total	Y
Delivery % Active SQL	Delivery ÷ total	Y
Sales Outbound % Active SQL	Sales Outbound ÷ total	Y

If unmapped Source Name values exist, list them under “Needs mapping” in the dry run.

---

Pipeline $ — targets vs actuals (HubSpot, V1)

Metric key	Rule	V1 dry run
Target Active SQLs $ Pipeline Value	Target — read from sheet or skip; no HubSpot compute.	Echo from sheet read optional
Active SQL $ Pipeline Value	Sum amount over active SQL cohort (same scope as Active SQLs count).	Y
$ New SQLs Pipeline Added	Flow: sum amount for deals newly entering SQL rollup set (first enter **sql_early	sql_mid
New SQLs Pipeline % Growth	WoW on $ New SQLs Pipeline Added (prior week column or last dry run).	Y if prior $
Avg Pipeline $ per New SQL	$ New SQLs Pipeline Added ÷ # New SQLs Added (guard divide-by-zero).	Y

---

Partner SQL block (HubSpot, V1)

Mirror Sales rows but cohort restricted to Partner attribution (same as $ New Partner SQL Pipeline Added).

Metric key	Notes	V1 dry run
Target Active Partner SQLs	Target — sheet	Optional read
Active # Partner SQLs	Active + partner attribution	Y
Partner SQL % Growth	WoW	Y if prior
Deals Advanced (Stage Change)	Partner cohort only	Y
Median Days in Current Deal Stage	Partner active SQL only	Y
# Partner Deals Stalled	Partner cohort + ≥ 14 days in current stage	Y
# New Partner SQLs Added	First SQL entry in W + partner attribution	Y
# Partner SQLs Lost	Same as # SQLs Lost (footnotes) + Partner cohort	Y
Target Active SQLs $ Pipeline Value (partner section)	Target row — sheet	Optional
Active Partner SQL $ Pipeline Value	Sum amount partner active SQL	Y
$ New Partner SQLs Pipeline Added	Flow $ in W + partner attribution	Y
Avg Potential $ per New Partner SQL	New partner $ ÷ # new partner SQLs	Y

---

Marketing (V1 — skip)

Do not compute in V1; list in dry run under “Skipped — Marketing V1.”

Includes (non-exhaustive if sheet adds rows):

• Target # MQLs, # Marketing Qualified Leads (MQLs), MQLs % Growth
• Target Total Content Assets Published, Total Content Assets Published, % Growth, problem/solution/service/partners/personal content splits
• Target Brainforge External Engagement, Total …, % Growth, influencer/commenter/profile viewer tiers
• Target Visitor Engagement, Total …, % Growth, profile views, actions, repeat engagers
• Target High-Intent Engagement, Total …, % Growth, links, downloads, event signups, DMs, Meetings Booked from Actions
• Advertising Budget $ Deployed and % Growth

---

Delivery (HubSpot / hybrid, V1)

Metric key	Rule	V1 dry run
Target Delivery-Sourced Leads	Target — sheet	Optional read
Delivery-Sourced Leads	Count leads/deals meeting Delivery source mapping with activity in W or active snapshot — RevOps define object (deal vs contact).	TBD
Delivery-Sourced Leads % Growth	WoW	After base metric

---

Partner attribution (deals)

Primary: Source Name (Record source) in the Partner bucket per Lead source mapping. OR partner_type / partnership_relationship populated (see hubspot-crm-property-audit-2026-04-01.md).

---

Appendix: dealstage → bf_sales_review_rollup

Source of truth: tools/hubspot-api-service/scripts/dealstage-to-rollup.ts — DEALSTAGE_TO_ROLLUP (mirror the rollup backfill job in scripts/hubspot/ when that ships).

bf_sales_review_rollup	Typical label	Example dealstage keys
mql	MQL	appointmentscheduled, presentationscheduled
sql_early	SQL - Early	938739088, decisionmakerboughtin, contractsent
sql_mid	SQL - Mid	closedwon, 1103130701, 1103130704
proposal	Proposal	1103130702, 1103130703, 3435911876
won	Won (rollup band)	1985819336 (Verbal Commit), 1103130705 (Signing), 1103130706 (Complete: Won) — [Actual] MBR # Contracts uses 1103130706 only (isCompleteWonDealstage in dealstage-to-rollup.ts).

---

Footnotes (RevOps)

• Stall threshold: 14 calendar days in the current deal stage (from hs_v2_date_entered_* or stage history).
• Active SQL: Does not include won or closed deals — only open deals in sql_early | sql_mid | proposal.
• Lead source: HubSpot deal Record source → Source Name (API: typically source_name). Governance: use all available context on how the deal was first initiated and where it originated to pick the primary source channel or relationship, then set Source Name accordingly. Bucket exact Source Name strings (paste below when ready):
  • Partner: …
  • Referral: …
  • Delivery: …
  • Sales Outbound: …
• # SQLs Lost: Entered HubSpot dealstage = Lost (primary pipeline) during week W, and immediately prior rollup ∈ {sql_early, sql_mid, proposal}.
• Active MQL to SQL Lead Source Count: Not used — skip in dry runs.

---

How to extend

1. read_sheet_values on '[Actual] WBR'!A7:B300 — align column B strings to this table.
2. Keep weekly rules aligned with [Actual] MBR when that tab’s metric map is updated in-repo.
3. gtm-wbr-weekly-actuals — Gate 1 only in V1.

---

Related

• wbr-actual-weekly-spec.md — row 5 / column resolution