The Power of Pivot Table Analysis
According to a straw-poll I recently took with data collection agencies, the sole output for many research firms that commission data collection are static cross-tabulations. I expect that this is not too dissimilar for many research firms that have internal data collection departments. An example of a cross-tabulation includes intention to purchase cross tabulated by level of satisfaction. Useful undoubtedly, but also restrictive in that the researcher is unable to interrogate beyond the initial specification. So, the researcher short of requesting data processing produce additional cross-tabs, cannot investigate new lines of thought that are prompted by the findings initially presented.
For it to be possible to interactively produce cross-tabs, one needs to at least have the data. According to that same poll only a minority of firms who commission data collection even take possession of the actual data file. Sure, the data collection agency commissioned could continually produce the updated cross-tab requests as the client raised them, but this is a very ‘stop-start’ process not at all conducive to lateral or creative thinking. The time delay in this static ‘request-produce’ elongated process can itself become a barrier to further investigation.
Requesting a-priori cross-tabs as a base of insight from quantitative research for me raises some questions of the degree to which the full insight is being wrung out of the research.
The Pivotal Solution?
There is a very simple and accessible solution and yet one that it seems is not being broadly applied. Enter the pivot table; these take the form of a contingency table or cross tab where the rows and columns allow you to break the data down by the categories of the variables in these areas. The body of the table specifies the data you want to calculate such as proportion or average for example.
Pivot tables are produced using the ubiquitous Microsoft Excel. Not everyone has access to SPSS software or the knowledge to use it but pivot tables require little prerequisite knowledge and they’re on almost every desktop. With the check of an option you can also produce charts of every description that provide a visual representation of the findings in these pivot tables. The beauty of these charts is that they are linked entirely to the associated pivot tables, so if you rearrange the data cut in your pivot table, the corresponding chart updates automatically to reflect the change. But the first step is to order the data from data processing or the data collection agency rather than rely on the ‘standard banner’ from cross-tabs.
The power of the pivots lies not only in the fact that there is virtually no barrier to their use but also that researchers are able to produce customised cross-tabs at their desk. If the one you first choose does not provide the information you thought it would, then you simply produce another in only seconds. Or, if after examining your first set of cross-tabs new questions are raised, as is usually the case, you can instantly pursue this new line of investigation to see where it goes. No waiting for outsourced cross-tabs to come back; no time delay at all. In other words, the data belongs with the researcher and not with the data collection agency or data processing.
Segmentation First
In my experience, the most useful cross-tabs come as a result of meaningful segmentation. Once again, this requires the researcher to take possession of data and to be able to conduct exploratory analysis to identify ‘real’ segments. Cross tabulating research on the basis of segments has the greatest prospect of presenting the client with actionable insight.
Taking possession of the data file also provides the opportunity to examine the file for potential errors, for example making sure that the correct filters and skips are being correctly applied and removing outliers and respondents who have not used the scale appropriately, although some of this is best undertaken at the completion of the pilot study.
Pivot tables are a powerful tool with virtually no barriers to entry other than perhaps the lack of awareness of the potential users. The challenge now is for the researcher to give them a go – I promise the pivot tables will not disappoint you!
