How to Use PivotTables for Social Media Analysis

I’m often given a large amount of data to analyze, which on its own doesn’t make sense or would be a major hassle to build any insights from. Luckily PivotTables exist and are an easy way to analyze data without jumping into more advanced analysis programs such as R. For this tutorial, I’ll be using data exported from Twitter Analytics and doing an example analysis on finding the top-clicked posts. I’ll also show how to create a trend chart from your PivotTable.

What is a PivotTable?

A PivotTable is a tool that allows you to generate insights or summarize specific data points from a larger and more detailed data set. PivotTables are commonly created in Excel and other business intelligence software.

How to create a PivotTable in Excel

Step 1: Back up data and convert to Excel file

Before doing anything with the data I’m given, I make a copy of the original data that I can revert to in case anything goes haywire (because, you know, technology). If your file is not an Excel Workbook file, make sure to save it as one to ensure your PivotTable works and saves properly.

Step 2: Select data for analysis

You can select your data a couple of ways. The first is by selecting only the data and excluding any of the blank cells around it. The other way is to select entire columns that contain data, which will select any blank rows below it. This method is great if you end up needing to add more data later on and reanalyze it.

Step 3: Create PivotTable

Now that the data is selected, click on the Data menu in Excel and then click PivotTable. (Depending on your version of Excel, making a PivotTable may be under the Insert menu option). Excel should now automatically create a new tab with a starter PivotTable and open the PivotTable Builder window. Since teaching how to use the PivotTable Builder could be a tutorial on its own, I recommend visiting Microsoft’s website for more advanced ways to use the builder. In the mean time, feel free to follow the instructions below for doing basic data analysis.

Using a PivotTable to find top-clicked URLs on Twitter

To find which tweets received the most URL clicks, you’ll need to first figure out which fields you need to use for the PivotTable Builder. In this analysis, I determined that I would need “Tweet text” as my row label (to show post text) and “url clicks” as my value (to show URL click data). Adding these fields into the corresponding PivotTable Builder is done by clicking and dragging the field names into the sections.

Since you want to see the total URL clicks for each tweet text, you need to make sure that it calculates the field by sum of URL clicks and not by count of URL clicks. To change this, click on the “i” icon to the right of the field name in the value section of the builder and then change the “Summarize by” option from “count” to “sum.”

After that’s done, you’ll want to sort the URL click data from largest to smallest number of clicks. To do this, click within the URL click data row and then hit Excel’s Sort button, found under the Data menu tab. For my data, I can now see that the #ShiaLaBeouf post received 10 URL clicks and was the highest-clicked post for my data.

Using a Pivot Table to create trending charts

Now that we know how to see which posts are performing best, let’s make a PivotTable that’ll allow us to chart out trends from the data. Since Twitter Analytics data includes a “time” data column, but is in the wrong format, you need to add a couple of custom function columns to your data. To grab the date text out of the “time” column and make sure it’s in a proper date format, I use the following function:

=DATEVALUE(LEFT(E2,10))

Be sure to format this entire column into a date format by selecting the whole column, right-clicking on the selection, clicking Format Cells…, and then selecting the first date option from the Format Cells window that opens (labeled as “(3/15/05). Once you hit ok, your cells should now be formatted as a date.

This will now show you when the post in that row was published. For my chart, I want to make a month-over-month trend, so I’ll need to create an additional custom function column in order to group all posts by a certain month and year. To do this, I use the following function:

=DATE(YEAR(B2),MONTH(B2),1)

Like the last date function, this whole column will need to be formatted into a date column. To do this (as shown in the example below), instead of selecting the first date option, select the “Mar-05” option and hit OK.

After confirming the two functions are working properly for the first row, you can implement the functions to the remaining rows of data. You can do this by selecting and copying the two function cells, then highlighting all the corresponding blank cells below the functions and hitting Paste. Be careful not to paste the function below your last row of data or you’ll get a #VALUE! error in your PivotTable. Once that’s completed, you’ll want to add the two new columns of data to your PivotTable.

Click back onto the tab sheet that contains your PivotTable, click on the PivotTable menu tab at the top of Excel, and then click Change Source. Excel will open a new window that shows the current data your PivotTable is pulling in.

To add your new columns, you can either reselect all the data or replace the “$C” with “$A” to include the new A and B columns. After hitting ok, you’ll be taken back to your PivotTable and should see the two new columns in your PivotTable Builder list of field names: Month and Date.

Next, simply drag and remove the “Tweet text” field and replace it with your new month field. You now have a PivotTable that contains a month-over-month trend of your URL clicks and can create a chart based on it. You can do this by selecting the data in the pivot table and then choosing one of the chart options in Excel’s Charts menu. For my example, I used the Clustered Column chart.

Related resources:

• PitvotTables in Excel
• 2 Essential PivotTable Skills for Marketers
• How to Import Cost Data to Effectively Measure Paid ROI