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:
- 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).
- 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.
Pingback: Privacy Bug in LinkedIn API – Demonstarted with #PowerQuery #Excel and #PowerBI – DataChant
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.
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.
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!
Can you share your code? Sounds like an error, not a bug.
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!
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!