How to create a Likert Scale Chart in Google Data Studio
How to create a top two and bottom to chart of likert scale survey questions in Google Data Studio.
Create a calculated field in your data source where H = the name of the column in your data source containing the
likert scale responses from your respondents. Create X When statements depending on the number of points on your likert scale
in '...' write the exact wording and casing of your responses as they output to your data source, the 'Then' statement can be written however you want the chart to display.
Examples Below:
CALCULATED FIELD CODE EXAMPLES
CASE WHEN REGEXP_MATCH(h,'Very Appealing') THEN 'Very Appealing / Somewhat Appealing' WHEN REGEXP_MATCH(h,'Somewhat Appealing') THEN 'Very Appealing / Somewhat Appealing' WHEN REGEXP_MATCH(h,'Feel Neutral') THEN 'Feel Neutral' WHEN REGEXP_MATCH(h,'Not Very Appealing') THEN 'Not Very Appealing / Not At All Appealing' WHEN REGEXP_MATCH(h,'Not at all Appealing') THEN 'Not Very Appealing / Not At All Appealing' END CASE WHEN REGEXP_MATCH(h,'Definitely Would Purchase') THEN 'Definitely / Probably Would Purchase' WHEN REGEXP_MATCH(h,'Probably Would Purchase') THEN 'Definitely / Probably Would Purchase' WHEN REGEXP_MATCH(h,'Might or Might Not Purchase') THEN 'Might or Might Not Purchase' WHEN REGEXP_MATCH(h,'Probably Would Not Purchase') THEN 'Definitely / Probably Would NOT Purchase' WHEN REGEXP_MATCH(h,'Definitely Would Not Purchase') THEN 'Definitely / Probably Would NOT Purchase' END CASE WHEN REGEXP_MATCH(h,'Very important') THEN 'Very / Somewhat Important' WHEN REGEXP_MATCH(h,'Somewhat important') THEN 'Very / Somewhat Important' WHEN REGEXP_MATCH(h,'Feel Neutral') THEN 'Feel Neutral' WHEN REGEXP_MATCH(h,'Not Very Important') THEN 'Not Very / Not at all Important' WHEN REGEXP_MATCH(h,'Not at all important') THEN 'Not Very / Not at all Important' END CASE WHEN REGEXP_MATCH(h,'Very likely') THEN 'Very / Somewhat Likely' WHEN REGEXP_MATCH(h,'Somewhat likely') THEN 'Very / Somewhat Likely' WHEN REGEXP_MATCH(h,'Not surel') THEN 'Not sure' WHEN REGEXP_MATCH(h,'Not very likely') THEN 'Not Very / Not at all Likely' WHEN REGEXP_MATCH(h,'Not at all likely') THEN 'Not Very / Not at all Likely' END CASE WHEN REGEXP_MATCH(h,'Agree completely') THEN 'Agree' WHEN REGEXP_MATCH(h,'Agree somewhat') THEN 'Agree' WHEN REGEXP_MATCH(h,'Feel Neutral') THEN 'Not sure' WHEN REGEXP_MATCH(h,'Disagree somewhat') THEN 'Disagree' WHEN REGEXP_MATCH(h,'Disagree completely') THEN 'Disagree' END CASE WHEN REGEXP_MATCH(h,'Always') THEN 'Agree' WHEN REGEXP_MATCH(h,'Sometimes') THEN 'Agree' WHEN REGEXP_MATCH(h,'Feel Neutral') THEN 'Not sure' WHEN REGEXP_MATCH(h,'Disagree somewhat') THEN 'Disagree' WHEN REGEXP_MATCH(h,'Disagree completely') THEN 'Disagree' END
Then you must create another calculated field with…
1. H being the calculated field you just created,
2."X" contains your newly created text values for your aggregated scale
3. Then # containing the ranking of those responses when they are displayed on a chart or put into a table
CASE WHEN H = "Agree" THEN 3 WHEN H = "Not sure" THEN 2 WHEN H = "Disagree" THEN 1 END CASE WHEN H = "Very / Somewhat Important" THEN 3 WHEN H = "Feel Neutral" THEN 2 WHEN H = "Not Very / Not at all Important" THEN 1 END
OUTPUT
This code is creating calculated fields in a data source for a survey that uses Likert scale questions. The first set of CASE statements maps the responses from the Likert scale to a new set of response categories, such as "Very Appealing / Somewhat Appealing" or "Definitely / Probably Would Purchase." These new response categories are defined in the THEN part of the CASE statement and can be written however you want the chart to display them.
The REGEXP_MATCH function is used to determine whether the responses in the h column match a specific pattern. In this case, the pattern is a particular response from the Likert scale, such as "Very Appealing" or "Definitely Would Purchase." If the response in the h column matches the pattern, the REGEXP_MATCH function returns TRUE and the corresponding value in the THEN part of the CASE statement is returned. If the response in the h column does not match the pattern, the REGEXP_MATCH function returns FALSE and the ELSE part of the CASE statement is returned (if there is an ELSE part).
The second set of CASE statements is creating another calculated field, 35agg, based on the values in the calculated field created in the first set of CASE statements. This calculated field assigns a ranking to each of the response categories based on the values in the THEN part of the CASE statement. For example, in the first CASE statement, the response category "Agree" is assigned the value 3, while the response category "Disagree" is assigned the value 1. This calculated field can be used to create a chart or table in Google Data Studio that displays the responses from the survey and ranks them based on the values assigned in this calculated field.
An example of how you can make a Likert scale table, showing your 5-point scale results, your top 2 and bottom 2 results, and if you want your totals overall AND, your totals for an age range breakdown or some other categorical variable within your data source.
This is three separate tables
Table 1: Top 2 Text Value from your recently created calculated field based on the question you want to display
Dimension-5 agg, your field with your aggregate top2 and bottom 2 text values
Three Metrics - one with a unique id for each respondent (CTD) (% of total)
Create two columns in your data source: One for each other variable besides total you will be showing
If you want to do age 18-29 and Age 30+ as two columns, you will need one column in your data sources that only tags people age 18-29, and one column that only tags those age 30+, Two separate columns instead of one allows you to use the percent of total function on your metrics and will allow you to easily compare the Total, to those age 18-29, to those age 30+, rather than showing you, if you only had one column for both age breakdowns, the total of the total (ie showing you how much of the 78% where age 18-29 and how much were Age 30 +
Then your other two metrics are the two new columns in your data source, use % of total, corresponding data for both.
EXAMPLE
Then for Style Settings
1. Show header
2. Label the dimension Top 2 or whatever you want to call it top 2 box
3. Dont show pagination
4. Transparent background
Then….
1. Under Rows per page, change to only 1
2. Under Sort, use the new field created in your data source for the order When X = “” then ““ field which I often label Q#order for easy searching
3. Sort from ascending to descending of the AVG based on which ever way you ranked them in your calculated field
Then Make Other two Charts
Copy this chart and paste below the Top2 chart
Expand the table down so that it can show 5 lines, and so the table scroll on the side no longer pops up
Change dimension to your raw question dimensions/Data Source to show the 5 point scale, remove any filter you had on the copied table that applies to the other data source you used and not this one
Copy Top2 chart again and paste below 5 point chart, change sort to the opposite it was on Top 2, to only display the bottom 2 agg results.
Align tables to the left
Change column widths to your liking (double click will auto adjust column widths as best that it can do, short table headers are better than longer).
Pros and Cons of This Visualization
This style is good when:
The sample sizes between variables are equal or near equal
This style is not good when:
These kinds of comparisons become less meaningful the more unequal each variable’s sample size is to the other(s) Since the percentages show you %’s based on just the people who are within X category and do not factor in the size of the variable into the percentage, sample size differences between variables must be considered. A trend in a variable X with Sample size of 50 is much less powerful/significant than a trend in variable Y with sample size of 250
