Churn Risk Starter Kit

You are a business research analyst. I am giving you a list of account/facility pairs pasted in the chat. For each one, search the web for recent, verifiable signals that would matter to a vendor managing a service agreement for that account. ### Rules 1. **Run a separate web search for each account/facility pair.** Do not rely on your training data. If you have a deep research or multi-step search mode, use it. 2. **Every factual claim must include the URL where you found it.** Place the URL in parentheses immediately after the claim. If you cannot provide a real URL for a claim, do not include the claim. 3. **"No Signal Found" is the correct answer for most mid-market commercial accounts.** I expect to see this for the majority of the list. That is the right answer. Do not pad results to make every account look interesting. 4. **Do not fall back to your training data.** If your web search returns nothing, write "No Signal Found." Do not fill it with what you "know." Your training data may be outdated or wrong. 5. **Do not invent names, dollar figures, dates, leadership titles, or events.** If you are uncertain whether a fact came from a search result or from your training, leave it out. 6. **Recency matters.** Prioritize signals from the past 12 months. Older signals (12–24 months) are acceptable if they're material (e.g., an ownership transfer that just closed) but flag the date. ### Signal Types to Look For For each account/facility, search for: - **Ownership Change** — M&A activity, parent-company transfers, acquisitions, divestitures - **Management Change** — new management company, organizational restructuring, third-party provider changes - **Leadership Change** — new operations leader, new VP, new director, new CFO, key contact turnover - **RFP/Procurement** — posted RFPs for facility services, maintenance, or service agreements on procurement portals (state, municipal, healthcare GPO, education) - **Expansion/Capital Project** — announced renovations, new construction, expansion permits, capital project disclosures - **Facility Closure/Downsizing** — announced closures, occupancy drops, layoffs, divestitures - **Competitor Activity** — appearance on a competitor's customer-portfolio page, competitor case studies, awarded contracts to other vendors ### Output Format Use this exact table format: | Customer Name | Facility Name | Signal Type | Signal Detail | Signal Date | Source | |---|---|---|---|---|---| | [exact name] | [exact name] | [from list above, or "No Signal Found"] | [1–2 sentences with URL] | [Month YYYY or "N/A"] | [Source type with URL] | Use the customer and facility names exactly as I provide them. Do not abbreviate, rephrase, or correct spelling — the names need to match my internal data exactly so I can join this back. ### Source Types I Trust - Local business journals (Denver Business Journal, Salt Lake Tribune, regional papers) - Press releases on the customer's own site - Municipal filings (planning permits, state procurement portals) - Industry publications and trade journals relevant to the customer's vertical - Public business databases and company directories - LinkedIn (for leadership changes — but require a public post or company page, not training-data inference) ### Important I am feeding this research into another AI for a churn-risk analysis. Fabricated information will corrupt the entire downstream diagnosis. Accurate but thin results are far more valuable than detailed but unreliable results. If you only find one real signal across the whole list, give me that one signal and mark every other account "No Signal Found." Do not pad. ### Accounts to Research are Below

You are helping a field-service business leader compute health metrics across a portfolio of service agreements. Your job in this step is **calculation only** — produce a per-agreement health-signal table from the operational data. The diagnosis comes in the next prompt; do not jump ahead. ## Your Data I will upload four files and paste in one table. Read all of them carefully before producing any output. **Required:** 1. **Service agreement registry** — a CSV with one row per active service agreement. Includes agreement ID, customer and facility name, branch, agreement type, ACV, contract start and renewal dates, equipment count and age, account manager, and lead tech assignments. 2. **Service visit history** — a CSV with every visit and work order across the agreements over the last 12–18 months. Includes work order ID, agreement ID, visit type (scheduled PM, demand service, warranty callback, emergency), scheduled date, completed date, completion status, deficiency flag, and response time where available. 3. **AR & billing history** — a CSV with every invoice and payment for the agreements over the same window. Includes invoice ID, agreement ID, invoice type (contract billing, service, parts, emergency surcharge), amount, invoice date, payment date, days past due, dispute flag, and open balance. 4. **Service notes** — a CSV with narrative entries from coordinators, dispatchers, account managers, and lead techs. Includes note ID, agreement ID, date, author role, author name, and note content. **Pasted into the chat:** 5. **External signal table** — a small table I generated in a separate research chat. One row per account/facility with any external signals found. If I am uploading files in batches, wait until I tell you I have finished uploading before producing the health table. Do not begin analyzing as files arrive. ## Critical Rules Before you compute anything, internalize these rules. They override any default behavior. **Calculation integrity:** - Compute every metric directly from the data I uploaded. Do not estimate, infer, or carry forward defaults. - Use exact agreement IDs, work order IDs, invoice IDs, and customer/facility names as they appear in the data. Do not rename, abbreviate, or paraphrase any value. - If a field needed for a calculation is missing or blank for a specific agreement, mark that metric "Insufficient data" for that agreement and note the missing field. Do not substitute zeros. - Calculate metrics for **every** agreement in the registry. Do not skip any. **Evidence integrity:** - This step does not produce a diagnosis. Do not assign churn risk, do not predict non-renewal, do not recommend save plays. Those come in the next prompt. - Do not invent agreements, customers, work orders, or invoices that don't appear in the data. ## Metrics to Compute For each agreement in the registry, compute the following: ### 1. PM Compliance % - Count scheduled preventive maintenance visits (visit type indicates a scheduled PM, including any "Scheduled PM," "Quarterly PM," "Semi-annual PM," etc. labels in the data). - Count how many of those completed (any completion status containing "Completed" — including "Completed - Deficiency Found"). - PM compliance % = completed PMs / scheduled PMs × 100. - Track no-shows, cancellations, and customer-initiated reschedules separately. ### 2. Response Time (Demand & Emergency) - For demand service, warranty callback, and emergency visits, average the response time field (hours from request to on-site). - Compute three windows where data allows: full 12–18 month average, last 6 months, last 90 days. If the data window is shorter, compute what you can and note the limitation. - Flag any agreement where the last-90-day average is materially worse than the full-window average (mark "Trend: degrading"). Define "materially" as 25%+ worse. ### 3. Callback Rate - Count warranty callback visits AND any demand service visits where the work summary, notes, or visit pattern indicates a return to the same problem within 30 days. Real-world ERPs frequently log repeat service as Demand rather than Warranty Callback — read the work summary text to catch these. - Callback rate = callbacks / total demand+emergency visits × 100. ### 4. Deficiency Rate - Count visits where deficiency_flag is Yes OR completion status is "Completed - Deficiency Found." - Deficiency rate = deficiency visits / total completed visits × 100. ### 5. Cancellation / Reschedule / No-Show Pattern - Count scheduled visits with status Canceled, Rescheduled, or No-Show. - Distinguish customer-initiated (notes or work summary indicates customer postponed, canceled, or wasn't accessible) from company-initiated (tech unavailable, weather, scheduling conflict). - Customer-initiated cancellations are a churn signal. Company-initiated reschedules are an operational issue. ### 6. AR Aging Trend - For each agreement's contract billing invoices, compute average days past due across the full window AND across the last 6 months. - Flag agreements where last-6-month avg DPD is materially worse than full-window avg DPD (mark "Trend: degrading"). - Note any open balance on invoices older than 60 days. ### 7. Disputes & Credits - Count disputed invoices in the last 12 months. - Sum dispute dollar amount. - Note any credit issued and the amount. ### 8. Last-90-Day Flags For each agreement, list any of the following that occurred in the last 90 days: - Emergency visit - Warranty callback or demand-service repeat on same equipment - Customer-initiated cancellation of a scheduled PM - Disputed invoice - New open balance over 30 days - Service note flagging a customer complaint, escalation, vendor review, contact change, or competitive bid mention This last-90-day window is the urgency signal. The master analysis will weight it heavily. ## Output Format Produce a single table with one row per agreement, sorted by ACV descending. Columns: | Column | Content | |---|---| | Agreement ID | From the registry | | Customer / Facility | Customer name + facility name, exactly as in the registry | | Branch | From the registry | | Agreement Type | From the registry | | ACV | Dollar value, from the registry | | Renewal Date | From the registry | | Days to Renewal | Calculated from today's date (state the date you used) | | PM Compliance % | Calculated, with raw counts in parens (e.g., "67% (4 of 6)") | | Response Time (full / last 90d) | Average hours, both windows. Mark "Trend: degrading" if applicable. | | Callback Rate % | With raw count | | Deficiency Rate % | With raw count | | Customer-Initiated Cancellations | Count, with note IDs or WO IDs as evidence | | AR Aging (full / last 6mo avg DPD) | Both windows. Mark "Trend: degrading" if applicable. | | Disputes (count / $) | Last 12 months | | Open Balance > 60d | Dollar amount, or 0 | | Last-90-Day Flags | Bullet list of any flags from the list above | | External Signal Type | From the pasted external signal table | Format as a clean table I can copy into a spreadsheet. After the table, include: **Data quality notes (2–4 sentences, plain English):** Any agreements where a metric was "Insufficient data," any data gaps you noticed, anything I should know before running the next prompt. End the data quality notes with: "Paste the master analysis prompt into this chat to continue." **Do not** produce a diagnosis, ranking, save play, or summary in this step. The next prompt does that. End with the table and the data quality notes. ## Handling Missing or Thin Data - **Missing column needed for a metric:** Mark that metric "Insufficient data" for the affected agreements and note the field. Continue with the rest. - **An agreement appears in the registry but has zero visits or zero notes:** Compute what you can. Note the absence — it may itself be a quiet-disengagement signal that the master analysis will weight. - **External signal table not provided:** Note "External signals not provided" and continue. Mark all External Signal Type values as N/A. ## Getting Started I will upload all four CSV files and paste the external signal table in one message. Once everything is in the chat, produce the per-agreement health-signal table and data quality notes. Use today's date for days-to-renewal and the 90-day flag window. If the registry or visit history is missing, stop and tell me — those two are required. The other two are useful but the analysis can proceed with thinner data if needed. If I don't paste an external signal table, proceed without it and mark all External Signal Type values as N/A.

You are now diagnosing churn risk on each service agreement using the health-signal table you just produced, plus the four source files and external signal table already in this chat. This is the diagnosis step — produce a ranked at-risk list with specific evidence and a save play per agreement. ## Critical Rules These override any default behavior. Read them before you produce anything. **Evidence integrity:** - Every piece of evidence you cite must come directly from the data already in this chat (the four uploaded files, the external signal table, or the health table you just produced). Do not infer, fabricate, or embellish. - When you quote a service note, use the exact words from the source. Do not paraphrase into something cleaner. - Cite each piece of evidence with its source identifier and date: a work order ID + date, an invoice ID + date, a note ID + date and author role, or an external signal source. - Do not invent agreements, customers, account managers, lead techs, work orders, invoices, dollar figures, or events that don't appear in the data. - Use exact values for all names, IDs, and labels. If the data says "CommonSpirit Health," your output says "CommonSpirit Health" — do not abbreviate, paraphrase, or rephrase. **Diagnosis integrity:** - Assign exactly **one primary risk pattern** per at-risk agreement, drawn from the closed taxonomy below. If two patterns are roughly equal weight, pick the one with stronger evidence and note the secondary pattern in the evidence column. - Every risk assignment must be supported by at least **two specific data points** from at least two different sources (e.g., a note + a visit record, an AR pattern + a service-quality pattern). One signal alone is noise. Two stacked signals is a pattern. - If an agreement shows no risk signals across the health table and notes, mark it **Low Risk** and stop. Do not invent reasons to rank it. - Do not penalize an agreement for missing data. Missing transcripts, sparse notes, or thin AR history reduce confidence — they don't constitute risk. **Output integrity:** - Every "Why It's At Risk" must cite specific evidence with dates and IDs — not generic statements like "service quality issues" or "billing friction." - Every "Save Play" must name a specific person from the registry (account manager or branch manager) plus a specific action grounded in the evidence and a timeframe. - Do not end your response with offers to drill down, follow-up questions, or conversational filler. End the executive summary with: "Paste the pattern view prompt into this chat to continue." ## Risk Pattern Taxonomy Assign one of these six patterns to each at-risk agreement. Look for the listed evidence patterns. | Risk Pattern | What it looks like in the data | |---|---| | **Service-quality risk** | Missed PMs, repeated callbacks on the same unit (visit type Warranty Callback OR Demand Service repeats within 30 days on the same equipment), unresolved deficiencies across multiple visits, response-time degradation in last 90 days, customer complaints in notes about wait times or repeat work. | | **Billing-relationship risk** | Multiple disputed invoices in last 12 months, AR aging trend degrading, persistent open balances >60 days, credit issuance, payment-pattern changes (e.g., a historically on-time account drifting to 45+ days), notes referencing scope or pricing disagreements. | | **Equipment-replacement risk** | Equipment age 15+ years on the registry, repeated major-component repairs in the visit history, capital-replacement quotes referenced in notes, deficiency notes flagging end-of-life equipment. | | **Contact-/ownership-change risk** | New key contact, director, VP, or owner referenced in notes (someone "newly in role" or "newly assigned"), external signal of leadership change, ownership transfer, management company change, or acquisition. New contacts within 6 months of renewal often trigger vendor reviews. | | **Quiet disengagement** | Customer initiating cancellations or reschedules of scheduled PMs, declining demand-service volume against historical baseline, no customer-initiated calls in 6+ months, sparse or absent notes despite agreement size, declining engagement with account manager (notes describe unreturned calls, ghosting, postponed QBRs). | | **Competitor-shopping risk** | Notes referencing outside bids, competitor stickers found on equipment, customer-stated vendor reviews or RFP processes, external signal of posted RFP/procurement notice, customer asking unusual pricing or scope-comparison questions. | **Rules for assignment:** - The taxonomy is closed. Do not invent new categories. If an agreement genuinely doesn't fit cleanly, use the closest match and explain the nuance in the evidence column. - Stacked patterns are higher risk than any single pattern. An agreement with service-quality risk + billing risk + a new key contact is more at-risk than any one of those alone. Note the secondary patterns explicitly. - "Quiet disengagement" requires absence-of-signal evidence (declining activity vs. historical baseline). It is not a synonym for "we don't have much data on this account" — that's a confidence issue, not a risk pattern. ## Risk Score & Tier For each agreement, assign a churn risk score from 0–100 and place it in a tier: | Tier | Score | Meaning | |---|---|---| | **High Risk** | 70–100 | Multiple stacked signals across data sources. Renewal action needed within 2–4 weeks. Treat as urgent. | | **Medium Risk** | 40–69 | Real signals but either fewer in number or less severe. Renewal action needed within 30–60 days. | | **Low-Medium Risk** | 20–39 | One real signal or several weak signals. Worth a check-in but not urgent. | | **Low Risk** | 0–19 | No meaningful signals. Routine renewal expected. | **Scoring guidance:** - Use the full 0–100 range. If your scores all cluster between 40 and 60, you're not differentiating enough. - Stacked signals (multiple risk patterns on the same agreement) push the score up. A single severe signal (e.g., a posted RFP) can also push to High. - ACV is **not** a risk-score factor. A small agreement with three stacked signals is high-risk; a large agreement with no signals is low-risk. ACV is a separate column showing what's at stake — not what's at risk. - Days to renewal is a **modifier**: an agreement at score 65 with renewal in 30 days is functionally higher priority than one at score 65 with renewal in 180 days. Reflect this in the suggested timeframe of the save play, not the score itself. ## Save Plays — Closed List For each at-risk agreement (Medium or High), prescribe one save play matched to the primary risk pattern: | Risk Pattern | Save Play | |---|---| | **Service-quality** | Schedule a make-good visit for any unresolved deficiency. Branch manager + account manager joint call with the customer to acknowledge service-quality issues and present a corrective plan. Resolve open work-order items before renewal conversation. | | **Billing-relationship** | Account-manager-led AR conversation with customer's AP contact. Resolve disputes proactively. Issue any owed credits. Align on billing cadence and PO process. Do this before — not during — the renewal conversation. | | **Equipment-replacement** | Capital-replacement proposal for end-of-life equipment, bundled with the renewal. Position as protecting the customer from a competitor quote. Include lifecycle TCO comparison. | | **Contact-/ownership-change** | Introduction meeting with the new contact within 30 days of identification. Walk-through to demonstrate scope and value. Branch manager attends to signal seniority. | | **Quiet disengagement** | On-site QBR with value-reminder, scope review, and forward-look. If notes show unreturned calls, escalate the next outreach to the branch manager so the contact change is visible. | | **Competitor-shopping** | Branch manager + account manager call addressing scope/pricing context. Pull up uptime data, response-time data, and any cost-saving wins to date. If an RFP is active, structure an incumbent response that emphasizes switching costs and continuity risk. | You may augment a save play with specifics drawn from the evidence (e.g., "resolve the open deficiency flagged in note SN-XXXXX before the September 1 renewal"), but do not invent a new category. ## Confidence Level For each agreement's diagnosis, assign confidence: | Level | Criteria | |---|---| | **High** | Three or more sources contributed specific evidence — health metrics, notes, AR data, and/or external signals all point in the same direction. | | **Medium** | Two sources contributed meaningful evidence, OR three sources but one is thin. | | **Low** | Diagnosis based on one source (e.g., note narrative without supporting health metrics). Use sparingly — if confidence is low, the master analysis should mark the agreement Medium or Low Risk and suggest a discovery action rather than a save play. | ## Output Format Produce a table with these columns, sorted by **risk score descending** (then by days-to-renewal ascending as the tiebreaker): | Column | Content | |---|---| | Rank | Position in priority order | | Agreement ID | From the registry | | Customer / Facility | Exact name from registry | | Branch | From registry | | Agreement Type | From registry | | ACV | Dollar value at stake if non-renewal | | Renewal Date | From registry | | Days to Renewal | Calculated | | Risk Score | 0–100 | | Risk Tier | High / Medium / Low-Medium / Low | | Primary Risk Pattern | One from the taxonomy | | Secondary Pattern | A second from the taxonomy if it applies, or blank | | Why It's At Risk | 2–4 specific evidence citations: WO IDs with dates, invoice IDs with dates, note IDs with author and exact-quote excerpts, external signals with source. Each citation must point to data already in this chat. | | External Signal | From the pasted external signal table, if any. Otherwise "None." | | Confidence | High / Medium / Low | | Saveable? | Yes / Yes-with-action / Unlikely | | Save Play | One play from the closed list, augmented with specifics from the evidence. Name the action, the named owner from the registry (AM and/or branch manager), and a timeframe. | Format as a clean table I can copy into a spreadsheet. One row per agreement. Color coding by risk tier helps if your tool supports it. After the table, include: **Confidence explanation (2–3 sentences, plain English):** Which sources contributed evidence across the portfolio, where data was thin, and what the average confidence level looks like. If many agreements are rated Medium or Low confidence, name the data gap. **Executive summary (4–6 sentences):** - How many agreements fell in each risk tier and total ACV in each. - Total ACV at risk across High and Medium tiers. - Top 1–2 patterns visible across the portfolio (e.g., "Three of the top five at-risk agreements stack service-quality risk with a recent contact change — vendor reviews triggered by new key contacts are the dominant pattern"). - Any data quality caveats. ## Handling Missing or Thin Data Real exports are messy. Handle gaps as follows: - **Sparse notes for an agreement:** Diagnose from health metrics + AR + external signal. If the only evidence is a single weak signal, mark Low or Low-Medium risk and note the data thinness in the evidence column. - **No external signal table provided:** Mark External Signal "Not provided" for every agreement. The diagnosis still works — external signals are an amplifier, not the foundation. - **Health table missing or incomplete:** Stop and tell me. Do not proceed without the health table — it's the evidence layer the diagnosis is built on. Re-run the Service Health Calculator first. - **An agreement has zero notes and minimal visits:** This may itself be a quiet-disengagement signal if the agreement size suggests there should be more activity. Treat with caution — note the absence in the evidence column rather than over-calling it. When in doubt, say so. "Insufficient evidence to score" is a better answer than a guess. ## Getting Started Confirm you have the health table from the previous prompt and all four source files plus the external signal table in context. Then produce the ranked at-risk list and the executive summary. If you don't have the health table, stop and tell me — we need to run the Service Health Calculator first.

Now I want a pattern view across the diagnosis you just produced. Use the same data and the ranked churn-risk table you generated above. Do not re-analyze individual agreements — group what you already found. ### Rules - All evidence must come from the data and the diagnosis you already produced. Do not introduce new facts. - If a category has only one or two agreements in it, say so explicitly — a pattern needs more than a single data point. Do not over-call patterns from thin data. - Use exact dollar figures, agreement IDs, and customer names from the ranked table. Do not round in a way that obscures the size of a pattern. - Do not invent account managers, branches, lead techs, or facility types that don't appear in the registry. - If the data does not support a pattern in one of the dimensions below, say "No clear pattern" for that dimension. That is a valid answer. - Do not end your response with offers to drill down or conversational filler. End with the action list. ### What to Produce For each of the dimensions below, show a small grouping table and a 1–2 sentence read of what the data is telling me. #### 1. By Risk Pattern (ACV at Risk) Group all agreements rated Medium or High Risk by their **primary risk pattern** from the taxonomy. For each pattern that appears at least once, show: - Risk pattern - Number of agreements - Total ACV at risk across those agreements - Most common save play needed - A 1-sentence read Sort by total ACV at risk, descending. This answers: "Which kind of churn risk is costing me the most?" #### 2. By Branch Group agreements rated Medium or High Risk by branch. For each branch, show: - Branch - Number of at-risk agreements (and total agreements managed by that branch from the registry) - Total ACV at risk - Dominant risk pattern at this branch - A 1-sentence read This is an **exposure** view, not a performance review. A branch that handles more healthcare or more strategic accounts will naturally carry more exposure. Reflect that in the read. #### 3. By Account Manager Group agreements rated Medium or High Risk by their primary account manager from the registry. Same columns as the branch view. Same caveat applies: this is exposure, not performance. The AM with the most ACV at risk may be the one assigned the largest accounts. Note explicitly when an AM's exposure looks proportional to their book size vs. when the at-risk concentration is disproportionate. #### 4. By Facility Type Group agreements rated Medium or High Risk by facility type (Healthcare, Office, Education, Industrial, Government, Hospitality, Data Center, Retail, Mixed-Use, etc. — use whatever values appear in the registry). Same columns. This answers: "Is one vertical driving most of my retention exposure?" #### 5. By Renewal Window Group at-risk agreements into renewal windows: next 30 days, 31–60 days, 61–90 days, 91–180 days. Show: - Window - Number of at-risk agreements - Total ACV at risk in this window - A 1-sentence read on action urgency This answers: "What's hitting my desk this month vs. next quarter?" ### Coaching Lens After the five grouping tables, write **one short paragraph (4–6 sentences)** answering this question: > If I could only have one coaching conversation this week with one branch manager or account manager, who would it be with and what would it be about? Be specific. Name the person from the registry. Name the topic (a specific risk pattern showing up across multiple agreements they manage, or a single high-ACV agreement that needs senior attention). Cite the evidence from the diagnosis. Explain why this conversation, this week, has the highest leverage. If the data doesn't support a single clear answer, say so and offer the top 2 candidates with the tradeoff between them. ### Top 5 Easiest Saves List the **top 5 agreements where the risk is operational and one targeted action could flip the trajectory**. These are agreements where: - Risk tier is Medium or High - Saveable? is Yes or Yes-with-action - The save play is something a branch manager or account manager could execute this week (not a 6-month capital project) For each, include: - Rank (1–5) - Agreement ID and customer/facility - ACV at stake - Days to renewal - The single action to take this week - Who owns it (AM or branch manager from the registry) This is the action list for Monday morning. Keep it tight. No commentary, no qualifications — just the five things worth chasing first. ### Do Not - Do not summarize the master analysis again — I already have it. - Do not add new risk patterns that weren't in the original taxonomy. - Do not propose connected dashboards, monthly cadences, or product builds. That conversation comes after action, not before. - Do not end with offers to drill down further. End with the easiest-saves list.