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

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

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

Click on the Send button.Screenshot_4

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

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.

Screenshot_12

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

Click on To Table in Transform tab.Screenshot_2

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

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

Screenshot_4

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

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

Click on the expand button of column likes.values.Screenshot_7

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

Screenshot_9

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

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.

7 comments

  1. Pingback: Privacy Bug in LinkedIn API – Demonstarted with #PowerQuery #Excel and #PowerBI – DataChant

  2. chen Reply

    Great post. I have a quick question though. I followed your instruction and everything is working just fine. However, I wasn’t able to schedule refresh. Is there any way to fix that ?

    Thank you in advance.

    • Gil Raviv Reply

      Thank you Chen. Schedule refresh is not supported at this stage. There are 3rd party client-based solutions like Power Update, or you can write VBA that will periodically perform the refresh of the Excel file.

  3. Anonymous Reply

    Fantastic solution to my problem! Thank you for posting! I am having difficulty in Power BI where I paste in the FnGetUpdates piece … I get the error: Token Literal expected. in the Advanced Editor. Any fixes or am I doing something wrong?

    Thank you!

  4. Gil Raviv Post authorReply

    Can you share your code? Sounds like an error, not a bug.

  5. Amalie Reply

    Thank you for this post. I have followed your instructions and it is working. However, it only track one month back. Where do you change the time frame?

    Thank you!

  6. Peter Reply

    Great post! However, I’m having some issues when trying to “edit the credentials”. Excel tells me that “A web API key can only be specified when a web API key name is provided”.

    Please help!

Leave a Reply