"How do thousands of raw gift transactions become a donor strategy we can trust?"
From raw gifts to reconciled donor profiles
I built a donor-level data model and a repeatable review workflow. Transaction-level giving was reconciled into a clean base of 3,000+ unique donors and documented in an SOP for reuse.
The same data, many possible answers
Transaction-level gifts had to become a donor-level population for segmentation and presentation. Filters, gift measures, dates, recency and distributions all needed consistent definitions. Without them, two analysts would get two different donor bases.
Author of the method and its source of truth
I defined the donor-level methodology, created a Markdown source-of-truth SOP, specified the filters and rollups, reconciled distributions and structured the confirmation needed before segmentation. The final data-assessment document was delivered securely.
How it was done
- Rules before rollup
Mandatory population rules were applied before any aggregation.
- Aggregate by unique donor
Gifts were rolled up to one record per donor.
- Derive the profile variables
Date, count, value, recency and category variables.
- Exclude the noise
Non-analytical fields were removed from the model.
- Reconcile
Pivot-style and Power BI checks confirmed that totals matched the source.
- Document for reuse
Expected outputs and confirmation requirements were written into the SOP.
The rollup, made visible
Each grey dot is a gift, and each coloured node is a donor sized by number of gifts. Hover a donor.
Recency · frequency · value framework
The profile variables every donor receives, with fictional field names
| Variable | Definition (illustrative) | Used for |
|---|---|---|
| last_gift_date | Most recent gift inside the analysis window | Recency bands, lapsed-donor flags |
| gift_count | Number of qualifying gifts per unique donor | Frequency tiers, one-time vs repeat |
| gift_value_band | Banded total value (values never published) | Value tiers, upgrade potential |
| first_gift_year | Year of first qualifying gift | Tenure and lifecycle stage |
| primary_category | Dominant appeal or fund category | Motivation and messaging |
What the numbers say
Unique donors reconciled from transaction-level giving.
VerifiedMarkdown source-of-truth SOP that any analyst can rerun.
VerifiedGift values disclosed. Only the method is shown.
PolicyFinal data assessment delivered securely, with profile inputs and next-stage confirmations.
VerifiedFigures use approved public wording: rounded, generalized or indexed so no client can be identified. Evidence standard
The value created
The organization now has a reproducible bridge from raw donation transactions to segmentation-ready donor profiles. There is less risk of different analysts applying different population or rollup logic, and the next project starts from a documented method rather than from scratch.
What this means for you
Is your donor or customer file transaction-level and hard to profile?
I can define the rollup, reconcile it to source, and hand you an SOP your own team can rerun every quarter, so segmentation starts from a base everyone agrees on.
Confidentiality note: this case study is anonymized. The sector label is generalized and there are no client names, proprietary templates or source screenshots. All visuals are rebuilt with synthetic data that keeps the analytical concept but none of the original values. Contribution is described with specific verbs (owned, designed, developed) because this was delivered within a wider team.