Quantitative Market Research Tables
The typical output of a quantitative market research survey is a series of data tables. The job of the analyst, presenter and/or final report writer is to bring a body of dry tabular data to life and make it easy for the users of the research to quickly assimilate and understand the key findings. Below we present some simple ways to improve the insight generated by a data table in order to emphasis the key take-outs from the research. We start by showing how data tables can be enhanced and then go on to consider how data tables can be replaced.
This guide is not a tutorial on how to use Excel or any other program to present tabular data, however, reference will be made to some of the functions found in Excel and other programs to demonstrate how market research result users can be helped to quickly take in findings. Excel and other spreadsheet packages includes some useful tools to improve the layout of a market research table. Here we will show some of our favourites that have proved popular with our clients.
Conditional formatting icons can highlight changes
Excel’s conditional formatting capabilities include Icon sets. Icon sets show icon like arrows, tick markets, circles etc and a particular icon can be inserted into an individual cell in Excel based on the values in that particular cell. The icons can be inserted alongside the cell’s value or instead of the cell’s value.
Icons are useful for demonstrating change across time.
Opposite are two Figures. In one we have replaced the actual numbers for the change in proceedings at the Royal Courts of Justice with an icon to flag up the direction of the changes. The actual numbers showing the change could have been included in the “Change” column, although this would have reduced the impact of the icon. In the second – this time from a dashboard – a green triangle icon has been used to show a recent change with an underlying trend also shown.
Icons can also highlight differences between groups
Icons are also useful for flagging up where one group of respondents in a survey differs from another. In the Figure opposite, the Arrears indicator shows debt ownership levels that strongly discriminate between those in arrears and those not in arrears (i.e where there is statistically significant difference). By including an Icon it is easy to highlight those debt products where significant differences in ownership occur.
Conditional highlights can also be used to show differences between groups.
We have seen that icons can show differences between groups. Another means of fulfilling the same role and useful if more than two groups are being compared is the Highlight cell rules in Excel’s conditional formatting options. The Highlight cell rules allow you to change the formatting of a cell based on a criteria specified by you.
The Figure opposite has some cells shaded and the percentage figures shown in bold. These cells are those where a particular Group owns a product to an above average extent, specifically ownership is at least 3 percentage pointer higher than for All consumers. This highlight where there are key differences in ownership levels between these groups, making it easier for individuals using the table to quickly see where differences occur. Therefore, for Product A, Groups 1 and Group 3 have above average ownership levels, while Group 2 has below average ownership levels for all the products.
Line sparklines are great for highlighting trends
Sparklines are tiny charts that can be inserted into an Excel worksheet cell. They provide a visual representation of data. A sparkline should be positioned near its data for greatest impact.
Line sparklines can show trends in a series of data values. In the figure opposite, the trend is much easier to see with a sparkline than from the raw data. Line sparklines allow you to show the original data and the trend for multiple data series on a single table.
Bar sparklines can highlight highs and lows
Bar sparklines are useful for showing highs and low and relative sizes.
In the Figure opposite, traffic light colours are used to highlight the comparative ownership of Products A to G across the the three consumer Groups 1 to 3. Under this colour scheme, red bars (stop) show the lowest level of ownership for each product across the three groups, while the green bars (go) show the highest level of ownership. The yellow bars show the mid level of ownership. So it is clear that for Product A, Group 3 has the highest level of ownership and Group 2 the lowest.
Used in this manner, bar sparklines are best limited to highlighting differences across a maximum of three groups. If more groups are added, it becomes difficult to see which group has the highlest or lowest ownership.
Frequency tables are easy to replace with graphics
Instead of enhancing a table it can be simply replaced with a graphic, which can make interpretation of the results easier. A simple frequency table (See our Guide How to Lay Out and Interpret a Market Research Table for definition) is often best shown in a simple bar chart.
But note, just because it is a chart does not mean the rule of presenting market research data should be ignored (again see the above Guide), so Bases must be shown along with a Figure source.
Comparative profiles can be shown in graphic form
Graphics can also be used to show more complex data than frequency tables. Assume you are trying to compare the profile (see the above Guide for definition) of two groups of consumers based on the age, housing tenure, working status and parental positon of each group. One option is to show a table with the profile pecentages side by side in a separate column. This table is likely to be very long and it will not be immediately clear to individuals looking at it where one profile differs from another.
A better option is to put the data in graphic form as is shown opposite. On the graphic, we have highlighted with Yellow stars those profile groups found to be much more common among consumers who have been in arrears compared with consumers who have not. Now it becomes easily observable that consumers who have gone into arrears are more likely, compared with consumers who have not got into arrears, to:
- be under 35 years of age
- have children
- live in rented accommodation
- not be retired
- work in part time employment
- not be in paid work due to long term illness or disability
Penetration data becomes clearer in a graphic
Like profile data, presenting penetration data in a long table can make it hard for those looking at the results to see clearly which groups are more likely to undertake an action, get into a particular situation or buy/own a particular product or service. The two Figures opposite shows the same data concerning the likelihood of consumers who own any unsecured debt (unsecured debtors) of falling into arrears. The table and the graphic shows the penetration of arrears among unsecured debtors for key social groups and contrasts that with arrears among unsecured debtors as a whole.
On the graphic is is much clearer that younger consumers, those with children, those in rented accommodation and those not working or working in part time jobs are the most likely to get into arrears difficulties. So while 8% of all unsecured debtors have fallen into arrears, this rises to 17% among those who are not in paid work because of long-term illness or disability but falls to just 1% for unsecured debtors aged 65 and over.
Ultimately don’t report it, dashboard it and/or visualise it
A growing trend in market research is to present results in a dashboard format or via a data visualisation tool like Tableau. At a simple level this could result in a collection of graphics similar to those that would appear in a PowerPoint presentation or written report. At a more complex level, it could be mean users have access to the structured research results and can drill down and manipulate the results for themselves, showing the results in various graphic formats and tables.