The business problem
Plenty of activity. No shared answer.
Outreach notes, emergency calls, viability, sold work and revenue lived in different exports. Account names varied by source and historical records were incomplete. Leadership could see that people were busy, but not reliably connect effort to outcomes.
Is relationship marketing making the phone ring?
The dashboard needed to answer that question without collapsing every downstream issue into “marketing worked” or “sales failed.” Calls, viable opportunities, conversions and revenue each needed their own signal.
The operating view
This representative interface uses synthetic account names and values. Its structure mirrors the working system; no customer, contact, revenue or proprietary ranking data is shown.
| Account | Priority | Status | Touches | Calls | Viable | Converted | Revenue index | CRM grade |
|---|---|---|---|---|---|---|---|---|
| Agency North 01 | Tier 1 | Working | 12 | 8 | 6 | 3 | A | |
| Agency North 02 | Tier 1 | Generating leads, low closing | 10 | 7 | 6 | 1 | B+ | |
| Agency Central 01 | Tier 2 | Touching, no return | 9 | 0 | 0 | 0 | C+ | |
| Agency West 01 | Tier 1 | Untapped | 0 | 4 | 3 | 1 | B | |
| Agency Lakes 01 | Tier 2 | Called, not viable | 0 | 3 | 0 | 0 | C | |
| Agency Metro 01 | Tier 3 | Review | 2 | 2 | 1 | 0 | C+ |
How I built it
Consolidated multi-year job and revenue records so older performance stopped disappearing from current reports.
Built an alias layer that maps inconsistent referral-source names to one clean account name.
Separated program-to-date activity from current-year calls, conversions and annual revenue.
Translated the funnel into plain-language statuses that point to the stage needing attention.
Two systems, two jobs
Where we choose to invest
Forward-looking marketing priority based on opportunity, geography, response capability, relationship strength, historical performance and growth potential.
What the relationship is producing
A current performance designation maintained in the CRM. Keeping it separate from priority prevents a promising target from being mistaken for an already productive client.
The data-quality work
A polished dashboard is only useful if the same account means the same thing in every source.
- Separated raw names from standardized names.
- Mapped alternate spellings and department naming conventions.
- Excluded dispatch-only records from relationship grades.
- Added both lower and upper date boundaries to annual formulas.
- Kept “no data” distinct from a genuine zero.
- Reconciled annual totals against source history before summarizing them.
What changed
Marketing focus became visible
The team can distinguish productive relationships, untouched opportunities and accounts receiving effort without a return.
Funnel friction became specific
Calls, viable jobs and conversions are separate measures, making handoffs easier to discuss without defaulting to blame.
History became usable
Current activity can be viewed beside annual and all-time performance without losing older revenue or mixing reporting periods.
Strategy, data architecture, build and translation
I defined the business questions, consolidated and cleaned the source data, designed the mapping and formula architecture, built the dashboard, tested edge cases and translated the output into language leadership and field teams could use.
Confidentiality note: organization names, account names, values, ranking thresholds and operational details have been removed or replaced with synthetic examples. Revenue is represented as an index rather than currency.