---
name: data-transformation-and-dataset-preparation
description: >
  Builds the analysis-ready file and the codebook that documents it: recoding and
  collapsing categories against the analysis plan, deriving variables with their
  formulas recorded, constructing nets and summary variables, reverse-coding and
  checking scale direction, banding continuous variables, structuring
  multi-response data, reshaping between wide and long, merging files and
  wave-stacking trackers with a variable-comparability check first, and verifying
  every derived variable against source on a sample of cases. Use for "recode
  these variables", "collapse the age bands", "build a net", "reverse the scale",
  "derive a new variable", "merge these files", "stack the waves", "reshape wide
  to long", "produce the analysis file", "write the codebook".
category: 04 Data Preparation
ref: "04.04"
tier: 1
inherits: [K2, K3, K4, K5]
---

# Data Transformation and Dataset Preparation

## 1. One-line description
Turns a clean dataset into the analysis-ready file the analysis plan requires, building every recode, collapse, net, derived variable, band, reshape and merge as a documented and reversible construction, verifying each one against source on real cases, and producing a codebook complete enough that someone who has never seen the study can interpret any variable in it.

## 2. What this skill is used for

**The research problem it solves.** Transformation is where the shape of the answer gets decided. A five-band age variable collapsed into three, a satisfaction scale reversed to match a tracker, a net of "any negative experience", a spend variable banded into quartiles, an index built from six items, two files merged on a customer ID: each of these creates something that was never asked, and each is legitimate. What makes them dangerous is that the constructed variable then looks exactly like a collected one. Nobody downstream can tell that "high value customer" is a threshold somebody chose, or that "overall satisfaction" is a mean of six items two of which were reverse-worded, or that the age bands were collapsed after the charts were drafted because three bars looked better than five.

Two failures dominate. The first is the collapse driven by presentation rather than analysis: categories combined because the chart is cluttered, a band boundary moved because it puts a segment above 50%, a net constructed to make a difference visible. The rule is simple and needs stating because it is broken constantly: **collapsing decisions are driven by the analysis plan, not by what makes the chart look better.** The second is silent scale reversal. A scale reversed without the direction being checked, or a battery in which one item runs the other way and is not caught, produces a result that is internally consistent, plausible, and exactly inverted. It is one of the most damaging undetected errors in research, because there is nothing wrong with the arithmetic and nothing obviously wrong with the output. The only reliable protection is a direction check against the questionnaire on every scale variable, and a verification pass that reads real cases back to source.

**Where it sits.** After cleaning and missing-data decisions, immediately before analysis. It produces the file every analytical skill consumes, and the codebook every downstream reader depends on.

**Typical use cases.**
- Building the analysis file specified by an analysis plan.
- Collapsing demographic and behavioural categories into reportable groups.
- Deriving indices, composites, nets and summary measures from item batteries.
- Reverse-coding items and verifying scale direction across a whole instrument.
- Banding continuous variables such as age, spend, tenure or frequency.
- Merging survey data with client records, panel profile data or a previous wave.
- Stacking tracker waves into a single analysis file after checking comparability.
- Producing a codebook for a dataset that will outlive the project team.

**Who uses it.** Data managers and quantitative analysts building analysis files; researchers constructing derived measures; tracker managers stacking waves; anyone inheriting a dataset who needs to know what its variables actually contain.

## 3. When to use it

- An analysis plan exists and the file does not yet contain the variables it names.
- Categories need collapsing for reporting and the rule must be defensible.
- Derived measures, indices, nets or composites are required.
- Scales need reversing, or the direction of existing scales needs verifying.
- Continuous variables need banding and the boundaries have consequences.
- Two or more files must be merged and the key relationships must be checked before the merge.
- A tracker wave must be added to a stacked file and comparability must be established first.
- A dataset will be handed to somebody else, archived, or reused, and needs a codebook.
- Somebody has asked what a variable in an inherited file actually contains and nobody knows.

## 4. When NOT to use it

- **The data is not clean.** Transformation propagates defects and hides them: an out-of-range value inside a mean, a fragmented category inside a net, a duplicate inside a merge key. Once a defect is inside a derived variable it is much harder to find and much harder to explain. Run **04.01 Data Validation** and **04.02 Data Cleaning** first. The boundary with cleaning is intent: restoring a value to what it should have been is cleaning; building a new structure on correct values is transformation.
- **Missing-value conventions have not been decided.** A derived variable inherits the missing treatment of every component. An index built across six items with different missing rules produces a value whose base nobody can state. Settle **04.03 Missing Data Handling** first, and make every derivation state explicitly what it does when a component is missing.
- **There is no analysis plan and no objectives to work from.** Without them, collapsing and banding decisions have nothing to be driven by except appearance, which is precisely the failure this skill exists to prevent. Where no plan exists, derive only what the objectives clearly require, and state that the collapse rules were chosen without a documented plan.
- **The transformation is being requested because the current cut does not show what somebody wants.** Moving a band boundary, changing a net's composition or recollapsing categories after seeing the results is not preparation, it is result-shaping, and per K4 §4.2 it is the same act as cherry-picking with an extra step. If a genuinely better cut is proposed after the data is seen, report both, say which was specified in advance, and let the reader see the difference.
- **The analysis needs the underlying detail rather than a summary.** Banding a continuous variable discards information irreversibly for anyone who only receives the banded file. Where a relationship might be non-linear, where a threshold is the research question, or where a model will be fitted, keep the continuous variable and let the analysis skill decide. Banding is a reporting convenience and should be additive, never destructive.
- **The merge key is not reliable.** A merge on a partially matching, duplicated or reused key produces a file that looks complete and contains silently wrong joins. If the key cannot be validated, do not merge: report the match rate, describe the unmatched records, and analyse the files separately. A wrong join is undetectable downstream and contaminates every variable it touched.
- **Waves are not comparable and the difference cannot be quantified.** Stacking waves whose question wording, response list, scale, routing or mode changed produces a trend that is an artefact. The comparability check comes first, and where a variable is not comparable, it is excluded from the stacked file or carried with an explicit break marker. Trend interpretation belongs to **05.05 Trend and Tracker Analysis**, which is entitled to assume the stacking was checked.
- **The construction cannot be explained.** Per K4 §6.3, a derived variable whose formula cannot be stated in one line, and reproduced, does not belong in the file. That includes anything inherited from a previous project whose construction nobody can describe.

## 5. Required inputs

**Required.** Without these the skill cannot run. If absent, ask. Where work must proceed, choose the least destructive option, state it, and log it, per K5 §5.

- **The cleaned dataset**, with the 04.02 cleaning log and the 04.03 missing-data conventions.
- **The analysis plan or the research objectives**, which is what every collapse, band, net and derivation is justified against. This is the input that distinguishes preparation from presentation.
- **The questionnaire as fielded**, including every scale, its full label set and its direction, because scale direction is verified against the instrument and never against a variable name.
- **The codebook or data dictionary for the source file**, so that transformations are built on verified meanings.
- **For any merge: the key definition, its uniqueness properties in each file, and the expected match rate.** Without these the merge cannot be validated, only performed.
- **For any wave stack: the previous waves' questionnaires and codebooks**, which is what the comparability check is run against.

**Optional, and what each one adds.**

- **The previous wave's or previous study's derived variable specifications:** allow identical construction, without which a comparison across waves compares two different measures with the same name.
- **A house or client standard for demographic bands, segment definitions or index construction:** turns a judgement into a convention and makes results comparable with the client's other data.
- **Client record data or panel profile data:** enables merges that add measured rather than claimed variables, and permits validation of self-reported behaviour against records.
- **The intended reporting structure (table specification, chart list, deck outline):** reveals which collapses are actually needed, and usually shows that fewer are needed than assumed.
- **Sample and weighting design:** determines whether a band or a collapse will produce a subgroup too small to report, which is better known before the file is built.
- **Any published or standard definitions the client must align with:** determines band boundaries where external comparability matters more than internal fit.

## 6. Questions to ask before starting

1. **What does the analysis plan actually require, variable by variable?** Determines the whole build and prevents the common outcome of a file with forty derived variables of which six are used. *Default if unanswered:* derive only what the objectives clearly require, list what was not built, and state that the plan was unavailable.
2. **Which collapses are specified in advance and which are being proposed now?** Determines whether a collapse is a plan decision or a post-hoc one, which changes how it must be reported. *Default:* mark every collapse with the date it was decided, and report post-hoc collapses alongside the pre-specified version.
3. **Are there existing conventions for bands, nets or indices that must be matched?** A tracker, a client norm or a published standard usually outranks a better-fitting bespoke cut. *Default:* match the existing convention, and where it is materially misleading, report both and say what differs.
4. **What does each derivation do when a component is missing?** Determines the base of every derived variable and is the most commonly unspecified detail in the whole skill. *Default:* require all components present, set the derived variable to missing otherwise, report the resulting base, and state the rule.
5. **Will the underlying continuous variable be retained?** Determines whether banding is additive or destructive. *Default:* always retain the source variable alongside the band.
6. **For a merge: what is the key, is it unique in each file, and what match rate is expected?** Determines whether the merge is safe. *Default:* do not merge until this is answered; report match rates and unmatched records instead.
7. **For a wave stack: what changed between waves?** Determines which variables are comparable. *Default:* run the full comparability check on wording, response list, scale, routing, mode and base definition, and exclude or break-mark anything that differs.

## 7. Step-by-step methodology

**1. Write the transformation specification before building anything.** For each variable the analysis plan requires: its name, its label, its source variables, its construction rule in one line, its treatment of missing components, its expected valid range or category list, its expected base, and the plan objective it serves. The specification is written first because it is the thing that gets checked against, and because a variable that cannot be specified before it is built usually should not be built. *Correct result:* a specification table with no blank construction rules and no variable that cannot be traced to an objective.

**2. Verify scale direction on every scale variable in the source file, item by item, against the questionnaire.** Not against the variable name, not against the value labels in the data, and not against the assumption that a battery is consistent. Record for each scale: the number of points, the label at each end, which end is the positive one, and the numeric code attached to it. Then check consistency within each battery, because a single item running the other way is the defect this step exists to catch. Where the questionnaire is unavailable for a variable, mark the direction as unverified and do not build anything from it that depends on direction, per K4 §6.2. *Correct result:* a scale register covering every scale variable with direction confirmed against source, and every battery checked for internal consistency.

**3. Reverse-code deliberately, once, and prove it.** Reverse-coding is applied to make items run in a consistent direction before they are combined. Three rules make it safe. Reverse in a new variable, never in place, so the original remains. Use an explicit formula, stated in the log (for a 1 to 5 scale, new = 6 minus old), and state the scale length in the formula so a five-point rule is never applied to a seven-point item. Prove it by cross-tabulating the original against the reversed variable and confirming that the diagonal runs the way it should, and by checking that a reverse-coded item now correlates positively rather than negatively with the rest of its battery. **A silently reversed scale is one of the most damaging undetected errors in research**, because everything about the resulting output is coherent except its meaning. *Correct result:* a reversal log entry per item with the formula and the proof, and a battery correlation check that is now uniformly positive.

**4. Recode and collapse categories against the analysis plan, and record what is lost.** For each collapse: state the source categories, the target categories, the mapping, and the reason drawn from the plan. Legitimate reasons are analytical: the categories are not distinguished by the research question, a subgroup is too small to report separately, the client's other data uses these groupings, or a previous wave defined them this way. Illegitimate reasons are presentational: too many bars, an untidy legend, a category whose value is inconvenient. Two disciplines protect the result. Retain the source variable, always, so the collapse is reversible. And check the collapse against the distribution afterwards, because a collapse that puts 80% of the sample into one category has destroyed the variable's usefulness whatever the plan said. *Correct result:* a mapping table per collapse with the plan justification, the retained source variable, and a post-collapse distribution check.

**5. Derive variables with the formula recorded in the file's documentation, not in the analyst's head.** Every derived variable carries: the exact formula or logic, the source variables, the missing-component rule, the valid range, and the label. For an index or composite, additionally record whether items were standardised before combining, how the scale of the result should be read, and whether the internal consistency of the items was assessed. Where a derivation involves a threshold (high value, frequent user, at risk), the threshold is a decision and it must be recorded with its justification, because the threshold is the finding for anybody who uses that variable. *Correct result:* every derived variable reproducible by someone else from the documentation alone, without asking a question.

**6. Construct nets and summary variables explicitly, and never as a sum of parts.** A net of two or more categories is the count of respondents in any of them, not the sum of the individual percentages, because respondents can be in more than one and would be double counted. Define each net by its member codes, state the base, and check the net against a direct count. Where a net has a natural direct equivalent in the questionnaire (an "any" question that was actually asked), compare the two and report the difference rather than assuming they agree, since they frequently do not and the gap is informative. Watch the terminology: net also means a difference between two ends of a scale, and a file containing both senses without labels is a file that will produce a wrong chart. *Correct result:* a net definition table with member codes, base, direct-count verification, and unambiguous naming.

**7. Band continuous variables with stated boundaries, and be honest that the boundaries are arbitrary.** Every band boundary is a choice, and different defensible choices produce different findings from the same data. Three approaches, each with a cost. Substantively meaningful boundaries (retirement age, a price point, a contract term) are the most defensible and the least statistically tidy. External standard boundaries (a published age classification, a client's own segments) buy comparability and may fit the data badly. Distribution-driven boundaries (quartiles, equal intervals) fit the data and are unstable across waves and samples, since quartile boundaries move as the sample moves and a quartile in wave 3 is not a quartile in wave 4. State which approach was used and why, retain the continuous variable, and where a boundary sits near a mode, test whether moving it a little changes the conclusion, since a boundary that does is a boundary the finding depends on. *Correct result:* a band definition with the approach named, the boundaries stated, the continuous source retained, and a sensitivity note wherever a boundary is close to a concentration of cases.

**8. Structure multi-response data explicitly, and choose the layout the analysis needs.** Multi-response arrives in two shapes and they are not interchangeable. Multiple dichotomies means one binary variable per option, which handles arbitrary numbers of selections and is what most analysis wants. Multiple response sets means a fixed number of "mention" variables in selection order, which preserves order and wastes space. Document which shape each question uses, document whether order carries meaning (first mention is a real finding for prompted awareness and meaningless for a checkbox list), and define every "any of" net from the dichotomies rather than from the mention variables. Record the base as respondents and note that percentages sum above 100. *Correct result:* a documented structure per multi-response question, the shape chosen for the analysis, and nets built from dichotomies.

**9. Reshape between wide and long only when the analysis requires it, and verify the row count arithmetic.** Wide (one row per respondent) is right for cross-sectional analysis and reporting. Long (one row per respondent per item, wave or occasion) is right for repeated measures, multilevel models and some visualisation. When reshaping, check the arithmetic explicitly: rows in long should equal respondents multiplied by occasions, minus any legitimately absent combinations, and any discrepancy means a duplicate key or a lost record. Record which shape the analysis file is in, because a file whose shape is undocumented will eventually have a percentage calculated on the wrong denominator. *Correct result:* a reshaped file whose row count reconciles exactly, with the discrepancy of zero recorded.

**10. Merge files only after validating the key, and report the match in full.** Before merging: confirm the key exists in both files, confirm its uniqueness in each (a one-to-many relationship the analyst believes is one-to-one silently multiplies rows), check format compatibility (leading zeros, trailing spaces, case, numeric versus text keys are the usual culprits), and estimate the expected match rate. After merging: report matched, unmatched left, unmatched right, and the resulting row count against the expected one. Then check whether unmatched records differ systematically from matched ones, because a merge that fails disproportionately for one group introduces a bias that looks like a finding. Never let a merge quietly reduce the base without that reduction being reported and profiled. *Correct result:* a merge report with the key validation, the four counts, the row-count reconciliation, and a profile comparison of matched against unmatched.

**11. Run the wave-comparability check before stacking any tracker wave.** For every variable to be stacked, compare across waves: question wording, response option list and its order, scale length and direction, routing and base definition, mode and device mix, and the missing-value conventions. Classify each variable as fully comparable, comparable with a caveat, or not comparable. Only fully comparable variables enter a stacked trend file unmarked; caveated variables carry a break marker in the data itself so no analysis can use them without meeting it; non-comparable variables are excluded from the stack and reported as a series break. The check comes before the stack because after the stack a break is invisible. *Correct result:* a comparability table covering every stacked variable, with break markers in the file, not only in a document.

**12. Verify every derived variable against source on a sample of cases.** This step is mandatory and it is the one most often skipped. For every derived, recoded, reversed, banded, netted, reshaped or merged variable, select at least ten cases spanning the range of values, including at least one at each extreme, at least one with a missing component, and at least one that crosses a band boundary. Recompute the derived value by hand from the source variables and compare. Then check the aggregate: the derived variable's distribution against the source's, its base against the expected base, and its valid range against the specification. Record the sample checked and the result. **A derived variable that has not been verified against source on real cases is not finished**, whatever the code says, because the commonest transformation errors (an off-by-one in a reversal, a band boundary applied exclusively where it should be inclusive, a missing rule that silently zeroes) are all invisible in aggregate and obvious in a single case. *Correct result:* a verification record naming the cases checked per variable and the outcome, with any discrepancy traced and resolved.

**13. Write the codebook, as a required output.** For every variable in the analysis file: name, label, type, valid values and their labels, missing codes and their meanings, source (collected or derived), construction rule where derived, base description, scale direction and length where applicable, wave availability where relevant, and any break marker. The test is that someone who has never seen the study can open the codebook and interpret any variable in the file without asking a question. A dataset without a codebook is a dataset with a shelf life of about six weeks, which is how long the person who built it remembers what they did. *Correct result:* a codebook covering every variable, with no unexplained derived variables and no undocumented missing codes.

## 8. Analytical framework

Every transformation is recorded in the category's shared log structure, extended with the two fields transformation specifically needs:

    Original variable(s) → Transformation → Rule → Reason (from the analysis plan) → Impact → Verification → Reversal

**Original variable(s).** The source, named exactly as it appears in the cleaned file.
**Transformation.** Which operation: recode, collapse, reverse, derive, net, band, reshape, merge, stack.
**Rule.** The formula or mapping, in a form that can be re-executed, including the missing-component rule.
**Reason.** The analysis plan requirement this serves. "For reporting" is not a reason. "The plan compares under-35s with 35 and over on adoption, and no finer split is analysed" is.
**Impact.** What it changes: the base, the distribution, what becomes possible and what becomes impossible.
**Verification.** The cases checked against source and the outcome.
**Reversal.** The retained source variable or the mapping that permits reconstruction. Every transformation is additive by default: the source stays in the file.

Against the K2 chain, transformation sits between Evidence and Analysis, and it is the step where a construct can quietly replace a measurement. The codebook is what keeps the two distinguishable downstream: any variable a report describes as though it were asked, when it was in fact derived, has broken the chain.

## 9. Output format

**1. Transformation specification and log.** The required record, one row per transformation.

| ID | Source variable(s) | New variable | Transformation type | Rule (re-executable) | Missing-component rule | Reason (plan reference) | Impact | Verification result | Reversal |
|---|---|---|---|---|---|---|---|---|---|

**2. Scale register.** Every scale variable: points, end labels, positive end, numeric coding, direction verified against the questionnaire (yes or unverified), battery consistency check result.

**3. Reversal log.** Item, scale length, formula, cross-tab proof, post-reversal battery correlation direction.

**4. Collapse and band definitions.** Source categories, target categories, mapping, approach used (substantive, external standard, distribution-driven), plan justification, post-collapse distribution, boundary sensitivity note where a boundary sits near a concentration.

**5. Derived variable dictionary.** Name, formula, components, missing rule, valid range, threshold justification where a threshold exists, and the objective it serves.

**6. Net definition table.** Net name, member codes, base, direct-count verification, and disambiguation where the word net is used in more than one sense.

**7. Merge report.** Key, uniqueness in each file, format checks, matched, unmatched left, unmatched right, row-count reconciliation, and profile comparison of matched against unmatched.

**8. Wave comparability table.** Variable, wording change, response list change, scale change, routing change, mode change, classification (comparable, caveated, not comparable), break marker applied.

**9. Verification record.** Per transformed variable: cases checked, method, aggregate checks run, discrepancies found and resolved.

**10. Codebook.** The required output, covering every variable in the analysis file.

**Where the evidence is thin**, the format does not get filled. A scale whose direction could not be verified is marked unverified in the register and nothing direction-dependent is built from it. A merge whose key cannot be validated produces a match-rate report and two separate files, not a merged one. A wave variable whose comparability cannot be established is excluded from the stack and reported as a break, not stacked with a hopeful note. Per K4 §1, a specification row is not a reason to build a variable that the source cannot support.

## 10. Quality checks

Run before the analysis file is released. These sit on top of K4 §8.

1. Does every transformation trace to a requirement in the analysis plan or the objectives?
2. Was every collapse and band decided before the results were seen, and is any post-hoc collapse marked and reported alongside the pre-specified version?
3. Is the source variable retained for every recode, collapse, reversal and band?
4. Has scale direction been verified against the questionnaire for every scale variable, and is every unverified one marked?
5. Does every battery containing reverse-worded items show uniformly positive internal correlation after reversal?
6. Does every derived variable have a stated formula, a missing-component rule and a stated base?
7. Has every derived variable been verified against source on at least ten real cases spanning the range, including extremes, a missing component and a boundary case?
8. Is every net defined by member codes and verified against a direct count rather than summed from parts?
9. Are band boundaries stated with the approach used, and is a sensitivity note present wherever a boundary sits near a concentration of cases?
10. Does the row count reconcile exactly after every reshape and every merge?
11. Does the merge report include a profile comparison of matched against unmatched records?
12. Has the wave-comparability check been run and are break markers present in the data itself, not only in a document?
13. Does the codebook cover every variable, with no derived variable lacking its construction rule?
14. Could someone who has never seen the study interpret any variable in the file from the codebook alone?
15. Is any variable in the file that nothing in the analysis plan uses, and if so, is that deliberate?

## 11. Common failure modes

| Failure | How to recognise it | How to prevent it |
|---|---|---|
| **Silent scale reversal** | A coherent, plausible, exactly inverted finding; a battery item correlating negatively with its siblings | Verify direction item by item against the questionnaire; reverse in a new variable with a stated formula; prove with a cross-tab and a post-reversal correlation check |
| **Collapse for the chart** | Bands changed after a draft chart; a category boundary that happens to put a figure above a round number | Collapse rules dated and traced to the plan; post-hoc collapses reported alongside the pre-specified version |
| **Net as a sum of parts** | A net larger than the largest possible base, or exactly equal to the sum of overlapping categories | Define nets by member codes and verify against a direct count |
| **Threshold as fact** | "High value customers" reported with no statement of what the threshold was | Thresholds recorded with their justification in the codebook; the derived label always carries its definition |
| **Band boundary doing the work** | A finding that reverses when a boundary moves by one unit | Sensitivity check on any boundary near a mode; retain the continuous variable |
| **Destructive banding** | The continuous variable is gone from the analysis file | Transformations are additive; the source variable always stays |
| **Merge multiplication** | Row count rises after a merge that was believed one-to-one | Validate key uniqueness in both files before merging; reconcile row counts after |
| **Merge attrition unprofiled** | A base that drops at the merge with no note on who was lost | Report matched and unmatched counts and compare their profiles |
| **Stacking non-comparable waves** | A trend that starts exactly at a wave where the questionnaire changed | Comparability check before the stack; break markers in the data, not only in a document |
| **Derived variable with no missing rule** | A composite whose base nobody can state | Every derivation specifies what happens when a component is missing |
| **Undocumented inherited variable** | A variable in a file that nobody can explain | Per K4 §6.3, an unexplainable variable does not enter the analysis file |
| **AI: plausible construction** | A derived variable built from an assumed formula rather than a specified one | Formula comes from the specification or from the researcher; never inferred from a variable name |
| **AI: verifying in aggregate only** | Distribution checks pass while individual cases are wrong | Case-level verification on at least ten cases per variable, including extremes, missing components and boundaries |
| **AI: helpful extra variables** | An analysis file with derived variables nobody requested | Build only what the plan requires; list anything else separately as a proposal |

## 12. AI guardrails

Skill-specific only. K4 applies in full and is not repeated here.

1. **Never determine scale direction, scale length or code meaning from a variable name or a value label.** Verify against the questionnaire, or mark unverified and build nothing direction-dependent from it, per K4 §6.2.
2. **Never transform in place.** Every recode, collapse, reversal and band creates a new variable and retains the source. A transformation that cannot be reversed from the file is a transformation that should not have been applied.
3. **Never build a derived variable without a stated formula and a stated missing-component rule.** An unstated missing rule silently determines the base.
4. **Never construct a net by summing component percentages.** Nets are counts of respondents in any member category, verified against a direct count.
5. **Never choose or change a collapse, band boundary or net composition after seeing its effect on a result**, and where one is proposed post hoc, report it alongside the pre-specified version with both labelled.
6. **Never merge on an unvalidated key**, and never report a merged file without matched and unmatched counts and a profile comparison.
7. **Never stack tracker waves without the comparability check**, and never let a non-comparable variable into a trend file without a break marker in the data itself.
8. **Never release a derived variable that has not been verified against source on real cases**, and never treat an aggregate distribution check as a substitute for case-level verification.
9. **Never leave a variable in the analysis file whose construction cannot be described**, including inherited ones, per K4 §6.3.
10. **Never describe a derived variable in downstream output as though it were asked.** The codebook exists so that a report can say "derived from Q12 and Q14" instead of implying a question that was never put to anybody.
11. **Never build variables the analysis plan does not require** in order to make the file look complete. List proposals separately.

## 13. Best-practice principles

- **The analysis plan drives the collapse, and nothing else may.** If a category structure changes after somebody has seen a chart, the change has to be reported as post-hoc. This single discipline prevents most of the quiet result-shaping that happens in preparation.
- **Every transformation is additive.** The source variable stays. A file where every derived variable sits beside its inputs is a file anyone can audit; a file where transformations overwrote their sources is a file that has to be trusted.
- **Direction is checked, never assumed, and checked item by item.** Batteries are not uniformly coded as often as people expect, and the single reversed item inside an otherwise consistent battery is the classic undetected error.
- **A threshold is a finding.** "High value", "frequent", "at risk" and "engaged" are all decisions dressed as descriptions. Whoever set the threshold made the finding, and the definition has to travel with the label.
- **Band boundaries are arbitrary and should be admitted to be.** Test whether the conclusion survives moving a boundary. If it does not, the conclusion is about the boundary, not about the respondents.
- **Distribution-driven bands do not travel.** Quartiles recomputed each wave are not comparable across waves, and a "top quartile" that moves is a moving target reported as a fixed one.
- **Verify at case level, always.** Aggregate checks pass while individual values are wrong. Ten hand-checked cases per derived variable catch more errors than any amount of distribution inspection.
- **Merge failures are rarely random.** The records that fail to match usually differ from those that match, in ways that matter. Profile them before deciding the merge was successful.
- **The comparability check has to precede the stack.** After stacking, a break in a series is invisible, and a wave-on-wave movement caused by a wording change is indistinguishable from a real one.
- **Multi-response structure is a decision, not a format.** Which shape the data is in determines what can be calculated and how easily a wrong denominator gets used.
- **The codebook is the dataset's memory.** Without it, an analysis file has a useful life of about as long as the person who built it remembers the details, which is shorter than everybody assumes.

## 14. Worked example

**INPUT.** A fictional public sector agency runs an annual citizen satisfaction tracker across four service areas. Wave 5, achieved n=2,400, cleaned and with missing-data conventions settled. The analysis plan specifies: an overall service quality index from an eight-item battery, comparison of under-35s with 35 and over, a net of "any negative service experience", banded contact frequency, and a stacked trend file covering waves 1 to 5.

**PROCESS.**

*Steps 1 to 2.* Specification written for 19 derived variables. Scale register built for all 34 scale variables against the questionnaire. Two findings: items 3 and 7 of the eight-item quality battery are negatively worded and coded in the same direction as the rest, meaning that in the raw file a high score on item 3 means dissatisfaction. Also, the battery's scale changed from 5 points in waves 1 to 3 to 7 points in waves 4 and 5.

*Step 3.* Items 3 and 7 reverse-coded into new variables using new = 6 minus old for waves 1 to 3 and new = 8 minus old for waves 4 and 5, with the scale length in the formula precisely so that the wrong constant cannot be applied to the wrong wave. Cross-tab proof run on both. Post-reversal, all eight items correlate positively within the battery, where previously items 3 and 7 correlated negatively with the other six. That negative correlation was the detection signal, and had the reversal not been done the index would have been coherent, plausible and wrong by roughly the contribution of two items.

*Step 5, and the judgement call.* The index is a mean of the eight items. Two questions arise. First, the scale change means wave 5 index values are on a 1 to 7 scale and waves 1 to 3 on 1 to 5. **Resolution:** the index is built twice, once in raw scale units for within-wave reporting, and once rescaled to a 0 to 100 range for trend comparison, with both in the file, both in the codebook, and an explicit note that the rescaling assumes the two scales measure the same construct with the same interval properties, which is an assumption and not a fact. The trend chart uses the rescaled version and carries the assumption on the page. Second, the missing-component rule: requiring all eight items present would drop 214 respondents. **Resolution:** require at least six of eight, compute the mean of those present, and add a component-count variable so any analysis can restrict to complete cases. The rule is stated in the codebook, and the index is reported both ways once, showing a difference of 0.4 points on a 0 to 100 scale.

*Step 4.* The plan specifies under-35 versus 35 and over. The source is a seven-band age variable, retained. Collapse mapped and justified against the plan. Post-collapse check: 31% under 35, 69% over, which is a usable split. A separate request arrives to move the boundary to 40 because "the story is cleaner". Declined as a plan deviation, and reported: the finding at 35 is a 6-point gap, at 40 it is a 9-point gap, both reported, with the pre-specified cut labelled as such and used in the headline.

*Step 6.* The "any negative experience" net is defined from five dichotomies. Direct count gives 512 respondents; the sum of the five individual percentages would have implied 731, a 43% overstatement caused by respondents reporting more than one negative experience. The net is built from the dichotomies and verified against the direct count.

*Step 7.* Contact frequency banded. Distribution-driven quartiles were the initial proposal and are rejected for a tracker, because quartile boundaries move each wave and a "top quartile" that redefines itself annually cannot support a trend. Substantive boundaries used instead (none, 1 to 2, 3 to 5, 6 or more contacts a year), which match the agency's own operational reporting. Continuous variable retained.

*Step 11.* Comparability check across five waves on 34 tracked variables: 28 fully comparable, 4 caveated (the scale change on the quality battery, plus three response lists that gained an option at wave 4), 2 not comparable (a question rewritten at wave 3). The two non-comparable variables are excluded from the stack and reported as a series break. Break markers are written into the stacked file as a variable, so that no analysis can use the caveated items without encountering the marker.

*Step 12.* Verification on 19 derived variables, 12 cases each. Two errors found and fixed: the contact frequency band applied its upper boundary exclusively where the specification said inclusive, misplacing 38 cases; and the index component-count variable counted reverse-coded items twice. Neither was visible in the aggregate distribution. Both were visible immediately in a single hand-checked case.

**OUTPUT.** An analysis file with 19 documented derived variables and every source retained; a scale register covering 34 variables with direction verified; a reversal log with formulas and proofs for the two negatively worded items; collapse and band definitions with plan justifications and one declined post-hoc request reported both ways; a net verified against a direct count; a stacked five-wave file with break markers embedded; a verification record naming 228 hand-checked cases and the two errors it caught; and a codebook covering every variable in the file.

## 15. Advanced usage

**Index construction and internal consistency.** Where a composite is built from a battery, assess whether the items behave as one construct before averaging them. Items that do not correlate with the rest are either measuring something else or are reverse-coded and uncorrected, and the second possibility should be excluded before the first is concluded. Report the basis on which items were combined, and where a composite is used as an outcome, hand the construct-validity question to **02.07 Scale and Measurement Selection** rather than resolving it inside a preparation step.

**Preparing for modelling rather than reporting.** Modelling and reporting want different files. Reporting wants collapsed categories and bands; modelling wants continuous variables, dummy structures with a declared reference category, and no collinear derived variables built from each other. Build one file with everything and document which variables are for which purpose, rather than building two files that will drift apart. Coordinate with **05.06 Correlation, Regression and Causal Claim Control** on the dummy and reference-category conventions.

**Retrospective documentation of an inherited file.** Where a file arrives with undocumented derived variables, attempt reconstruction: correlate each derived variable against candidate sources, test candidate formulas against a sample of cases, and confirm or reject each hypothesis. What can be recovered is the formula. What cannot is the reason it was chosen and the threshold justification. Document what was reconstructed, mark what could not be, and per K4 §6.3 do not report a variable whose construction remains unknown.

**Long-running trackers and definition drift.** Over many waves, definitions drift: a band gets extended, a net gains a member, a scale is refreshed. Maintain a versioned variable specification with the wave range each version applies to, and store the version in the stacked file itself. The alternative, which is a document describing what was supposed to happen, does not survive contact with a team change.

**Multi-country files.** Category lists, band conventions and scale usage differ by market for real reasons. Build market-specific variables and a harmonised cross-market variable side by side, document both, and never let the harmonised version silently replace the local one, since the local version is usually the one that is correct for in-market reporting and the harmonised one is a compromise built for comparison.

## 16. Skill chain

**Recommended previous skills:**
- **04.02 Data Cleaning.** Hands over a clean file with harmonised categories and a cleaning log, so that transformations are built on values that are correct rather than merely present.
- **04.03 Missing Data Handling.** Hands over the missing-value conventions every derived variable must respect, and without which no derived variable has a statable base.
- **01.07 Analysis Plan Development.** Hands over the requirement set that justifies every collapse, band, net and derivation, and is what separates preparation from presentation.

**Recommended next skills:**
- **04.05 Weighting and Base Management.** Takes the prepared file and its subgroup structure, which the weighting targets are defined against.
- **05.01 Descriptive Analysis.** Takes the analysis file and the codebook, and must state the derivation rule alongside any figure for a derived variable.
- **05.05 Trend and Tracker Analysis.** Takes the stacked file with its comparability classifications and break markers already established, and is entitled to assume the stacking was checked.
- **09.01 Audience Segmentation** and **05.06 Correlation, Regression and Causal Claim Control**, which both consume derived variables and need the construction rules to interpret their outputs.

**Runs well alongside:**
- **02.07 Scale and Measurement Selection**, where a composite's construct validity rather than its arithmetic is in question.
- **K2**, since the codebook is what keeps a derived variable distinguishable from a measured one all the way into the report.
- **13.03 AI Output Verification**, run against the derived variables and the codebook before analysis begins.

---
A Yazi Supplied Skill and resource.