Catching usage contraction before the renewal, and what the agent may do alone

Compute usage contraction in the warehouse as dollars at risk, sync three fields to the CRM account, and write down what a renewal agent may do without a named approver.

Metered usage Contraction signal CRM account Renewal agent Approval gate Proposal log Playbook file Named approver dollars at risk, driver three fields, daily sync inputs and outcome discounts beyond band, escalation
Usage becomes dollars at risk in the warehouse; the renewal agent acts within the playbook and sends the rest to a named approver.
On this page
  1. Dollars at risk, computed where the usage lives
  2. Three fields and an as-of date
  3. The playbook says what the agent may do alone
  4. Some contraction is a support case
  5. A batch rewrite, five months before renewal
  6. Every proposal goes in the log
  7. Hidden fields for the first month

Buyers on committed-use contracts usually see a shortfall before their vendor does. Their finance team reads the usage invoice every month, divides spend by the commit, and knows by renewal how small a number to sign. The vendor’s CRM account shows a renewal date and nothing about the run rate.

I think the vendor should know first, and that’s mostly a data problem. A renewal agent that drafts offers is the easy part to demo. Underneath it I want a contraction signal I’d trust enough to act on, and a written line between what the agent drafts and what it does alone.

Dollars at risk, computed where the usage lives

Contraction is a warehouse calculation, because that’s where metered usage sits, rated at contract prices, next to the commits.

For each account and product line, I compare the trailing 28 days of usage with two baselines: the committed run rate, and the same 28 days thirteen weeks earlier. Whole weeks keep the weekday mix even in both windows.

Usage below commit but flat against last quarter is an account that never ramped. Usage that fell is a drop with a date on it, and someone can ask what changed that week. The level rules use both: a big shortfall is act-level either way, a moderate one only if it’s also falling.

The output is annualized dollars at risk and a top driver, the product line and workspace that fell most. I’d skip the health score. A 62 out of 100 tells a customer success manager (CSM) to worry. “$150K, batch compute, one workspace” tells them whom to call and what to ask.

-- contraction_signal.sql: one row per account with a live commit, rebuilt daily.
-- billing.usage_daily: one row per account, product line, workspace and day,
-- with billed_amount (NUMERIC) rated at contract prices.
-- billing.commit_lines: one row per account and product line per contract term,
-- with committed_annual_amount (NUMERIC).
-- A pooled commit is split by the mix in the contract's first full quarter.
-- Thresholds are starting points. Tune them from the proposal log.
WITH params AS (
  SELECT CURRENT_DATE - 1 AS as_of
),
commits AS (
  SELECT c.account_id, c.product_line, c.committed_annual_amount
  FROM billing.commit_lines AS c
  CROSS JOIN params AS p
  WHERE p.as_of BETWEEN c.term_start AND c.term_end
),
workspace_usage AS (
  -- Trailing 28 days, and the same 28 days thirteen weeks earlier.
  SELECT
    u.account_id,
    u.product_line,
    u.workspace_id,
    SUM(CASE WHEN u.usage_date > p.as_of - 28
             THEN u.billed_amount ELSE 0 END) * 365.0 / 28 AS trailing_annual,
    SUM(CASE WHEN u.usage_date <= p.as_of - 91
             THEN u.billed_amount ELSE 0 END) * 365.0 / 28 AS prior_q_annual
  FROM billing.usage_daily AS u
  CROSS JOIN params AS p
  WHERE (u.usage_date > p.as_of - 28 AND u.usage_date <= p.as_of)
     OR (u.usage_date > p.as_of - 119 AND u.usage_date <= p.as_of - 91)
  GROUP BY u.account_id, u.product_line, u.workspace_id
),
lines AS (
  SELECT
    c.account_id,
    c.product_line,
    c.committed_annual_amount,
    COALESCE(SUM(w.trailing_annual), 0) AS trailing_annual,
    COALESCE(SUM(w.prior_q_annual), 0)  AS prior_q_annual
  FROM commits AS c
  LEFT JOIN workspace_usage AS w
    ON w.account_id = c.account_id
   AND w.product_line = c.product_line
  GROUP BY c.account_id, c.product_line, c.committed_annual_amount
),
top_line AS (
  SELECT account_id, product_line
  FROM (
    SELECT
      account_id,
      product_line,
      committed_annual_amount - trailing_annual AS shortfall,
      ROW_NUMBER() OVER (
        PARTITION BY account_id
        ORDER BY committed_annual_amount - trailing_annual DESC, product_line
      ) AS rn
    FROM lines
  ) AS ranked
  WHERE rn = 1 AND shortfall > 0
),
top_workspace AS (
  -- Only a workspace that actually fell. A line that never ramped has none.
  SELECT account_id, workspace_id
  FROM (
    SELECT
      w.account_id,
      w.workspace_id,
      w.prior_q_annual - w.trailing_annual AS fall_annual,
      ROW_NUMBER() OVER (
        PARTITION BY w.account_id
        ORDER BY w.prior_q_annual - w.trailing_annual DESC, w.workspace_id
      ) AS rn
    FROM workspace_usage AS w
    JOIN top_line AS t
      ON t.account_id = w.account_id
     AND t.product_line = w.product_line
  ) AS ranked
  WHERE rn = 1 AND fall_annual > 0
),
accounts AS (
  SELECT
    account_id,
    SUM(committed_annual_amount) AS committed_annual,
    SUM(trailing_annual)         AS trailing_annual,
    SUM(prior_q_annual)          AS prior_q_annual
  FROM lines
  GROUP BY account_id
),
scored AS (
  SELECT
    a.account_id,
    a.committed_annual,
    GREATEST(a.committed_annual - a.trailing_annual, 0) AS dollars_at_risk,
    GREATEST(a.committed_annual - a.trailing_annual, 0)
      / NULLIF(a.committed_annual, 0) AS at_risk_share,
    (a.trailing_annual - a.prior_q_annual)
      / NULLIF(a.prior_q_annual, 0) AS change_vs_prior_q
  FROM accounts AS a
)
SELECT
  s.account_id,
  p.as_of,
  ROUND(s.committed_annual, 0)  AS committed_annual,
  ROUND(s.dollars_at_risk, 0)   AS dollars_at_risk,
  ROUND(s.change_vs_prior_q, 3) AS change_vs_prior_q,
  CASE
    WHEN s.at_risk_share >= 0.25 THEN 'act'
    WHEN s.at_risk_share >= 0.10 AND s.change_vs_prior_q <= -0.10 THEN 'act'
    WHEN s.at_risk_share >= 0.05 OR s.change_vs_prior_q <= -0.10 THEN 'watch'
    ELSE 'none'
  END AS signal_level,
  CASE WHEN t.product_line IS NOT NULL
       THEN t.product_line
            || COALESCE(', workspace ' || CAST(tw.workspace_id AS VARCHAR), '')
  END AS top_driver
FROM scored AS s
CROSS JOIN params AS p
LEFT JOIN top_line AS t ON t.account_id = s.account_id
LEFT JOIN top_workspace AS tw ON tw.account_id = s.account_id;

Gate the job on the metering pipeline’s completeness check. A day of late usage files looks exactly like a customer leaving.

Three fields and an as-of date

The CRM shows the signal but doesn’t compute it. A daily job upserts three read-only fields onto the account, plus the date they describe. A second copy computed in the CRM would drift within a quarter, and people would believe that one.

Field Type Refresh As-of
contraction_level Picklist: none, watch, act Daily upsert by account ID Shares contraction_as_of
contraction_dollars_at_risk Currency, annualized Same upsert Shares contraction_as_of
contraction_driver Text(255): product line and workspace Same upsert Shares contraction_as_of
contraction_as_of Date: last day of the 28-day window Same upsert Stale after 48 hours

A failed run leaves the last good values in place with their old date, and never blanks them. Past the playbook’s age limit, the agent treats the signal as missing. Drafts land on the record the CSM already has open, as in putting an LLM inside the CRM.

The playbook says what the agent may do alone

I want the line between drafting and doing in a file people review. A sentence in a system prompt gets lost in the next prompt edit, and nobody notices until a discount goes out.

The renewal agent reads a playbook keyed by signal level and contract size. Each rule lists what the agent may do alone and what needs a named person. In a first version, nothing the agent does alone reaches the customer.

# renewal_playbook.yml: read by the renewal agent on every run.
# Reviewed like code. The tools that release an option enforce it too.
version: 1
signal_max_age_hours: 48        # older than this counts as no signal

contract_sizes:                 # by committed annual amount, USD
  small: { below: 100000 }
  mid: { below: 750000 }
  large: { below: null }        # everything else

discount_band_pct:              # off the price book, without a person
  small: 10
  mid: 8
  large: 5

price_book_terms_months: [12, 24, 36]

route_to_support:               # checked before any renewal action
  when_any:
    - { signal: error_rate_change_vs_prior_q, at_least: 0.5 }
    - { signal: failed_integration_days_28d, at_least: 3 }
  action: open_support_case
  pause_renewal_actions_days: 14

rules:                          # a level or size with no rule gets no action
  - level: watch
    sizes: [small, mid, large]
    allowed_alone: [summarize_account]
    needs_approval: []
  - level: act
    sizes: [small, mid]
    # hold_csm_calendar_slot is internal; the CSM sends any customer invite.
    allowed_alone: [summarize_account, hold_csm_calendar_slot, draft_renewal_options]
    needs_approval:
      - { action: discount_beyond_band, approver_role: deal_desk }
      - { action: terms_outside_price_book, approver_role: deal_desk }
      - { action: executive_escalation, approver_role: cs_director }
  - level: act
    sizes: [large]
    allowed_alone: [summarize_account, draft_renewal_options]
    needs_approval:
      - { action: hold_csm_calendar_slot, approver_role: account_csm }
      - { action: discount_beyond_band, approver_role: deal_desk }
      - { action: terms_outside_price_book, approver_role: deal_desk }
      - { action: executive_escalation, approver_role: vp_customer_success }

Each approver role resolves to a named person when the request goes out, and the log records who. The tool that releases an option checks the band itself, so a model that misreads the playbook still can’t get a 12% discount out the door. If finance already keeps discount thresholds in an approval table, as in the quote-to-cash post, read the band from there.

Some contraction is a support case

A customer that stopped needing a workload is a commercial question. A workload failing on the platform belongs to support. If errors on the driving workspace are climbing or an integration keeps failing, the customer wants the bug fixed, and an offer of rollover credits suggests nobody noticed. The route_to_support block opens a case and pauses renewal actions for two weeks. It needs two columns the query above leaves out, error rate and failed integration days.

A batch rewrite, five months before renewal

Run the playbook on a fictional account: a data-platform company that bills on usage, and a logistics customer committed to $480K a year, about $395K of it on the batch compute line.

Seats are flat, and the CRM account looks fine. In late spring the customer’s data team rewrote the nightly job behind most of that compute to process only changed rows. Trailing 28-day batch compute now sits 38% below its committed run rate, and about as far below the same weeks last quarter. That’s roughly $150K a year at risk, with the driver field naming batch compute and one workspace. Error rates are flat, so nothing routes to support.

Nobody’s unhappy. The customer got better at using the product, and the vendor should be the one to say so, before the customer brings the usage report to the renewal call.

The account is mid-size, at the act level. Five months before renewal, the agent writes the account summary, holds check-in time on the CSM’s calendar, and drafts two options from the price book: a smaller commit on a two-year term at the standard rate, or the current commit at a 12% discount with unused credits rolling over. The mid-size band is 8%, so only the first goes into the check-in prep. The second goes to the deal desk with its inputs, unsent.

Every proposal goes in the log

Each proposal writes one row: the signal and its as-of date, the playbook version, the options and prices, what the agent did alone, and who approved what. When the renewal closes, the row gets the signed commit, the term and the option chosen.

That’s what the playbook gets tuned from. If the deal desk approves nearly every mid-size request between 8 and 12%, the band is in the wrong place. If act-level accounts keep renewing at full commit, the thresholds are too eager.

Hidden fields for the first month

A CSM who calls about a drop that was really a metering gap spends the next real call rebuilding trust, so for the first month the job and the agent write only to the log and the CRM fields stay hidden.

Each Friday someone in customer success goes down the act-level list and notes which accounts actually shrank. Seasonal businesses will show up, since a quiet season looks like contraction against the quarter before. Every account that didn’t shrink points at a threshold or baseline to move, and the fields go live once a Friday pass finds only a few.