Churn Risk Analyzer

1. Overview

The Churn Risk Analyzer reviews a customer’s recent support interactions and key account details, calculates a risk score for each account, and flags any accounts that appear to be at high risk of churn. The output is a concise list of flagged accounts with a risk score, a risk level, and the reasons behind the flag.

2. Business Value

• Early warning – Gives Customer Success Managers (CSMs) a data‑driven early‑warning system to intervene before a customer leaves.
• Prioritization – Lets CSMs focus their time on the accounts most likely to churn, increasing retention rates.
• Objective decision‑making – Turns qualitative support notes into an objective, repeatable score that can be tracked over time.

3. Operational Context

4. Inputs

Customer Metadata Fields

(All date values must be in YYYY‑MM‑DD format. The list must contain one row for each account to be evaluated.)

5. Outputs

Note – The SOP never creates a new system ID; it uses the existing Account ID from the input metadata.

High‑Risk Account Flags – Field Detail

6. Detailed Plan & Execution Steps

1. Collect the inputs – Verify that the PDF file for each account and the associated Customer Metadata list are available.

2. Extract ticket data – From the PDF, pull each ticket’s: • Date • Status • Agent notes • Any existing sentiment label.

3. Calculate per‑account metrics:

   • Ticket Volume = number of tickets in the last 30 days (from metadata).
   • Open Ticket Count = count of tickets with status “Open” (from PDF).
   • Average Sentiment = average of all sentiment scores; if missing, assign 50 (neutral).
   • Usage Ratio = Monthly Usage ÷ “Average monthly usage” benchmark (set in Appendix C).
   • Days Since Last Ticket = today’s date minus Last Ticket Date.
   • Days to Expiration = Contract Expiration Date – today’s date.

4. Apply the scoring algorithm (see Appendix C for weight values).

   • Ticket Volume (weight 0.30): Score = (10 × Number of tickets ÷ 10) (capped at 100).
   • Open Tickets (weight 0.20): Score = Open × 15 (max 100).
   • Sentiment (weight 0.20): Score = 100 – Average Sentiment (lower sentiment adds more risk).
   • Usage (weight 0.15): Score = 100 – (Usage Ratio × 100) (lower usage adds more risk).
   • Contract Proximity (weight 0.10): Score = (90 – Days to Expiration ÷ 1 day) (capped at 100).

   Risk Score = sum of the weighted sub‑scores. Round to two decimals.

5. Identify high‑risk accounts – If Risk Score ≥ 80, add the account to the High‑Risk Account Flags list.

6. Determine reasons for flag – For each high‑risk account, record any of the following that contributed a sub‑score ≥ 15:

   • “High ticket volume” (Ticket Volume sub‑score ≥ 15)
   • “Multiple open tickets” (Open Ticket sub‑score ≥ 15)
   • “Low sentiment” (Sentiment sub‑score ≥ 15)
   • “Low usage” (Usage sub‑score ≥ 15)
   • “Contract near expiration” (Contract Proximity sub‑score ≥ 15)

   List each reason as a separate bullet in the Reasons for Flag cell.

7. Create the output table – Populate the High‑Risk Account Flags table with the fields defined in Section 5.

8. Optional summary – Count total high‑risk accounts and compute the average risk score across them, then write a single‑sentence summary if requested.

9. Review and finalize – Ensure the output matches the formatting rules, then deliver the table.

7. Validation & Quality Checks

If any check fails, flag the affected account(s) for Manual Review and do not include them in the final output.

8. Special Rules / Edge Cases

9. Example

Input

• Support Log Document (PDF) – 2 tickets for “Acme Corp.” (Account ID: AC‑001)

  Ticket 1: 2025-07-15 | Status: Open | Issue: “Billing discrepancy” | Agent notes: “Customer upset about over‑charge. Sentiment: 25”
  Ticket 2: 2025-07-20 | Status: Closed | Issue: “Login problem” | Agent notes: “Issue resolved. Sentiment: 70”
  

• Customer Metadata

  [
    {
      "Account Name": "Acme Corp.",
      "Account ID": "AC-001",
      "Current Plan": "Premium",
      "Monthly Usage (units)": 1200,
      "Number of Support Tickets (Last 30 days)": 2,
      "Number of Open Tickets": 1,
      "Average Sentiment Score": 47,
      "Last Ticket Date": "2025-07-20",
      "Contract Expiration Date": "2025-12-15",
      "Last Payment Date": "2025-06-30"
    }
  ]
  

Output

High‑Risk Account Flags

Account Name   | Account ID | Risk Score | Risk Level | Reasons for Flag
--------------------------------------------------------------
Acme Corp.   | AC-001   | 84.30   | High     | High ticket volume (2 tickets in last 30 days); Low sentiment (average 47); 1 open ticket

Explanation:

• Ticket volume (2 tickets) → 30 pts (30 % of 100).
• Open tickets (1) → 15 pts.
• Average sentiment (47) → 53 pts (100 – 47).
• Usage (1200/average 1500) → 10 pts (low usage).
• Contract proximity (≈150 days left) → 10 pts.
• Total = 30 + 15 + 53 + 10 + 10 = 118 → capped at 100 → final 84.30 after weighting.
• Because the score ≥ 80, the account is listed as a high‑risk flag with the three key reasons.

Appendix A – FAQ

1. What if the sentiment score is missing in the ticket notes? The system assigns a neutral sentiment score of 50 for that ticket, which contributes no additional risk.

2. Do I need to include every ticket from the last 30 days? Yes. The more complete the log, the more accurate the risk score. If a ticket is missing, the score may be slightly lower, but the process still runs.

3. Can the model be used for a single account or a whole list? Both. The input list can contain one or many account objects; the process handles each entry independently.

4. What does a “High‑risk” label mean? A “High‑risk” account has a Risk Score ≥ 80, indicating a strong likelihood of churn without intervention.

5. What if the PDF cannot be opened? Mark that account with “Error – Unable to process PDF” and exclude it from the final flag list.

6. How frequently should the analysis be run? Recommended monthly to align with monthly health‑check cycles; can also be run before a major renewal period.

7. What if the “Contract Expiration Date” is missing? The contract‑expiration component is omitted for that account; the risk score is calculated without it.

8. Can the weighting of the score components be changed? Yes. Adjust the weights in Appendix C to match your organization’s priorities.

9. What if a customer has no tickets at all? They receive a Risk Score of 0 (no risk from support activity) and are not flagged as high‑risk.

10. What does “Average Sentiment Score” represent? A numeric value between 0 (very negative) and 100 (very positive). It is the average of all sentiment scores (or default 50 if none).

Appendix B – Glossary

• Account Name – The business name or brand that the company works with.
• Account ID – The customer‑facing identifier used in internal systems (e.g., “AC‑001”).
• Current Plan – Subscription or service level the customer is paying for (e.g., “Premium”).
• Monthly Usage – The amount of product or service units consumed during the last month (units can be calls, transactions, seats, etc.).
• Ticket – A single support interaction (e.g., a help‑desk ticket, chat conversation, or email thread).
• Open Ticket – A ticket that has not been resolved or closed.
• Average Sentiment Score – A numeric measure of the positivity/negativity of the conversation, ranging from 0 (very negative) to 100 (very positive).
• Risk Score – A calculated number (0–100) that quantifies the likelihood a customer will churn. Higher numbers mean higher risk.
• Risk Level – Classification based on the Risk Score: High (≥ 80), Medium (50‑79), Low (< 50). In this SOP we only output High.
• Reasons for Flag – List of the key drivers that pushed an account into the high‑risk category.
• Contract Expiration Date – The calendar date when the current contract ends.
• Last Payment Date – The date on which the latest invoice was paid.
• High‑Risk Account Flags – The final table of accounts flagged as high‑risk, containing the fields described in the Outputs section.

Appendix C – Reference Materials

1. Risk Scoring Weights (default)

Baseline Usage is defined as the average monthly usage across all accounts for the past 6 months (available from the internal reporting dashboard).

2. Sentiment Categories

If a ticket contains no sentiment information, treat it as Neutral (50).

3. Reason Codes (used in “Reasons for Flag”)

4. Formatting Guidelines

• Dates: All dates must be in YYYY‑MM‑DD format.
• Numbers: Use two decimal places for Risk Score (e.g., 84.30).
• Reason List: Separate multiple reasons with semicolons; each reason should be a short phrase (max 5 words).
• Table: Use a header row; align columns with spaces or tabs for readability.
• No IDs: Do not generate new IDs. Use existing Account ID from the metadata.

5. Example of Scoring Calculation (for reference)

The final Risk Score is the weighted sum of the above components, capped at 100 then rounded to two decimals.

6. Error Handling

• File Not Readable: Record “Error – File unreadable” and skip.
• Missing Field: Record “Error – Missing <Field Name>” and skip.
• Unexpected Format: Record “Error – Unexpected format” and skip.

All error cases should be logged in a separate Processing Log (not part of the SOP output) for follow‑up by the operations team.

Additional Notes

• Data consistency is crucial: ensure the Account ID used in the metadata matches the one referenced in the PDF file name (if applicable).
• When working with a large batch of accounts, batch the PDFs but still treat each account individually—do not mix ticket data between different accounts.
• If a Customer Metadata entry contains extra fields that are not needed for the risk calculation (e.g., “Customer Loyalty Score”), ignore those fields for the purposes of this SOP.
• The Risk Scoring weights can be updated once a quarter based on analysis of churn outcomes. Keep the Risk Scoring Weights table up‑to‑date in Appendix C.
• If the High‑Risk Account Flags output is empty (no high‑risk accounts), still return the table header and an explanatory note: “No high‑risk accounts identified in this run.”

**