메뉴
Build the retention cohort — at-risk (non-renewing) or recently churned subscriptions normalized with cancel reason, LTV, LTD, plan, and MRR. Use for any request to pull cohort data before a retention run.
Codex 또는 Claude로 설치 이 Prompt를 복사해 Codex, Claude 또는 다른 어시스턴트에 붙여 넣으면 Skill 페이지를 검토하고 설치를 진행할 수 있습니다.
SOC 직업 분류 기준
| name | churn-cohort-builder |
| description | Build the retention cohort — at-risk (non-renewing) or recently churned subscriptions normalized with cancel reason, LTV, LTD, plan, and MRR. Use for any request to pull cohort data before a retention run. |
Assembles the retention cohort from Zoho Billing reports. Two types:
| Field | Source |
|---|---|
subscription_id, customer_id, customer_name | report row |
plan_code, plan_name | report row |
mrr | recurring_amount normalized to monthly (annual ÷ 12, quarterly ÷ 3) |
currency, country | report row; "Unknown" if absent |
cancel_reason_raw | free-text from report — classifier buckets it later |
cancellation_date | scheduled end date (pre-churn) or actual cancel date (post-churn) |
ltv | report field; default 0 + flag ltv_unavailable: true if missing |
ltd | report field (days); compute from activated_at if absent; 0 + ltd_unavailable: true if both absent |
cohort | "pre_churn" or "post_churn" |
0. Verify tools — before any API calls, confirm these tools are available in the session:
ZohoBilling_get_non_renewing_subscriptions_reportZohoBilling_get_churned_subscriptions_reportZohoBilling_list_organizationsIf any are missing, stop and tell the user: "The Zoho Billing connector is connected but the tool <name> is not enabled. Go to Settings → Connectors → Zoho Billing → enable that tool, then retry."
1. Get org ID — call list_organizations if unknown; ask if multiple exist (never guess).
2. Pull reports — run in parallel if both cohorts needed:
Pre-churn: ZohoBilling_get_non_renewing_subscriptions_report
from_date: today, to_date: today+30, per_page: 100, page: 1 — paginate while has_more_page: true.
Post-churn: ZohoBilling_get_churned_subscriptions_report
from_date: today-7, to_date: today, per_page: 100, page: 1 — paginate same way.
Always use per_page: 100 — the API default of 25 multiplies round-trips 4×.
3. Normalize rows to the output schema. Pass missing cancel reasons through as empty string.
4. Write output:
<workspace>/retention-runs/<YYYY-MM-DD>/cohort-<pre_churn|post_churn>.json
Print a one-line headline (N subs, total MRR at risk) and a 10-row preview table. Note any ltv_unavailable / ltd_unavailable rows.
subscription_id, keep post-churn."" — classifier will bucket as unknown.Show Annual Recurring Revenue with QoQ and YoY trend and a growth trajectory. Use when the user asks about ARR, annual recurring revenue, annual run rate, or year-over-year revenue growth. Do NOT use for MRR, NRR, or monthly breakdowns.
Identify overdue invoices most likely heading toward write-off, while a save is still possible. Use when the user asks about bad debt, write-off risk, invoices unlikely to be recovered, or wants to see which AR is at risk of becoming bad debt. Do NOT use for general overdue lists or collections prioritisation.
List subscriptions whose payment card expires in the next 30 or 60 days, ranked by MRR at risk. Use when the user asks about expiring cards, cards about to expire, or wants to prevent involuntary churn from payment method expiry. Do NOT use for current payment failures or dunning analysis.
Show the full product catalog: all products, their plans with pricing and billing frequency, addons, and one-time items. Use when the user asks what plans are available, wants to see the product catalog, pricing, or available addons and items. Do NOT use for subscription status, customer-specific data, or coupon lookup.
Decompose churn into voluntary (customers who chose to leave) vs involuntary (failed payments) and identify which plans and products are driving losses. Use when the user asks why customers are churning, churn composition, voluntary vs involuntary split, or which products have the highest cancellation rate. Do NOT use for MRR, NRR, or retention rate questions.
Daily AR worklist: top 20 overdue invoices ranked by recovery probability (outstanding amount × payment-behaviour score). Use when the user asks who to chase for payments today, wants a collections prioritisation, or needs a daily AR action list. Do NOT use for aggregate overdue totals or bad-debt analysis.