If you're an avid reader of the HubSpot blog, you’ve probably noticed that it’s looking a little different lately. More than just the typical redesign or fresh coat of paint, the whole structure of the house that is our blog has been changed to accommodate different topical areas.
Of course, more than just the front-end changed to accommodate this redesign. On the back-end, the URLs on which each piece of content lived changed, too.
The blogging team came to me afterwards because their reporting had been affected by this change. Corey and Pam typically downloaded a Page Performance report and used the associated metrics to assess the relative performance of a post. However, with the changes in many URLs, they found that the Excel spreadsheet split the metrics associated with one article onto two or more lines. That meant they'd have to manually identify and subsequently "mash up" all the duplicate lines of data -- an exhaustingly manual process.
Luckily, this wasn’t a hard problem to solve with the use of a pivot table. What I'm about to walk you through in this post is exactly what I showed them -- and while you might never have this exact problem, you may find this helpful next time you need to summarize data, or sort through reporting that comes out as a big messy download.
If you learn by doing, you can follow along by downloading this sample report.
How to Create a Pivot Table
Step 1: Familiarize yourself with your data so you can set a game plan.
I walked through each column of data so that I could understand exactly what we were looking at. Here’s what I learned about each column, and how they relate to each other:
1) URL- Each row had a unique URL associated with it, with no duplicates -- that means we can't use URL as the common element to identify duplicate rows.
2) Title - We could, however, use this as our unique identifier, since the title remained the same in every duplicate instance. That's good news.
3) Views - Views are tied back to the URL, so we would need to simply sum the page views that were associated with each instance of a page or article title.
4) CTA % - This is the number of times any CTA on the page on which the article appears gets clicked, divided by the number of times that article has been viewed. To get the summarized CTA % of an article, we would need to calculate a weighted average for each article. So we needed to create a new column to represent the numerator in this fraction -- or the number of times a CTA was clicked for each URL. (Don't worry -- it’s easier done than said.) Simply multiply the page views by the CTA rate.
5) Links and Comments - Unlike views, HubSpot would continue counting redirected inbound links after all the URLs had been changed. So, if we simply summed up all the links or comments associated with an article title, we’d effectively be double counting all the links and comments that were associated with the original URL. Here, we’re looking for the maximum value associated with an article title so we could count all the links and comments presently associated with the article. Not perfect, but better.
6) Author and Post Date - Exactly as advertised.
Step 2: Actually create the pivot table.
So, looking at the raw excel download, let's go to the Insert menu and click on Pivot Table.
Make sure you’ve placed your cursor somewhere in the data array and Excel will select an entire continuous array for inclusion in your pivot table (look for the blinking dotted line). Click OK.
Excel will now create a new table with a blank pivot table. Off to the right you will see all your column titles from the raw Excel file primed and waiting to be placed into different parts of the pivot table, like so:
Note: Your pivot table may look different depending on which version of Excel you're working with. However, the general principles remain the same.
Step 3: Start organizing your data points.
Since our whole aim was to organize these data by post titles -- and not URLs as the report had done -- we want use Title to define our row labels. To do that, just drag and drop Title to the Row Labels box.
Now we can easily summarize all the metrics that we want to calculate in the Values section. Let’s start with Views. Simply drag Views into the Values box. There’s a chance that when you do this, the Total column will show 1s and 2s. That happens when your sourced data has blank rows at the bottom of it, which is not uncommon. So, the default setting for this variable will be to summarize by frequency rather than to simply add it up. You can go back and delete the extra rows, or you can tell the pivot table to add up Views. To take the latter approach, you click on the arrow next to Count of Views in the Value box and select Value Field Settings. From there, move the highlighted selection from Count to Sum and click OK.
Next, we need to add the Comments and Links fields to the report. Drag them both to the Values box under Sum of Views. Thinking back to how this data is reported, you may recall that we were concerned about double counting and so we needed to report the maximum of each variable associated with each Title. This is really tricky to do in a formula, but very easy in a Pivot Table. Again, you will need to go into Value Field settings for each field, this time selecting Max instead of Sum. Do that for each variable.
Step 4: Create a calculate field.
(Note: This step may not be necessary for you, depending on what you're using your pivot table for. We're including this step because it was needed to help solve the particular scenario outlined in the introduction.)
Finally, we need to calculate the weighted average CTA % associated with each Title. To do this, you will need to create a calculate field on your Pivot Table. From the menu, select Fields, Items, & Sets and then Calculated Field.
Name your field something, like "CTA Click Rate," and then move on to the Formula field. Highlight the "0" and then select the field you created on the original report -- CTA Clicks -- and click Insert Field. Next, type a "/" to indicate that you want the field to divide CTA Clicks by something. Then insert Views and click OK.
Step 5: Update your LinkedIn profile.
Congrats, you have just mastered pivot tables! Add the words “Pivot Table” and “Wizard” to your LinkedIn profile. You've earned it!
Start Digging Into Your Data
Depending on what you need your pivot table for, you might not be done. The blogging team, for instance, wanted the final report to show dates and authors associated with each Title. If you try and do this in a pivot table, you'll end up getting very frustrated. So from there, they'd want to use the incredibly powerful VLOOKUP (Vertical Lookup) formula.
Or perhaps your data isn't sorted the way you'd like. No problem. Now that your data's organized, you can sort it to appear in the way that's most helpful for your analysis using either the Sort or Filter functions. (I prefer the Filter function when working with large and complex data sets, but use whatever makes you most comfortable.)
And if the idea of filtering, sorting, and vlookup-ing is stressing you out -- don't worry. We've got a few posts coming down the pike for you! So stay tuned.
Are there any other Excel functions you'd like to learn? Leave a note in the comments for future blog posts.