BrandX campaign effectiveness · Step 5 of 6

Data preparation and analysis

From raw survey responses to validated tables and research findings.

Synthetic portfolio study

What this step does

Step 5 takes the simulated field file from Step 4 and turns it into evidence. The file is cleaned, coded against a codebook, weighted to population targets and tabulated in SPSS Custom Tables. The tables are the source for every figure in the Step 6 deck. BrandX, the respondents and their answers are synthetic; the workflow, the decisions and the syntax are the ones I would use on a live study.

1,295records received from the field

1,095completed interviews

1,000valid responses analysed

Two hundred non-completes (screen-outs, abandons and invalid records) and 95 quality failures (75 speeders and 20 straight-liners) were excluded. The final sample splits evenly by study group:

500 exposed
watched Channel A or B in the past month

500 control
watched neither channel

What Step 5 produces

Prepared dataset: 1,000 cases, coded, labelled and weighted, with derived analysis variables.
Documented variable definitions: codebook entries and derivation rules for every variable used in a table.
Analysis specification: banner, bases, testing rules and table plan, written before tabulation.
Statistical tables: 17 SPSS Custom Tables exports covering the brand funnel and the advertising diagnostics.
Interactive results: a Power BI report of the weighted tables, filterable by subgroup, under Explore the Results.
Hand-off to Step 6: validated tables that the deck and the Step 6 decisions are built on.

All six outputs are documented on this page. Downloadable files are listed, with their availability, under Run & Validate; the syntax shown in the accordions is copyable as-is.

Prepare the Data

Eight steps take the 1,295-record field file to a weighted, analysis-ready SPSS dataset of 1,000 cases. Each step states the problem it solves, the rule applied, a short example from the file and the result. Long syntax sits inside "View SPSS syntax" so the reasoning reads first.

The raw file

The simulated field delivery looks like a file from an online panel provider: one row per respondent who started the survey, response text rather than codes, a completion status, an interview length and a quality flag. The preview below shows the first ten records with the columns that matter for cleaning. The complete 13-column preview and the full column list are in the expandable section.

First ten raw records, selected columns, before cleaning
respondent_id	Completed	Completion_Time (min)	Data_Quality	A5_ChannelA	A5_ChannelB	B4	Cleaning outcome
EL5	1	9.4	Straight-liner	Rarely	2-3 times a week	Neutral	Removed: identical answers on all six C5 statements
E347	1	15.6	Valid	Rarely	Never	Very likely	Kept
C208	1	5.9	Valid	Never	Never	Very unlikely	Kept
E43	1	9.9	Valid	Rarely	2-3 times a week	Somewhat likely	Kept
E98	1	11.5	Valid	Never	2-3 times a week	Neutral	Kept
C373	1	16.2	Valid	Never	Never	Very unlikely	Kept
E438	1	11.8	Valid	Never	Daily	Very likely	Kept
CL1	1	11.4	Straight-liner	Never	Never	Neutral	Removed: identical answers on all six C5 statements
ES13	1	1.9	Speeder	Daily	Never	Very unlikely	Removed: finished in 1.9 minutes against a 12.6-minute median
C49	1	11.2	Valid	Never	Never	Somewhat unlikely	Kept

Shaded rows carry a Data_Quality flag; the last column states why each one is removed. The A5 columns decide the study group: any answer other than "Never" on Channel A or B places a respondent in the exposed group.

View the complete ten-record preview and the full column list

First ten raw records, thirteen columns, before cleaning
respondent_id	Completed	Termination_Point	Completion_Time	Data_Quality	A2	A3	A4	A5_ChannelA	A5_ChannelB	Exposed_Flag	B4	C4
EL5	1	Completed	9.4	Straight-liner	Man	45-54	Ontario	Rarely	2-3 times a week	1	Neutral	3
E347	1	Completed	15.6	Valid	Man	35-44	Ontario	Rarely	Never	1	Very likely	5 - Enjoyed it a lot
C208	1	Completed	5.9	Valid	Man	55-64	Prince Edward Island	Never	Never	0	Very unlikely
E43	1	Completed	9.9	Valid	Woman	25-34	Ontario	Rarely	2-3 times a week	1	Somewhat likely	5 - Enjoyed it a lot
E98	1	Completed	11.5	Valid	Woman	45-54	Prince Edward Island	Never	2-3 times a week	1	Neutral	3
C373	1	Completed	16.2	Valid	Man	25-34	Quebec	Never	Never	0	Very unlikely
E438	1	Completed	11.8	Valid	Woman	65+	Quebec	Never	Daily	1	Very likely	4
CL1	1	Completed	11.4	Straight-liner	Woman	25-34	Ontario	Never	Never	0	Neutral
ES13	1	Completed	1.9	Speeder	Man	45-54	Quebec	Daily	Never	1	Very unlikely	2
C49	1	Completed	11.2	Valid	Man	55-64	Quebec	Never	Never	0	Somewhat unlikely

Blank C4 cells belong to control respondents, who never see the advertisement. Exposed_Flag is the routing flag set by the survey platform from the Channel A and B answers; it is not used for analysis. The analysis variable, Study_Group, is re-derived from A5_ChannelA and A5_ChannelB in SPSS (step 4) and checked against the routing flag. The full file holds 73 field variables: A1 to A6 (screening, media and category behaviour), B1 to B5 (brand measures, including seven open-text B1 boxes, seven B2 check-boxes and eight B2a ratings), C1 to C7 (advertising diagnostics) and D1 to D10 (shopper and demographic profile).

1Cleaning: 1,295 records to 1,000 valid responses

Purpose: Remove records that are not complete interviews, and completed interviews whose answers cannot be trusted.
Decision: Two passes in Excel. First, exclude every record that did not reach the end of the questionnaire, using the Completed and Termination_Point fields. Second, among completed interviews, remove speeders (finished in under 3.6 minutes, roughly a quarter of the 12.6-minute median, with borderline cases reviewed against their response pattern) and straight-liners (identical answers on all six C5 statements).
Example: From the preview aboveES13 completed the survey in 1.9 minutes and is removed as a speeder. EL5 and CL1 gave the same answer to all six agreement statements and are removed as straight-liners. C208 finished in 5.9 minutes, above the threshold, and is kept.
Result: 200 non-completes excluded (70 screened out at A1, 60 at A6, 51 abandoned mid-survey, 19 invalid), then 95 quality failures removed from 1,095 completes. 1,000 valid responses remain, 91.3% of completed interviews, a removal rate within the usual 5 to 10% commercial range.

200Non-completes excluded15.4% of the file

75Speeders removed6.8% of completes

20Straight-liners removed1.8% of completes

1,000Valid responses91.3% of completes

View Excel checks

Completeness screen
=IF(AND(Completed=1,Termination_Point="Completed"),"Keep","Exclude")

Speeder detection (threshold = about a quarter of the 12.6-minute median)
=IF(Completion_Time<3.6,"Review","Valid")

Straight-lining detection (six C5 agreement statements, exposed respondents)
=IF(COUNTA(UNIQUE(C5_Connected:C5_Believable))=1,"Flag","Valid")

2Coding and labelling against the codebook

Purpose: The cleaned file still holds response text ("Somewhat likely", "2-3 times a week"). SPSS needs numeric codes to tabulate and test, and every output needs to read in plain language.
Decision: One reference, BrandX_Full_Codebook.xlsx, lists every variable with its question wording, value codes and measurement level. Closed questions are coded to their numeric value in Excel with a lookup against the codebook, then the SPSS syntax attaches variable and value labels. Open text (B1_1 to B1_7, C6 and the "Other, please specify" fields) is imported as string and coded separately (step 5 and the Code Open Responses tab).
Example: From the codebookB4 "Very unlikely" becomes 1, "Somewhat likely" becomes 4. A2 "Woman" becomes 2. Provinces keep their alphabetical codes, so Alberta is 1 and Yukon is 13, matching the codebook exactly.
Result: A numeric SPSS file in which every closed variable carries the codebook's labels, so tables print "Somewhat likely" rather than "4" without any later relabelling.

View SPSS syntax: codebook labels (excerpt)

* Encoding: UTF-8.
* Variable and value labels from BrandX_Full_Codebook.xlsx (excerpt).
VARIABLE LABELS
  A2 'What is your gender?'
  A3 'What is your age range?'
  A4 'What province or territory do you currently live in?'
  A5_ChannelA 'Past-month viewing frequency, TV Channel A'
  A5_ChannelB 'Past-month viewing frequency, TV Channel B'
  A6_PotatoChips 'Past-month consumption frequency, potato chips'
  B2_BrandX 'Aided brand awareness: BrandX selected'
  B2a_BrandX 'Overall impression rating: BrandX (1 Poor - 10 Excellent)'
  B3 'How familiar are you with BrandX?'
  B4 'When considering purchasing potato chips, how likely are you to consider BrandX?'
  B5 'Likelihood to recommend BrandX to a friend or family member (1-10)'
  C1 'Have you seen or heard any advertisements for BrandX recently? (pre-exposure)'
  C3 'After watching, do you recall seeing this ad before? (post-exposure)'
  C4 'On a scale of 1 to 5, how much did you enjoy the advertisement?'
  C7 'Which of the following do you believe was the main message of the advertisement?'.

VALUE LABELS A2 1 'Man' 2 'Woman' 3 'Gender diverse' 4 'Prefer not to say'.
VALUE LABELS A3 1 '18-24' 2 '25-34' 3 '35-44' 4 '45-54' 5 '55-64' 6 '65+'.
VALUE LABELS A5_ChannelA TO A5_ChannelE
  1 'Daily' 2 '2-3 times a week' 3 'A few times a month' 4 'Rarely' 5 'Never'.
VALUE LABELS B3
  1 'I have never heard of this brand'
  2 'I have heard of this brand but know very little about it'
  3 'I am somewhat familiar with this brand but have never consumed it'
  4 'I am familiar with this brand and have consumed it occasionally'
  5 'I am very familiar with this brand and consume it regularly'.
VALUE LABELS B4
  1 'Very unlikely' 2 'Somewhat unlikely' 3 'Neutral' 4 'Somewhat likely' 5 'Very likely'.
VALUE LABELS C4
  1 '1 - Did not enjoy it at all' 2 '2' 3 '3' 4 '4' 5 '5 - Enjoyed it a lot'.
VALUE LABELS C5_Connected TO C5_Believable
  1 'Strongly disagree' 2 'Somewhat disagree' 3 'Neither agree nor disagree'
  4 'Somewhat agree' 5 'Strongly agree'.
VALUE LABELS C7
  1 'Great taste and flavor' 2 'Made with high-quality ingredients'
  3 'A healthier or better snack choice' 4 'Fun and enjoyable for any occasion'
  5 'Crunchy and satisfying texture' 6 'Limited-time offer or promotion'
  7 'Other (please specify)'.
EXECUTE.

3Demographic groupings: age bands, regions and age-by-gender cells

Purpose: The questionnaire captures six age brackets and 13 provinces and territories. Subgroup columns need larger groups to be readable and testable.
Decision: Collapse age to the three bands used in the Step 3 quotas (18-34, 35-54, 55+) and provinces to six regions. Build an age-by-gender cell variable for the interlocking quota check. Keep the original six age brackets for profiling.
Example: From the recode rulesA3 codes 1 and 2 (18-24, 25-34) become Age_Group3 = 1 "18-34". A4 codes 1, 3 and 12 (Alberta, Manitoba, Saskatchewan) become Region6 = 2 "Prairies".
Result: Age_Group3, Region6 and AgeGender6. Age_Group3 carries every subgroup table; Region6 is used for the sample profile and is the sensible raking dimension if the weighting is refined (step 7).

View SPSS syntax: demographic derived variables

* Three-band age group (matches the field quota bands).
NUMERIC Age_Group3 (F1.0).
RECODE A3 (1, 2 = 1) (3, 4 = 2) (5, 6 = 3) INTO Age_Group3.
VARIABLE LABELS Age_Group3 'Three-band age group'.
VALUE LABELS Age_Group3 1 '18-34' 2 '35-54' 3 '55+'.
VARIABLE LEVEL Age_Group3 (ORDINAL).

* Six-region reporting group from the 13 alphabetical province codes.
NUMERIC Region6 (F1.0).
RECODE A4 (2 = 1) (1, 3, 12 = 2) (9 = 3) (11 = 4) (4, 5, 7, 10 = 5) (6, 8, 13 = 6) INTO Region6.
VARIABLE LABELS Region6 'Six-region reporting group'.
VALUE LABELS Region6 1 'BC' 2 'Prairies' 3 'Ontario' 4 'Quebec' 5 'Atlantic' 6 'Territories'.
VARIABLE LEVEL Region6 (NOMINAL).

* Age x gender analysis cell (men and women only; gender-diverse respondents stay in totals).
NUMERIC AgeGender6 (F1.0).
DO IF (A2 = 1).
+ RECODE Age_Group3 (1=1) (2=3) (3=5) INTO AgeGender6.
ELSE IF (A2 = 2).
+ RECODE Age_Group3 (1=2) (2=4) (3=6) INTO AgeGender6.
END IF.
VARIABLE LABELS AgeGender6 'Age x gender analysis cell'.
VALUE LABELS AgeGender6 1 'M18-34' 2 'F18-34' 3 'M35-54' 4 'F35-54' 5 'M55+' 6 'F55+'.
VARIABLE LEVEL AgeGender6 (NOMINAL).
EXECUTE.

4Study group, channel exposure and category usage

Purpose: Define who counts as exposed and who counts as control, and build the category-usage flags the behavioural columns rely on.
Decision: Exposure means opportunity to see: any past-month viewing of Channel A or Channel B at any frequency other than "Never" places a respondent in the exposed group; "Never" on both places them in control. The study group is derived in SPSS from the two channel questions rather than taken from the platform's routing flag, and the two are cross-checked. A heavy chip user eats potato chips daily or two to three times a week.
Example: From the preview aboveE98 answered "Never" for Channel A and "2-3 times a week" for Channel B: exposed. C373 answered "Never" for both: control. Being exposed does not mean the respondent saw the BrandX spot, only that they watched a channel that carried it; this is why group differences are read as descriptive, not causal.
Result: Study_Group (500 exposed, 500 control), a Channel_Exposure detail variable kept for a future channel-level read, and Chip_Consumer / Heavy_Chip_User (210 heavy and 290 light users in the exposed group; 219 and 281 in control).

View SPSS syntax: study group and channel exposure

* Channel viewing flags (codes 1-4 = viewed at least rarely; 5 = never).
NUMERIC ChannelA_View_Flag ChannelB_View_Flag (F1.0).
RECODE A5_ChannelA (1 THRU 4 = 1) (5 = 0) INTO ChannelA_View_Flag.
RECODE A5_ChannelB (1 THRU 4 = 1) (5 = 0) INTO ChannelB_View_Flag.
VARIABLE LABELS ChannelA_View_Flag 'Viewed Channel A at least rarely'
                ChannelB_View_Flag 'Viewed Channel B at least rarely'.
VALUE LABELS ChannelA_View_Flag ChannelB_View_Flag 0 'Never' 1 'Viewed'.
VARIABLE LEVEL ChannelA_View_Flag ChannelB_View_Flag (NOMINAL).

* Study group: 1 = Exposed (either channel), 2 = Control (neither).
NUMERIC Study_Group (F1.0).
DO IF (ChannelA_View_Flag = 1 OR ChannelB_View_Flag = 1).
+ COMPUTE Study_Group = 1.
ELSE IF (ChannelA_View_Flag = 0 AND ChannelB_View_Flag = 0).
+ COMPUTE Study_Group = 2.
END IF.
VARIABLE LABELS Study_Group 'Study Group (Exposed vs. Control)'.
VALUE LABELS Study_Group 1 'Exposed' 2 'Control'.
VARIABLE LEVEL Study_Group (NOMINAL).

* Channel exposure detail, kept for a future channel-level read.
NUMERIC Channel_Exposure (F1.0).
DO IF (ChannelA_View_Flag = 1 AND ChannelB_View_Flag = 0).
+ COMPUTE Channel_Exposure = 1.
ELSE IF (ChannelA_View_Flag = 0 AND ChannelB_View_Flag = 1).
+ COMPUTE Channel_Exposure = 2.
ELSE IF (ChannelA_View_Flag = 1 AND ChannelB_View_Flag = 1).
+ COMPUTE Channel_Exposure = 3.
ELSE.
+ COMPUTE Channel_Exposure = 0.
END IF.
VARIABLE LABELS Channel_Exposure 'Channel A/B Exposure Detail'.
VALUE LABELS Channel_Exposure 0 'Neither Channel (Control)' 1 'Channel A Only'
  2 'Channel B Only' 3 'Both Channels (A & B)'.
VARIABLE LEVEL Channel_Exposure (NOMINAL).

* Potato-chip consumer and heavy-user flags (banner: Chip usage).
NUMERIC Chip_Consumer Heavy_Chip_User (F1.0).
RECODE A6_PotatoChips (1 THRU 4 = 1) (5 = 0) INTO Chip_Consumer.
RECODE A6_PotatoChips (1 THRU 2 = 1) (3 THRU 5 = 0) INTO Heavy_Chip_User.
VARIABLE LABELS Chip_Consumer 'Potato-chip consumer status'
                Heavy_Chip_User 'Chip consumption behaviour'.
VALUE LABELS Chip_Consumer 0 'Non-consumer' 1 'Consumer'
             /Heavy_Chip_User 0 'Light user' 1 'Heavy user'.
VARIABLE LEVEL Chip_Consumer Heavy_Chip_User (NOMINAL).
EXECUTE.

* Check: study groups should land at 500 / 500 and agree with the field routing flag.
FREQUENCIES VARIABLES=Study_Group.
CROSSTABS /TABLES=Study_Group BY Exposed_Flag /CELLS=COUNT.

5Unaided recall: from seven text boxes to one flag per brand

Purpose: B1 asks respondents to list the chip brands that come to mind, in up to seven boxes of free text. Analysis needs one clean yes/no per brand per respondent.
Decision: Recode each box against a 17-brand code frame that absorbs spelling and capitalisation variants, then collapse the seven coded boxes into one binary flag per brand (mentioned anywhere = 1). Add a catch-all "None / Unrecognized" flag so every respondent is counted in the base, and register the flags as a multiple response set so SPSS treats them as one question. The seven B2 aided-awareness check-boxes arrive as binaries and are registered the same way.
Example: From the code frame"Lays", "lay's" and "Lays chips" all recode to 2 Lay's. If a respondent writes "Lays" in box 1 and "Lay's" again in box 4, both boxes carry code 2, and the respondent-level flag B1_Mention_Lays equals 1 once. Percentages are therefore respondent-based: the share of people who named the brand, not the share of mentions.
Result: Nineteen flags (17 brands, other, none) and two multiple response sets, $B1_Unaided_Awareness and $Aided_Awareness. The "Other brands" row reads 0.0% in every column of the unaided table, which means no mention fell outside the named frame; in a live study that row is where new competitors first appear.

View SPSS syntax: B1 code frame, brand flags and MRSETS

* B1 open-ends to numeric brand codes (excerpt of the 17-brand frame).
NUMERIC B1_1_recoded TO B1_7_recoded (F2.0).
RECODE B1_1 TO B1_7
  ('', ' ' = 0)
  ('brand x', 'Brand X', 'brandx', 'Brandx', 'BrandX', 'BrandX chips' = 1)
  ('lay''s', 'Lay''s', 'lays', 'Lays', 'Lays chips' = 2)
  ('dorito', 'doritos', 'Doritos' = 3)
  ('pringle', 'Pringle', 'pringles', 'Pringles' = 4)
  ('ruffles', 'Ruffles', 'Ruffles All Dressed' = 5)
  ('cheetos', 'Cheetos' = 6)
  ('Kettle', 'Kettle Brand', 'kettle chips', 'Kettle Chips' = 7)
  ('Miss Vickie''s', 'miss vickies', 'Miss Vickies', 'Miss Vickys' = 8)
  ('old dutch', 'Old dutch', 'Old Dutch' = 9)
  ('tostitos', 'Tostitos' = 10)  ('takis', 'Takis' = 11)
  ('Sun Chips', 'sunchips', 'SunChips' = 12)  ('cape cod', 'Cape Cod' = 13)
  ('utz', 'Utz' = 14)  ('Herr''s', 'herrs', 'Herrs' = 15)
  ('PC chips', 'President''s Choice' = 16)
  ('Compliments', 'Great Value', 'no name', 'store brand' = 17)
  (MISSING = 0) (ELSE = 99)
  INTO B1_1_recoded B1_2_recoded B1_3_recoded B1_4_recoded
       B1_5_recoded B1_6_recoded B1_7_recoded.
VALUE LABELS B1_1_recoded TO B1_7_recoded
  0 'No mention / Blank' 1 'BrandX' 2 'Lay''s' 3 'Doritos' 4 'Pringles' 5 'Ruffles'
  6 'Cheetos' 7 'Kettle Brand' 8 'Miss Vickie''s' 9 'Old Dutch' 10 'Tostitos'
  11 'Takis' 12 'SunChips' 13 'Cape Cod' 14 'Utz' 15 'Herr''s'
  16 'President''s Choice / PC' 17 'Private Label / Store Brands' 99 'Other Brands'.

* One binary flag per brand: mentioned at any of the seven positions.
COMPUTE B1_Mention_BrandX = ANY(1, B1_1_recoded, B1_2_recoded, B1_3_recoded,
    B1_4_recoded, B1_5_recoded, B1_6_recoded, B1_7_recoded).
COMPUTE B1_Mention_Lays   = ANY(2, B1_1_recoded, B1_2_recoded, B1_3_recoded,
    B1_4_recoded, B1_5_recoded, B1_6_recoded, B1_7_recoded).
* ... repeated for codes 3 to 17 and 99 ...
COMPUTE B1_Mention_None = (B1_Mention_BrandX = 0 AND B1_Mention_Lays = 0 AND
    B1_Mention_Doritos = 0 AND B1_Mention_Pringles = 0 AND B1_Mention_Ruffles = 0 AND
    B1_Mention_Cheetos = 0 AND B1_Mention_KettleBrand = 0 AND B1_Mention_MissVickies = 0 AND
    B1_Mention_OldDutch = 0 AND B1_Mention_Tostitos = 0 AND B1_Mention_Takis = 0 AND
    B1_Mention_SunChips = 0 AND B1_Mention_CapeCod = 0 AND B1_Mention_Utz = 0 AND
    B1_Mention_Herrs = 0 AND B1_Mention_PC = 0 AND B1_Mention_StoreBrand = 0 AND
    B1_Mention_Other = 0).
VARIABLE LABELS B1_Mention_BrandX 'Unaided recall: BrandX'
  B1_Mention_None 'Unaided recall: None / Unrecognized / Don''t Know'.
VALUE LABELS B1_Mention_BrandX TO B1_Mention_None 0 'Not mentioned' 1 'Mentioned'.
VARIABLE LEVEL B1_Mention_BrandX TO B1_Mention_None (NOMINAL).
EXECUTE.

* Multiple dichotomy sets for CTABLES.
MRSETS
  /MDGROUP NAME=$B1_Unaided_Awareness CATEGORYLABELS=VARLABELS
    VARIABLES=B1_Mention_BrandX B1_Mention_Lays B1_Mention_Doritos B1_Mention_Pringles
    B1_Mention_Ruffles B1_Mention_Cheetos B1_Mention_KettleBrand B1_Mention_MissVickies
    B1_Mention_OldDutch B1_Mention_Tostitos B1_Mention_Takis B1_Mention_SunChips
    B1_Mention_CapeCod B1_Mention_Utz B1_Mention_Herrs B1_Mention_PC
    B1_Mention_StoreBrand B1_Mention_Other B1_Mention_None VALUE=1
  /MDGROUP NAME=$Aided_Awareness CATEGORYLABELS=VARLABELS
    VARIABLES=B2_BrandX B2_Lays B2_Pringles B2_Ruffles B2_Utz B2_KettleBrand
    B2_Herrs B2_Other B2_None VALUE=1.

6Analysis variables: funnel nets, ad diagnostics and banner flags

Purpose: Every table and every deck chart must use the same definition of "top-2 box", "promoter" or "heavy user". Defining them once, in the file, prevents drift between outputs.
Decision: Three families of derived variables. Brand funnel nets: impression 9-10 (asked only of respondents aware of BrandX), familiarity and consideration top-2 box (points 4 and 5 on their 5-point scales), and recommendation bands on the 1-10 scale (detractor 1-6, passive 7-8, promoter 9-10). Advertising diagnostics: recall and recognition "yes" flags, enjoyment top-2 box, six C5 agreement top-2 box flags, and taste / quality nets from the single-response C7. Banner flags: primary shopper, high-frequency snack buyer, partner status and children in household.
Example: From the recode rulesA B5 recommendation score of 9 becomes Recommendation_Band = 3 "Promoter"; a score of 6 becomes 1 "Detractor". NPS is promoters minus detractors. Children_Banner deliberately leaves households with only adult children system-missing, so the "No" column means no children at all and does not absorb empty-nesters; its two columns sum to fewer than 1,000 (exposed 157 + 205, control 165 + 215).
Result: Twenty-three analysis variables, each with a label that prints on the tables, plus the four banner flags. Their definitions are repeated in the variable specification under Plan the Analysis.

View SPSS syntax: derived analysis variables

* --- Brand funnel nets (Section B, total base) ---.
NUMERIC BrandX_Impression_910 Familiarity_T2B Consideration_T2B
        Recommendation_Band Recommendation_910 (F1.0).
RECODE B2a_BrandX (9, 10 = 1) (1 THRU 8 = 0) INTO BrandX_Impression_910.
RECODE B3 (4, 5 = 1) (1 THRU 3 = 0) INTO Familiarity_T2B.
RECODE B4 (4, 5 = 1) (1 THRU 3 = 0) INTO Consideration_T2B.
RECODE B5 (1 THRU 6 = 1) (7, 8 = 2) (9, 10 = 3) INTO Recommendation_Band.
RECODE B5 (9, 10 = 1) (1 THRU 8 = 0) INTO Recommendation_910.
VARIABLE LABELS BrandX_Impression_910 'BrandX impression 9-10'
                Familiarity_T2B 'BrandX familiarity Top-2 Box'
                Consideration_T2B 'BrandX consideration Top-2 Box'
                Recommendation_Band 'BrandX recommendation band'
                Recommendation_910 'BrandX recommendation 9-10'.
VALUE LABELS BrandX_Impression_910 0 '1-8' 1 '9-10'
  /Familiarity_T2B Consideration_T2B 0 '1-3' 1 '4-5'
  /Recommendation_Band 1 'Detractor (1-6)' 2 'Passive (7-8)' 3 'Promoter (9-10)'
  /Recommendation_910 0 '1-8' 1 '9-10'.
VARIABLE LEVEL BrandX_Impression_910 Familiarity_T2B Consideration_T2B
  Recommendation_910 (NOMINAL) /Recommendation_Band (ORDINAL).

* --- Advertising diagnostics (Section C, exposed base) ---.
NUMERIC AdRecall_Yes AdRecognition_Yes Enjoyment_T2B (F1.0).
RECODE C1 (1 = 1) (2, 3 = 0) INTO AdRecall_Yes.
RECODE C3 (1 = 1) (2, 3 = 0) INTO AdRecognition_Yes.
RECODE C4 (4, 5 = 1) (1 THRU 3 = 0) INTO Enjoyment_T2B.
VARIABLE LABELS AdRecall_Yes 'Recent BrandX ad recall'
                AdRecognition_Yes 'Recognizes shown ad as seen before'
                Enjoyment_T2B 'Ad enjoyment Top-2 Box'.
VALUE LABELS AdRecall_Yes AdRecognition_Yes 0 'No/Not sure' 1 'Yes'
  /Enjoyment_T2B 0 '1-3' 1 '4-5'.

NUMERIC C5_Connected_T2B C5_Informative_T2B C5_Creative_T2B
        C5_Memorable_T2B C5_TryBrandX_T2B C5_Believable_T2B (F1.0).
RECODE C5_Connected   (4, 5 = 1) (1 THRU 3 = 0) INTO C5_Connected_T2B.
RECODE C5_Informative (4, 5 = 1) (1 THRU 3 = 0) INTO C5_Informative_T2B.
RECODE C5_Creative    (4, 5 = 1) (1 THRU 3 = 0) INTO C5_Creative_T2B.
RECODE C5_Memorable   (4, 5 = 1) (1 THRU 3 = 0) INTO C5_Memorable_T2B.
RECODE C5_TryBrandX   (4, 5 = 1) (1 THRU 3 = 0) INTO C5_TryBrandX_T2B.
RECODE C5_Believable  (4, 5 = 1) (1 THRU 3 = 0) INTO C5_Believable_T2B.
VARIABLE LABELS C5_Connected_T2B 'Connected with me: Top-2 Box'
  C5_Informative_T2B 'Informative: Top-2 Box'  C5_Creative_T2B 'Creative: Top-2 Box'
  C5_Memorable_T2B 'Memorable: Top-2 Box'
  C5_TryBrandX_T2B 'Made me want to try BrandX: Top-2 Box'
  C5_Believable_T2B 'Believable: Top-2 Box'.
VALUE LABELS C5_Connected_T2B TO C5_Believable_T2B 0 '1-3' 1 '4-5'.

NUMERIC C7_Taste C7_Quality C7_TasteQuality (F1.0).
RECODE C7 (1 = 1) (2 THRU 7 = 0) INTO C7_Taste.
RECODE C7 (2 = 1) (1, 3 THRU 7 = 0) INTO C7_Quality.
RECODE C7 (1, 2 = 1) (3 THRU 7 = 0) INTO C7_TasteQuality.
VARIABLE LABELS C7_Taste 'Prompted taste message'
                C7_Quality 'Prompted quality-ingredients message'
                C7_TasteQuality 'Prompted taste-or-quality net'.

* --- Classification flags for the banner ---.
NUMERIC Shopping_Role SnackFreq2 Significant_other_Flag Children_Banner (F1.0).
RECODE D1 (1 = 1) (2, 3 = 0) INTO Shopping_Role.
RECODE D2 (1 THRU 3 = 1) (4 THRU 6 = 0) INTO SnackFreq2.
RECODE D6 (2 = 1) (1, 3, 4 = 0) (5 = SYSMIS) INTO Significant_other_Flag.
IF (D7 = 2) Children_Banner = 0.
IF (D7 = 1 AND (D8_Under5 = 1 OR D8_5_12 = 1 OR D8_13_17 = 1)) Children_Banner = 1.
VARIABLE LABELS Shopping_Role 'Primary grocery shopper'
  SnackFreq2 'Snack purchase frequency: High (daily to weekly) vs Low (monthly or less)'
  Significant_other_Flag 'Significant other'
  Children_Banner 'Children in Household (Banner Group)'.
VALUE LABELS Shopping_Role 0 'Non-primary' 1 'Primary'
  /SnackFreq2 0 'Low' 1 'High'
  /Significant_other_Flag 0 'No' 1 'Yes'
  /Children_Banner 0 'No' 1 'Yes - Under 18'.
VARIABLE LEVEL AdRecall_Yes TO Children_Banner (NOMINAL).
EXECUTE.

7Weighting: rim weighting to census targets

Purpose: The Step 3 quotas were set for field efficiency, not to mirror the population: age bands of 35 / 34 / 31 percent against a census profile closer to 29 / 32 / 39, and provinces rounded to workable targets. Reported percentages should describe Canadian adults, which is how the deck presents them.
Decision: Rim weighting (raking) on three dimensions, age (A3), gender (A2) and province (A4), to census targets, iterated until each margin matches within 0.0001. The weight is inspected before use (histogram, minimum, maximum and standard deviation) and the weighted profile is checked against the targets. From this point every table shows weighted percentages on unweighted bases.
Example: Reading a weighted tableThe base row of every table is requested as TOTALS[UCOUNT], so it reports the count of real respondents (500 exposed, 500 control) while the percentages are weighted. The SPSS footnote "cell counts of some categories are not integers" on each output is the visible trace that the weight is on.
Result: Weight_Final2, applied to every table. The achieved unweighted file was already close to plan (500 / 500 by study group; 482 men, 502 women, 16 gender-diverse; age bands 349 / 338 / 313), so the weight is aligning a quota sample to the population rather than repairing a field problem. Because the rake runs on all 13 provinces and territories, the three territorial targets are tiny (around 0.1% each); in a live study the refinement would be to rake on Region6 instead. The weighting efficiency and effective sample size are not reported here; they belong beside the bases in a live deliverable.

View SPSS syntax: rim weighting (SPSSINC RAKE)

* Rim weighting (raking) to census targets: age (A3), gender (A2), province (A4).
WEIGHT OFF.
SPSSINC RAKE
 DIM1=A3
   1 .10989246  2 .18358062  3 .17081187
   4 .14810840  5 .15471817  6 .23288848
 DIM2=A2
   1 .490  2 .500  3 .005  4 .005
 DIM3=A4
   1 .118971  2 .137440  3 .036162  4 .020801  5 .013253
   6 .001097  7 .026241  8 .001004  9 .391272  10 .004355
   11 .218007  12 .030242  13 .001153
 FINALWEIGHT=Weight_Final2
 /OPTIONS ITERATIONS=50 CONVERGENCE=.0001 DELTA=.5 SHOW=NO
 /PLOT HISTOGRAM=YES.

* Review the weight before using it.
DESCRIPTIVES VARIABLES=Weight_Final2 /STATISTICS=MEAN STDDEV MIN MAX.

* Apply, then confirm the weighted profile matches the targets.
WEIGHT BY Weight_Final2.
FREQUENCIES VARIABLES=A2 A3 A4.

8Validation and codebook

Purpose: Confirm the prepared, weighted file behaves as designed before any production table is run, and leave it documented so another analyst can pick it up cold.
Decision: Four checks: the weighted profile against the targets; the unweighted frequencies that will become the banner bases; exposed-only items (C3 onwards) missing for every control respondent; and the two multiple response sets registered and readable. The file is documented in BrandX_Full_Codebook.xlsx: every field and derived variable with its name, question wording, value labels, measurement level and derivation rule.
Example: Banner bases from the unweighted frequenciesIn the exposed group: 238 men and 252 women; 176 / 171 / 153 by age band; 265 primary shoppers; 269 high-frequency snack buyers; 210 heavy chip users; 252 with a partner; 157 with children under 18; 208 / 203 / 89 urban / suburban / rural. Rural (89 exposed, 103 control) is the smallest column and is read against the Step 3 floor of n = 50.
Result: An analysis-ready SPSS file, its codebook, the syntax file and the CTABLES exports, kept together so every number in the deck can be traced to its table and every table to its syntax.

View SPSS syntax: validation checks

* Weighted profile against targets; unweighted bases for the banner columns.
FREQUENCIES VARIABLES=A2 Age_Group3 Region6 Study_Group.
WEIGHT OFF.
FREQUENCIES VARIABLES=Study_Group Shopping_Role SnackFreq2 Heavy_Chip_User
  Significant_other_Flag Children_Banner D9.
WEIGHT BY Weight_Final2.

* Exposed-only items must be missing for every control respondent.
CROSSTABS /TABLES=Study_Group BY C3 /CELLS=COUNT /MISSING=INCLUDE.

* Multiple response sets registered and readable.
MRSETS /DISPLAY NAME=[$B1_Unaided_Awareness $Aided_Awareness].

Code Open Responses

Two open-ended questions are analysed. Unaided brand recall (B1) has a natural code frame, brand names, and is coded in SPSS. The campaign message take-away (C6) has no ready-made frame and is read first through word frequencies, before any theme frame is imposed.

The synthetic verbatims carry the texture of real open ends: capitalisation and spelling variants ("Lays", "lay's"), typos ("A chisp commercial", "buy BarndX chiips") and non-answers ("idk", "?", "can't really say").

B1: Unaided brand recall

"When you think of potato chips, what brands come to mind first? Please list as many as you can think of."

Purpose: Measure spontaneous recall of BrandX and its competitors without prompting, and compare the exposed and control groups.
Decision: Explore the raw text first to see which spellings need grouping, then code in SPSS with the 17-brand frame from preparation step 5 so the flags carry the weight and the significance tests. Report respondent-based percentages: the share of people who named the brand anywhere in their list.
Example: Exploration before codingA custom BrandX Verbatim Analyzer (a browser tool that counts comma-separated brand mentions, filters and exports a frequency table) was used to read the raw B1 text and surface the variants the code frame needed to absorb. The tool is not hosted with this page; see the availability table under Run & Validate.
Result: The coded table below. Weighted percentages, unweighted bases (total 1,000; exposed 500, control 500). A letter marks a column significantly higher than the lettered column at 95% confidence (A = exposed, B = control).

Unaided brand recall, coded results, total and by study group
Rank	Brand	Total	Exposed (A)	Control (B)
1	Lay's	45.2%	42.1%	48.2%
2	Pringles	39.8%	38.8%	40.8%
3	Ruffles	34.3%	31.7%	36.8%
4	Doritos	29.2%	30.4%	28.0%
5	BrandX	23.2%	30.3% B	16.1%
6	Miss Vickie's	19.4%	21.0%	17.9%
7	Kettle Brand	18.9%	15.9%	21.8% A
8	Cheetos	15.6%	15.5%	15.6%
9	Tostitos	11.7%	11.6%	11.8%
10	Old Dutch	11.4%	12.5%	10.4%
11	Takis	10.8%	9.0%	12.5%
12	SunChips	9.7%	9.0%	10.4%
13	Cape Cod	6.7%	7.6%	5.7%
14	Herr's	5.2%	3.6%	6.8% A
15	Private label / store brands	5.1%	4.7%	5.5%
16	Utz	4.3%	4.9%	3.7%
17	President's Choice / PC	2.6%	2.6%	2.7%
–	Other brands	0.0%	0.0%	0.0%
–	None / unrecognized / don't know	3.0%	3.8%	2.2%

Reading the table. Lay's leads unaided recall in both groups. BrandX sits fifth overall at 23.2%, but the study-group split is the finding: 30.3% of exposed respondents name BrandX unprompted against 16.1% of control, the only brand with a significant exposed-over-control difference. Kettle Brand and Herr's run higher in control; with a self-selected exposure group this is expected noise rather than a campaign effect, and it is listed among the limitations in Step 6.

C6: Unaided message take-away

"In your own words, please describe the main message or key takeaway of the BrandX potato chips advertisement you just saw."

Purpose: Check what exposed respondents took from the advertisement in their own words, as a test of the prompted C7 result before a theme frame is imposed.
Decision: A weighted word-frequency pass in Python: normalise case and common misspellings, drop stop words, count each word once per respondent (so a respondent who writes "taste" three times contributes one weighted mention) and report the share of respondents using each word. This is the read that feeds the word cloud on slide 27 of the deck. Word shares are kept separate from coded theme shares: a respondent can use several words, so word percentages must not be added together.
Example: From the verbatims"Full of flavoour. That's what stuck with me", "full of flavour. thats what stuck wwith me" and "Full off lavour" are the same take-away with different typos; after normalisation each contributes one weighted mention of flavour. "idk", "?" and "can't really say" are counted as no clear take-away.
Result: The word table below, on the exposed base (unweighted n = 500). 21% of exposed respondents gave no clear take-away.

Share of exposed respondents using each word in their C6 answer
Word	% of respondents	Message it points to
chips	43%	context, no message
flavour	16%	Taste
taste	16%	Taste
BrandX	9%	context, no message
good	8%	Taste / general
crunchy	7%	Texture
ingredients	6%	Quality
brand	5%	context, no message
people	5%	Social / fun
quality	4%	Quality
delicious	4%	Taste
bold	4%	Taste
love	4%	Taste / general
yummy	4%	Taste
friends	4%	Social / fun
crunch	4%	Texture
healthier	3%	Health
fun	3%	Social / fun
premium	2%	Quality
texture	2%	Texture

Each row is the share of respondents who used that word at least once. Rows overlap and are not additive. Representative verbatims from the deck: "That the flavour is bold and you'll love the taste" (4% of respondents); "That satisfying crunch sound" (2%); "Less greasy than other chips, maybe healthier" (2%).

Reading the table. Taste language leads (flavour and taste each at 16%, with delicious, bold, yummy and love behind them) and quality language trails (ingredients 6%, quality 4%, premium 2%). The unaided read and the prompted C7 result agree: taste and flavour is the message respondents take out (34% prompted), roughly double the intended quality-ingredients message (19% prompted).

View Python script: weighted word frequencies

# C6 weighted word frequencies for the exposed group (n = 500)
# Documented reconstruction of the method behind C6_word_cloud_data.xlsx
import re
import pandas as pd
from collections import defaultdict

df = pd.read_csv("C6_exposed_with_weights.csv")   # respondent_id, C6, Weight_Final2

STOP = {"the", "a", "an", "and", "of", "to", "it", "is", "was", "that", "they",
        "are", "in", "on", "for", "with", "i", "you", "about", "them", "their"}
FIX = {"flavor": "flavour", "flavoour": "flavour", "flavoru": "flavour",
       "cruncchy": "crunchy", "chisp": "chips", "chhips": "chips",
       "barndx": "brandx", "thhink": "think"}
NO_ANSWER = re.compile(r"^(\?|idk|dont know|don't know|dnot know|cant really say|"
                       r"i can'?t rem+e?mber|don'?t r+ecall the message|dont recall the message)$")

def tokens(text):
    words = re.findall(r"[a-z']+", str(text).lower())
    return {FIX.get(w, w) for w in words if w not in STOP and len(w) > 2}

weighted = defaultdict(float)
no_clear = 0.0
total_w = df["Weight_Final2"].sum()

for _, row in df.iterrows():
    text = str(row["C6"]).strip().lower()
    if NO_ANSWER.match(text):
        no_clear += row["Weight_Final2"]
        continue
    for w in tokens(text):                     # one count per respondent per word
        weighted[w] += row["Weight_Final2"]

out = (pd.DataFrame({"word": weighted.keys(), "weighted_n": weighted.values()})
         .assign(pct_of_respondents=lambda d: 100 * d.weighted_n / total_w)
         .sort_values("weighted_n", ascending=False))
out.to_csv("C6_word_cloud_data.csv", index=False)
print(f"No clear take-away: {100 * no_clear / total_w:.0f}% of exposed respondents")

Future extension Coded theme flags. The SPSS file carries ten placeholder flags for C6 (C6_Taste, C6_Quality, C6_Health, C6_Fun, C6_Texture, C6_Promotion, C6_BrandOnly, C6_NoClearMessage, C6_Other and a C6_LowConfidence review flag). They are not populated in this study. In a live engagement the word-frequency read would become the dictionary for coding them, low-confidence auto-codes would be routed to a human coder, and the coded flags would run through the same banner as C7 so unaided and prompted message shares sit side by side. Because they are unpopulated, no coded theme percentages are reported on this page.

Plan the Analysis

Before any table is run, the analysis is written down as a specification: the questions to answer, the columns every question is broken by, how variables become nets, who is counted, how differences are tested, and which tables get produced. The specification is executed exactly as written under Run & Validate.

Three terms used throughout

Banner: The set of columns every table is broken by: here, study group plus eight subgroup breaks. A "nested" banner shows exposed and control side by side within each subgroup.
Base: Who is counted in a table, and how many of them. Bases on this page are unweighted respondent counts; percentages are weighted.
Top-2 box: The share choosing the two highest points of a scale: 4 and 5 on a 5-point scale, or 9 and 10 on a 10-point scale.

Research questions the tables answer

The Step 2 objectives translate into five questions. Each maps to a table family below.

Do exposed respondents report higher brand awareness, familiarity, consideration and recommendation than control? (Objective 1, H1)
Among exposed respondents, how many recall and recognise the advertising? (Objective 2, H2)
How do exposed respondents rate the advertisement itself: enjoyment and the six agreement statements? (Objective 3, H3)
What message do exposed respondents take out, unprompted and prompted? (Objective 4, H4)
Which audiences show the largest differences on each of the above? (Objective 5, H5, read across the subgroup columns of every table)

The channel-level comparison proposed in Step 2 (Objective 6, H6) was not tabulated in this wave. Channel_Exposure is retained in the file for a follow-up read; the deck and Step 6 report study-group results only.

1. Banner plan

One banner serves the whole study. For the Section B brand measures each subgroup pair is nested under study group, so every column pair is exposed against control within that subgroup. For the Section C advertising diagnostics the same breaks run flat on the exposed base, because control respondents never saw the advertisement.

Study banner: breaks, columns, unweighted bases and source variables
Break	Columns	Exposed n	Control n	Source variable
Study group	Exposed (A) / Control (B)	500	500	Study_Group
Gender	Man / Woman	238 / 252	239 / 251	A2 (codes 1, 2)
Age	18-34 / 35-54 / 55+	176 / 171 / 153	174 / 171 / 155	Age_Group3
Shopping role	Primary / Non-primary	265 / 235	274 / 226	Shopping_Role
Snack purchase	High / Low frequency	269 / 231	281 / 219	SnackFreq2
Chip usage	Heavy / Light user	210 / 290	219 / 281	Heavy_Chip_User
Partner	Yes / No	252 / 230	237 / 241	Significant_other_Flag
Children	Under 18 / No children	157 / 205	165 / 215	Children_Banner
Community type	Urban / Suburban / Rural	208 / 203 / 89	214 / 183 / 103	D9

Gender-diverse respondents (n = 16) and "prefer not to say" answers on partner status are counted in the totals but are not tested columns.

2. Variable specifications

Every banner point and net is defined once, in writing, and executed in preparation steps 3 to 6.

View the variable specification table

Spec item	Definition	Source variable(s)
Exposed / Control	Any viewing of Channel A or B at any frequency other than "Never" = Exposed; "Never" on both = Control	A5_ChannelA, A5_ChannelB → Study_Group
Age bands	18-34 / 35-54 / 55+, collapsed from the six questionnaire brackets	A3 → Age_Group3
Unaided awareness	Brand named at any of the seven B1 positions, after coding to the 17-brand frame	B1_1 to B1_7 → $B1_Unaided_Awareness
Aided awareness	Brand ticked in the B2 check list	B2_BrandX to B2_None → $Aided_Awareness
Impression top-2 box	9 or 10 on the 10-point impression scale, asked only of respondents aware of BrandX	B2a_BrandX → BrandX_Impression_910
Familiarity and consideration top-2 box	Points 4 and 5 on the 5-point scales	B3 → Familiarity_T2B; B4 → Consideration_T2B
Recommendation	Detractor 1-6, passive 7-8, promoter 9-10 on the 1-10 scale; NPS = promoters minus detractors	B5 → Recommendation_Band, Recommendation_910
Ad recall and recognition	"Yes" on C1 (recent recall) and C3 (recognition after viewing); "No" and "Not sure" are the complement	C1 → AdRecall_Yes; C3 → AdRecognition_Yes
Enjoyment and statement agreement	Points 4 and 5 on the 5-point scales (enjoyed / agree)	C4 → Enjoyment_T2B; C5_* → C5_*_T2B
Message nets	Taste, quality and taste-or-quality flags from the single-response C7	C7 → C7_Taste, C7_Quality, C7_TasteQuality
Heavy chip user	Eats potato chips daily or 2-3 times a week	A6_PotatoChips → Heavy_Chip_User
High-frequency snack buyer	Buys snacks daily, several times a week or weekly	D2 → SnackFreq2
Children in household	At least one child under 18 vs no children; adult-only households excluded from both	D7, D8_* → Children_Banner
Region	Six regions collapsed from 13 provinces and territories (profile only)	A4 → Region6

3. Base definitions

Content	Base	Unweighted n
Brand awareness, familiarity, consideration, recommendation (B1 to B5)	All QC-valid completes, exposed and control	1,000 (500 / 500)
Brand impression (B2a)	Respondents aware of BrandX (aided)	660 (393 / 267)
Advertising diagnostics (C1, C3, C4, C5, C6, C7)	Exposed respondents only	500
Advertising source (C2)	Exposed respondents recalling a BrandX ad (C1 = Yes)	subset of 500
All tables	Weighted percentages (Weight_Final2), unweighted bases (preparation step 7)	–

The impression base matters: because only aware respondents rate the brand, the impression figures describe people who already know BrandX, not the population. The deck states this base on every impression slide.

4. Testing rules

Column proportions are compared with two-proportion z-tests at the 95% confidence level, two-sided, the standard set in Step 2. A letter beside a value means that column is significantly higher than the lettered column. Tests run on weighted proportions with unweighted bases; SPSS rounds fractional weighted cell counts to integers before testing and excludes categories with a proportion of zero or one (for example the empty "Other brands" row), as its footnotes state.

No multiple-comparison adjustment is applied (ADJUST=NONE). The subgroup comparisons are exploratory and the deck reports them with that caveat. The Step 3 reporting floor is applied at interpretation: cells below n = 50 are not cited, and the rural column (89 exposed, 103 control) is read as indicative. In Step 6 the within-subgroup exposed-versus-control differences are shown with ▲ / ▼ markers, the same test rendered for a non-technical reader.

5. Table plan

Eleven table families cover the five research questions. Each traces to a hypothesis and to the deck slide it feeds.

Table	Stub (rows)	Banner and base	Question	Deck slides
B1 Unaided brand awareness	$B1_Unaided_Awareness, 17 brands plus none	Nested, total (1,000)	1, 5	6, 9, 10, 11, 12
B2 Aided brand awareness	$Aided_Awareness, 7 brands plus other / none	Nested, total (1,000)	1, 5	6, 9, 10, 11, 12
B2a Brand impression	BrandX_Impression_910	Nested, aware of BrandX (393 / 267)	1, 5	6, 13, 14
B3 Familiarity	Familiarity_T2B	Nested, total	1, 5	6, 15, 16
B4 Consideration	Consideration_T2B	Nested, total	1, 5	6, 17, 18
B5 Recommendation	Recommendation_Band (detractor / passive / promoter)	Nested, total	1, 5	6, 19, 20
C1 Recent ad recall	AdRecall_Yes	Flat, exposed (500)	2, 5	7, 22
C3 / C4 Recognition and enjoyment	C3 categories; C4 full distribution and top-2 box	Flat, exposed	2, 3, 5	7, 23, 24
C5 Ad statements	Six top-2 box agreement flags (full scale in the long version)	Flat, exposed	3, 5	7, 25, 26
C6 Unaided take-away	Weighted word frequencies (Code Open Responses)	Exposed	4	27
C7 Prompted main message	Seven message options	Flat, exposed	4, 5	7, 28, 29

Run & Validate

The specification is executed in IBM SPSS Statistics Custom Tables (CTABLES), the module research agencies use for production tabulation, with the weight applied. This tab shows a readable example output, explains how to read it, gives the syntax patterns that produced all eleven table families, and lists what is available for download.

Example output: consideration by study group and subgroup

An extract from Table B4 (consideration top-2 box), transcribed from the CTABLES export. The full table carries all eight subgroup breaks; the extract shows the total, gender and age columns.

Table B4 extract: BrandX consideration top-2 box, exposed versus control within total, gender and age
B4. Likely to consider BrandX	Total		Men		Women		18-34		35-54		55+
B4. Likely to consider BrandX	Exposed (A)	Control (B)	Exposed (A)	Control (B)	Exposed (A)	Control (B)	Exposed (A)	Control (B)	Exposed (A)	Control (B)	Exposed (A)	Control (B)
Base (unweighted)	500	500	238	239	252	251	176	174	171	171	153	155
1-3 (not top-2 box)	42.4%	63.9% A	44.7%	63.9% A	39.8%	63.8% A	41.4%	58.8% A	41.3%	70.1% A	44.2%	62.7% A
4-5 (top-2 box)	57.6% B	36.1%	55.3% B	36.1%	60.2% B	36.2%	58.6% B	41.2%	58.7% B	29.9%	55.8% B	37.3%

How to read it

Base: the first row is the unweighted number of respondents in each column; the percentages beneath are weighted.
Percentage: each column sums to 100% across the two rows. The highlighted row is the top-2 box net used in the deck.
Comparison groups: within every subgroup, the pair of columns is exposed (A) against control (B). The banner nests study group under gender and age, so the men's pair is compared with itself, not with women.
Significance notation: a letter marks the column that is significantly higher than the lettered column at 95% confidence. "57.6% B" means the exposed share is significantly higher than control; "63.9% A" on the 1-3 row is the same test from the other side.
Reading the result: in every column pair shown, the exposed group reports higher consideration than control, with the widest gap among 35-54s (58.7% vs 29.9%).

Syntax patterns behind the tables

Three structural patterns cover the plan. Each element of the specification maps to a CTABLES clause: the stub and banner become the /TABLE expression, nesting is written with the > operator, the testing rules become /COMPARETEST, the weighted-percentage / unweighted-base convention becomes COLPCT.COUNT with TOTALS[UCOUNT], and the exposed-only base becomes a FILTER.

Pattern 1: nested brand measure (produces B1 to B5, B2a)

The block that produced the example above. The > operator nests Study_Group under each subgroup; MERGE=YES places the significance letter beside the higher percentage.

View SPSS syntax: Table B4, consideration

* Table B4: consideration top-2 box, exposed vs control within every subgroup.
WEIGHT BY Weight_Final2.
CTABLES
  /VLABELS VARIABLES=Consideration_T2B Study_Group A2 Age_Group3 Shopping_Role
    SnackFreq2 Heavy_Chip_User Significant_other_Flag Children_Banner D9
    DISPLAY=LABEL
  /TABLE Consideration_T2B [C][COLPCT.COUNT PCT40.1, TOTALS[UCOUNT 'Base' F40.0]]
    BY Study_Group [C]
     + A2 [C] > Study_Group [C]
     + Age_Group3 [C] > Study_Group [C]
     + Shopping_Role [C] > Study_Group [C]
     + SnackFreq2 [C] > Study_Group [C]
     + Heavy_Chip_User [C] > Study_Group [C]
     + Significant_other_Flag [C] > Study_Group [C]
     + Children_Banner [C] > Study_Group [C]
     + D9 [C] > Study_Group [C]
  /SLABELS POSITION=ROW VISIBLE=NO
  /CATEGORIES VARIABLES=Consideration_T2B [0, 1] EMPTY=INCLUDE TOTAL=YES POSITION=BEFORE
  /CATEGORIES VARIABLES=Study_Group Age_Group3 D9 ORDER=A KEY=VALUE EMPTY=INCLUDE
  /CATEGORIES VARIABLES=A2 [1, 2] EMPTY=INCLUDE
  /CATEGORIES VARIABLES=Shopping_Role SnackFreq2 Heavy_Chip_User
    Significant_other_Flag Children_Banner [1, 0] EMPTY=INCLUDE
  /CRITERIA CILEVEL=95
  /TITLES
    TITLE='Table B4: Top-2 Brand Consideration, BrandX (Exposed vs Control)'
    CORNER='B4. When you are considering purchasing potato chips, how likely are you '+
      'to consider BrandX?'
    CAPTION='Base: all QC-valid completes (unweighted n=1,000). Weighted percentages, '+
      'unweighted bases. Column proportions tested at 95% confidence (z-test).'
  /COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES
    CATEGORIES=ALLVISIBLE MERGE=YES STYLE=SIMPLE SHOWSIG=NO.

Pattern 2: multiple response set (produces B1 and B2)

INCLUDEMRSETS=YES extends the column tests to the set's categories, and the explicit category list fixes the row order to the total-sample ranking. The set includes B1_Mention_None so the base reconciles to every respondent.

View SPSS syntax: Table B1, unaided awareness

* Table B1: unaided brand awareness by study group (multiple dichotomy set).
CTABLES
  /VLABELS VARIABLES=$B1_Unaided_Awareness Study_Group DISPLAY=LABEL
  /TABLE $B1_Unaided_Awareness [C][COLPCT.COUNT PCT40.1, TOTALS[UCOUNT 'Base' F40.0]]
    BY Study_Group [C]
  /SLABELS POSITION=ROW VISIBLE=NO
  /CATEGORIES VARIABLES=$B1_Unaided_Awareness
    [B1_Mention_Lays, B1_Mention_Pringles, B1_Mention_Ruffles, B1_Mention_Doritos,
     B1_Mention_BrandX, B1_Mention_MissVickies, B1_Mention_KettleBrand,
     B1_Mention_Cheetos, B1_Mention_Tostitos, B1_Mention_OldDutch, B1_Mention_Takis,
     B1_Mention_SunChips, B1_Mention_CapeCod, B1_Mention_Herrs, B1_Mention_StoreBrand,
     B1_Mention_Utz, B1_Mention_PC, B1_Mention_Other, B1_Mention_None]
    EMPTY=INCLUDE TOTAL=YES POSITION=BEFORE
  /CATEGORIES VARIABLES=Study_Group ORDER=A KEY=VALUE EMPTY=INCLUDE TOTAL=YES POSITION=BEFORE
  /CRITERIA CILEVEL=95
  /TITLES
    TITLE='Table B1: Unaided Brand Awareness by Study Group (Exposed vs Control)'
    CAPTION='Base: all QC-valid completes (unweighted n=1,000). Weighted percentages, '+
      'unweighted bases. Column proportions tested at 95% confidence (z-test).'
  /COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES
    CATEGORIES=ALLVISIBLE MERGE=YES STYLE=SIMPLE SHOWSIG=NO.

Pattern 3: exposed-only diagnostics (produces C1, C3 / C4, C5, C7)

A filter restricts the base to exposed respondents for the whole Section C run and is switched off afterwards. The banner is flat because there is no control column; letters therefore compare subgroups with each other.

View SPSS syntax: Table C5, ad statements

* Section C tables run on the exposed base only.
USE ALL.
COMPUTE filter_$ = (Study_Group = 1).
FILTER BY filter_$.

* Table C5: six agreement statements, top-2 box, flat subgroup banner.
CTABLES
  /VLABELS VARIABLES=C5_Connected_T2B C5_Informative_T2B C5_Creative_T2B
    C5_Memorable_T2B C5_TryBrandX_T2B C5_Believable_T2B Study_Group A2 Age_Group3
    Shopping_Role SnackFreq2 Heavy_Chip_User Significant_other_Flag Children_Banner D9
    DISPLAY=LABEL
  /TABLE C5_Connected_T2B [COLPCT.COUNT PCT40.1, TOTALS[UCOUNT 'Base' F40.0]]
     + C5_Informative_T2B [COLPCT.COUNT PCT40.1]
     + C5_Creative_T2B [COLPCT.COUNT PCT40.1]
     + C5_Memorable_T2B [COLPCT.COUNT PCT40.1]
     + C5_TryBrandX_T2B [COLPCT.COUNT PCT40.1]
     + C5_Believable_T2B [COLPCT.COUNT PCT40.1]
    BY Study_Group [C] + A2 [C] + Age_Group3 [C] + Shopping_Role [C] + SnackFreq2 [C]
     + Heavy_Chip_User [C] + Significant_other_Flag [C] + Children_Banner [C] + D9 [C]
  /SLABELS POSITION=ROW VISIBLE=NO
  /CATEGORIES VARIABLES=C5_Connected_T2B [0, 1] EMPTY=INCLUDE TOTAL=YES POSITION=BEFORE
  /CATEGORIES VARIABLES=C5_Informative_T2B C5_Creative_T2B C5_Memorable_T2B
    C5_TryBrandX_T2B C5_Believable_T2B [0, 1] EMPTY=INCLUDE
  /CATEGORIES VARIABLES=Study_Group [1] EMPTY=INCLUDE
  /CATEGORIES VARIABLES=A2 [1, 2] EMPTY=INCLUDE
  /CATEGORIES VARIABLES=Age_Group3 D9 [1, 2, 3] EMPTY=INCLUDE
  /CATEGORIES VARIABLES=Shopping_Role SnackFreq2 Heavy_Chip_User
    Significant_other_Flag Children_Banner [1, 0] EMPTY=INCLUDE
  /CRITERIA CILEVEL=95
  /TITLES
    TITLE='Table C5: Agreement with Ad Statements, Top-2 Box (Exposed Only)'
    CAPTION='Base: exposed respondents only (unweighted n=500). Weighted percentages, '+
      'unweighted bases. Column proportions tested at 95% confidence (z-test).'
  /COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES
    CATEGORIES=ALLVISIBLE MERGE=YES STYLE=SIMPLE SHOWSIG=NO.

FILTER OFF.
USE ALL.

Outputs and availability

Every output of Step 5 is listed below with its purpose and current availability. Files are linked only where a verified path exists; at the time of writing none of the outputs are hosted with this page, so the table records what exists rather than offering links that would break.

Output	Purpose	Availability
realistic_brandX_survey_dataset.csv (1,295 records) and generator script	Raw field file and the Python simulator that produced it (Step 4)	Documented, download pending
BrandX_Full_Codebook.xlsx	Variable names, question wording, value labels, measurement levels, derivation rules	Documented, download pending
SPSS syntax file	Labels, derivations, coding, weighting, validation and all CTABLES blocks	Shown on this page (copyable)
B1_Unaided_Awarness.xlsx; B2_aided_Awarness.xlsx	Unaided and aided awareness by study group (deck slides 6, 9)	Documented, download pending
B1_unaided_Awarness_exposed.xlsx; B2_aided_Awarness_exposed.xlsx	Awareness profile within the exposed group (slides 10, 11)	Documented, download pending
B1 and B2 _exposed_Control_Subgroups.xlsx	Awareness, exposed vs control nested within subgroups (slide 12)	Documented, download pending
B2a, B3, B4, B5 _exposed_Control_Subgroups.xlsx	Impression, familiarity, consideration, recommendation, nested (slides 13 to 20)	Documented, download pending
C1_Ad_Awarness.xlsx; C3-C4_Prompted_Ad_Awarness_AdEnjoyment.xlsx	Recall, recognition and enjoyment, exposed only (slides 22 to 24)	Documented, download pending
C5_Ad_Statements.xlsx; C5_Ad_Statements_Full.xlsx	Six agreement statements, top-2 box and full scale (slides 25, 26)	Documented, download pending
C7__Main_Message.xlsx	Prompted main message, exposed only (slides 28, 29)	Documented, download pending
C6_Unaided_Main_Message.xlsx; C6_word_cloud_data.xlsx	Weighted verbatim frequencies and word-cloud data (slide 27)	Documented, download pending
BrandX Verbatim Analyzer (browser tool)	Exploration of raw B1 text before coding	Not hosted with this page
CTABLES output screenshots	Visual evidence of the SPSS output	Not available; see note below

Asset note. The earlier version of this page referenced two screenshots (spss_output_crosstab.png and spss_output_significance.png) that are not present alongside the file. Rather than display broken images, the example output above is transcribed from the Table B4 export. When exports are hosted, replace the "download pending" labels with links and add the screenshots as enlargeable figures; the page's image viewer and keyboard controls remain in place for that purpose.

Quality assurance approach

Two checks are built into the workflow. First, headline percentages are re-derived with CROSSTABS column-proportion tests (/CELLS BPROP) under the same weight, as an independent route to the same numbers. Second, the unweighted shares are read from filtered counts in the cleaned file, to see how far weighting moves each estimate. The figures reported in Step 6 were checked against their table exports before the deck was finalised. The weighting efficiency and effective sample size, which would accompany a live deliverable, are not reported for this study.

Explore the Results

The weighted tables behind the Step 6 deck, as an interactive Power BI report. Choose a question, narrow the sample to a subgroup, and compare exposed with control respondents.

Open the report full screenOn a phone, the full-screen view is easier to read.

How to read it

Question: the dropdown covers A5 and every Section B and C question. Section B compares exposed with control; Section C shows exposed respondents only, because only they were asked about the advertisement.
Filters: gender, age, region, community, chip usage, shopping role and snack purchase narrow both groups at once. Reset clears them.
▲ ▼: exposed significantly higher or lower than control at 95%, from a two-proportion z-test on weighted percentages with unweighted bases.
Bases: shown under the chart. Results on fewer than 50 respondents are flagged and not tested.

All figures are weighted (rim weight by age, gender and province) and match the SPSS tables above. BrandX and its respondents are synthetic.

Hand-off to Step 6

Step 5 ends with validated, weighted tables rather than conclusions. Step 6 reads those tables against the four decisions set out in Step 1, and adds one further cut of the same data, Channel A only, Channel B only and both-channel viewers, to answer the channel allocation question.

Data preparation and analysis

What this step does

What Step 5 produces

Prepare the Data

The raw file

Code Open Responses

B1: Unaided brand recall

C6: Unaided message take-away

Plan the Analysis

Three terms used throughout

Research questions the tables answer

1. Banner plan

2. Variable specifications

3. Base definitions

4. Testing rules

5. Table plan

Run & Validate

Example output: consideration by study group and subgroup

How to read it

Syntax patterns behind the tables

Pattern 1: nested brand measure (produces B1 to B5, B2a)

Pattern 2: multiple response set (produces B1 and B2)

Pattern 3: exposed-only diagnostics (produces C1, C3 / C4, C5, C7)

Outputs and availability

Quality assurance approach

Explore the Results

How to read it

Hand-off to Step 6

Moe Pouyan

Location

Contact