Analyze Facebook Insights with Excel

Update 6/6/2016: Due to a Facebook bug here, it is not possible to read private Insights from your page, luckily the public metrics are still available. So this tutorial is still relevant.

Finally!!! I have waited 9 months for this feature to work in Excel (here is the proof). If you have the latest Excel on Office 365 version 16.0.6568.2036, or the latest Power Query Add-In on earlier version of Excel, you can now create amazing Facebook Insights reports and dashboards on Excel.

Recently I have shown you here and here how to create such reports using Power BI Desktop. There are also some Facebook API bugs here and here that are not fixed yet by Facebook, but after waiting 9 months, I will not let minor obstacles to stop me.

In today’s tutorial, I will show you the basic steps to create your first Facebook Insights report. We will assume that you have Facebook permissions to read the Page Insights, but this blog is also relevant for (partially) analyzing pages you don’t own.

Before we begin, here is a short teaser and a proof that this tutorial can help you. The following screenshot shows the number of shares for the official Microsoft Excel facebook page by top countries and date. As both of us don’t own this page, but can retrieve such data for analysis, must be a strong enough incentive to continue reading, right?

By the way, check out the unusual peak in the number of people talking about Excel on February 18, and later on February 23. The second series of peaks can be explained by the release of 6 new functions in Excel (That was announced here), but I didn’t figure out yet what caused the earlier peak on February 18 (My current guess is below).

My bad guess: Perhaps this photo can explain the first peak? Probably not. If you know the answer, please share it in the comments below.

The basic report – Analyzing Data Chant Facebook page

In today’s post I will show you how I created the query and the first PivotTable that summarizes the impressions of my Facebook page by country and date. When you follow the steps below, please use your own page.

Note: If you don’t own a page on Facebook, or lack permissions to read insights of your company or customer’s page, you can still read data from Microsoft Excel Page or any other page on Facebook (with minor modifications that I will describe soon).

Stay tuned for my next blog post to learn how to compare multiple Facebook pages.

So I have a Facebook Page which is dedicated for my blog. I don’t invest much in it, but it will help us to demonstrate how to build reports in Excel with Facebook Insights.

Let’s start by opening Excel. Make sure you are on the latest version: 16.0.6568.2036. If you are not, you will end up with an endless refresh later on (Which is why I have waited 9 months for this version).

In the Data section, click New Query, then click From Other Sources and finally From Facebook.

In the Facebook window, enter your Facebook Page object name (as it is identified by Facebook). For example: in my case my page’s object name is datachantblog.

Note: If you don’t own any Facebook page, you can use any page you want with a minor modification that will be described later on.

If you are not sure what is your page object name, go to the page on Facebook and copy the folder name in the URL:

Back in Excel Facebook window, select the –Custom– Connection and enter insights, then click OK. In the Query Editor, make sure you have the formula bar visible (You can activate it by enabling the Formula Bar check box in the View tab).

Add the suffix “/page_impressions_by_country_unique” to the string that ends with “/insights” and press Enter.

Note: If you don’t own a Facebook page, don’t use “page_impressions_by_country_unique”. Instead use “page_storytellers_by_country”. This entry point will return data even if you don’t own the page.

Now add the suffix “/since=01-01-2016” to the string above. Skipping this step may result in a small 3-days worth of data from Facebook. 

Click the filter button in column period, select day. Then uncheck all other values, and click OK. 

Click the expand button in column values and click OK.

Now let’s remove unnecessary columns. Click Choose Columns in Home tab, and select the values that are highlighted in this screenshot. Then click OK in the Choose Columns pane.

You can see that you have a mix of List and Record objects in column values.value. Further inspection inside the List object will reveal empty lists. Let’s filter out these objects and keep only the meaningful Record objects.

In Add Column tab, click Add Custom Column.

In the Add Custom Column window, enter the following formula and click OK.

if [values.value] is record then

    true

else

    false

Now we have a new column, Custom with TRUE for Records and FALSE for Lists. Let’s filter it to contain only TRUE values, and remove all the rows with List objects in values.value.

Click the filter button in column Custom, select TRUE and click OK. 

Delete the column Custom, and switch the order of the two columns, so values.end_time will be the first column (You can do it by dragging and dropping the first column to the end). 

You can see now that values.value has the expand button in its header. The button didn’t surface before because of the mix of lists and records on the same column. (Hidden massage for Microsoft: I wish that Power Query team would make it simpler, and design a better experience here).

Let’s click the expand button, uncheck Use original column name as prefix, and click OK.

We now have a new table with all the country codes.

Disclaimer: If your page is not popular on certain countries, the country list will contain only the countries of users who viewed your page. As a result, in the future, if you have views from new countries, the query will not include these views when you refresh it. To overcome this problem, we can create another query that includes all the country codes and combine this query in our current one. I have wrote about this solution here. Stay tuned for my next blog post, to have this solution on Excel as well.

Now it is time to unpivot all the country columns. On a personal note, this is my favorite transformation (and I wrote tons of blog posts about it, so if you are a new reader of my blog – don’t miss the unpivot series).

Select the first column, right click on its header, and select Unpivot Other Columns.

The table will now get flat with three columns. We have a new column for the countries and another for the number of impressions. Let’s rename all the columns to Date, Country and Impressions.

Note: If you don’t own a Facebook Page, the value column will include the number of people talking about the page.

We should also change the types of Date and Impressions. By selecting Date and Whole Number respectively. You can select the right data type in Home tab under Data Type menu. 

That’s is. We can now load the data to Excel. Click Close & Load in Home tab.

In the Load To window, select Only Create Connection, then check Add this data to the Data Model, and click Load.

After the data is loaded, go to Insert tab and click PivotTable.

In the Create PivotTable window, ensure that the highlighted option is checked and click OK.

That’s it. You are ready to build your PivotTable. I would build a PivotTable with Impressions field in the Values section, Country in the Columns and Date in Rows and see the results. You can also download the results here.

What can I do if I don’t own a Facebook Page?

You can build your own report, based on pages that you don’t own.

In this section I will show you how to convert the query we have prepared above, and read insights from the Facebook Excel page. Clearly we don’t own this page, so if it works, we can safely assume that we can use the query below to any other Facebook page.

Edit the query that we have created above, and change the Facebook string to:

https://graph.facebook.com/v2.2/microsoftexcel/insights/page_storytellers_by_country?since=01-01-2016

Close and Load the new query to the Data Model, and build a new PivotTable according to the following screenshot:

Or even better, check out the peaks of Shares by creating this PivotChart:

You can also download the workbook that we have prepared here.

Again, if you can explain the first peak, please share with us.

That’s is for today. On my next blog post, I will show you how to analyze Page Insights of multiple Facebook pages. We did it last month with Power BI Desktop and compared between Power BI, Tableau and Qlik. But now you can do it in Excel 🙂

Follow me on Twitter & Facebook and join my chants of big data & bigger insights.