Vocabulary Layer
Vocabulary Layer
Vocabulary Layer
Vocabulary Layer
CRM Schema Design
Deriving a fundraising CRM's controlled vocabulary from 20 years of records
Reporting Infrastructure
RESULTS
Decades of ad hoc configuration became a controlled vocabulary derived from real records, spanning funds, campaigns, appeals, and the full prospect pipeline. It underpins a reporting layer whose metrics stay reliable.
CHALLENGE
After two decades and many hands, a fundraising CRM's configuration lists had grown past the structure that clean and reliable reporting needed.
STACK
Raiser's Edge, Airtable, Excel
CRM Schema Design
Deriving a fundraising CRM's controlled vocabulary from 20 years of records
Reporting Infrastructure
RESULTS
Decades of ad hoc configuration became a controlled vocabulary derived from real records, spanning funds, campaigns, appeals, and the full prospect pipeline. It underpins a reporting layer whose metrics stay reliable.
CHALLENGE
After two decades and many hands, a fundraising CRM's configuration lists had grown past the structure that clean and reliable reporting needed.
STACK
Raiser's Edge, Airtable, Excel
CRM Schema Design
Deriving a fundraising CRM's controlled vocabulary from 20 years of records
Reporting Infrastructure
RESULTS
Decades of ad hoc configuration became a controlled vocabulary derived from real records, spanning funds, campaigns, appeals, and the full prospect pipeline. It underpins a reporting layer whose metrics stay reliable.
CHALLENGE
After two decades and many hands, a fundraising CRM's configuration lists had grown past the structure that clean and reliable reporting needed.
STACK
Raiser's Edge, Airtable, Excel
The systems, decisions, and outcomes described here are my own work, accurately represented. Organization-specific configurations and data have been abstracted to maintain confidentiality, not to overstate or obscure.
Configuration as Schema
In a fundraising CRM, the configuration lists are easy to read as metadata: funds, campaigns, appeals, and packages on the gift side; prospect statuses and action types on the campaign side. They are better understood as the schema the whole operation thinks in. They define what can be entered cleanly, what can be grouped, and what can be reported.
Over two decades, lists like these grow by accretion. Many people add an option to solve the problem in front of them, and the set drifts away from any shared convention. This is normal for a long-lived system, but it has a cost. When the vocabulary stops mapping to the real work, data entry becomes a judgment call, new records get named in whatever style their creator prefers, and every downstream query and report inherits the inconsistency.
I treated the configuration as foundation rather than decoration. Before changing any process or building any dashboard, the lists themselves had to become a deliberate, legible set.
Deriving the Standard
I started with the data, not a blank-slate taxonomy. I analyzed roughly twenty years of fund, campaign, appeal, and package records, reading the patterns in how IDs and descriptions had been written over time. I ran the same analysis on prospect statuses and action types, where the lists had grown to dozens of entries.
The analysis showed which categories saw real use, which were duplicates of each other, and which were one-off entries that never recurred. That distribution did the deciding. Instead of inventing a structure and hoping people would adopt it, I formalized the categories the work already relied on and dropped the noise.
Deriving the standard from real records, rather than imposing one, is what let it fit the operation. The vocabulary had to stay faithful on three axes at once: legible to staff after minimal training, true to how a gift or a prospect moves, and consistent enough to mirror in adjacent tools like Google Sheets.
The Vocabularies
The fund vocabulary separates three concerns that had been tangled together. Structure lives in the ID, built from ordered, controlled segments (program, purpose, restriction, and an optional label, as in YTH-PRG-R-SMITH). Readability lives in the Description, a Title Case mirror of the ID. Everything that changes or needs detail, such as funder names, dates, and restrictions in plain language, lives in Notes. Campaigns and appeals then default to the funds most gifts should use, so coding cascades instead of being re-selected by hand.
The prospect vocabulary works the same way, as a set of controlled lists. I reduced dozens of accumulated action types to a relevant set grouped by pipeline stage: research, cultivation, solicitation, and stewardship, alongside event, volunteer, and administrative work. Prospect statuses and opportunity statuses became short, ordered pipelines rather than open-ended option lists.
The decisive choice was making the action types semantically load-bearing. A subset maps directly to campaign metrics, so an action's type is not just a label, it is the unit reporting later counts on.
The Reporting Base
With the vocabulary settled, the reporting layer became possible. I built an Airtable base that mirrors the CRM's action and status sets and rolls them into the metrics the team uses to read its own work: visits made, meaningful outreach, asks made, and totals grouped by research, cultivation, and stewardship.
These rollups only hold because the underlying types are controlled. Meaningful outreach exists as a metric because a specific set of action types defines it. The stage groupings work because each type maps cleanly to one bucket. The lists are governed as a strategic set rather than an open field. Adding a type is a deliberate decision about what the team measures, not a quick fix in a dropdown. That governance is the difference between configuration that decorates the database and configuration insights can depend on.
The systems, decisions, and outcomes described here are my own work, accurately represented. Organization-specific configurations and data have been abstracted to maintain confidentiality, not to overstate or obscure.
Configuration as Schema
In a fundraising CRM, the configuration lists are easy to read as metadata: funds, campaigns, appeals, and packages on the gift side; prospect statuses and action types on the campaign side. They are better understood as the schema the whole operation thinks in. They define what can be entered cleanly, what can be grouped, and what can be reported.
Over two decades, lists like these grow by accretion. Many people add an option to solve the problem in front of them, and the set drifts away from any shared convention. This is normal for a long-lived system, but it has a cost. When the vocabulary stops mapping to the real work, data entry becomes a judgment call, new records get named in whatever style their creator prefers, and every downstream query and report inherits the inconsistency.
I treated the configuration as foundation rather than decoration. Before changing any process or building any dashboard, the lists themselves had to become a deliberate, legible set.
Deriving the Standard
I started with the data, not a blank-slate taxonomy. I analyzed roughly twenty years of fund, campaign, appeal, and package records, reading the patterns in how IDs and descriptions had been written over time. I ran the same analysis on prospect statuses and action types, where the lists had grown to dozens of entries.
The analysis showed which categories saw real use, which were duplicates of each other, and which were one-off entries that never recurred. That distribution did the deciding. Instead of inventing a structure and hoping people would adopt it, I formalized the categories the work already relied on and dropped the noise.
Deriving the standard from real records, rather than imposing one, is what let it fit the operation. The vocabulary had to stay faithful on three axes at once: legible to staff after minimal training, true to how a gift or a prospect moves, and consistent enough to mirror in adjacent tools like Google Sheets.
The Vocabularies
The fund vocabulary separates three concerns that had been tangled together. Structure lives in the ID, built from ordered, controlled segments (program, purpose, restriction, and an optional label, as in YTH-PRG-R-SMITH). Readability lives in the Description, a Title Case mirror of the ID. Everything that changes or needs detail, such as funder names, dates, and restrictions in plain language, lives in Notes. Campaigns and appeals then default to the funds most gifts should use, so coding cascades instead of being re-selected by hand.
The prospect vocabulary works the same way, as a set of controlled lists. I reduced dozens of accumulated action types to a relevant set grouped by pipeline stage: research, cultivation, solicitation, and stewardship, alongside event, volunteer, and administrative work. Prospect statuses and opportunity statuses became short, ordered pipelines rather than open-ended option lists.
The decisive choice was making the action types semantically load-bearing. A subset maps directly to campaign metrics, so an action's type is not just a label, it is the unit reporting later counts on.
The Reporting Base
With the vocabulary settled, the reporting layer became possible. I built an Airtable base that mirrors the CRM's action and status sets and rolls them into the metrics the team uses to read its own work: visits made, meaningful outreach, asks made, and totals grouped by research, cultivation, and stewardship.
These rollups only hold because the underlying types are controlled. Meaningful outreach exists as a metric because a specific set of action types defines it. The stage groupings work because each type maps cleanly to one bucket. The lists are governed as a strategic set rather than an open field. Adding a type is a deliberate decision about what the team measures, not a quick fix in a dropdown. That governance is the difference between configuration that decorates the database and configuration insights can depend on.
The systems, decisions, and outcomes described here are my own work, accurately represented. Organization-specific configurations and data have been abstracted to maintain confidentiality, not to overstate or obscure.
Configuration as Schema
In a fundraising CRM, the configuration lists are easy to read as metadata: funds, campaigns, appeals, and packages on the gift side; prospect statuses and action types on the campaign side. They are better understood as the schema the whole operation thinks in. They define what can be entered cleanly, what can be grouped, and what can be reported.
Over two decades, lists like these grow by accretion. Many people add an option to solve the problem in front of them, and the set drifts away from any shared convention. This is normal for a long-lived system, but it has a cost. When the vocabulary stops mapping to the real work, data entry becomes a judgment call, new records get named in whatever style their creator prefers, and every downstream query and report inherits the inconsistency.
I treated the configuration as foundation rather than decoration. Before changing any process or building any dashboard, the lists themselves had to become a deliberate, legible set.
Deriving the Standard
I started with the data, not a blank-slate taxonomy. I analyzed roughly twenty years of fund, campaign, appeal, and package records, reading the patterns in how IDs and descriptions had been written over time. I ran the same analysis on prospect statuses and action types, where the lists had grown to dozens of entries.
The analysis showed which categories saw real use, which were duplicates of each other, and which were one-off entries that never recurred. That distribution did the deciding. Instead of inventing a structure and hoping people would adopt it, I formalized the categories the work already relied on and dropped the noise.
Deriving the standard from real records, rather than imposing one, is what let it fit the operation. The vocabulary had to stay faithful on three axes at once: legible to staff after minimal training, true to how a gift or a prospect moves, and consistent enough to mirror in adjacent tools like Google Sheets.
The Vocabularies
The fund vocabulary separates three concerns that had been tangled together. Structure lives in the ID, built from ordered, controlled segments (program, purpose, restriction, and an optional label, as in YTH-PRG-R-SMITH). Readability lives in the Description, a Title Case mirror of the ID. Everything that changes or needs detail, such as funder names, dates, and restrictions in plain language, lives in Notes. Campaigns and appeals then default to the funds most gifts should use, so coding cascades instead of being re-selected by hand.
The prospect vocabulary works the same way, as a set of controlled lists. I reduced dozens of accumulated action types to a relevant set grouped by pipeline stage: research, cultivation, solicitation, and stewardship, alongside event, volunteer, and administrative work. Prospect statuses and opportunity statuses became short, ordered pipelines rather than open-ended option lists.
The decisive choice was making the action types semantically load-bearing. A subset maps directly to campaign metrics, so an action's type is not just a label, it is the unit reporting later counts on.
The Reporting Base
With the vocabulary settled, the reporting layer became possible. I built an Airtable base that mirrors the CRM's action and status sets and rolls them into the metrics the team uses to read its own work: visits made, meaningful outreach, asks made, and totals grouped by research, cultivation, and stewardship.
These rollups only hold because the underlying types are controlled. Meaningful outreach exists as a metric because a specific set of action types defines it. The stage groupings work because each type maps cleanly to one bucket. The lists are governed as a strategic set rather than an open field. Adding a type is a deliberate decision about what the team measures, not a quick fix in a dropdown. That governance is the difference between configuration that decorates the database and configuration insights can depend on.
CONTACT INFO
Ready to Connect?
Currently open to Austin roles in operations, systems, and CRM. I'd love to hear what you're building toward.
CONTACT INFO
Ready to Connect?
Currently open to Austin roles in operations, systems, and CRM. I'd love to hear what you're building toward.
CONTACT INFO
Ready to Connect?
Currently open to Austin roles in operations, systems, and CRM. I'd love to hear what you're building toward.