ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
How Donor Weighting & Scoring Works
3
4
0. Pull the raw gift data.
5
Export gift-level data from your database (Donor ID, Gift Date, Gift Amount) into the Raw Gift Data tab, one row per gift, already filtered to whatever should count as a gift. This works the same way regardless of which CRM the export came from.
6
7
1. Score each factor 1-5.
8
The Donor Scoring tab automatically rolls up each donor's Most Recent Gift Date, # Gifts, and Total Giving from the Raw Gift Data tab (within the Lookback Window set on Assumptions), then converts each to a 1-5 score using the band tables on the Assumptions tab, so they're all on the same scale.
9
10
2. Set the weights.
11
Decide how much each factor should count toward the final score. The three weights on the Assumptions tab must add up to 100%. Starting point: Recency 30%, Frequency 30%, Monetary 40%. Change these anytime, every score on the Donor Scoring tab recalculates automatically.
12
13
3. Blend into one weighted score.
14
Weighted Score = (Recency Score x Recency Weight) + (Frequency Score x Frequency Weight) + (Monetary Score x Monetary Weight), scaled to a 0-100 range.
15
16
4. Assign tier & owner.
17
The Donor Scoring tab looks up each donor's weighted score against the Tier Cutoffs table on the Assumptions tab and returns the matching tier name and owner automatically.
18
19
What to edit vs. what not to.
20
Yellow cells are the only ones meant to be changed: on Raw Gift Data, every row you add; on Assumptions, the three weights, the Lookback Window, the score band cutoffs, and the tier cutoffs/owners; on Donor Scoring, only the Donor column. Everything else is a formula.
21
22
Seasonality note.
23
A flat Recency clock penalizes donors who reliably give once a year (e.g. every December). If that's a meaningful share of your file, consider scoring recency against each donor's own typical gift cycle instead of a single calendar-wide band table.
24
25
Lookback window.
26
The 'Lookback (Years)' cell on the Assumptions tab controls how far back Gifts and Total Giving are counted (3 years by default). Most Recent Gift Date always looks at a donor's full history, not just the window, so long-lapsed donors still show their true last gift date.
27
28
Tier Summary.
29
The Tier Summary tab totals actual gift dollars by Tier for the last 3 calendar years (set by the As-Of Year cell there). It uses each donor's CURRENT Tier applied back across all years, not the tier they were in at the time of each individual gift.
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100