Mastering Pivot Tables: Data Structure

Data. Data. Data. We’ve got so much of it, particularly as digital marketers. Figuring out effective ways to organize, analyze, and extract insights from data separate the good digital marketers from the great ones. Getting comfortable with Pivot tables will help you quickly draw insights from large data sets so you can spend less time analyzing and more time using insights to drive decision making.

Foundational to pivot tables is data structure. A pivot table is simply an interface to manipulate a large data set. Your dataset needs to be organized in a particular structure to leverage the power of a pivot table – that’s what we’ll review in this post.

There are two types of variables you might have in a data set: categorical and quantitative. Categorical variables are basically anything that’s not a number. Months, years, colors, sales regions, or gender are all categorical variables – they define a value in a particular category. Quantitative variables on the other hand are numbers. A few examples include impressions, clicks, revenue, or average sales price.

Take a made up dataset, which you could organize in two ways. Sales by month and year. If you were just creating a table, you might organize it like this…

Unfortunately this structure cannot be used in a pivot table because the data is not organized into columns. But what does that does that mean?! Each element – month, year, and sales, needs to be in a column with the corresponding categorical values AND quantitative values as rows. It would look something like this…

By doing so, you can now leverage the power of the pivot table to layout the same type of table we had in the first place. In another post, we’ll get into how to setup a pivot table. But for now, just remember that your data needs to be structured in columns to use the harness pivot tables.