Real questions, real prompts: how people use the product

The blended number wasn't wrong - it was just hiding which motion was actually working (Enterprise expansion) and which one was quietly leaking (SMB churn) until we cohorted it. Cohorted retention like this is a view most CRMs reserve for their top tier, if they offer it at all.

WITH cohort AS (
  SELECT s.ID, c.ID AS CompanyID,
    CASE WHEN c.numberofemployees < 250 THEN 'SMB' ELSE 'Enterprise' END AS segment,
    DATEPART(year, s.createdate) AS cohort_yr, DATEPART(quarter, s.createdate) AS cohort_q,
    s.hs_status, DATEDIFF(month, s.createdate, GETUTCDATE()) AS months_live
  FROM Subscription s JOIN Company c ON c.ID = s.CompanyID
)
SELECT segment, cohort_yr, cohort_q, COUNT(*) AS subs,
  ROUND(100e0 * SUM(CASE WHEN hs_status='active' THEN 1 ELSE 0 END) / COUNT(*), 1) AS retained_pct
FROM cohort WHERE months_live >= 18
GROUP BY segment, cohort_yr, cohort_q
ORDER BY segment, cohort_yr, cohort_q;

The as-recorded trend showed margin falling from 60.8% to 52.1% at Q2 and staying down - a five-alarm fire in any board deck. Corrected for the fact that "raw_cost" quietly started folding in logistics spend that quarter, the trend was flat at roughly 60%. The drop was an accounting artifact, not a business event - and the correction is a plain-English note the AI remembers, applied once and reused on every future margin question, not a schema migration or an analyst ticket.

SELECT DATEPART(year, d.closedate) AS yr, DATEPART(quarter, d.closedate) AS qtr,
  ROUND(100e0 * SUM(d.amount - d.raw_cost) / SUM(d.amount), 1) AS margin_as_recorded,
  ROUND(100e0 * SUM(d.amount - (d.raw_cost -
    CASE WHEN d.closedate >= '2026-04-01' THEN 0.09 * d.amount ELSE 0 END)) / SUM(d.amount), 1) AS margin_corrected
FROM Deal d
WHERE d.hs_is_closed_won = 'true'
GROUP BY DATEPART(year, d.closedate), DATEPART(quarter, d.closedate)
ORDER BY yr, qtr;

At the configured 70%, one rep's Proposal book looked more than twice another's. Adjusted for how each rep actually used the stage - one moved a deal there the moment an email landed, another only after a call confirmed real intent - the ranking flipped. The correction wasn't a data-model change; it's a note the AI applies to every future question about that pipeline.

WITH rep_hist AS (
  SELECT d.OwnerID,
    SUM(CASE WHEN d.hs_is_closed_won='true' THEN 1 ELSE 0 END) AS won,
    SUM(CASE WHEN d.hs_is_closed='true' THEN 1 ELSE 0 END) AS closed
  FROM Deal_Stage ds JOIN Deal d ON d.ID = ds.DealID
  JOIN PipelineDeal p ON p.ID = ds.PipelineStageID
  WHERE p.Label = 'Proposal'
  GROUP BY d.OwnerID
)
SELECT o.firstName + ' ' + o.lastName AS rep,
  SUM(d.amount) AS open_value,
  ROUND(100e0 * rh.won / rh.closed, 1) AS rep_win_rate,
  SUM(d.amount) * rh.won / rh.closed AS weighted_at_rep_rate
FROM Deal d
JOIN PipelineDeal p ON p.ID = d.dealstage AND p.Label = 'Proposal' AND p.IsClosed = 0
JOIN rep_hist rh ON rh.OwnerID = d.OwnerID
LEFT JOIN Owner o ON o.ID = d.OwnerID
GROUP BY o.firstName, o.lastName, rh.won, rh.closed
ORDER BY weighted_at_rep_rate DESC;

Every dollar sitting in "OnHold" was counted at 10% in the weighted forecast. Its true win rate across 27 historical deals was 0% - dead pipeline inflating the board number. "Proposal" was configured at 70% but delivered 62.7% in reality. Multiplied across a full pipeline, that gap explains the chronic difference between "forecast" and bank balance - and it's four numbers from one query against your own history.

WITH agg AS (
  SELECT d.pipeline, ds.PipelineStageID, COUNT(*) AS deals_entered,
    SUM(CASE WHEN d.hs_is_closed_won='true' THEN 1 ELSE 0 END) AS won
  FROM Deal_Stage ds JOIN Deal d ON d.ID = ds.DealID
  GROUP BY d.pipeline, ds.PipelineStageID
)
SELECT p.PipelineLabel, p.Label AS stage, p.Probability AS configured_prob,
  a.deals_entered, ROUND(100e0 * a.won / a.deals_entered, 1) AS actual_win_rate
FROM agg a JOIN PipelineDeal p ON p.ID = a.PipelineStageID AND p.Pipeline = a.pipeline
WHERE p.IsClosed = 0 AND a.deals_entered >= 25
ORDER BY p.PipelineLabel, p.DisplayOrder;

Reach and conversion ranked in opposite order. The low-volume, targeted sends converted 5–20× better than the mass-blast emails that reached thousands. Marketing headcount naturally gravitates to the campaigns with the biggest reach numbers - this view says the smaller, targeted sends were quietly doing the conversion work.

WITH eng AS (
  SELECT EmailCampaignID, ContactID, MIN(Created) AS first_touch
  FROM EmailCampaignEvent WHERE Type IN ('OPEN','CLICK') AND ContactID IS NOT NULL
  GROUP BY EmailCampaignID, ContactID
),
first_deal AS (
  SELECT cd.ContactID, MIN(d.createdate) AS first_deal_date
  FROM ContactDeals cd JOIN Deal d ON d.ID = cd.DealID
  GROUP BY cd.ContactID
)
SELECT ec.Name, COUNT(*) AS prospects_engaged,
  ROUND(100e0 * COUNT(fd.first_deal_date) / COUNT(*), 1) AS conversion_pct
FROM eng
JOIN EmailCampaign ec ON ec.ID = eng.EmailCampaignID
LEFT JOIN first_deal fd ON fd.ContactID = eng.ContactID
  AND fd.first_deal_date BETWEEN eng.first_touch AND DATEADD(day, 90, eng.first_touch)
GROUP BY ec.Name
HAVING COUNT(*) >= 25
ORDER BY conversion_pct DESC;

Raw #1 by pipeline ($7.6M, 14.2% close rate) dropped to $1.1M once weighted by his own history - nowhere near the top of the weighted board. Peter West topped the weighted leaderboard instead; Nala Adams (90.7% close rate) ranked second. Raw, weighted, and win-rate are three different rankings, and only one of them tells you who to actually coach.

WITH rep_rate AS (
  SELECT OwnerID,
    SUM(CASE WHEN hs_is_closed_won='true' THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS win_rate
  FROM Deal WHERE hs_is_closed = 'true'
  GROUP BY OwnerID
)
SELECT o.firstName + ' ' + o.lastName AS owner,
  SUM(d.amount) AS open_value,
  SUM(d.amount) * rr.win_rate AS weighted_value,
  ROUND(rr.win_rate * 100, 1) AS win_rate_pct
FROM Deal d
JOIN rep_rate rr ON rr.OwnerID = d.OwnerID
LEFT JOIN Owner o ON o.ID = d.OwnerID
WHERE d.hs_is_closed = 'false'
GROUP BY o.firstName, o.lastName, rr.win_rate
ORDER BY open_value DESC;

Win rate wasn't a straight line. Light-touch deals converted at 51%, then collapsed to 24% in the 6–40 touch range - the highest-effort, lowest-converting, biggest time-sink deals - before recovering to 59% at 41+ touches. "Log more activity" was the wrong coaching instruction; the real question was why the middle bucket was so costly.

WITH de AS (
  SELECT DealID, COUNT(*) AS eng_count FROM DealEngagements GROUP BY DealID
)
SELECT
  CASE WHEN eng_count BETWEEN 1 AND 5 THEN '1-5 touches'
       WHEN eng_count BETWEEN 6 AND 40 THEN '6-40 touches'
       ELSE '41+ touches' END AS bucket,
  COUNT(*) AS deals,
  ROUND(100e0 * SUM(CASE WHEN d.hs_is_closed_won='true' THEN 1 ELSE 0 END)
        / SUM(CASE WHEN d.hs_is_closed='true' THEN 1 ELSE 0 END), 1) AS win_rate
FROM Deal d JOIN de ON de.DealID = d.ID
GROUP BY CASE WHEN eng_count BETWEEN 1 AND 5 THEN '1-5 touches'
              WHEN eng_count BETWEEN 6 AND 40 THEN '6-40 touches'
              ELSE '41+ touches' END;

Enterprise accounts were 72% of logos and 93% of ARR - a logo-count dashboard and a revenue dashboard tell almost opposite stories about where to invest. The percentage-of-total math (a window function) is what makes the gap visible in one query instead of a spreadsheet exercise.

WITH seg AS (
  SELECT CASE WHEN numberofemployees < 1000 THEN 'Other' ELSE 'Enterprise' END AS size_band,
    annual_recurring_revenue__c AS arr
  FROM Company WHERE annual_recurring_revenue__c > 0
)
SELECT size_band, COUNT(*) AS companies, SUM(arr) AS total_arr,
  ROUND(100e0 * SUM(arr) / SUM(SUM(arr)) OVER (), 1) AS pct_of_arr,
  ROUND(100e0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_logos
FROM seg GROUP BY size_band;