Collection Performance Analysis

Collection Performance Analysis

Produces a same-period MoM collections comparison across four data streams: payments received, refunds issued, payment failures, and renewal failures. Both windows span the same number of days for a like-for-like comparison.

Step 0 — Setup

Org ID: Retrieve via list_organizations if not already known. Ask if multiple orgs exist — never guess.

Date windows — use the same logic as sales-performance-analysis (so all pillars are always comparable). Compute and confirm once:

today         = current date
cm_start      = first day of the current calendar month
cm_end        = today
lm_start      = first day of the prior calendar month
lm_end        = prior month's day number matching today's day
              → May 19 run: cm = May 1–19, lm = April 1–19
              → if today is May 31 and prior month has 30 days: lm_end = April 30

State the windows and offer custom dates:

"Comparing May 1–19 vs April 1–19. Want to use different dates instead?"

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

Step 1 — Fetch all data (6 calls in parallel, then 1 more)

Parallel batch:

After parallel batch (current period only):

Paginate all calls while has_more_page: true. Use API total fields for period sums — do not aggregate rows when pagination is in play.

Step 2 — Compute collections metrics

2a. Payments received

2b. Collection efficiency

Collection efficiency = payments collected as a fraction of gross invoiced.

If called standalone (not via orchestrator), pull invoice totals:

• Call ZohoBilling_get_invoice_details_report with both date windows (summary totals only — per_page: 1, use the API total field).

If called via orchestrator, use gross_invoiced_cp and gross_invoiced_pp from the sales-performance-analysis output directly.

efficiency_CP = total_collected_CP / gross_invoiced_CP × 100
efficiency_PP = total_collected_PP / gross_invoiced_PP × 100

Note: values can exceed 100% when prepayments or credit applications are counted as payments — flag this rather than treating it as an error.

2c. Refunds

From refund detail rows: top 3 customers by refund amount. Most common reason if the field is populated.

2d. Payment failures

From failure detail rows: top 3 failure reasons by count. Customers with > 1 failure in CP (card likely needs update — flag for outreach).

2e. Renewal failures (current period only)

From call #7:

• Count of renewal failures
• Total MRR at risk
• Top 5 customers by MRR at risk

These are forward-looking — they signal involuntary churn that will hit next month's subscriber count if not resolved.

2f. Net collections position

net_collected_CP = total_collected_CP − total_refunds_CP
net_collected_PP = total_collected_PP − total_refunds_PP
mom_net_pct      = (net_CP − net_PP) / net_PP × 100

Step 3 — Produce the analysis output

## Collection Performance — <period_label_cm> vs <period_label_lm>

### Collections Summary
| Metric | <PP label> | <CP label> | Δ |
|---|---|---|---|
| Total collected | $X | $Y | +$Z (+N%) |
| Refunds issued | $X | $Y | +$Z |
| Net collected | $X | $Y | +$Z (+N%) |
| Collection efficiency | N% | N% | +Npp |
| Payment count | N | N | +N |
| Avg payment value | $X | $Y | +$Z |

**Collections MoM: <direction and magnitude in plain English>**

### Payment Failures
| Metric | <PP label> | <CP label> | Δ |
|---|---|---|---|
| Failed attempts | N | N | +N |
| Failure rate | N% | N% | +Npp |
| Failed amount | $X | $Y | +$Z |

Top failure reasons (CP): <reason 1> (N), <reason 2> (M)
Repeated failures: <N customers — flag for card update>

### Refund Breakdown
- CP: $X across N refunds (N% of collected)
- PP: $Y across M refunds (N% of collected)
- Top refunded: <customer> ($X), <customer> ($Y)

### Renewal Failures — Forward-Looking
- N renewal failures · $X MRR at risk in current period
- Top 5 at-risk: <customer> ($MRR), ...

Output contract (for orchestrator)

{
  "pillar": "collections",
  "metrics": {
    "total_collected_cp": <number>,
    "total_collected_pp": <number>,
    "mom_collections_pct": <number>,
    "collection_efficiency_cp_pct": <number>,
    "collection_efficiency_pp_pct": <number>,
    "refund_rate_cp_pct": <number>,
    "refund_rate_pp_pct": <number>,
    "payment_failure_rate_cp_pct": <number>,
    "payment_failure_rate_pp_pct": <number>,
    "net_collected_cp": <number>,
    "renewal_failures_mrr_at_risk": <number>
  },
  "repeated_failure_customers": [{"name": "...", "failures": <count>}],
  "renewal_failures": [{"customer": "...", "mrr": ...}],
  "flags": ["<repeated failure customers>", "<efficiency > 100%>"]
}

Edge cases

• Zero payments: verify date window first; state explicitly if genuinely empty.
• No refunds: skip refund section; set rate = 0.
• Renewal failure report unavailable: skip that sub-section; note it.
• Efficiency > 100%: note "prepayments or credit applications included" — not an error.
• Multi-currency: use base-currency totals throughout.
• Prior month shorter than today's day: note truncation; comparison is still valid since we're comparing the same calendar range in each month.