Tabellab

From Survey Answers to Segments: Counting Respondents per Need, Segment Shares and Average Behaviour

Lesson 1 of 20Lab

All lessons
  1. From Survey Answers to Segments: Counting Respondents per Need, Segment Shares and Average BehaviourLabFree
  2. Persona Rules: Assigning a Persona with IFS, Counting Personas and Checking Rule Overlap with COUNTIFSLab Locked
  3. Persona Sizing: Shares Within a Market, Projection onto the Customer Base and the Largest PersonaLab Locked
  4. Persona Value: Customers, Revenue, Revenue per Customer, Share of Revenue and a Value IndexLab Locked
  5. Journey Stages: Step Conversion, Share of the First Stage, People Lost and a Funnel ChartLab Locked
  6. Drop-off Between Stages: Drop-off Rates, People Lost, the Biggest Leak with INDEX and MATCH and a What-IfLab Locked
  7. Touchpoints per Stage: An Inventory with COUNTIF and SUMIF, the Importance–Satisfaction Gap and a Fix-First ListLab Locked
  8. Channel Preference by Persona: A Crosstab with COUNTIFS and Mixed References, Row Shares and the Preferred ChannelLab Locked
  9. Survey Quality: Duplicate Emails, Speeders, Straight-Liners and the Clean SampleLab Locked
  10. Needs per Persona: Average Ratings with AVERAGEIF, an Index Against All Respondents and a Heat MapLab Locked
  11. Sample Size and Margin of Error: How Sure Are We About a Persona's Share?Lab Locked
  12. Weighting a Survey to the Customer Base: Shares of Base and Sample, Weights and Weighted Averages with SUMPRODUCTLab Locked
  13. Persona Lifetime Value: Profit per Customer, Expected Lifetime from Retention, Value Rank and the Total Value PoolLab Locked
  14. Journey by Persona: A Step-Conversion Matrix, End-to-End Rates, the Weakest Step and a Below-Average FlagLab Locked
  15. Acquisition Cost by Persona: Spend and New Customers with SUMIF, Cost per New Customer, Value ÷ Cost and a ChartLab Locked
  16. Time to Purchase by Persona: Days Between Dates, AVERAGEIF, a Purchase Window with COUNTIFS and the MedianLab Locked
  17. Budget Allocation by Persona: Shares of Value ÷ Cost, a Budget Split, Expected New Customers and Value CreatedLab Locked
  18. Persona Trends and Migration: SLOPE, FORECAST.LINEAR, a Line Chart and Stay Rates with COUNTIFSLab Locked
  19. Journey What-Ifs: Baseline Profit, a Traffic × Conversion Grid with Mixed References, Target Check and Green HighlightingLab Locked
  20. Final Project: A Persona Priority Scorecard with Weighted Scores, Rank, the Priority Persona and Channel, and a ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

Why personas need data, not guesses

Most teams that talk about customer personas start in a meeting room. Somebody writes "Sam, 34, loves the outdoors" on a slide, the slide gets a stock photo, and a month later nobody can say what Sam changes about the website, the emails or the budget. A persona that does not change a decision is decoration. A persona built from data is different: it comes with a size, a behaviour and a value, so you can argue about it with numbers instead of opinions. The good news is that you do not need a research agency. A short customer survey, the order history that your shop system already keeps, and a spreadsheet are enough to build a first, honest version. This lesson shows the very first step of that work: turning raw survey answers into segments and describing each segment with a share and an average behaviour. The other nineteen lessons of the course build everything else on top of it. The example company is Brambleway Outdoor, a fictional online shop for hiking and camping gear. All customers, answers and numbers in this article and in the lab are invented for learning. You will use the same sixteen survey answers as in the lab above.

What a persona is, and what it is not

A persona is a short, memorable description of a group of customers who want similar things and behave in a similar way. It has a name, a few defining needs, a size and a value. Its purpose is practical: it lets everybody in the company picture the same customer when they write a product page, choose a channel or plan a campaign. Three terms are easy to mix up, so keep them apart from the start:

  • Segment: a group of respondents or customers defined by a measurable answer or behaviour, for example everyone whose main need is durability. A segment is a fact about your data.
  • Persona: the story and the label you attach to a segment (or to a combination of segments) so that people can work with it. A persona is a tool for communication.
  • Target group: the audience you actually decide to address with a campaign. It is a business decision that uses personas, size and value as input.
Build segments on needs and behaviour: what people say matters to them, what they buy, how often they go out. Never build them on age, gender, origin, religion, health or other protected characteristics. Behaviour usually predicts buying better anyway, and it keeps the work fair and easy to defend.

The data: sixteen survey answers from Brambleway Outdoor

The survey asked three things. The first question was What matters most to you when you buy gear? with four possible answers: low price, expert advice, fast delivery and durability. The other two columns are matched to each respondent: the number of trips per year the respondent reported in the survey, and the number of orders placed in the last twelve months, which comes from the shop system.

RespondentMain need when buying gearTrips per yearOrders in the last 12 months
R-01Durability146
R-02Low price43
R-03Expert advice62
R-04Fast delivery34
R-05Low price52
R-06Durability187
R-07Expert advice83
R-08Low price22
R-09Fast delivery45
R-10Low price64
R-11Expert advice52
R-12Durability125
R-13Low price31
R-14Fast delivery23
R-15Expert advice93
R-16Low price43

Sixteen answers is a teaching sample. A real survey would have hundreds, and later lessons show how to judge how far a sample can be trusted. For now the aim is to learn the mechanics that you will reuse on any size of data.

The concept in one picture

Every persona project, small or large, follows the same path. You collect answers, group them into segments, describe each segment with numbers, give the useful segments names and then use them for decisions. The lab in this lesson covers steps 2 and 3.

From survey answers to personas1 Survey16 answers2 Segmentsgroup by main need3 Describeshare + behaviour4 Personasnames and rules5 Decisions: which channel, which message, how much budgetBuild segments on needs and behaviour only.Never on age, gender or other protected characteristics.
Figure 1. From survey answers to decisions. Steps 2 and 3 are the subject of lesson 1; the later lessons add rules, sizing, value, the customer journey and channels.

Step 1: count the respondents per segment with COUNTIF

The first number you need is how many respondents fall into each segment. Excel's COUNTIF(range, criterion) counts the cells of a range that match a criterion. Here the range is the column with the main need, B4:B19, and the criterion is the segment name that is written next to the result cell. In the lab the segment names are in F4:F7 and the counts go into G4:G7. The formula for the first segment is =COUNTIF($B$4:$B$19,F4). The dollar signs make the range absolute: when you copy the formula down, the range stays B4:B19 and only the criterion moves from F4 to F5, F6 and F7. The result is 6 for Low price, 4 for Expert advice, 3 for Fast delivery and 3 for Durability. Always add a total and compare it with the number of rows in your data. =SUM(G4:G7) must equal 16, the number of answers. If it does not, a segment name is misspelled, a row is empty, or the range stops one row too early. This simple reconciliation catches most errors before they reach a slide.

Step 2: turn counts into shares

A count is hard to compare across surveys of different sizes, so convert it into a share of the sample: segment count ÷ total. In H4 write =G4/$G$8 and copy down. The total in G8 is locked with dollar signs, because every row must be divided by the same cell. Without the dollar signs the reference would move to G9, G10 and so on, and you would get errors or nonsense. The shares are 37.5 % for Low price, 25.0 % for Expert advice, 18.8 % for Fast delivery and 18.8 % for Durability. They add up to 100 %. Low price is the largest segment of this sample; Fast delivery and Durability are equally small.

Format the share column as a percentage and the count column as a whole number. A value like 0.375 is correct but hard to read; 37.5 % is what people remember.

Step 3: describe the behaviour with AVERAGEIF

Size alone does not make a persona. You also want to know what the segment does. AVERAGEIF(range, criterion, average_range) works like COUNTIF, but it returns the average of a second column for the matching rows. The segment test stays the same, B4:B19 against the name in column F, and the average range is the column you want to describe.

  • Average trips per year (I4): =AVERAGEIF($B$4:$B$19,F4,$C$4:$C$19), copied down to I7.
  • Average orders in 12 months (J4): =AVERAGEIF($B$4:$B$19,F4,$D$4:$D$19), copied down to J7.

Compare each segment with the average of all 16 respondents: 6.6 trips and 3.4 orders per respondent. The Durability segment goes out 14.7 times a year on average and orders 6.0 times, more than any other segment on both measures. Fast delivery shows a different pattern: only 3.0 trips a year, but 4.0 orders, probably smaller purchases that are needed quickly.

A fully worked example: the result table

Put the three steps together and the sheet in the lab produces this table. The numbers are the exact values of the Coach-checked cells, so you can use them to verify your own sheet.

Main need (segment)RespondentsShare of sampleAverage trips per yearAverage orders
Low price637.5 %4.02.5
Expert advice425.0 %7.02.5
Fast delivery318.8 %3.04.0
Durability318.8 %14.76.0
Total16100 %6.6 (all)3.4 (all)

Respondents and average trips per segmentRespondentsAverage trips per year048121664Low price37.5 % of sample47Expert advice25.0 % of sample33Fast delivery18.8 % of sample314.7Durability18.8 % of sample
Figure 2. Respondents (blue) and average trips per year (yellow) for each main-need segment of the sample.

Read the table as a first sketch of four personas. The largest group, Low price (37.5 %), goes out 4.0 times a year and orders 2.5 times. Expert advice (25.0 %) travels more often, 7.0 trips, but orders no more often than Low price. The two small segments are the interesting ones for the business: Fast delivery buys often, Durability buys often and travels a lot. With only three people each, though, they are questions to test, not conclusions.

Step by step in Excel

  1. Type or paste the survey rows into A3:D19, with one respondent per row and a heading in row 3. One question per column.
  2. List the segment names once in F4:F7. Copy them from the data or from your questionnaire so that the spelling matches exactly.
  3. In G4 enter =COUNTIF($B$4:$B$19,F4) and copy it down to G7. In G8 enter =SUM(G4:G7) and compare the result with the number of respondents.
  4. In H4 enter =G4/$G$8, copy down to H7 and format the cells as percentages.
  5. In I4 enter =AVERAGEIF($B$4:$B$19,F4,$C$4:$C$19); in J4 enter =AVERAGEIF($B$4:$B$19,F4,$D$4:$D$19). Copy both down to row 7.
  6. Compare each segment with the overall average and write one sentence per segment that starts with the number, not with an adjective.

Common mistakes and how to spot them

  • Unlocked ranges. =COUNTIF(B4:B19,F4) copied down becomes B5:B20 in the next row and silently counts the wrong cells. If the counts look too small, check the dollar signs.
  • Wrong divisor for the share. Dividing by the first segment instead of the total gives shares above 100 %. Divide by the locked total, $G$8.
  • COUNTA instead of COUNTIF. COUNTA counts every filled cell and returns 16 for each segment. COUNTIF counts only the matches.
  • Averages from the wrong column. In AVERAGEIF the third argument chooses what is averaged. Pointing it at the orders instead of the trips gives a plausible, wrong number.
  • Misspelled categories. "Low Price" and "Low price" are matched case-insensitively, but "Low-price" or a trailing space is a different text. The total check in G8 exposes it.
  • Over-reading small segments. A segment of three people can move by 10 points when one answer changes. Report the count next to the share.

How to read the result without fooling yourself

A sample is not the customer base. People who answer surveys are usually more engaged than those who do not, and a small difference in the way the invitation is sent can change the mix. Later lessons in this course teach you to calculate the margin of error of a share, to weight an unbalanced sample back to the customer base and to remove careless answers. For now keep a simple rule: always show the count next to the percentage, and say how many people answered. Also keep in mind what a segment is built on. The main need is a stated attitude; trips and orders are behaviour. A good persona uses both: what people say they care about and what they actually do. When they disagree, behaviour is the better guide for spending money.

Write down the question wording and the answer options next to your sheet. In six months nobody remembers whether "Durability" was a free-text answer or one of four options, and the interpretation depends on it.

Quick reference: functions in this lesson

Function / ideaWhat it does hereExample
COUNTIFCounts respondents of one segment=COUNTIF($B$4:$B$19,F4)
SUMAdds the segment counts; must equal the number of answers=SUM(G4:G7)
Absolute reference $Keeps a range or total fixed while copying=G4/$G$8
AVERAGEIFAverage of a column for one segment=AVERAGEIF($B$4:$B$19,F4,$C$4:$C$19)
Percent formatShows 0.375 as 37.5 %Format > Percent

Real-world uses

  • Choosing the message. A shop whose largest segment cares about price tests offers and price-match banners; a shop with a strong expert-advice segment invests in guides and live Q&A.
  • Briefing agencies and freelancers. A one-page table of segment sizes and behaviour is a better brief than a paragraph of adjectives.
  • Prioritising the roadmap. If a segment that cares about fast delivery is large and orders often, a shipping upgrade has a clear audience and a measurable benefit.
  • Tracking change. Repeat the survey every half year with the same question and the same formulas. A shifting mix of needs is an early warning that customers, or the market, are changing.
  • Checking the story of other teams. When someone says "our customers are all bargain hunters", you can answer with a share and a count.

What you will build in the rest of the course

Lesson 1 is free. Lessons 2 to 20 make up the paid course. Each lesson has its own live lab with a Coach that checks every cell, and each builds on the persona work you started here.

  • Lesson 2, persona rules: assign personas with IFS from behaviour scores and check rule overlap with COUNTIFS.
  • Lesson 3, persona sizing: project the survey mix onto the customer base per market with SUMIF, XLOOKUP and ROUND.
  • Lesson 4, persona value: revenue per customer, share of revenue and a value index against the share of customers.
  • Lesson 5, journey stages: nine stages, step conversion, people lost and a funnel chart.
  • Lesson 6, drop-off: the biggest leak with INDEX and MATCH, and a what-if for fixing it.
  • Lesson 7, touchpoints: an inventory, the importance and satisfaction gap and a fix-first list.
  • Lesson 8, channels: a persona by channel crosstab with mixed references and a chart.
  • Lesson 9, survey quality: duplicates, speeders and straight-liners.
  • Lesson 10, needs index: average ratings per persona, an index and a heat map.
  • Lesson 11, sample size: the margin of error of a share and the respondents you need.
  • Lesson 12, weighting: correct a lopsided sample with weights and SUMPRODUCT.
  • Lesson 13, lifetime value: profit per customer, retention, lifetime and the value pool.
  • Lesson 14, journey by persona: a step-conversion matrix and each persona's weakest step.
  • Lesson 15, acquisition cost: cost per new customer by persona and its ratio to lifetime value.
  • Lesson 16, time to purchase: days between dates, a purchase window and the median.
  • Lesson 17, budget split: allocate a budget by value ÷ cost and estimate the outcome.
  • Lesson 18, trends and migration: SLOPE, FORECAST.LINEAR and stay rates.
  • Lesson 19, what-ifs: a traffic by conversion grid with a target check.
  • Lesson 20, final project: a weighted priority scorecard with rank, priority persona and chart.

Summary

A data-based persona starts with segments. COUNTIF counts the respondents of each segment, a locked total turns counts into shares with =G4/$G$8, and AVERAGEIF describes what each segment does. In the Brambleway Outdoor sample the largest segment is Low price with 6 of 16 respondents (37.5 %), while the three-person Durability segment travels 14.7 times a year. Keep segments on needs and behaviour, show counts next to percentages and treat small groups as questions to test.

Now scroll up to the lab and build the sheet yourself. It takes about fifteen minutes, the Coach checks each step, and you will end up with a segment table you can reuse on your own survey export. All companies, people and numbers in this course are fictional, and benchmarks are illustrative.

Frequently asked questions

What is a customer persona and how is it different from a segment?

A segment is a group of customers defined by a measurable answer or behaviour, such as everyone whose main need is durability. A persona is the name and short description you attach to a segment so that a team can picture it and make decisions. The segment is the data, the persona is the working label.

How do I count survey respondents per answer in Excel?

Use COUNTIF(range, criterion), for example =COUNTIF($B$4:$B$19,F4), where B4:B19 holds the answers and F4 holds the answer to count. Lock the range with dollar signs before copying the formula down, and check that the counts add up to the number of respondents.

How do I calculate the share of each segment?

Divide the segment count by the total number of respondents and lock the total, for example =G4/$G$8, then format the cells as percentages. The shares of all segments must add up to 100 %. Always show the count next to the percentage so readers can see how small a segment is.

What does AVERAGEIF do in a persona analysis?

AVERAGEIF(range, criterion, average_range) averages one column for the rows of one segment, for example the trips per year of everyone whose main need is durability. It turns a label into a behaviour profile and lets you compare each segment with the overall average.

Should personas use age or gender?

No. Build personas on needs and behaviour, such as what customers value, how often they buy and how they move through the journey. Protected characteristics are unfair grounds for treating customers differently, and behaviour usually predicts buying better than demographics.

How many survey answers do I need?

The answer depends on how precise you need to be. As a rough guide, 400 answers give a margin of error of about 5 points for a share of 50 %, and smaller segments need more. The sixteen answers in this lab are a teaching example only; later lessons show how to calculate the margin of error and the sample size you need.

Keep going: Customer Personas & Journey Mapping: Survey Segments, Persona Size & Value, Journey Drop-Off, Touchpoints & Channel Preference

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Persona Rules: Assigning a Persona with IFS, Counting Personas and Checking Rule Overlap with COUNTIFS
  2. Persona Sizing: Shares Within a Market, Projection onto the Customer Base and the Largest Persona
  3. Persona Value: Customers, Revenue, Revenue per Customer, Share of Revenue and a Value Index
  4. Journey Stages: Step Conversion, Share of the First Stage, People Lost and a Funnel Chart
  5. Drop-off Between Stages: Drop-off Rates, People Lost, the Biggest Leak with INDEX and MATCH and a What-If
  6. Touchpoints per Stage: An Inventory with COUNTIF and SUMIF, the Importance–Satisfaction Gap and a Fix-First List
  7. Channel Preference by Persona: A Crosstab with COUNTIFS and Mixed References, Row Shares and the Preferred Channel
  8. Survey Quality: Duplicate Emails, Speeders, Straight-Liners and the Clean Sample
  9. Needs per Persona: Average Ratings with AVERAGEIF, an Index Against All Respondents and a Heat Map
  10. Sample Size and Margin of Error: How Sure Are We About a Persona's Share?
  11. Weighting a Survey to the Customer Base: Shares of Base and Sample, Weights and Weighted Averages with SUMPRODUCT
  12. Persona Lifetime Value: Profit per Customer, Expected Lifetime from Retention, Value Rank and the Total Value Pool
  13. Journey by Persona: A Step-Conversion Matrix, End-to-End Rates, the Weakest Step and a Below-Average Flag
  14. Acquisition Cost by Persona: Spend and New Customers with SUMIF, Cost per New Customer, Value ÷ Cost and a Chart
  15. Time to Purchase by Persona: Days Between Dates, AVERAGEIF, a Purchase Window with COUNTIFS and the Median
  16. Budget Allocation by Persona: Shares of Value ÷ Cost, a Budget Split, Expected New Customers and Value Created
  17. Persona Trends and Migration: SLOPE, FORECAST.LINEAR, a Line Chart and Stay Rates with COUNTIFS
  18. Journey What-Ifs: Baseline Profit, a Traffic × Conversion Grid with Mixed References, Target Check and Green Highlighting
  19. Final Project: A Persona Priority Scorecard with Weighted Scores, Rank, the Priority Persona and Channel, and a Chart

Buy course — $9.90 See the full course