Facebook page insights with Power BI

As promised here, today I will walk you through the creation of a powerful brand analysis dashboard with Power BI Desktop which connects to Facebook page insights data.

Before we start, you can see the result here.

Recently, the Power BI team have published a tutorial that analyzes pages on Facebook. While the official tutorial showed you the basics, it didn’t drill down into Facebook page insights, which hold additional crucial data for marketers. To gain more from your Power BI and better analyze your brand, we will tap into Facebook pages’ insight information, and create a dashboard that compares between different brands. Even those you don’t manage 🙂

To download the actual Power BI Desktop file click here.

Here are the main elements we will focus on this tutorial:

1. Load dynamic country codes (You will see why we need it later)
2. Import Facebook Insights from Power BI page
3. Transform the query into a function that can load any brand from Facebook
4. Combine insights from multiple brands. In our case: Power BI, Tableau & Qlik
5. Analyze fans by county and brand

So let’s get started 🙂

Dynamic Country Codes & the Countries Slicer

In this tutorial we will get country codes from Facebook. While Power BI can associate the country codes to the relevant countries on the map, for us, mere mortals, it is sometimes easier to see the country by its real name. So we will want to load to our dashboard a table of country codes and their equivalent names.

We will start with the this website on wikipedia. It has a table that can serve us well. Let’s copy the URL Address: https://en.wikipedia.org/wiki/ISO_3166-1

Now, open Power BI Desktop (Please use the latest version), click the Get Data drop down menu in Home tab, and then click Web.

The From Web window will appear. Paste the URL address https://en.wikipedia.org/wiki/ISO_3166-1 in the text box URL, and click OK.

Click Connect in Access Web Content window.

In the Navigator window click on the third table which is marked in red below. You will see a preview of that table on the right. It has countries and two-letter codes. This is the table we need.

Check the 3rd box and click Edit.

We will only need the first two columns. In Home tab click Choose Columns.

Select the first two columns in Choose Columns window, and click OK.

Rename the columns to Country and Code, and rename the query to Country Codes.

That’s is. Let’s save this query. Click Close & Apply in Home tab.

We can now create a slicer with the Country Codes table.

In the Visualizations pane, select the Slicer (Highlighted in yellow below), then click the Country field under the Fields pane.

We now have a Slicer control in the dashboard. The nice thing about this control, is that it uses country names instead of codes. But under the hoods, it will filter records by their country codes.

A sidetrack – Building a dynamic list of country codes

Later on in this tutorial we will need a dynamic list of all the country codes. Let’s show you how to create a query that returns such a list. I promise it will be useful.

In Home tab, click Edit Queries.

Right click on the query Country Codes, and click Reference. This action will create a new query that starts with a reference to this query. Referencing a query is quite useful to build a streamline of queries, each is depending on the output of the previous one.

Let’s rename the new query to CodeList. Renaming can be done by editing the query Name in the right pane under Properties.

Delete the column Country in CodeList (Simply press the Delete key). Remember – This deletion is done on the new query. It will not affect the original one.

Right click on the header of column Code, and click Drill Down.

That’s it. You can see that the single column table was transformed into a list. We will use it soon. For now, let’s save it by clicking Close & Apply in Home tab.

Insights of Power BI Page

We will now create the base query that imports insights from the Power BI Facebook page. In the next section we will generalize our work to work on any Facebook page.

We will start by searching for Power BI page on facebook. After you find it you will notice that its URL contains the Facebook Object ID: microsoftbi

This ID can be used with Facebook REST API to retrieve data from Power BI page. Copy it. We will use it in a minute.

Back in Power BI Desktop.

Click the upper section of the Get Data command in Home tab.

The Get Data window will appear. Click Other in the left section, click Facebook and then click Connect.

If this is your first time using the Facebook connector in Power BI Desktop, you will follow simple login steps. Once you are done you will see the screen below. Paste the text microsoftbi in the first text box (“ME”, Username or Object ID).

Select –Custom– in Connection drop down menu, and enter insights in box below –Custom–. Then click OK.

You will now see a preview of the Facebook Page Insights. There are many different metrics that are provided by Facebook (The preview in the Query Editor only shows few of those). In this tutorial we will focus on the metrics that have county information.

Click the filter icon of the column name, click Text Filters and then click Ends With…

In the Filter Rows window write _country in the highlighted box, and click OK.

Click Choose Columns in Home tab.

In the Choose Columns window select the highlighted columns as shown in the screenshot below, and then click OK.

Click the expand button in the header of column values.

Select all columns, uncheck the box Use original column name as prefix and click OK.

After you expand the columns as shown above, you will see two new columns: value and end_time. You can see that the column value contains a mix of Records and Lists. Drilling down on the list, you will notice that the Lists are empty, while the Records contain essential data. To be able to expand the Records through the UI, we will need to filter out all rows with List objects.

The following steps will help us to filter out List objects in column value. In Add Column tab click Add Custom Column.

Enter the following custom formula and then click OK:

if [value] is list then 0 else 1

The previous step will create a new column with zero values for rows with List objects in the column value. It will now be easier to filter out these rows.

Click the filter command in the header of column Custom. In the filter window uncheck the zero and click OK.

Now we can expand the column value. Click the expand icon in the column header, select all fields, then uncheck the box Use original column name as prefix, and click OK.

Oh, did you notice we have country codes as fields in the value records?

So we have new columns with country codes of fans who like or shared the Power BI page.

Let’s go a bit deeper now and see the auto-generated M code behind our expand operation above. The reason we go deeper now, will be explain in a minute. Click Advanced Editor in Home tab.

In the Advanced Editor window you can see that Power Query had generated the list of the country codes as part of the expand operation (Table.ExpandRecordColumn).

But what if we don’t have Power BI fans in certain countries? If we want to scale up our query to support other brands, the current M expression will not be adaptive enough to expand the records that contains new countries.

Oh, and what if we will have new countries in the near future? Wouldn’t you prefer to have a resilient dashboard that doesn’t require further work when a new country is declared?

The make our query resilient to changes in countries and to recognize new countries that were not added by Power Query to the original M expression, we will use the query that we have created above. Remember the query CodeList?

Switching between the auto-generated country list and the dynamic CodeList query is easy. Delete the section that starts with

{“IN”, “US”, …

Make sure you delete the entire line, and instead enter:

CodeList)

Once you click Done in the Advanced Editor window, you will get a data privacy warning. Click Continue in the yellow bar.

The reason for this warning is that Power Query engine recognizes two sources of data. The first from Wikipedia (where we took the country codes to build our dynamic list), and the second from Facebook.

I will not explain here the rationale of this feature (I just hope that one day my friends from Microsoft will be brave enough to improve this feature. I left them a good spec to consider just before I left Microsoft).

For now, let’s just follow the steps. Assign the privacy level Public for both sources, and click Save.

That is better. Back in the Query Editor, you can see that the dynamic list we have added above is working well. We have all the country codes as columns. This time the codes are dynamic, and we will have less trouble once we move to a generic query that accepts any Facebook page in the next section.

Let’s scroll right to the last column and delete column Custom.

Now we can change the type of the column end_time to Date/Time/Timezone. To do it, right click on the column header and select Change Type, then click Date/Time/Timezone.

Next three step are cool. We will unpivot the country columns to a data structure that can be used in the dashboard.

Select the last column end_date.

While the last column is still selected, scroll left to the first two columns and add them to the selection by clicking the CTRL key while clicking on their headers.

Now right click on any of the selected headers, and click Unpivot Other Columns.

I like it when it happens 🙂

All the columns with country codes have turned into Attribute and Value columns.

We will rename the column Attribute to Country Code and keep the column Value in its current name, as it represents different types of information. To rename a column simply double click on the column name and edit it.

Let’s also rename the other columns:

• name –> Insight Name
• period –> Period
• end_time –> Time

That’s it, our query is ready for its next big transformation, becoming a function query.

Note: If you are new to Power BI, you can stop right here, and use the query we have built to analyze single pages. To change the Facebook Page you wish to analyze, follow the next twp steps:

In the Query Settings pane which is located on the left side of Query Editor, click on the settings icon of the first item in APPLIED STEPS. Then change the Object ID from microsoftbi to your preferred page.

From a single Facebook Page to many

In this section we will extend our work to support the analysis of multiple facebook pages, and not just a single page. We will also transform out query into a function query which will fit any page you wish to analyze.

Let’s rename the query we have prepared earlier to GetInsightsByPage (You can rename the query in Query Settings –> Properties –> Name).

In Home tab click Advanced Editor.

You will notice (as seen in the highlighted text below) that the M expression includes the hard-coded term microsoftbi

We will change the M expression as follows:

Add the first line:

(facebookPage) =>

Replace the text:

microsoftbi

with the new text:

” & facebookPage & “

Click Done, and click Close & Apply in Home tab.

Now we have a generic function query that accepts any facebook page and returns its country-related insights. The next step if to create a list of brands we want to measure.

Let’s compare between three competing brands: Power BI, Tableua and Qlik.

In Home tab click Enter Data.

In The Create Table window enter the data that is shown in the following screenshot, then name is as Brands and click Load.

We now have a new table called Brands with the columns Brand and Facebook Page. Let’s combine it with the function query we have created to compare the different brands.

In Home tab click Edit Queries.

Right click on the query Brands and click Reference.

Rename the new query to Brand Results.

Click Add Custom Column in Add Column tab.

Enter the following formula in the Custom Column Formula box and click OK:

GetInsightsByPage([Facebook Page])

Here is a bug with a simple workaround that will be shown in a minute. When we try to combine between a function query and a second query that contains a reference, Power BI Desktop fails to understand the origin of the Data Sources, and produces this weird message:

The workaround is to turn off the Privacy Levels checks (i.e. Enable Fast Combine).

In File tab click Options and Settings and then click Options.

The Options window will appear. Select Privacy on the left pane, and check the second option as shown in the following a screenshot. Then click OK.

You will need to click Refresh Preview in Home tab

That’s it. We have a workaround for the error. We can now see a new column with three Table objects:

Click the expand button next to the column Custom.

Select all columns, uncheck the box at the bottom (Use original column name as prefix) and click OK.

Delete the column Facebook Page.

Now Brand Results contains the following data:

• Number of fans who shared the page by country (aka: Storytellers if you use Facebook lingo)
• Number of fans who liked the page by country

Let’s focus on the storytellers (those who share the pages), and create a dedicated new query.

Right click the query Brand Results and click Reference.

Rename the new query to Share Results.

Filter the column Insight Name, and select only values with page_storytellers_by_country

Rename the column Value to Shares, and change its type to Whole Number,

Click Close & Apply.

That’s it we have created the necessary queries to build our dashboard. The next section will walk you through the dashboard preparation.

Building the Dashboard

Let’s build the relationship between the Country Codes in the Share Results table and our slicer.

Click Relationships in Power BI Desktop.

Drag the field Code in Country Codes and drop it on Country Code in table Share Results.

Back in the Report view. click the Map icon under Visualizations.

Click on the fields: Brand, Country Code and Shares and ensure that each field is placed under the correct section in the following screenshot:

Since the results may contains more than one record (multiple records by time. Usually 3 records of the last period), we will also apply a Maximum operation on the values. Click the drill down menu in the Shares field under Values and select Maximum.

We can also add a new slicer for the period, as our data contains a mix of daily, weekly and monthly metrics.

Select the Slicer icon in the Visualizations then click on the field Period as marked below.

We now have a map with the Facebook storytellers of Power BI, Tableau and Qlik, and can start building a powerful dashboard which is controlled by country and period slicers.

I have added two simple & powerful visualizations in the final dashboard. You can watch the final result here.

Download the actual Power BI Desktop file here.

Follow @datachant on on twitter.

Looking forward to your feedback

@gilra