Every RevOps person has, at some point, been asked, “What does our next quarter look like?” You have to pull together multiple data sources and sift through thousands of opp, account, and contact records. You stitch together the signals, and voilà, a magical analysis appears, but nobody is quite sure the data is correct.
Here is a simple RevOps playbook, with the exact questions to ask to rebuild the contact-recency analysis and turn it into weekly action.
The analysis rests on the idea that deals die of silence. Loss rates climbed from roughly 62% for deals touched in the last two weeks to 85% at 14-30 days without a customer response, and to 100% in the past 31 days. This playbook walks you through pulling every number in that report from Ask Aurasell, checking that the numbers hold together, and setting it up so the 14-day trigger runs on its own.
Plan on about 45 minutes the first time. After that, the weekly refresh takes about 10.
Throughout, the analysis callouts show what each step returned in the anonymized Q2 report (end-of-April snapshot), so you know what good output looks like and can spot when something is off. That report used 0-14 and 14-30 day bands, so your counts may shift slightly for deals sitting right at day 14.
Before you start: lock the definitions
Every number downstream depends on three definitions. Ask Aurasell will answer whatever you ask, so if the definitions drift between questions, the totals won't reconcile. Open your session by stating them once.
Prompt 0: set the ground rules
For this whole conversation, use these definitions:
- "Q2 pipeline" = open opportunities with a close date between May 1 and July 31.
- "Qualified" = stage is Qualify or later (Qualify, Scoping/Value Scoping, Demo/POV, Business Case, User Workshops, Go/No-Go, Negotiation/Closing). Exclude pre-qualify stages (Research & Prospecting, Discovery) and post-sale Onboarding.
- "Days since contact" = days since the most recent customer-side interaction of any kind: inbound email reply, meeting held, or call connected. Outbound emails with no reply do NOT count.
- Recency bands: 0-13 days, 14-30 days, 31+ days.
- Deals with no recorded customer-side activity go in the 31+ band.
Confirm these definitions back to me before we continue.Why this matters: The original report used "0-14" and "14-30," which overlap at day 14. Using 0-13 / 14-30 / 31+ puts every deal in exactly one band. Also confirm "customer-side" means the customer actually engaged, not that a rep sent something. That distinction is the whole point of the analysis.
If your stage names differ from the list above, swap in yours and keep them consistent for the rest of the session.
Step 1: Size the book
You need two totals: the full open book and the qualified subset. Use the qualified number for forecasting; the full book shows how much early-stage noise sits around it.
Prompt 1a
How many open Q2 opportunities are there in total, and what is their combined amount? Then give me the same count and amount for qualified Q2 pipeline only.What you're looking for: two lines, like 368 deals / $26.67M total and 107 deals / $13.23M qualified. Write both down; every later total must add back up to these.
Step 2: Split the book by contact recency
This is the report's core table.
Prompt 2a
Break down all open Q2 opportunities by recency band (0-13, 14-30, 31+ days since last customer-side contact). For each band show deal count and total amount. Then show the same breakdown for qualified Q2 pipeline only. Put it in one table with a totals row.Check: the band rows must sum to the Step 1 totals. If they don't, some deals fall outside the bands, almost always those with no activity at all. Follow up with:
Prompt 2b
How many qualified Q2 deals have no customer-side activity recorded at all? List them with owner, stage, and amount, and confirm they are counted in the 31+ band.From the Q2 analysis
Contact recency | All open Q2 | Qualified Q2 |
|---|---|---|
0-14 days | 211 / $16.31M | 68 / $8.81M |
14-30 days | 39 / $4.42M | 13 / $2.18M |
31+ days | 85 / $5.26M | 26 / $2.24M |
Total | 368 / $26.67M | 107 / $13.23M |
Only one qualified deal ($50K) had no customer-side activity, and it was correctly counted in the 31+ band. Two-thirds of qualified dollars ($8.81M) were in healthy shape; the remaining $4.42M was split almost evenly between saveable and effectively lost.
Step 3: Prove the signal with historical loss rates
The open book tells you where deals are now. To show that silence predicts loss, you need closed deals from prior quarters, measured at the same point in their life.
Prompt 3a
Look at qualified opportunities that closed (won or lost) in the last four quarters. For each one, find the number of days since last customer-side contact as of 25 days before the start of its quarter. Group them into the 0-13, 14-30, and 31+ bands and show the close-lost rate, deal count, and amount for each band.What you're looking for: a rising loss rate across the bands (in the original analysis, 62% → 85% → 100%). The jump between the first and second band is the number to manage to.
From the Q2 analysis: the move from 62% to 85% is a 23-point swing that happens in just two weeks of silence, and it's still reversible while the deal sits in the 14-30 band. Note that even the freshest band lost 62% of the time: activity alone isn't progress, because a reply from someone who can't buy is still a reply.
Sanity checks. If a band has fewer than about 20 closed deals, say so in the report; the percentage will swing a lot on small samples. If the 31+ band isn't close to 100% lost, ask Ask Aurasell to list the wins in that band. They usually turn out to be renewals or deals where contact happened but wasn't logged, which is itself a hygiene finding.
If Ask Aurasell can't reconstruct the "as of" snapshot for historical deals, use this simpler version and note the limitation:
For qualified opportunities closed lost in the last four quarters, what was the gap in days between the last customer-side contact and the close-lost date? Show the distribution in the 0-13, 14-30, and 31+ bands.Step 4: Break it down by rep
Prompt 4a
For qualified Q2 pipeline, give me a table by opportunity owner with columns for each recency band (0-13, 14-30, 31+), showing deal count and amount in each, plus a total. Sort by total pipeline, descending. Include a totals row.Check: the totals row must match the qualified line from Step 2.
From the Q2 analysis: ten owners held qualified Q2 pipeline. Rep A had the largest book by far ($4.36M, with 22 deals under 14 days), followed by Rep B ($2.31M). One row belonged to Former Rep X, a departed rep still holding 4 qualified deals ($350K). That's your first hint that Step 5 will find something.
Prompt 4b: rank the at-risk books
Using the same data, rank owners by "at-risk" amount, defined as the sum of qualified pipeline in the 14-30 and 31+ bands. Show 14-30 count and amount, 31+ count and amount, and at-risk total. Flag any owner whose 14-30 amount is dominated by a single deal over $250K.From the Q2 analysis
Rep | 14-30 Days | 31+ Days | At-risk total |
|---|---|---|---|
Rep D | 4 / $648K | 6 / $440K | $1.09M |
Rep B | 1 / $164K | 4 / $745K | $909K |
Rep A | 2 / $660K | 2 / $85K | $745K |
Rep C | 1 / $500K | 1 / $200K | $700K |
Rep F | 4 / $170K | 4 / $182K | $352K |
Former Rep X | 0 / $0 | 4 / $350K | $350K |
Each rep needs a different play. Rep D has the largest pool, concentrated enough for a single focused working session. Rep B's risk is mostly already past day 31, so it's a scrub conversation, not a rescue. Rep F's is spread evenly and needs a systematic sweep. The single-deal flag surfaced the two first-call priorities: Rep A with $660K and Rep C with one $500K deal, both freshly in the saveable window.
Step 5: Find orphaned deals
Deals owned by people who have left get zero attention by definition.
Prompt 5a
List every open Q2 opportunity (any stage) whose owner is a deactivated or inactive user. Show owner, account, stage, amount, and days since last customer-side contact. Subtotal by owner.Prompt 5b: decide what to rescue
Of those orphaned deals, which have had any customer-side contact in the last 30 days? Those are the ones to reassign first.Any orphaned deal older than 31 days is a close-lost candidate unless the new owner makes one fresh-channel attempt this week.
From the Q2 analysis: two departed reps still owned 8 open deals worth $450K; all four qualified ones ($100K), and one of them, Account 8, showed a customer email just 1 day earlier. Reassign that deal today, because someone on the customer side is still engaged and nobody is answering.
Step 6: Pull the working lists
The tables tell leadership where the problem is. Reps and managers actually work from these lists.
Prompt 6a: the intervene-now list
List every qualified Q2 deal in the 14-30 day band. Show owner, account, stage, amount, days since last customer-side contact, the channel of that last contact (email, call, meeting), and the name and title of the last customer contact who engaged. Sort by amount descending.The channel and contact columns tell the manager how to change the play: if the last touch was an email with a junior contact, the move is a call or an exec-to-exec note to someone else on the buying committee.
From the Q2 analysis: this list included 13 deals totaling $ 2.18 M. Rep A ($660K across 2 deals), Rep D ($648K across 4), and Rep C ($500K in 1) together held $1.81M of it, so three manager conversations cover more than 80% of the saveable window.
Prompt 6b: the scrub list
List every qualified Q2 deal in the 31+ band that is currently in commit or best case. Show owner, account, amount, forecast category, and days since last customer-side contact.Pull these deals from the forecast unless the rep can point to a concrete, dated customer commitment.
From the Q2 analysis: this band held 26 qualified deals worth $2.24M, and historically 100% of deals like these closed lost. Rep B alone had $745K here across 4 deals, and Former Rep X's $350K made up another big share. Leaving these in commit, or best case, inflates coverage and hides the real gap to the Q2 number.
Step 7: Reconcile before you publish
Run through this list before anything goes in front of leadership. A single number that doesn't tie out will cost the whole analysis its credibility.
Check | How to confirm |
|---|---|
Band totals = book totals | Step 2 rows sum to Step 1, for both full and qualified views |
Rep totals = qualified total | Step 4a totals row matches Step 2 qualified total |
At-risk ranking is actually ranked | Step 4b is sorted by at-risk total, not by rep name or total pipeline |
No-activity deals are accounted for | Step 2b count appears inside the 31+ band |
Orphaned qualified deals appear in the rep table | Departed owners show up as rows in Step 4a |
Loss-rate sample sizes are stated | Step 3 shows deal counts next to each percentage |
If anything fails, ask Ask Aurasell directly: "These two numbers should match but don't. Which deals explain the difference?"
What these checks would have caught in the Q2 analysis:
A gap in the full-book table. The three bands add up to 335 deals and $25.99M, but the stated total is 368 deals and $26.68M ($0.68M unaccounted for). The qualified view ties out exactly, so the gap is in pre-qualify deals, most likely ones missing activity data or falling between bands. Run Prompt 2b against the full book to find them.
A ranking that wasn't ranked. The original at-risk table listed reps out of order (for example, a $352K book above a $745K one) despite being labeled as ranked by dollars.
Rounding drift. One rep's 31+ amount appeared as $182K in one table and $181.5K in another. That's harmless on its own, but enough to make a careful reader question the rest. Pick one precision and use it everywhere.
Step 8: Make it run on its own
The report's main recommendation is that a qualified deal crossing day 14 should automatically reach the manager, not wait for the next pipeline review. Set that up once.
Prompt 8a: the trigger
Create an alert that fires when any qualified opportunity reaches 14 days without customer-side contact. Notify the opportunity owner and their manager, and include the account, amount, stage, last contact name, and last contact channel.Prompt 8b: the weekly digest
Every Monday morning, send me a summary of qualified pipeline for the current quarter: the recency-band table, the at-risk ranking by owner, any new orphaned deals, and any deals that moved into the 14-30 band since last week.If alerts or scheduled digests aren't available in your Ask Aurasell setup, save Prompts 2a, 4b, 5a, and 6a as a set and rerun them every Monday.
Quick reference: the full prompt sequence
# | Purpose | Output |
|---|---|---|
0 | Lock definitions | Confirmed definitions |
1a | Size the book | Total and qualified counts and dollars |
2a-2b | Recency split | Band table, no-activity deals |
3a | Historical loss rates | Loss rate by band |
4a-4b | Rep view | Rep table, at-risk ranking |
5a-5b | Orphaned deals | Reassignment list |
6a-6b | Working lists | Intervene-now list, scrub list |
7 | Reconcile | Clean, tied-out numbers |
8a-8b | Automate | Day-14 alert, Monday digest |
Ask Aurasell's exact capabilities and field names depend on your configuration. If a prompt returns something unexpected, ask it how it interpreted the request, then restate it using the field and stage names your instance uses.