Practical framework / 5 MIN READ
Keep category changes from silently rewriting the chart
Treat reporting mappings as reviewed business logic with a visible history.
AI-assisted research and analysis for the SNS Creative Fieldbook
Sources checked 2026-11-06
A mapping from raw campaign codes to business categories can change a report without changing a single source event. Reliable comparisons require clarity about which mapping was used and whether history was intentionally restated.
What you will leave with: Create a mapping register with ownership, effective dates, collision tests and a deliberate historical policy.
Use this when
- A channel’s historical total shifts after someone edits a lookup sheet.
- New campaign codes fall into an unexplained other category.
This lesson governs analytical classifications, not campaign naming conventions or editorial content taxonomy.
Separate raw identity from interpretation
Retain the original source value and its source system. Store the reporting category separately so a new interpretation does not destroy the original evidence. A campaign code may legitimately map differently under a region view and a strategic-objective view.
List the mapping key, output category and owner. Avoid broad text rules that accidentally capture unrelated codes. If matching uses patterns, document precedence and test ambiguous examples. The reader should be able to explain why a specific raw record entered a category without consulting an undocumented spreadsheet formula.
Choose a controlled maintenance method
dbt describes seeds as reviewable, version-controlled CSV files suitable for relatively static mappings and cautions against storing sensitive production data there. A controlled database table or governed sheet can serve a similar organizational purpose when it supports review and history.
Choose the tool to fit update frequency and access needs. Keep secrets and unnecessary personal data out of mapping files. Require a reason for material reclassifications and identify the reviewer. A technically convenient location is not enough if nobody knows which copy is authoritative.
Decide what changes mean for history
Distinguish correcting an erroneous old rule from adopting a new business taxonomy. A correction may require restating past reports. A new strategic grouping may instead start at an effective date, with an optional separately labeled restated view for comparison.
Preserve the mapping version used for important published decisions. Test that effective-date intervals neither overlap nor leave unintended gaps. Do not apply a new category retrospectively merely because the dashboard refreshes all periods. State the chosen policy in plain language so stakeholders understand why history did or did not change.
Test coverage and reconcile the move
Check unique keys, permitted categories, unknown values and totals before and after a mapping release. A classification-only change should preserve the overall measure while moving amounts between categories, unless the release also includes an explicit scope change.
Prepare a small movement bridge for consequential edits: which categories gained or lost records and why. Assign unmapped values to a visible review queue rather than an invisible default. Add examples from real failures to the test set, using non-sensitive fixtures, so future rule changes do not revive the same ambiguity.
Make the reasoning concrete.
A hypothetical lookup reclassifies $2,000 of partner campaign cost from referral to partnerships. Total cost remains $12,000. If the dashboard recalculates all history without explanation, readers may mistake the category movement for a media strategy change.
Overwrite the lookup
Opening: Refresh historical charts using only the latest category.
Hypothesis: The taxonomy becomes consistent today.
Risk: Prior reports cannot be reproduced and the movement is unexplained.
Release a versioned mapping
Opening: Record the reason, historical policy and $2,000 category bridge.
Hypothesis: The total remains reconciled and readers understand the restatement.
Risk: A separate current-taxonomy view needs clear labeling.
Publish the mapping release with its historical treatment and retain the original codes. No campaign performance changed merely because its category name did.
Take the questions into your next brief.
Use these prompts in your own notes. Nothing is submitted from this page.
Key
Which raw identity drives the mapping?
Ownership
Who approves category changes?
History
Does this correction restate past periods or start prospectively?
Tests
How are collisions, gaps and unknowns detected?
Bridge
Which amounts move between categories?
Watch for the wrong conclusion.
- Replacing raw codes with friendly labels.
- Letting overlapping patterns pick arbitrary categories.
- Hiding unknown values in other.
- Restating history without a release note.
Your next action
Version one important reporting lookup and test its uniqueness, coverage and total-preservation behavior.
Sources & scope
Frameworks, worksheets and hypothetical examples are original editorial synthesis. Linked primary sources support only the stated concepts. Source checking is not an independent audit of effectiveness. No named human author or reviewer is claimed.
- dbt: Add seeds to your DAG →Primary technical documentation · Supports: Describes version-controlled, reviewable CSV seeds for relatively static mappings and warns against sensitive production data in seeds. Effective-date and dashboard governance steps are editorial recommendations.