Data and AI Training

Home | Power BI | Excel | Python | SQL | Generative AI | Visualising Data | Analysing Data

LBAG Survey Dataset

In May 2020, The London Business Analytics Group (LBAG) ran a survey of members to find out which topics would be most interesting for future talks. Four topics were proposed for data analysis: street level crime, COVID tests numbers, the performance of English schools, and results of the UK election in 2019.

Respondents to the survey were asked to provide a Likert style score to indicate their interest in the topic (Very boring, Dull, OK, Interesting, Fascinating) as a single answer to a multiple-choice question. There was a final question where respondents could add an overall comment. There were 81 respondents.

Data Description and Links

This data is in an Excel spreadsheet in a public web location here. The spreadsheet has three tables: Survey, Questions and ScoreSentiment.

The Survey table records the results. There is a column for the score of each topic (Crime, COVID, Schools, Elections). The survey tool assigned a numeric value, Response Id, for each answer: Boring -> 1, Dull -> 2, OK -> 3, Interesting -> 4, Fascinating -> 5. The table also has a Comment column where the respondent may have entered some free text.

The Question table has a row for each of the four multiple choice questions. This contains the Question Code, which is the column header in the table Survey table, and a full title and text of the question.

The Response table has a row for each of the possible responses to the multiple choice questions. The ResponseText lists the possible responses ( Boring, …, Fascinating). The Response Id column is the corresponding value recorded in the Survey table (1 to 5). The Sentiment column groups the scores into three categories (Negative, Neutral, Positive). The Points column reflects the survey designers’ view on any numerical weights to be assigned to each Response and penalises the topics that respondents consider boring more than it rewards topics that respondents find interesting.

Suggested Analysis Approach

Analyse and visualise the dataset to answer this question: which topic(s) are the most and (least) appealing to respondents?

You may want to:

• build a pivot table or visualisation using the Survey table. Is it useful? If not why not?
• add up the scores for each topic (easy but perhaps not the best approach)
• count how number of respondents voted i.e. by ResponseText (Boring, Dull, OK, Interesting, Fascinating) for each topic, for example:
  • a stacked column chart: ResponseText on the x-axis, number of respondents on the y axis, topic as legend
  • a small multiples (trellis) chart: ResponseText on the x-axis, number of respondents on the y axis, one small chart for each topic
  • a 100% stacked bar chart showing the sentiment (positive, neutral, negative) for each topic

Also, consider if you can apply a points scheme so it is possible to sum the results - if this makes sense at all

• apply the existing points scheme
• design a more suitable points scheme e.g. 2 for 2 Fascinating, 1 for Interesting, 0 otherwise

Useful resources during the exercise

During the case study, the prompt for an AI model may be useful. If this is a Power BI exercise, replace the reference to Excel in the prompt below.

In Excel, I have an Excel table named Survey with columns: RespondentId, A, B, C.
The column header and first two rows look like this.

RespondentId, A,B,C
1, 100, 200, 300
2, 101, 202, 303

I want to unpivot the data to achieve a result like this.

RespondentId, Category, Value
1, A, 100
1, B, 200
1, C, 300
2, A, 101
2, B, 202
2, C, 303

How can I achieve this?