Worksheet: Cleansing and Analysing a Client Data Set

Cover teacher: you need no subject knowledge

What this practises: GCSE Digital Technology Unit 2: Digital Practices, Section 2.1.1 Cleansing and organising data (correct errors, insert missing values, remove duplicates, remove outliers, use standard data formats, validate data against known standards or rules) and Section 2.1.2 Data analytics (descriptive statistics to identify audience needs, trends and preferences, reported with suitable charts), using functions from the Appendix A spreadsheet skills list. This is practice only, not NEA work. The cinema and its survey are invented and have nothing to do with the WJEC client brief. Do not let pupils use their own Unit 2 files today.

Print: this sheet, one per pupil, and the stimulus sheet “Sinema’r Neuadd: client brief and audience survey”, one per pupil if you can. Pupils need a calculator and highlighters. If you are in a computer room, pupils may open sinema-neuadd-survey.csv in a spreadsheet to check their counts, but the lesson works fully on paper.

Run: 5 min read the brief and the rules · 13 min Step 2, the cleansing log · 7 min Step 3, validation rules · 15 min Step 4, the analysis · 8 min Step 5, the chart · 7 min Step 6, findings and success criteria for the website. Total 55 minutes.

Collect: this sheet.

What you are doing

A community cinema wants a website and a game that will bring in more 11 to 18 year olds. A volunteer typed up a survey by hand, and it is full of mistakes. Before anyone designs anything, you will clean the data, set rules so the mistakes cannot be typed again, work out what the audience wants, and turn that into success criteria for the website. Every product decision must come from the data.

Step 1: read the brief

Read sections 1 and 2 of the stimulus. The rules in section 2 are the “known standards” you will check every value against. The brief says 29 people replied. Count the data rows.

Step 2: the cleansing log

Go through the data column by column. Highlight every value that breaks a rule in section 2. Log at least 10 problems. For each one, choose a technique: correct error, insert missing value, remove duplicate, remove outlier, use standard format. If you cannot know the true value, do not guess: say what you will do instead.

(b) Four people have no rating in column I. Should you insert a value for them? Explain.

Step 3: validation rules

Write the Data Validation setting that would have stopped each kind of mistake being typed in.

Step 4: analyse the clean data

Work from your cleaned data: 29 people, rows 2 to 30 once the duplicate is removed. Count by hand using highlighters. For each question, write the formula you would use in the spreadsheet, then the result. Give money to 2 decimal places.

(f) Dafydd thinks the average audience member is 26. Work out the mean age of the raw data and of the cleaned data (leave out anyone whose age you removed). Explain the difference.

Step 5: report it as a chart

Sketch the chart you would put in your report to show how 11 to 18 year olds heard about the cinema. Choose a suitable chart type. Give it a title, label both axes and show the values.

Step 6: from findings to success criteria

Write three findings about 11 to 18 year olds, each with a figure. Turn each one into a specific, measurable success criterion for the website.

Step by step

Success criteria

I can cleanse a data set against known rules, choosing the right technique for each problem and never guessing a value I cannot know.
I can write validation rules that stop the same errors being entered again.
I can use spreadsheet functions to produce statistical evidence from the data, and check the results make sense.
I can present results in a suitable chart and justify my choice.
I can use my data to explain and add validity to conclusions, and say where the data does not support a conclusion.

Row	Column	Value now	Technique	Change it to, and why

Column	Validation setting	Error alert message the volunteer would see
B age
E favourite_genre
G max_ticket_price_gbp
I rating_1_to_5

Question	Formula	Result
(a) How many people chose each genre? (One line per genre, including anyone with no answer.)
(b) How many people are aged 11 to 18? How many of them heard about the cinema via TikTok, and what percentage is that?
(c) The mean maximum ticket price for everyone, for 11 to 18 year olds, and for people aged 19 and over.
(d) The mean rating, using only valid ratings. How many ratings is it based on?
(e) Which start time do 11 to 18 year olds prefer? Give the count for each time.

Cleansing and analysing a client data set