An Easy Way to Automate Your Facebook Reporting Using Pivot Tables

I hate doing things twice. I also hate doing things by hand. When it comes to PPC reporting, there is nothing quite as cumbersome as recreating reports from scratch month after month.

Thankfully we have the tools available to us to make easy and effective automated reports a cinch. Assuming you have Excel, you’re pretty much set. Rather than duplicate our work every month, we’ll do the work once, and leave ourselves in the best position possible to simply update the data and get on to the analysis.

These principles can apply to any type of reporting, but we’ll be focusing on a Facebook report.

Step 1 – Create your reports

One of the keys to reporting with ease is using reports in their rawest, most standard form. Trimming data adds unnecessary manual labor that our computers can easily do for us.

For our example report we want to give our client an idea of the amount of traffic, Likes, and a couple of specific actions they’re generating on Facebook and how much they’re costing them. Since Facebook’s standard Advertising Performance report only gives you a sum of all actions together and we want data about specific actions, we’re actually going to be pulling two reports. If you want to save yourself some time and can afford to report on actions in general, you can actually avoid pulling a second report.

The first report to pull is the standard Advertising Performance report. We’re going to whip up some year-to-date numbers for the client. Set your time summary to monthly. You can go as deep or as shallow as you want with how you summarize your data – for our purposes, we’ll stick with the campaign level. And don’t worry: your hands won’t be tied if you decide to report by account later on.

Since we want to report on some specific actions, we’ll pull a second report, the Actions by Impression Time report. Same set up as before, monthly with a year-to-date timeline.

Export your reports and we’ll be ready to get started in Excel.

Step 2 – Create tables

The first key to getting our reports set up for easy updating is to put our data in tables. It’s really important to note that you don’t simply use manual filtering or naming ranges here, but an actual table. This is going to be key later on as we add data to our table and it automatically recognizes it as part of the table.

Go ahead and fix the ridiculous way Facebook formats the word “Date” and then make the table. As with everything in Excel, you have two options: I prefer either clicking the “New” button under the Tables ribbon, or simply selecting one of the table styles under the ribbon.

Now that we’ve made our table, let’s name it for good measure. Select a cell inside the table, go to the Tables section of the ribbon and click rename, and rename it something useful to you. I usually go with FBDATA. Remember no spaces and no numbers.

Repeat those last steps for your Actions by Impression Time table (I named this table ACTIONDATA) and we’ll be done making tables. You should end up with something along the lines of this:

Step 3 – Pivot away

We’re going to turn these two tables into three pivot tables that will seamlessly provide our customer with the data he needs.

Go into your FBDATA table, navigate to the Tables section of the ribbon and click “Summarize with Pivot Table.” Your data range should automatically populate, but if it doesn’t just type in FBDATA and you’ll be good to go. Put this pivot table on the report tab.

Before we go any further, make another pivot table using your FBDATA table and put it on the same sheet just to the right of the table. We’ll be back for that one later.

Let’s go to the Report tab and populate our first pivot table. Our client wants to see numbers on Clicks, Impressions, Page Likes, Page Post Likes, Page Post Shares, Spend, CTR, CPC, and CPL. Let’s make it for him.

For our rows we want to summarize first by Date, and secondarily by Campaign. If you want to report at the account level, include Date only. For our values first we’re going to drag Clicks, Impressions and Spend. Don’t forget to format your fields correctly as Sum and give them the correct number type (numbers, currency or percentages).

Now that you have those three, let’s work on the fields that deal with percentages and averages. You can’t get CPC or CTR numbers directly from your table because it will either sum them into a total, or it’s going to find the average of all your CPCs and give you an inaccurate number.

Next, select “Formulas” then “Calculated Fields” under the pivot table section of the ribbon:

Give your new field a name; I tend towards names like “Actual CTR” or something similar. In the formula bar we’ll actually input the fields we want the pivot table to go through.

Continue the process until you’ve got fields for CTR, CPC, and CPL. Even though it’s available here, we’re not going to include numbers on total page likes in this table; we’ll save those figures for the Actions table. Speaking of…

Lets make our next pivot table. Repeat your previous steps and populate the data from your ACTIONDATA table into its pivot table. We’re going to take a little bit of a different approach to building this one. It starts out the same with Campaign and Date as our rows. We are going to place “Action Type” as our column and “Sum of Actions” as our value. Since this gives us more info than we need, we’re going to filter our columns to only include Page Likes, Page Post Likes, and Page Post Shares.

Step 4 – Make it pretty

So that’s great – I gave you two discombobulated pivot tables and I said I was gonna give you a PPC report. We’re almost there.

First things first: let’s go back to that second pivot table you made from FBDATA. Select “Date” as your rows and “Clicks” and “Page Likes” as your values.

Click any cell inside the pivot table, go to the chart portion of the ribbon and select a column chart. You just created a chart that will automatically update stats as your pivot table updates. Move the chart onto the “Reports” tab and let’s start beautifying this lovely pile of pivot. You should be looking at something like this:

First, let’s make sure our two tables match up line for line. Do you have months with no action data? You may have to do some one-time manual labor. If you find you have any missing months, you’ll need to go to your ACTIONDATA table and input columns for each action you want to track for any month that is missing.

Make sure you input your date in the same format as the rest or you’ll end up with an extra entry. Copy and paste if you can. Add one column for each of your tracked actions (in this case three) and put as the total number of actions. This will give your pivot table something to latch on to. After you’re done, your pivot tables should match up exactly. That’s going to be important in a minute.

In my opinion, pivot tables look a little hokey in their native form. The nice thing is that there are a ton of options for sprucing them up. Choose a style that looks clean and professional and if applicable fits with the rest of your reporting. Lets also get rid of some of the extraneous formatting. We don’t want totals for columns, we don’t want triangles, and we don’t want headers.

After you’ve done that, take some time to go through your pivot table fields and rename them. I tend to replace “Sum of” with “Total” or “Average.” Don’t forget to do this on your chart’s pivot table too. I also decided that since my client only has one campaign, it would be redundant to include the campaign name and date. So I’m going to remove “Campaign” from my rows. So fresh and so clean.

This is purely a personal preference but on my chart I like to track page likes as a line rather than columns, and I like to track it on a secondary axis. I think it looks pretty so I’m gonna do it.

If you also want to do this, it’s pretty simple. I start by selecting “Total Likes” in the legend and then selecting the line chart type. Then I select the line, right click, and choose “Format Data Series.” Under the axis menu pick the option for secondary axis. Looking good.

It’s totally a personal preference thing for me, but I like having my chart up at the top with the client info; it gives them a clear picture right away. If you need to move your pivot table to make room, it’s really easy. Just highlight everything (including the blank filter bar at the top), and drag it with the hand cursor down to where you want it. You can also use the “Move” command on the pivot table section of the ribbon.

You’re so close to being done. Line up your two pivot tables so that the rows match and simply hide the space between them and the Date and Campaign names on your second pivot table.

How’s about that? You just made a year-to-date report with two sheets of data.

Hide those data tabs and send it off.

Step 5 – Update update update

Now this would kind of be a waste if this were a one-off report. But the beauty is that you just did the leg work for (roughly) the rest of your life.

Next month when reporting time comes again, pull those same reports. Copy and paste the data into your two tables (update the actions if you need). Now go to one of your pivot tables, go into the pivot table section of the ribbon, click “Refresh” and “Refresh All” and you’re done.

Seriously.

It probably took you longer to read that than it will take you to do it.

Enjoy.

Strange there seems to be a 30 year gap in my data…