Pivottable use for social media

How to use pivotTable for social media analytics in 10 easy steps

Social media analytics is the core part of the digital marketing when it comes to measure the RoI.

To bring your work in the lime light is the necessity of every professional to present your work in the best possible way to your clients or boss. How you completed your journey from conception to execution depends on how you sum up everything in your report.

“Bad human communication leaves us less room to grow” Rowan D.Williams

Therefore, pivotTable reporting for Social media analytics has been shared here so that you can present your campaign reports easily for the non-technical audience. Though the data that has been taken for this report is quite small but for the understanding of a pivotTable concept, it will be really helpful.

STEP 1

Download all your data in a raw form any source such as Google Anlaytics, Google Adwords, Facebook ad manager, business intelligence or any CRM software.

Social media analytics - raw data downloaded from Facbook Ads Manager

Raw data downloaded from Facebook Ads manager

STEP 2

Gather all the data at one place, you don’t have to delete any rows and columns as for the pivotTable, it’s all about the game of pick and choose. This will be explained below in detail.

Next step is to create one blank row at the top, and one column on right side in order to differentiate the table from other data (if there is any).

Social media analytics - add one line up and right side of pivotTable

Add one line up and right side of the table

STEP 3

To create the pivotTable, you can use the shortcut key  Alt+N+V ; else,  you can go to ‘insert‘ option from the ribbon and select PivotTable.

Social media analytics- pivotTable usage to make reports via shortcut key

PivotTable creation

 

STEP 4

Once you have done this, you will get the option to where you want to place the pivotTable, either on the existing sheet or on a new sheet. Select new sheet and press ok.

Social media analytics- Pivot table creation on a new sheet

Select the new sheet option and press OK

STEP 5

Now you are on the pivotTable sheet. You can see a field list on your right side.

Social media analytics- pivotTable creation on new sheet

PivotTable fields appears on right side

 STEP 6

Imagine what you want to focus, here we want to see the breakup of gender and their performing results, so put the placements option in the ‘row labels’. The 2nd item that you have to drag is age group so that you can be more specified about the gender.

In the ‘values’ field box, drag the item that you want to evaluate. For instance, we want to see the consumption of the impression and how many likes we got with respect to gender and Age group.

Simply drag the impression and likes in Values field and see the magic.

Social media analytics- Dragging the items in row label fields

Drag the items in row labels and Values field

STEP 7

Now you will see a table having all the information you wanted to see. One thing to keep in mind that most of the time the values appear in ‘count’Double check that your value are showing as SUM not the COUNT, and you can view this from the ‘options’ tab.

Social media analytics- show the value as sum instead of Count

Select how you want to see the values

STEP 8

You will see these total options at the end of the every column, if you want to keep it so its fine otherwise you can remove it.

Social media analytics- showing sub totals in the table

Table showing sub-totals in the table

STEP 9

To remove these totals, go to design tab and select sub totals> do not show sub totals. This will show the entire grand total at the end of the table instead of each segment.

Social media analytics- Select the "do not show sub-totals" from the menu

Select the “Do not show sub-totals” from the menu

STEP 10

To arrange the data in proper shape, to show your data in tabular form, so here is the option to present the data in tabular form.

Social media analytics - showing the data in tabular form

Select the tabular form from the menu

Now, you have your final output ready. now it’s time for icing on the cake, you can go to design tab and select the option branded tab, where you can play around with branded rows, branded columns and with other default provided pivot table style options.

Social media analytics - design your pivotTable from menu options

Beautify your PivotTable with different options

Random Checklist to get accurate data

  • In raw data, do not add a line in between the table. By doing this, excel will consider the data only from above the broken line.
  • If you have modified the raw data, don’t forget to refresh the pivot table sheet by selecting the OPTIONS tab.
  • Don’t forget to delete the grand total coming at the end of row; otherwise, total figure will be reflected in the pivot table.

If you want to ask anything related to the pivotTable reporting, write your comments below or else contact me through email.

2 Comments

  • mohammad May 9, 2016 Reply

    today i have learn something new thanks admin

  • Dorie July 11, 2016 Reply

    That kind of thnniikg shows you’re an expert

Leave a Reply