Context & How to Use This Document

This is the Analytics team's execution guide for the 2026 Sales Compensation plan launching July 1. It covers everything you need to build, validate, and run the commission calculation pipeline — plus a clear split between what must be done before July 1 and what can be completed during July's first run.

Query file: All commission queries are in 2026_Commission_Queries.sql. Each query references only orders_enriched (never sales_data_bob) and uses practice_id as the non-PHI join key. Do not add any patient-level fields to these queries.

Reference guide: For comp mechanic details, see reference.html — all section links in this doc point directly to the relevant anchor.
PHI Rule: No patient-level fields in any output, query, or reporting. Join on practice_id only. Never use sales_data_bob — deprecated and contains PHI. Approved source: strive-analytics.strive_analytics.orders_enriched.

Launch Phase Summary

PhaseDeadlineWhat Needs to Be True
🔴 Must-Have
Pre-July 1		 June 30, 2026 Tables confirmed, fields validated, PART-HUNTER-1 + TSM-MOMENTUM scheduled, AE-1→AE-5 chain tested, partnership_deals schema live
🟡 July Run
First payroll month		 July 31, 2026 First live monthly run for all plans, backward simulation for territory analysis, clawback exposure report built, CSM portfolio seeded
🔵 Ongoing
Post-launch		 Rolling AE bonus calibration, GPO tracking validation, CSM plan finalization, rep YTD earnings dashboards

Data Dependencies

Required BigQuery Tables

TableUsed ByStatus CheckPre-July 1?
orders_enriched All plans Confirm: adj_gross_profit, practice_id, is_sale, complete_processing_date, order_id all exist and populated Required
sales_ae_activation AE-1 → AE-5 Confirm: rep_id, practice_id, activation_date, handoff_date, ae_clawback_window_end Required
book_of_business_history TSM-2 → TSM-5 Confirm rep attribution (rep_name, manager, director, VP) is current and correct for July 1 territory mapping Required
sla_contracts TSM-7, TSM-8, PART-1 Confirm: contract_id, practice_id, rep_name, signed_date, moq_committed, signing_bonus_paid, consecutive_miss_months Required
partnership_deals PART-HUNTER-1 → PART-HUNTER-5 New fields required — see §Partnership section. HubSpot → BQ nightly sync must include all new fields. Required
partnership_sla_contracts PART-1 Confirm: mirrors sla_contracts structure for telehealth group MOQ contracts Required
gpo_provider_scripts PART-2 Confirm: gpo_name, provider_npi, practice_id populated. GPO_Provider key = CONCAT(gpo_name, "_", provider_npi). Validate against orders_enriched join. July OK
csm_bucket_clinics CSM-1 Confirm: practice_id, csm_rep_name, csm_bucket_entry_date, csm_bucket_reason, reactivation_date July OK

New Fields Required on partnership_deals

These fields must exist in BigQuery (synced from HubSpot) before PART-HUNTER queries can run.

FieldTypeDescription
signing_bonus_paidBOOLEANTrue when Finance has disbursed the full signing bonus at contract execution. Set by WF-11 in HubSpot. Applies to MOQ deals.
consecutive_miss_monthsINTEGERRolling count of consecutive below-MOQ months. Resets to 0 on any compliant month. Updated monthly by WF-12 / PART-HUNTER-1.
clawback_triggeredBOOLEANTrue when consecutive_miss_months reaches 2. Prevents duplicate recovery tasks. Once true, never resets.
clawback_amount_recoveredFLOATDollar amount Finance has recovered. 100% of bonus if triggered months 1-6; 50% if months 7-12.
csm_handoff_dateDATEdeal_signed_date + 12 months. Clawback window closes; rep comp ends. Set at contract signing by WF-11. Required for all MOQ partnership deals.
deal_statusSTRINGActive / Clawback Triggered / CSM Handoff / Lost

Metric Definitions

These are the exact definitions used in the commission queries. If a source table or field produces a different result than described here, the query logic will be wrong — validate each definition against real data before first run.

AE Metrics §2 Ref Guide

Activation
First date a practice_id accumulates 10 scripts (COUNT DISTINCT order_id WHERE is_sale = TRUE) within any trailing 60-day window. The activation_date is the end of that window. Open question: how do we handle a clinic that briefly crosses 10 scripts and then falls back? Is activation a one-time irreversible event?
Scripts
COUNT(DISTINCT order_id) where is_sale = TRUE in orders_enriched. Confirm this is the correct flag for "dispensed fills" vs. transfers or voids.
Activation baseline
~5 scripts/month equivalent — the trailing 60-day count at activation / 2. TSM commission clock starts from handoff_date, not activation_date.
handoff_date
First calendar day of the month following activation_date. E.g., activation April 14 → handoff_date = May 1. This is the TSM commission start date.
Day-90 payment window
90 calendar days after activation_date. AE earns 10% of incremental GP above activation baseline if clinic has ≥5 Rx/month at that point.
Going dark
Zero orders in any 60-day window within 6 months of activation_date. Triggers full clawback of activation bonus + kicker. After 6 months, no clawback applies.
Velocity kicker tier
Count of distinct practice_ids activated by that rep in the calendar month. Applies retroactively to all activations in the month once the tier is reached (e.g., rep's 5th activation this month triggers +$300 for all 5 clinics, not just the 5th).

TSM / SLA Metrics §3 Ref Guide §4 SLA

3-month trailing avg
Average monthly script count (or GP) over the 3 most recently completed calendar months. Not the trailing 90 days — full calendar months only.
Prior 3-month avg
The 3 calendar months immediately before the trailing 3 months (i.e., months -4, -5, -6). Used for momentum tag comparison.
Momentum tag
Growing: last 3-mo avg > prior 3-mo avg × 1.10. Declining: last 3-mo avg < prior 3-mo avg × 0.90. Plateau: everything else. 2-month stability buffer: tag change only affects baseline after 2 consecutive months in new tag. Need a prior_momentum_tag column in analytics store to implement this.
Baseline (Growing)
Real trailing 3-month average GP. No discount.
Baseline (Plateau)
Trailing 3-month average GP × 0.85. Creates a synthetic earnable spread of 15% for retention-focused TSMs.
Baseline (Declining)
Prior baseline holds — does not step down automatically. Natural floor is enforced via MAX() logic.
Incremental GP
MAX(0, actual_monthly_adj_gp - baseline_gp). Zero if below baseline.
Commission floor
Trailing 3-month avg < 10 scripts → zero commission. Clinic stays in book. Immediate — no buffer.
Drop-out
2 consecutive months below 10 scripts → clinic exits TSM book into CSM bucket. TSM-6 generates the flag; route to Ops before finalizing payroll.
SLA baseline freeze
MAX(3-month trailing avg GP at signing, MOQ-equivalent GP). Rep cannot sign a low MOQ to manufacture a lower baseline.
SLA clawback trigger
2 consecutive months below committed MOQ → contract voids. 100% recovery months 1-6; 50% months 7-12. TSM-8 flags these; same 2-month logic as drop-out.

Partnership / Hunter Metrics §7 Ref Guide §7.1a Clawback

MOQ Partnership Deal Structure: Partners sign both an SLA and a MOQ commitment → signing bonus = monthly_moq_committed × $1.00. Clawback applies (100% months 1–6, 50% months 7–12). 12-month CSM handoff at contract month 12.
MOQ signing bonus
monthly_moq_committed × signing_bonus_rate (default $1.00). Paid in full at contract execution. PART-HUNTER-1 and PART-HUNTER-2 assume signing_bonus_paid = TRUE means Finance has disbursed it.
MOQ hit (monthly)
Scripts delivered in a calendar month ≥ monthly_moq_committed. Measured as COUNT(DISTINCT order_id WHERE is_sale = TRUE) in orders_enriched for that practice_id, for that calendar month.
consecutive_miss_months
Rolling counter. Increments when MOQ missed; resets to 0 when MOQ hit. Clawback fires when this reaches 2. Stored on partnership_deals; updated by PART-HUNTER-1 output (or WF-12 in HubSpot).
Clawback amount
100% of signing bonus if triggered in months 1–6 since contract_signed_date. 50% if months 7–12. $0 after month 12 (CSM handoff).
12-month CSM handoff
All MOQ partnership deals transfer to CSM at contract_signed_date + 12 months. csm_handoff_date must be populated for all deals. After handoff, no rep comp or clawback actions fire.
Delivered GP
SUM(adj_gross_profit) from orders_enriched for a given practice_id, WHERE is_sale = TRUE, within the plan year, while still in 12-month ramp window (date ≤ ramp_end_date). Used for Stream 2 GP milestone.
GP milestone
$50M annual delivered GP threshold per rep. If exceeded: 0.1% × total annual delivered GP. Run at Q4 close (PART-HUNTER-4). Confirm whether this is per-rep or team-level.

CSM Metrics §5 Ref Guide

⚠ CSM Plan — Open: The comp mechanic is defined (retention tiers + reactivation kicker) but several analytical questions need answers before CSM-1 can run cleanly. See Open Items section.
Active clinic (CSM)
≥1 Rx fill placed in the calendar month (not trailing 60 days — just that month). Simpler than AE/TSM definitions.
Retention rate
Active clinics ÷ total clinics in CSM portfolio at month start. Measured last business day of month.
Portfolio size minimum
≥10 clinics required to qualify for retention bonus. Prevents gaming with tiny books.
Reactivation
Clinic exits CSM bucket to TSM ownership when it clears 10 scripts in trailing 60-day window. Same threshold as AE activation.
CSM bucket entry
Trailing 3-mo avg scripts < 10 for 2 consecutive months → enters CSM bucket. Tracked via csm_bucket_entry_date and csm_bucket_reason. TSM-6 flags candidates; Ops confirms transfer.

AE Plan — Task List

👤 Account Executive (AE) Queries: AE-1 → AE-5 §2 Ref Guide
🔴 Pre-July 1 — Must Complete
  • Confirm sales_ae_activation table exists with: rep_id, practice_id, activation_date, handoff_date, ae_clawback_window_end. §2.3 Handoff Rule
  • Validate AE-1 query output against a known set of recently activated clinics. Spot-check 10–15 records.
  • Validate AE-2 velocity kicker logic: confirm kicker is applied to ALL activations in the month once tier is hit, not just the marginal one. §2.2 Kicker Tiers
  • Validate AE-3 Day-90 trailing: confirm activation baseline (~5 scripts/month) is correctly seeded in query logic. §2.1 Structure
  • Validate AE-4 clawback flags: confirm "going dark" detection (0 orders in 60-day window within 6-month window) is firing correctly. §2.4 Clawback
  • End-to-end test: run AE-1 → AE-5 for June 2026 (or last full month of data). Verify payroll summary totals are reasonable.
  • ⚠ Calibration item: Is $250 base activation bonus correctly sized? Pull 2025 historical activation counts by rep and calculate what payouts would have been. Flag outliers. Decision needed from leadership before July 1.
🟡 July — First Month Run
  • Run AE-5 payroll summary for July and deliver to Finance by agreed cutoff date.
  • Build AE activation rate report by rep — track velocity kicker tier distribution to flag over/under-performers.
  • Monitor AE-4 clawback flags daily for first 30 days — surface any "going dark" clinics from June activations.

TSM / Sr. TSM / SLA — Task List

📈 Territory Sales Manager (TSM / Sr. TSM) + SLA Contracts Queries: TSM-MOMENTUM → TSM-8 §3 Ref Guide
🔴 Pre-July 1 — Must Complete
  • Confirm book_of_business_history reflects correct rep→territory→manager→director→VP hierarchy as of July 1. Any territory mapping errors will cascade into all TSM and override queries. §6 Overrides
  • Schedule TSM-MOMENTUM as the first job in the monthly commission run — must complete before TSM-1 executes. §3.2 Momentum Tags
  • Implement prior_momentum_tag storage: the 2-month stability buffer requires you to know last month's tag. Create an analytics table or BQ view that persists the prior month's tag for each clinic. §3.2
  • Validate TSM-1 baseline logic: run for a sample clinic book and verify Growing/Plateau/Declining baselines match manual calculation. §3.3 Baseline
  • Validate TSM-2 incremental GP logic: confirm MAX(0, actual_gp - baseline) never produces negative commission. Spot-check 20+ clinics.
  • Validate TSM-6 drop-out detection: confirm 2-consecutive-month rule matches reference guide definition. Route drop-out list to Ops. §3.4 Drop-out
  • Create production view v_tsm_monthly_commissions from TSM-2 output — referenced by TSM-3 and TSM-4 (avoids query duplication). See TSM-3 comment in SQL file.
  • Confirm sla_contracts baseline freeze logic: stored baseline should be MAX(trailing avg at signing, MOQ). Validate against existing SLA contract records. §4.2 Baseline Freeze
  • End-to-end test: run TSM-MOMENTUM → TSM-5 for June 2026 data. Verify rep commission totals are reasonable vs. prior-year benchmarks.
  • ⚠ Calibration item: Verify the 85% plateau discount is correctly generating meaningful earnable spread. If avg Plateau clinic GP is X, the $Y/month retention spread should be enough to motivate. Run against real portfolio data and flag if spread is too thin or too generous.
🟡 July — First Month Run
  • Run TSM-MOMENTUM → TSM-5 payroll chain for July. Deliver TSM-5 payroll summary to Finance.
  • Run TSM-7 (SLA renewal bonuses) — check for any contracts hitting renewal dates in July.
  • Run TSM-8 (SLA clawback flags) — surface any 2-consecutive-miss situations to Ops before payroll finalizes.
  • Build rep-level earnings dashboard showing baseline, monthly GP, commission by clinic — reps need visibility before the plan is credible. §3
  • Run territory backward simulation: using 2025 orders_enriched data, simulate what each rep would have earned under the 2026 plan. Use orders_enriched via practice_id join to book_of_business_history. Deliver results before territory analysis session.

Partnership / Hunter Rep — Task List

🤝 Partnership Hunter Rep Queries: PART-1, PART-2, PART-HUNTER-1 → PART-HUNTER-5 §7 Ref Guide
🔴 Pre-July 1 — Must Complete
  • Add 6 new fields to partnership_deals BQ table (or confirm HubSpot → BQ sync will create them): signing_bonus_paid, consecutive_miss_months, clawback_triggered, clawback_amount_recovered, csm_handoff_date, deal_status. See Data Dependencies section above.
  • Schedule PART-HUNTER-1 as a monthly recurring job — run on the 1st of each month for all active deals not yet past CSM handoff date. §7.1a Clawback
  • Validate PART-HUNTER-1 — MOQ path: create a synthetic MOQ deal, simulate 2 consecutive miss months, confirm clawback amount (100% months 1-6) fires correctly. Use synthetic data only.
  • Validate PART-HUNTER-2: confirm clawback exposure columns correctly switch from 100% to 50% at the 6-month mark.
  • Validate PART-1 (telehealth SLA clawback flags): confirm it mirrors TSM-8 logic correctly for MOQ-based telehealth group contracts.
  • ⚠ GPO open item (TBD — pending stakeholder approval): Validate that gpo_provider_scripts table is populated and GPO_Provider key = CONCAT(gpo_name, "_", provider_npi) reliably joins to orders_enriched.practice_id. If this join doesn't work, PART-2 GPO activation tracking will not function. Note: GPO/Franchise comp mechanics are not yet approved — do not build or run GPO tracking queries until approval is confirmed. §7.3 GPO
🟡 July — First Month Run
  • Run PART-HUNTER-1 for July — first live clawback monitor pass. Any deals already live before July 1 need their consecutive_miss_months seeded correctly from HubSpot data.
  • Run PART-HUNTER-2 for a portfolio overview — deliver to Partnership team and Finance showing total clawback exposure by deal.
  • Run PART-2 (GPO provider activations) for July — confirm GPO network providers are being tracked correctly. (TBD — pending stakeholder approval of GPO comp mechanics before this query should be run in production.)
  • Run PART-HUNTER-3 for YTD delivered GP — used for Stream 2 milestone tracking. Not payable until year-end, but important to monitor monthly so reps know where they stand.
  • Build PART-HUNTER-5 (rep earnings summary) as a monthly report — deliver alongside payroll run.

Management Overrides — Task List

🏆 Management Overrides (Managers, Directors, VPs) Query: TSM-4 §6 Ref Guide
🔴 Pre-July 1 — Must Complete
  • Confirm book_of_business_history has current manager → director → VP hierarchy for every rep. TSM-4 depends entirely on this being correct. §6.1 Override Rates
  • Validate TSM-4 output against the worked example in §6.2: AE Manager pool, TSM Manager pool, Director pool, VP pool. Numbers should be additive, not deducted from rep pay.
  • Confirm Partnership Manager override (10% of Partnership team variable) is calculated separately from TSM Manager override pool. TSM-4 may need a separate PART-OVERRIDE query or extension — review query against partnership team structure. §6.1
🟡 July — First Month Run
  • Run TSM-4 for July — add AE-5 variable to VP pool calculation (see TSM-4 note in SQL). Deliver to Finance alongside rep payroll.
  • Build manager-facing summary showing their team's total commission and the override pool they generated — helps managers understand the plan before explaining it to reps.

CSM Plan — Task List

⚠ CSM Plan — Partially Open: The retention bonus mechanic and reactivation kicker ($100) are defined in reference.html §5. CSM-1 query handles reactivation tracking. What's not yet resolved: the full retention bonus calculation query (CSM-2 / CSM-3 don't yet exist in the SQL file) and the portfolio seeding question.
📦 CSM (Customer Success Manager) Query: CSM-1 + TBD retention queries §5 Ref Guide
🔴 Pre-July 1 — Must Complete
  • Confirm csm_bucket_clinics table structure: practice_id, csm_rep_name, csm_bucket_entry_date, csm_bucket_reason, reactivation_date.
  • Seed CSM portfolio with all clinics currently in the stagnant/unmanaged bucket as of June 30. This is the starting portfolio for the retention bonus calculation.
  • Validate CSM-1 (reactivation kicker): confirm that clinics exiting the CSM bucket trigger the $100 reactivation bonus correctly when 10-script threshold is met. §5.2 Reactivation
  • ⚠ Missing query: Write CSM-2 (monthly retention rate calculation) and CSM-3 (retention bonus payroll input) — these do not exist in the SQL file yet. §5.1 Retention Tiers
🟡 July — First Month Run
  • Run CSM-1 for July to capture any reactivations. Deliver to Finance.
  • Once CSM-2 and CSM-3 are written and validated, run retention bonus calculation for July — first paid month for CSM retention bonus.
  • Build CSM portfolio dashboard: shows each CSM rep's current portfolio, activity rate, month-over-month trend, reactivations YTD.
  • ⚠ Open calibration: Are the retention bonus tier rates ($15 / $25 / $40 per active clinic) appropriately sized given expected CSM portfolio sizes? Pull data on how many clinics are currently in the stagnant bucket and what a typical CSM portfolio would look like. Flag if numbers are too thin to be motivating.

Open Items & Flags

These items need decisions or additional work before they can be considered resolved. Flag these to leadership — don't assume they'll work themselves out.

🔴 GPO Tracking — Needs Validation
PART-2 relies on a join between gpo_provider_scripts and orders_enriched using provider_npi / practice_id. If GPO providers don't have clean practice_id matches in orders_enriched (because they're ordering under a different practice entity), the activation tracking won't work. Action: pull PART-2 on test data and verify row counts are plausible. If join fails, escalate to RevOps and HubSpot team to define the GPO provider → practice_id mapping.
🔴 AE Bonus Calibration — Decision Required Pre-Launch
The $250 base activation bonus and $300–$750 velocity kicker tiers were set at plan design time without a full calibration run against historical data. Before July 1, run AE-1 and AE-2 against 2025 activation data to calculate what total AE comp would have been. Compare to budget and flag if it's materially different from what was budgeted. If the numbers are off, leadership needs to adjust tiers before reps are notified.
🟡 CSM Retention Query — Not Yet Written
CSM-1 exists (reactivation kicker) but the monthly retention rate calculation and tiered bonus query (CSM-2 / CSM-3) are not in the SQL file. These need to be written, tested, and validated before the first July payroll run. Reference the retention tier table at §5.1.
🟡 Momentum Stability Buffer — Prior Tag Storage
The 2-month stability buffer for momentum tag changes requires storing last month's tag. TSM-MOMENTUM calculates the current tag, but there's no persist mechanism for prior month's tag. Need to either (a) create a partitioned analytics table that stores monthly tags by practice_id, or (b) build the prior-month lookup into TSM-MOMENTUM via a BigQuery snapshot. Either way, this must be in place before TSM-MOMENTUM is reliable.
🟡 GPO / Franchise — Comp Structure Incomplete (TBD — pending stakeholder approval)
PART-2 tracks GPO provider activations ($100/provider). The $2,500 flat GPO signing fee is referenced in the reference guide but does not have a corresponding query. Need a PART-3 query (or Finance manual process) for the $2,500 flat signing fee. Franchise network comp is also not fully defined — separate from the GPO per-provider model. GPO and Franchise Network compensation mechanics have not yet received stakeholder approval — no queries should be built or run for these channels until approval is confirmed and communicated. §7.3 GPO

Query File

All commission queries for the 2026 plan are in a single SQL file. Open it directly in your editor or BigQuery console.

📄 2026_Commission_Queries.sql

Each query uses orders_enriched only — never sales_data_bob. All joins use practice_id as the non-PHI key.

What Else Is Missing — Broader Gaps

These are items the Analytics team should flag up — they may be owned by other teams but have Analytics dependencies or sequencing implications.

Finance Handoff Document

Who processes the actual commission payments? What format do Finance need the payroll outputs in? Are TSM-5 and AE-5 query outputs going directly to payroll or does Finance have a manual approval step? No document exists for this yet.

Territory Validation Before July 1

book_of_business_history drives all TSM-tier payroll and management overrides. If the territory mapping is wrong, every payroll number is wrong. Need a territory audit session before June 30 — ideally using the backward simulation output to spot-check against known rep performance.

Certification Exam Update

exam.html likely has questions based on the old comp plan structure. If reps are taking the certification before July 1, the questions need to reflect the upfront signing bonus + clawback model (not tranches) and the new CSM tier mechanics.

Backdating Policy

For clinics currently active and TSMs who already have a baseline established — what is the baseline-setting process as of July 1? Do we use June trailing data? Is there a special one-time baseline calculation for launch month? This needs a defined answer before TSM-1 can run for July.

GP Milestone — Team vs. Individual

The $50M GP milestone (PART-HUNTER-4) is written as per-rep. Is that correct? Or is it a shared team milestone where all Partnership reps qualify if the team collectively hits $50M? This materially changes the query logic and incentive design. Decision needed from leadership.

SLA Contract Template Sign-Off

Before any TSM or Partnership rep uses an SLA contract, there needs to be a signed, legal-reviewed SLA template. SLA_Contract_Template.docx exists — has Legal reviewed it? Are Partnership deal agreements covered under a separate template?