Analyze Linkedin Company Page with Excel and Power BI

Update: LinkedIn restricted the use of the company search APIs. You need to be a LinkedIn partner to be able to follow this article.

Your company’s page on Linkedin is a treasure trove of insightful data. In today’s post I will show you how to import this data to Excel or Power BI Desktop.

In my posts here and here, we focused on the companies-search Linkedin API call, which allowed us to extract companies by specific specialties/domains and perform analysis of industry, geolocation and correlation between different specialties.

Today we will focus on your company page on Linkedin, and learn how to extract your company’s status updates, their likes and comments, and the persons who were engaged with your page.

Few disclaimers before we start:

1. There is no official Linkedin connector in Excel / Power BI, but the idea is under review (here). As such, we are using the Web connector and will use an external tool to perform the authentication with Linkedin (more details below).
2. Linkedin restricted many data points in their APIs and opened them to selected partners. If you are not such a partner, you can still obtain a great deal of information from your company page, but you must be an administrator of the company page, or ask the relevant administrator to help you (more details below).

Let’s start by obtaining the access token from Linkedin REST API Console (The access token is a key which is required for all the API calls. The key encapsulates your identity and your permissions on Linkedin. It is obtained through AOauth 2.0 authentication method).

Click here to open Linkedin API Console.

Select OAuth 2 in the Authentication drop down menu, and log in to Linkedin.

Note: You will need to be the company’s page administrator. If you are not, ask the relevant administrator to follow these steps from his computer.

Click on the highlighted link: List all companies that the member is an administrator…

Click on the Send button.

Copy the text which is highlighted in yellow below. This is the access token that will be needed to perform the API calls on behalf of the company’s administrator. If you are not the administrator, ask him to keep this text and use it later on the computer with the Excel / Power BI Desktop that you will use later to pull the data.

Now let’s move to Excel or Power BI Desktop, and create a blank query with the M statement below:

(If you are not sure how to do it, follow the help page here).

(companyID) =>
let
 url = "https://api.linkedin.com/v1/companies/" & companyID & "/updates", 
 Source = Json.Document(Web.Contents(url, [ApiKeyName="oauth2_access_token",
     Query=[format="json"]])),
 values = Source[values],
 #"Converted to Table" = Table.FromList(values, Splitter.SplitByNothing(), null,
     null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",
     {"isCommentable", "isLikable", "isLiked", "numLikes", "timestamp",
      "updateComments", "updateContent", "updateKey", "updateType", "likes"},
     {"isCommentable", "isLikable", "isLiked", "numLikes", "timestamp",
      "updateComments", "updateContent", "updateKey", "updateType", "likes"}),
 #"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1",{"numLikes",
     "timestamp", "updateComments", "updateContent", "updateKey", "likes"}),
 #"Expanded updateComments" = Table.ExpandRecordColumn(#"Removed Other Columns",
     "updateComments", {"_total", "values"},
         {"comments._total", "comments.values"}),
 #"Expanded updateContent" = Table.ExpandRecordColumn(#"Expanded updateComments",
     "updateContent", {"companyStatusUpdate"}, {"companyStatusUpdate"}),
 #"Expanded companyStatusUpdate" =
     Table.ExpandRecordColumn(#"Expanded updateContent", "companyStatusUpdate",
         {"share"}, {"share"}),
 #"Expanded share" = Table.ExpandRecordColumn(#"Expanded companyStatusUpdate",
     "share", {"comment", "content"}, {"comment", "content"}),
 #"Renamed Columns1" = Table.RenameColumns(#"Expanded share",
     {{"comment", "status"}}),
 #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "datetime",
     each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [timestamp]/1000)),
 #"Removed Columns" = Table.RemoveColumns(#"Added Custom",
     {"timestamp", "comments._total"}),
 #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",
     {"updateKey", "datetime", "numLikes", "likes", "comments.values", "status",
         "content"}),
 #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",
     {{"comments.values", "comments"}})
in
 #"Renamed Columns"

Save this query function with the name FnGetUpdates.

Note: We will call this function in a minute, so make sure you name it correctly.

The function above receives a Linkedin company ID as an input parameter and extracts the company’s updates with all their underlying data (e.g. likes, comments, etc.).

Now let’s create a new blank query with the following code:

let
 url = "https://api.linkedin.com//v1/companies", 
 Source = Json.Document(Web.Contents(url, [ApiKeyName="oauth2_access_token",
     Query=[format="json",#"is-company-admin"="true"]]))
in
 Source

Click the Edit Credentials, and in the Access Web Content dialog select Web API (see screenshot below).

Now it is the time to copy the access token we got from Linkedin API Console and paste it in the Key box, then click Connect.

Note: If you are not the company page administrator, you can ask him to paste the access token in the Key box of this dialog. The key will be kept secure and encrypted on this computer. It will not be readable, and cannot be passed to any other computer or workbook.

You will notice the following record appearing in Query Editor. Click on List.

Click on To Table in Transform tab.

Click on the little expand button on the top right corner of Column1, check the fields id and name, then uncheck the box Use original column name as prefix, and click OK.

Click Add Custom Column in Add Column tab.

Type the following formula in the Custom column formula box and then click OK.

= FnGetUpdates(Text.From([id]))

Now in the custom column you will see a Table object. Each company page you manage will appear with its associated table.

Click on the expand button in column Custom, uncheck the box Use original column name as prefix, and click OK.

That’s it, the treasure trove is ready for looting  🙂

You can now perform different data extractions of users’ likes, comments or even extract specific persons and perform further extraction of their job’s headline and geo-location.

To extract the persons who liked your company’s updates, click on the expand button on column likes, check values and click OK.

Click on the expand button of column likes.values.

Click on the expand button of column likes.values.person and click OK in the expand dialog.

You can now see the persons’ first and last names and their Linkedin ID which will allow you to perform further API calls per person to extract more insights like their job’s headline and location.

Similarly you can expand the column comments to extract the comments;  their creation time; the persons who commented and their Linkedin IDs.

If you are interested in an Excel or Power BI dashboard for your company’s Linkedin page, please contact me at gilra@datachant.com or through the form below.

Stay tuned for the next post of Linkedin Insights in Excel and Power BI.