Market research data analysis: turn the records into a clear decision

Imagine you are deciding whether a cleaning-concentrate concept is clear enough for a revised prototype test. In the illustrative walkthrough below, one buyer expects supplied bottles and another expects to spray straight from the pouch. Seven of ten prepared records understand the offer, below its eight-of-ten gate. Your analysis must show those errors before a favorable relevance score advances the wrong concept.

Keep the source records and analysis together

The complete raw and prepared records, codebook and coded answers, and spreadsheet setup and formulas below let you reproduce the example. Keep the raw records separate from the prepared records and calculate summaries from the prepared base. This is a worked example, with no live survey connection.

Work in this order: inspect Raw source, document the prepared records, reconstruct the summaries, then read the diagnostic text. That sequence lets the analyst check the accounting before deciding which answer changes the card.

Define the concept and measure before touching the rows

Each record includes buyer experience, a binary comprehension code and a five-point relevance rating. The numeric records appear below. The coded answers supply ten authored responses keyed to those IDs; they illustrate coding rather than recover fieldwork.

Show the concept and audit the code

Concept card DA-v1: “One 50 mL cleaning concentrate pouch makes two 500 mL fills when diluted as instructed in the specified reusable bottle. Bottle not included. Proposed price USD 4 before tax. Unbuilt concept; performance unverified.” Ask “In your own words, what is the main benefit and how would you use it?” before relevance.

Code comprehension 1 only when the response describes refilling or preparing two usable fills in the separate bottle, without saying the pouch is ready-to-use or that the bottle is supplied. Code 0 when a material misunderstanding contradicts that use; retain ambiguity and analyst disagreement in the coding note. A01 says “Refill the separate bottle twice using concentrate and water” and receives 1. A03 says “Spray it straight from the pouch” and receives 0. A10 says “Two fills in the bottle I need to own; I cannot rate relevance” and receives 1 with relevance missing. The companion table’s ten responses preserve the original 7/10 codes and relevance values. They make the rule inspectable rather than validate an actual concept.

Keep an ambiguous answer beside the code and disagreement note. “A convenient refill” does not establish whether the person understood separate equipment and dilution. The coding rule needs to distinguish that incomplete answer from A03’s explicit ready-to-use error.

Clean each issue for the reason it affects the measure

The raw dataset has twelve rows. One repeats A03 exactly. One has relevance 6, outside the declared scale. The prepared dataset contains ten unique valid records: retain the first A03 and quarantine A11 rather than guessing its intended rating.

For a real study, invalid answers may justify item-level exclusion rather than whole-record exclusion. State and justify your actual rule before presenting results. The preparation is explicit and static; editing raw values does not automatically rerun cleaning. Reprepare the Analysis records and document the change.

Cleaning must preserve the information still valid. A10’s blank relevance does not erase its correct comprehension. A03’s identical duplicate should not get a second vote. This demonstration quarantines A11 as a whole record; another justified item-level rule would need its own prepared base and explanation.

Reproduce the denominators, then the percentages

Seven of ten prepared records have correct comprehension: 70%. Six of nine substantive relevance ratings are 4 or 5: 66.7%. One relevance answer is missing. Keeping the denominator at ten would produce 60%, a different measure.

By buyer group, comprehension is 3/5 for New and 4/5 for Repeat. Relevance top-two counts are 3/5 and 3/4 respectively. Those small bases do not establish a real group difference.

View the chart at full resolution.

The chart shows counts and bases rather than implying precision. The constructed example records support no statistical inference about a market.

The base changes with the question. Ten prepared records can answer comprehension; nine can supply a substantive relevance rating. A headline showing both on an unexplained common base would make the analyst’s accounting decision look like a buyer response.

Use the New/Repeat rows as prompts for a follow-up, not a segment launch strategy. First identify whether the same equipment or preparation error occurs in either group. A useful audience cut directs attention to an original answer; the small cells cannot establish which buyers need a different offer.

Find the edit in the original answer

A03 imagines ready-to-use spray. A05 imagines supplied filled bottles. A09 imagines a supplied new spray bottle. These are preparation and inclusion errors, so the next card needs to distinguish the concentrate, the separately required bottle and the dilution step.

A10 correctly describes the two fills and separate bottle but cannot rate relevance. That is a different diagnostic, not low appeal or total-record failure. The New/Repeat cells can guide investigation, but their small bases do not establish an experience effect.

The main quantity line can read: “50 mL concentrate for two 500 mL fills. Bottle not included; use the specified bottle and add water as instructed.” Save that proposed line in the revised card beside the same $4 before-tax price. The copy now makes the equipment and preparation part of the prominent quantity explanation.

Make the result a revised-card brief

The exercise’s decision is to revise before progression: 7/10 falls short of the declared 8/10 gate. A03, A05 and A09 provide the reasons for that instruction, while A10 remains in the comprehension base despite its missing relevance answer.

Retest the same unaided interpretation and coding rule under comparable exposure. If the offer is understood but less relevant, inspect what the required equipment asks the buyer to own and do. An unwanted requirement is a system or audience question; hiding it again would improve reactions to an offer the business does not supply.

Use our worked research examples to connect the analysis to action and our quantitative research guide to design the measure. Discuss an interpretable comparison with Sapien when the result can choose the next candidate or validation step.

Before sharing your analysis sheet, reopen the saved sheet and reconcile the prepared rows, exclusions and summary ranges. Its preparation is static: editing Raw source alone does not rerun cleaning. A colleague should be able to recover the 12 raw rows, 10-record comprehension base and 9-rating relevance base from the records and formulas below.

Give marketing the revised card and specific comprehension errors. Give product any unresolved equipment or handling requirement. Actual use and buying remain separate tasks. The analysis has finished its current job when the team can trace each recommended change to an answer and choose what to test next.

Copy the raw and prepared records

Copy these CSV blocks into a spreadsheet using its comma-separated import option. Preserve the blank relevance value for A10. The raw block retains both A03 rows and the invalid A11 rating; the prepared block implements the cleaning rule already explained.

Raw records: twelve rows

record_id,buyer_group,comprehension,relevance,example_status
A01,New,1,5,fictional
A02,New,1,4,fictional
A03,New,0,4,fictional
A04,New,1,3,fictional
A05,New,0,2,fictional
A06,Repeat,1,5,fictional
A07,Repeat,1,4,fictional
A08,Repeat,1,4,fictional
A09,Repeat,0,3,fictional
A10,Repeat,1,,fictional
A03,New,0,4,fictional
A11,Repeat,1,6,fictional

Prepared records: ten rows

record_id,buyer_group,comprehension,relevance,example_status
A01,New,1,5,fictional
A02,New,1,4,fictional
A03,New,0,4,fictional
A04,New,1,3,fictional
A05,New,0,2,fictional
A06,Repeat,1,5,fictional
A07,Repeat,1,4,fictional
A08,Repeat,1,4,fictional
A09,Repeat,0,3,fictional
A10,Repeat,1,,fictional

Copy the codebook and response coding

The codebook defines the allowed values and missing-answer rule. The response block supplies every authored answer and coding note, including A08’s potentially ambiguous wording and A10’s missing relevance. Preserve those notes when reviewing a code.

Codebook

field,type,allowed_values,meaning,missing_rule
record_id,text,unique identifier,one fictional prepared record,not allowed
buyer_group,category,New or Repeat,fictional buyer experience group,not allowed
comprehension,binary,0 or 1,declared intended benefit interpreted correctly,exclude missing from measure base
relevance,ordinal,1 to 5,1 Not at all to 5 Extremely relevant,blank excluded from substantive base
example_status,text,fictional,all records invented for instruction,not allowed

Responses and coding notes

record_id,buyer_group,comprehension,relevance,fictional_response,coding_note,stimulus_version,example_status,provenance
A01,New,1,5,Refill the separate bottle twice using concentrate and water.,Correct: two fills in separate equipment,DA-v1,fictional,Newly authored teaching answer; not recovered original fieldwork
A02,New,1,4,Make two diluted fills; I need the specified bottle already.,Correct: preparation and equipment explicit,DA-v1,fictional,Newly authored teaching answer; not recovered original fieldwork
A03,New,0,4,Spray it straight from the pouch.,Incorrect: concentrate treated as ready to use,DA-v1,fictional,Newly authored teaching answer; not recovered original fieldwork
A04,New,1,3,Use water and this pouch for two refills of my specified bottle.,Correct: dilution and separate bottle,DA-v1,fictional,Newly authored teaching answer; not recovered original fieldwork
A05,New,0,2,Two disposable full bottles come in the pack.,Incorrect: supplied filled bottles assumed,DA-v1,fictional,Newly authored teaching answer; not recovered original fieldwork
A06,Repeat,1,5,Two 500 mL fills from concentrate in the separate reusable bottle.,Correct: two prepared fills and equipment,DA-v1,fictional,Newly authored teaching answer; not recovered original fieldwork
A07,Repeat,1,4,Prepare two refills with water; the bottle is not included.,Correct: preparation and equipment,DA-v1,fictional,Newly authored teaching answer; not recovered original fieldwork
A08,Repeat,1,4,"It says two bottles, meaning two fills in the reusable bottle I must own.",Correct after explicit clarification; preserve potentially ambiguous wording,DA-v1,fictional,Newly authored teaching answer; not recovered original fieldwork
A09,Repeat,0,3,The pouch includes a new spray bottle I can use immediately.,Incorrect: bottle inclusion and ready-to-use assumption,DA-v1,fictional,Newly authored teaching answer; not recovered original fieldwork
A10,Repeat,1,,Two fills in the bottle I need to own; I cannot rate relevance.,Correct: two fills and separate equipment; relevance missing,DA-v1,fictional,Newly authored teaching answer; not recovered original fieldwork

Rebuild the analysis sheet

Create sheets named Raw source and Analysis. Import the raw CSV header at Raw source!A4 and its twelve records at A5:E16. Import the prepared CSV header at Analysis!A15 and its ten records at A16:E25. Do not paste a second copy of the prepared records into the summary area.

In Analysis, put Measure, Base, Count, Share and Interpretation across A4:E4. Enter Correct comprehension in A5 and Relevance rated 4 or 5 in A6. Copy each formula into the named cell below; format D5:D6 as percentages. The expected summaries are base 10, count 7, share 70%; and base 9, count 6, share 66.7%.

For subgroup checks, put Buyer group, Comp base, Comp correct, Relevance base and Relevance top two across A8:E8. Put New in A9 and Repeat in A10. Enter the formulas below; the expected count rows are New: 5, 3, 5, 3 and Repeat: 5, 4, 4, 3.

These formulas use the same prepared records and ranges as the worked analysis. They do not automate cleaning or supply statistical evidence for the small group differences. If you change the number of records, update and recheck every summary range before sharing the result.