SuperAPI AI Directory

collection-performance-analysis

General & other

View repository

Updated 10-16 06:52

About this skill

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:

# Tool Purpose Key params
1 ZohoBilling_get_customer_payments_report Payments received — current period filter_by: PaymentDate.CustomDate, from_date: <cm_start>, to_date: <cm_end>, sort_column: payment_date, sort_order: D, per_page: 100, page: 1
2 ZohoBilling_get_customer_payments_report Payments received — prior period same, from_date: <lm_start>, to_date: <lm_end>
3 ZohoBilling_get_refund_history_report Refunds — current period filter_by: RefundDate.CustomDate, from_date: <cm_start>, to_date: <cm_end>, per_page: 100, page: 1
4 ZohoBilling_get_refund_history_report Refunds — prior period same, from_date: <lm_start>, to_date: <lm_end>
5 ZohoBilling_get_payment_failures_report Failed payments — current period filter_by: FailureDate.CustomDate, from_date: <cm_start>, to_date: <cm_end>, per_page: 100, page: 1
6 ZohoBilling_get_payment_failures_report Failed payments — prior period same, from_date: <lm_start>, to_date: <lm_end>

After parallel batch (current period only):

# Tool Purpose
7 ZohoBilling_get_renewal_failure_report Renewal failures — current period (involuntary churn signal)

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

Metric Formula
Total collected (CP) API total from call #1
Total collected (PP) API total from call #2
MoM Δ CP − PP
MoM % (CP − PP) / PP × 100
Payment count CP / PP row counts from calls #1 and #2
Avg payment value CP total_CP / payment_count_CP
Avg payment value PP total_PP / payment_count_PP

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

Metric Formula
Total refunds CP / PP API totals from calls #3 and #4
Refund count CP / PP row counts
Refund rate CP total_refunds_CP / total_collected_CP × 100
Refund rate PP total_refunds_PP / total_collected_PP × 100
MoM refund rate Δ refund_rate_CP − refund_rate_PP (pp)

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

2d. Payment failures

Metric Formula
Failed count CP / PP row counts from calls #5 and #6
Failed amount CP / PP API totals
Failure rate CP failed_count_CP / (failed_count_CP + payment_count_CP) × 100
Failure rate PP failed_count_PP / (failed_count_PP + payment_count_PP) × 100
MoM Δ failure_rate_CP − failure_rate_PP (pp)

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.