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-1Screenshot_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.Screenshot_2

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.

Screenshot_3

Click Connect in Access Web Content window.

Screenshot_4

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.

Screenshot_5

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

Screenshot_6

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

Screenshot_7

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

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

Screenshot_10

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.

Screenshot_12

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.

Screenshot_13

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.

Screenshot_14

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.Screenshot_15

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

Screenshot_16

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.

Screenshot_17

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

Screenshot_18

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.

Screenshot_19

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.

Screenshot_23

Back in Power BI Desktop.

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

Screenshot_21

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

Screenshot_22

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.

Screenshot_87

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.Screenshot_27

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

Screenshot_29

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

Screenshot_30

Click Choose Columns in Home tab.

Screenshot_31

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

Screenshot_32

Click the expand button in the header of column values.

Screenshot_33

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

Screenshot_34

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.

Screenshot_35

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

Enter the following custom formula and then click OK:

if [value] is list then 0 else 1

Screenshot_37

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.

Screenshot_38

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

Screenshot_39

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?

Screenshot_41

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

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.

Screenshot_43

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).

Screenshot_44

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)

Screenshot_46

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

Screenshot_47

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.

Screenshot_48

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.

Screenshot_50

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.

Screenshot_51

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.

Screenshot_52

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.

Screenshot_53

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

I like it when it happens 🙂

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

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

Screenshot_56

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.

Screenshot_88

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.

Screenshot_58

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

Screenshot_59

We will change the M expression as follows:

Add the first line:

(facebookPage) =>

Replace the text:

microsoftbi

with the new text:

” & facebookPage & “

Screenshot_60

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

Screenshot_62

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.

Screenshot_61

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

Screenshot_63

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.

Screenshot_64

Right click on the query Brands and click Reference.

Screenshot_65

Rename the new query to Brand Results.Screenshot_66

Click Add Custom Column in Add Column tab.

Screenshot_67

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

GetInsightsByPage([Facebook Page])

Screenshot_68

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:

Screenshot_69

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.

Screenshot_70

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.

Screenshot_71

You will need to click Refresh Preview in Home tab

Screenshot_73

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

Screenshot_72

Click the expand button next to the column Custom.

Screenshot_74

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

Screenshot_75

Delete the column Facebook Page.Screenshot_76

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.

Screenshot_77

Rename the new query to Share Results.

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

Screenshot_78

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

Click Close & Apply.

Screenshot_79

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.

Screenshot_80

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

Screenshot_81

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

Screenshot_82

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

Screenshot_83

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.

Screenshot_90

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.

Screenshot_84

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.

Screenshot_89.png

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

10 comments

  1. Pingback: An update for the brave analysts of Facebook Insights and #PowerQuery | Data Chant

  2. Pingback: Analyze Facebook Insights with Excel – Data Chant

  3. Pingback: Analyze US Election Candidates in Excel and #PowerQuery – Data Chant

  4. Adithya Reply

    Hi suppose in share Results data set period field (day,week,days_28) i want replace like (day,month,last month) how can we do ?

Leave a Reply