Advanced Social Media Data Analysis With PivotTables [More Excel Tips]

In my last #MetricsMonday blog I explained the basics of PivotTables and how to conduct some simple analyses of data exported from Twitter Analytics. Since all the capabilities of PivotTables were too much to share in one post, I decided to provide some guidance on advanced capabilities you can build upon from our last analysis. For this blog, we will cover how to merge data points and create custom formulas within the PivotTable.

How to merge Excel data points for PivotTables

Looking back at my last analysis of Twitter Analytics click data, we were able to see which post received the most URL clicks, but we didn’t quite see the whole picture. You might have noticed that Twitter Analytics actually splits its data into “URL clicks” (organic clicks) and “promoted URL clicks” (clicks received during a paid promotion for a specific tweet). As a result, we only saw organic URL clicks that tweets received.

To see all clicks for a tweet (organic and promoted), we need to merge the two data points together using Excel’s Calculated Fields capability for PivotTables. To do so, click on the data within your PivotTable, click the PivotTable menu in Excel, click the Formulas button located to the left of the menu, and then choose Calculated Fields.

The Insert Calculated Field window should now be on the screen. First, you’ll want to name your new field. I named mine “Total URL clicks (Organic + Promoted).” Next, click in the Formula text box and remove the zero. You can now place the formula to add all of your clicks together.

To do so, scroll down in the field list and find “URL clicks.” Click on the Insert Field button. (You can also double-click the field name to add it.) You will now notice the field has been added to the formula text bar. Since we want to add organic and promoted clicks together, you’ll need to add “+ promoted URL clicks” to the field.

Click OK at the bottom of the Insert Calculated Field window. Your new field should be added to your PivotTable, and you can now begin your analysis for total URL clicks.

As we did with my last blog’s analysis, you’ll want to sort the “Total URL clicks” data from largest to smallest. To do this, click within the “Total URL clicks (Organic + Promoted)” data row and then click on Excel’s Sort button, found under the Data menu tab. Your data should now be sorted by tweets with the most total URL clicks.

How to create custom formulas for PivotTables

Using the same “Insert Calculated Field” directions I gave above, you can also create custom formulas. For this example, we will create an Engagement Rate formula: [(Engagements + Clicks)/Impressions].

Before we actually make the formula, we first need to merge the following data points like we did for “Total URL Clicks (Organic + Promoted).” Instead of clicking the OK button in the Insert Calculated Field window, you can choose the Add button located in the top right. This will make creating multiple calculated fields easier and faster.

Create the following Calculated Fields:

• Promoted Social Engagements:
  = ‘promoted retweets’+ ‘promoted replies’+ ‘promoted likes’

• Organic Social Engagements:
  = retweets + replies + likes

• Total Social Engagements (Organic + Promoted):
  = ‘Organic Social Engagements’ + ‘Promoted Social Engagements’

• Total Impressions (Organic + Promoted):
  = impressions + ‘promoted impressions’

When making the formulas above, you’ll notice your custom calculated fields will be added in your fields list automatically. This allows you to build upon your existing fields and make more advanced formulas like Engagement Rate. Once all the above formulas have been created, you can make the Engagement Rate calculated field by using the following:

= (‘Total Social Engagements (Organic + Promoted)’ + ‘Total URL Clicks (Organic + Promoted)’)/ ‘Total Impressions (Organic + Promoted)’

Now that you have made your Engagement Rate field, you can click OK to go back to your PivotTable. You’ll notice all the calculated fields you created have been added to the “values” field of your PivotTable, which is a bit of a data overload to try and analyze. To see only the Engagement Rate field, you can click and drag the other fields to the outside of the PivotTable Builder to remove them.

Once you have done that, change the format of the Engagement Field from “General” to “Percentage.” To do this, click the “i” icon next to the Engagement Rate field to bring up the PivotTable field settings, and then click the Number button. This will bring up the Format Cells window so you can select “Percentage” from the category list instead of “General.” (For this example, I left the decimal places at 3). Next, click OK until you get back to your PivotTable.

You should now see that your PivotTable is showing the Engagement Rate in percentages so you can properly do your analysis. As we did for Total URL Clicks, you can also sort this data from largest to smallest.

Related resources:

• Microsoft Excel Support: Calculate values in a PivotTable report
• WikiHow: How to Add a Custom Field in Pivot Table
• Contextures: Excel Pivot Table Calculated Field