Stop Tracking AUM in Your Head: Build a 5-Metric Dashboard
Most wealth managers can quote their top client's AUM from memory. Fewer can tell you—right now—how many open action items are sitting unresolved across their book, or which clients haven't had their meeting prep completed for a review scheduled in six days. That gap between what you know and what the data shows is where clients quietly start looking elsewhere.
The fix isn't a six-figure business intelligence platform. It's a one-screen dashboard built in a spreadsheet, fed by exports from your CRM and accounting system, updated weekly—no code required. This playbook walks you through exactly how to build it.
Why Your Memory Is the Worst KPI Tool You Own
Gut-feel management works fine when you have eight clients and one advisor. It breaks down at scale, during market volatility, and any time a key team member is out. The problem isn't that your team isn't paying attention—it's that attention is selective. You notice what's in front of you. Dashboards notice everything.
RIAs that have shifted to live data environments report catching potential churn signals weeks before a client conversation would have surfaced them. The data was always there. It just wasn't organized into a signal anyone was watching.
Your memory is also not auditable. A spreadsheet dashboard is. That matters for compliance, for onboarding new advisors, and for any future M&A due diligence.
The 5 Metrics That Actually Matter
Before you build anything, get clear on what you're measuring. These five metrics give you a full operational read on your firm without drowning in noise.
1. Net New Assets (NNA)
What it is: The dollar value of new contributions and new client assets minus withdrawals and departing client assets—over a rolling 30/90/365-day window.
Why it matters: AUM is a lagging number. NNA is a leading one. Flat AUM with negative NNA means market appreciation is masking real outflows. That's a problem you want to see before it compounds.
2. Incomplete Meeting Prep %
What it is: The percentage of upcoming client meetings (scheduled in the next 14 days) where the prep checklist in your CRM is incomplete.
Why it matters: Unprepared meetings erode trust faster than almost anything else. Tracking the rate of incomplete prep—not just flagging individual cases—tells you whether your process is holding under workload pressure.
3. Recurring Revenue Run-Rate
What it is: Your current annualized fee revenue based on actual billed and accrued advisory fees, updated monthly from your accounting system.
Why it matters: This is your real revenue visibility number. It separates your contracted revenue from market-dependent AUM percentages, and it's what a buyer or lender will ask for first.
4. Client NPS Trigger Score
What it is: A flag column (Red / Yellow / Green) in your CRM that marks any client who has given a detractor score (0–6) in a recent satisfaction check-in, or who has missed two or more scheduled touchpoints in the last 90 days.
Why it matters: You don't need a real-time sentiment AI. You need a simple flag that forces a conversation before a client calls to say they're leaving. This is your early-warning system.
5. Open Action Items (Age ≥ 14 Days)
What it is: The count of client-facing tasks in your CRM—paperwork to complete, referrals to follow up, estate planning items to coordinate—that are older than 14 days without resolution.
Why it matters: Action-item lag is the single most visible proxy for operational breakdown. Clients don't forget what you promised. They're just polite about it—until they aren't.
Where to Pull the Data (No Coding Required)
You don't need an API or a developer. Here's where each metric lives:
| Metric | Source | Export Method |
|---|---|---|
| Net New Assets | Custodian portal (Schwab, Fidelity, etc.) | Monthly CSV or PDF → manual input |
| Meeting Prep % | Salesforce / Redtail | Filtered view → export to CSV |
| Revenue Run-Rate | QuickBooks / Xero / Orion | Monthly invoice summary report |
| NPS Trigger Score | Salesforce / Redtail custom field | Filter by score range → export |
| Open Action Items | Salesforce / Redtail task module | Overdue task report → CSV |
In Salesforce: Use report builder to create a "Meetings in next 14 days with checklist status = incomplete" report. Schedule it to run weekly and email to your operations lead.
In Redtail: Use the Task/Activity view filtered by status = open and created date ≤ 14 days ago. Export to CSV. Paste into your dashboard tab.
Accounting: Ask your bookkeeper to send a monthly fee summary at the same time they reconcile the month. One column: total advisory fees billed. That's your run-rate input.
Build the Dashboard in 6 Steps
This is a Google Sheets or Excel build. One tab, five metric blocks, done in under two hours.
Step 1: Create a "Dashboard" tab. Freeze row 1 for headers. Use columns A–F. Reserve column A for metric names and column B for current values.
Step 2: Add a "Data" tab. Paste your raw CRM and accounting exports here. Never edit the Dashboard tab manually—all values should reference cells in the Data tab using simple formulas (e.g., =COUNTIF(Data!G:G,"Open")).
Step 3: Set up each metric block. Label rows 2–6 with your five metric names. In column B, enter the formulas that calculate each value from the Data tab. For NNA, this may be a manually updated cell until you automate the custodian feed.
Step 4: Add a "Status" column (column C). Use =IF(B3>X,"🔴","🟢") logic, or a traffic-light conditional formatting rule on the value cell itself. Set your own thresholds (e.g., NPS Trigger > 3 clients = red).
Step 5: Apply conditional formatting. Highlight column C. Set rules: if value contains "Red" → fill red; "Yellow" → fill amber; "Green" → fill green. Your ops team should be able to read the dashboard in under 30 seconds.
Step 6: Pin it. Share the dashboard link in your team Slack or Teams channel. Put it as the first agenda item in your weekly ops meeting. If no one looks at it, it doesn't work.
The Action-Item Lag That Almost Cost One RIA a Client
A regional RIA with roughly 180 client households implemented this dashboard after a difficult quarter. Within three weeks of going live, the open action items metric turned red: eleven items had been sitting unresolved for more than 14 days, eight of them tied to a single client household.
When the lead advisor reviewed the items, they found a mix of estate document follow-ups and an insurance beneficiary update that had been requested months earlier during an annual review. The client—a long-tenured relationship worth significant AUM—had sent a single follow-up email six weeks prior. It had been replied to with "we'll get this sorted" and then lost in the workload.
The advisor called the client proactively before the client called them. The client was, by their own admission, two weeks away from requesting a transfer. The conversation didn't just save the relationship—it surfaced two referrals the client had been sitting on, waiting to see how the firm handled exactly this kind of situation.
The dashboard didn't create that insight. The data was always there. The dashboard just made it impossible to miss.
Keeping the Dashboard Alive
A dashboard that isn't updated is worse than no dashboard—it creates false confidence. Here's the minimum cadence to keep it useful:
- Weekly (Monday morning): Ops lead exports CRM task and meeting prep reports, pastes into Data tab. Five-minute check of all five metrics in team standup.
- Monthly (first week): Finance or bookkeeper updates revenue run-rate. Advisor reviews NPS trigger flags with team.
- Quarterly: Review your threshold settings. Did your red/yellow/green cutoffs still make sense? Adjust based on where issues actually surfaced.
Assign one person—your operations manager, your COO, or a senior advisor on rotation—as the dashboard owner. Their job isn't to fix every red flag. It's to make sure the numbers are current and that every red flag has a named owner by end of day Monday.
That one habit, consistently maintained, turns a spreadsheet into an early-warning system for your entire firm.
You don't need to track AUM in your head. You need five numbers on one screen, updated weekly, reviewed consistently. Start with the metrics that matter most to your firm right now—even if that's just open action items and meeting prep—and build from there. The dashboard is never done. That's the point.