Preserve the observations behind the score
Start the workbook with behavior counts: attended events, selected email interactions and portal visits. Keep those observations separate from the points assigned to them. Changing a rubric should change the score without requiring you to reconstruct what happened.
The export needs a consistent row definition, usable numeric columns and a deliberate approach to duplicates and missing values. A repeated record can distort the analysis; a blank can mean something different from zero. Document what each column measures before experimenting with its weight. The chapter begins with the export already available and leaves the Configurable Joins construction to another chapter.
In the recording · 03:49 ↗Give the rubric its own control tab
A separate reference tab holds the choices you expect to revise: whether to include a behavior, its points per occurrence and its maximum contribution. Keep subscore categories visible too. A calculation tab can then apply those choices to historical records, while the raw-data tab remains available for inspection.
Capping a contribution prevents one frequently repeated behavior from dominating the total. If a fictional rubric gives four points per visit with a ten-point cap, three visits contribute ten points. That arithmetic expresses a design choice, not evidence that ten is the right weight. Compare alternatives deliberately and retain the underlying counts.
In the recording · 05:43 ↗Make errors noticeable while building
The chapter's error-handling example returns zero when a calculation fails, but David recommends replacing that fallback while building so errors can be found. That distinction matters: a formula failure should not quietly become a measured absence of engagement.
Keep an error indicator alongside the calculated value and resolve it before interpreting the score. When totaling the included behaviors and bonuses, check which columns qualify and whether rounding belongs at the end. A workbook that always displays a number can still be hiding broken references, unexpected text or an incomplete export.
In the recording · 07:47 ↗Choose a comparison that answers the question
The validation tab compares the new score with an existing measure, such as a staff evaluation or an observed outcome. Inspect both individual behaviors and the total. Grouped averages can reveal where a relationship changes, but small groups can look more convincing than their evidence supports.
Define the observation window before comparing outcomes. A score meant to describe earlier engagement should not contain activity recorded after the outcome it is being checked against. Keep the comparison measure out of its own predictors. Staff ratings also need scrutiny: they reflect a particular evaluation process rather than an unquestionable ground truth.
In the recording · 09:41 ↗Keep strength, direction and uncertainty together
Pearson correlation summarizes a linear relationship. Its sign gives the direction, while its magnitude describes strength. A coefficient of negative 0.65 and one of positive 0.65 have equal magnitude but opposite directions. Retain the sign when explaining what the score means.
Use the same eligible numeric pairs for the correlation and its sample count. A legitimate zero belongs in the analysis; an unknown value needs explicit handling. The chapter uses a nonnegative-value count as a proxy, which deserves inspection before reuse, especially if meaningful negative values or blanks are possible.
A p-value does not establish predictive accuracy or causation. Read it alongside the relationship's size, sample and purpose. Categorical variables require an appropriate analysis rather than automatic reuse of the same formula. Conditional formatting can aid scanning, but keep text labels and values visible so interpretation does not depend on distinguishing red from green.
In the recording · 12:02 ↗Test what survives beyond the workbook
Trying hundreds of weights and behaviors creates opportunities to find accidental patterns. Record the comparisons you tried and reserve data for a check that did not guide those choices. A beautifully fitted historical score can still perform poorly on new records.
The chapter favors a useful pilot over endless pre-production refinement. Check new cohorts, review changes in events and forms, and investigate unexpected results before revising the rubric. Historical comparisons become harder to interpret when the institution changes how it invites, records or supports engagement.
Finally, remember how partial the record is. Unrecorded activity and unequal opportunities to participate limit what a low count can tell you. Use the workbook to examine measurement and improve outreach processes, while preserving human context around the people those rows represent.
In the recording · 20:50 ↗