Here’s a situation: it’s the end of your company’s first quarter. Your boss asked you for a report of your progress over the last year – an easy-to-read chart that tells the story of your company’s growth by way of specific key performance indicators (KPIs), including conversion rates, monthly sales, social media engagement and any other metric you might be held to.
You’ve got your data set, and it breaks down every item you’re dealing with: individual sales, individual social media posts, individual PPC campaigns. It could be anything.
But the point is, it’s raw data. What are you going to do with it?
The answer: pivot tables.
What are pivot tables?
In brief, a pivot table is just another way of viewing your data; it restructures (or “pivots”) your raw numbers in a different way. Ideally, this way is more legible and can tell a clear story.
Microsoft Excel, the industry standard for spreadsheets, has put pivot tables front and center under the main menu when you click on Insert > PivotTable.
So, how can pivot tables give you insight into your business? There are a myriad of ways, but it depends on the type of information you’re working with. You can find comprehensive guides online; this won’t be another one of those. Instead, this should help motivate you to find new ways to understand and appreciate your data.
Pivot tables can help you understand your best social strategy.
The good minds at Facebook and Twitter allow you to export a thorough set of post and page data for any company account you have access to. And while the data itself can be many tabs wide, subdivided into countless columns (many of which you likely won’t even need), the fact that it’s available to you is a godsend.
Once you’ve exported your data from Facebook, for instance, how can you work with it?
Pivot the data from focusing on individual posts to a monthly overview. Select your data range and go to “Insert > Pivot Table.”
Now you can use the Pivot Table builder to see your data in a more meaningful way.
Use “Months” as your row label, and change the “value” to the sum of link clicks, for example. Here you’ll find a succinct snapshot of your monthly clicks, which you can reorganize any way you need.
From here, you can even insert a Pivot Chart to visually display important trends in your data.
Instead of clicks, you can sort the value as anything else – retweets, non-clickthrough clicks (reactions on Facebook, say) or comments. If you’re tracking performance over a period of time, this kind of data is invaluable.
Pivot tables can show you a paid campaign’s success over time.
Paid advertising is still the most common type of digital ad spend in 2018, be it through boosted Facebook posts or Google Adwords. And you can see your data results broken down by campaign quite clearly with either one, with the left-most column indicating the campaign and each subsequent column depicting a different total analysis – click-throughs, engagement, cost per click, total views, and so forth.
But what this top-level spreadsheet doesn’t show you very quickly is each campaign’s progress. For this, select your data set, format it as a table, and pivot it.
Use your values to track the metric you’re most interested in – if we’re talking about total impressions, then you’ll be able to see how many impressions were reached each day.
Then, you can use Pivot Charts to visual your campaigns progress over time.
This can give you a clearer idea of how campaigns fare against one another on a given day and possibly inform you as to why they might perform the way they do.
Pivot tables can show you hidden secrets about your own company.
Let’s turn away from paid and social marketing and look at the broader picture: your own company.
If you have a data set detailing your customers’ wants and needs, you can use that data to inform your next marketing initiative, or even shape the direction of your product.
We survey our customer base every six months or so, to gather information, like job title, company size, company revenue, industry Net Promoter Score, most requested feature and so on.
Even just asking a few questions can result in a pretty large data set. Using pivot tables makes it simple to cross-tabulate the data and find that sweet intersection of things like:
- What characteristics make up our promoters and detractors?
- What features should we be building for our promoters?
- What are some ways we can increase customer lifetime value?
You can use this information to direct remarketing efforts, loyalty programs or re-engagement campaigns.
As you can see, a pivot table is a useful little tool for reorganizing data in a way that tells a story and helps you make smart decisions about your product, sales and marketing.
Regardless of how you use your data, the bottom line is clear: getting familiar with pivot tables will show you a whole new side of your data you’d never be able to see otherwise.