SuperAPI AI Directory

dunning-cohort-builder

General & other

View repository

Updated 10-16 06:51

About this skill

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)

Component Weight What it measures
amount 0.35 bcy_amount at risk — primary revenue signal
ltv 0.30 Customer lifetime value
ltd 0.20 Lifetime duration in days — customer loyalty
retry 0.15 Retry count — higher retries = fewer chances left = more urgent

Customer tier thresholds

LTV tiers (billing currency)

Tier Condition
high LTV ≥ 5000
medium LTV ≥ 1000
low LTV < 1000

LTD tiers (days active)

Tier Condition
long_term LTD ≥ 365
standard LTD ≥ 90
new LTD < 90

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

Priority tier floors

Tier Label Composite score floor Override rule
P0 Emergency ≥ 75 Also P0 if retry_number ≥ 3 AND is_high_value == true
P1 High ≥ 50 —
P2 Medium ≥ 25 —
P3 Low < 25 —

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:

Field Value
error_code normalised gateway_error_code
error_messages distinct gateway_error_message values in the group
subscription_count count
total_bcy_amount sum of bcy_amount
avg_ltv mean ltv
avg_ltd mean ltd
p0_count count where priority_tier == "P0"
p1_count count where priority_tier == "P1"
high_value_count count where is_high_value == true
max_retry max retry_number
subscriptions full list, sorted by composite_score DESC

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.