Dunning Cohort Builder

Dunning Cohort Builder

Fetch, score, and group all subscriptions currently in dunning for downstream action planning.

Priority Scoring Rules

Weights (must sum to 1.0)

Customer tier thresholds

LTV tiers (billing currency)

LTD tiers (days active)

A subscription is high-value if ltv_tier == "high" OR ltd_tier == "long_term".

Priority tier floors

Step 0 — Setup

Date scope: Default to RetryDate.ThisMonth. Accept overrides from the user.

Announce before fetching:

"Fetching all dunning subscriptions for this month. Want a different time window?"

If called by the orchestrator, accept scope directly — don't re-prompt.

Step 1 — Fetch the dunning cohort

Call ZohoBilling_get_subscriptions_dunning_report with:

{
  "query_params": {
    "filter_by": "RetryDate.ThisMonth",
    "per_page": 200,
    "page": 1,
    "sort_column": "bcy_amount",
    "sort_order": "D",
    "select_columns": [
      {"field": "subscription_number",    "group": "subscriptions"},
      {"field": "customer_name",          "group": "subscriptions"},
      {"field": "email",                  "group": "subscriptions"},
      {"field": "plan_name",              "group": "subscriptions"},
      {"field": "product_name",           "group": "subscriptions"},
      {"field": "bcy_amount",             "group": "subscriptions"},
      {"field": "fcy_amount",             "group": "subscriptions"},
      {"field": "ltv",                    "group": "subscriptions"},
      {"field": "ltd",                    "group": "subscriptions"},
      {"field": "retry_number",           "group": "subscriptions"},
      {"field": "next_retry_at",          "group": "subscriptions"},
      {"field": "gateway_error_code",     "group": "subscriptions"},
      {"field": "gateway_error_message",  "group": "subscriptions"},
      {"field": "invoice_number",         "group": "subscriptions"},
      {"field": "last_sent_date",         "group": "subscriptions"}
    ]
  }
}

Paginate while has_more_page: true, incrementing page by 1 each call. Collect all records into a single flat list before scoring.

If the org has multiple organisations, retrieve org ID via list_organizations first and pass it in the X-com-zoho-subscriptions-organizationid header.

Step 2 — Normalise records

For each subscription record:

1. gateway_error_code: lowercase and trim. If empty or null → "unknown".
2. gateway_error_message: trim. If empty or null → "No error message returned".
3. Numeric fields (bcy_amount, ltv, ltd, retry_number): parse to numbers; treat null/missing as 0.
4. next_retry_at: parse to date; compute hours_until_next_retry from now (negative if overdue).

Step 3 — Compute priority scores

Component scores (each 0–100)

Normalise against cohort maximums so scores stay in range. If a cohort max is 0, set all scores for that component to 0.

max_amount = max(bcy_amount) across all records
max_ltv    = max(ltv)         across all records
max_ltd    = max(ltd)         across all records
max_retry  = max(retry_number) across all records

score_amount(r) = (r.bcy_amount   / max_amount) * 100
score_ltv(r)    = (r.ltv          / max_ltv)    * 100
score_ltd(r)    = (r.ltd          / max_ltd)    * 100
score_retry(r)  = (r.retry_number / max_retry)  * 100

Composite score

composite_score(r) = (0.35 × score_amount) + (0.30 × score_ltv) + (0.20 × score_ltd) + (0.15 × score_retry)
# Round to 1 decimal place

Customer tier

Apply LTV and LTD thresholds from the rules table above.

is_high_value = true if ltv_tier == "high" OR ltd_tier == "long_term".

Priority tier

Apply in order:

1. If composite_score ≥ 75 → P0
2. If retry_number ≥ 3 AND is_high_value == true → P0 (override)
3. If composite_score ≥ 50 → P1
4. If composite_score ≥ 25 → P2
5. Otherwise → P3

Step 4 — Group by gateway error code

Error groups

Group by normalised gateway_error_code. For each group compute:

Sort groups by total_bcy_amount DESC.

Cohort summary

total_subscriptions   count of all records
total_bcy_at_risk     sum of all bcy_amount
p0_count              …by priority_tier
p1_count
p2_count
p3_count
high_value_at_risk    count where is_high_value == true
dominant_error_code   error_code with highest subscription_count
dominant_error_pct    (dominant group count / total) × 100

Step 5 — Emit cohort output

Print a brief diagnostic:

Dunning cohort built: <total_subscriptions> subscriptions | <total_bcy_at_risk> at risk
P0: <n> | P1: <n> | P2: <n> | P3: <n> | High-value at risk: <n>
Top error code: <dominant_error_code> (<dominant_error_pct>%)

Then pass the structured cohort JSON to the action planner.

Edge Cases

• Empty cohort: print "No subscriptions currently in dunning for the selected period." and stop.
• ltv = 0 for all records: set all score_ltv = 0; note in diagnostic.
• retry_number = 0 for all: set all score_retry = 0; note in diagnostic.
• Pagination > 10 pages: warn "Large cohort — paginating through all records" and continue.