This is it. the last article of a series I started almost two years ago. After having made a spreadsheet to monitor the stats of Youtube channels, Instagram accounts, Twitch channels, Pinterest accounts and Twitter accounts, I am finally cloturing this list with a spreadsheet to monitor the amount of likes of multiple Facebook pages in Google Sheets.
Love it or hate it, Facebook has been around for well over a decade and as much as people like to call it dead, I have yet to meet one ecommerce business that has not used any form of Facebook Ads.
Anyhow, after having received a couple of emails asking me to adapt my “Monitor Social Media with Google Sheets” for it to be able to track Facebook pages and here we are.
With this new tab, you will be able to track the following data points:
- Amount of Likes
- Amount of “Talking About This”
- Average Gain/Loss of Likes per Day
- Average Gain/Loss of Likes per Month
As usual, if you are just interested in the spreadsheet, you can scroll down to “The Spreadsheet” section where you will find a link to the spreadsheet. From there you will be able to make a copy of the Google Sheets template to use in your own Google Drive. Do not request Edit access for it as I can’t grant it to anyone. You simply have to “Make a Copy” of the file.
The IMPORTXML Formula
If it’s your first time on this kind of article on my website, you have probably never heard of the IMPORTXML Formula from Google Sheets. IMPORTXML is an exclusive feature that Google Sheets offers that allows a spreadsheet to retrieve content from static websites.
Basically, this formula is a lightweight web-scraper built-in your favourite spreadsheet tool. Let’s quickly go over how it works. Create a new Google Sheets spreadsheet and type “=IMPORTXML(” in a cell.
These are the two parameters the IMPORTXML formula requires.
The first parameter is the URL we are going to extract data from. In this case and as for every tutorial of this Social Media in Google Sheets series, we are going to use the social analytics platform SocialBlade as the source of our data.
In this case, our test subject is going to be the Facebook page of the company Adobe which stats are revealed at this address: https://socialblade.com/facebook/page/adobe
As you can see in the screenshot below, we will be able to retrieve the amount of page likes and the amount of people “talking about this”. So let’s start with that.
By right-clicking on the amount of likes depicted in this screenshot and clicking on “Inspect“, we can start having an idea of the kind of HTML elements we are going to need to target.
In this case, we can see that the value corresponding to the amount of likes this page has is stored under the HTML tag <p style=”color:#aaa; font-size: 10pt;”> .And that’s all we need to build the second part of our IMPORTXML formula.
As mentioned, the first parameter for the formula is the URL and the second parameter is the XPATH corresponding to where the data we are interested in extracting is. You will recognize the “p“, the “style” and the “color:#aaa; font-size: 10pt;’” elements in the second parameter.
Now, we need to use a mix of INDEX and REGEXREPLACE to filter out the results of this formula and only display one clean data point at a time. We are starting with the amount of likes which I did like this:
Here is the written formula:
=REGEXREPLACE(INDEX(IMPORTXML("https://socialblade.com/facebook/page/adobe","//p[@style='color:#aaa; font-size: 10pt;']"),1,2),"[A-Za-z]+","")
And the same can be done for the “talking about this” number with a slight alteration to the INDEX part of the formula shared above:
=REGEXREPLACE(INDEX(IMPORTXML("https://socialblade.com/facebook/page/adobe","//p[@style='color:#aaa; font-size: 10pt;']"),2,2),"[A-Za-z]+","")
Using the same concept and this same IMPORTXML formula, I am then able to display both the “Average Gain/Loss of Likes per Day and per Month with this formula. Feel free to decompose it and replicate it yourself:
=TRANSPOSE(IMPORTXML("https://socialblade.com/facebook/page/adobe","//div[@style='width: 240px; height: 40px; line-height: 40px; float: left;']")
Finally, I put all of these formulas into a clean spreadsheet, used some tricks to have auto populating cells and I eventually ended with a clean template that I can share with you all.
I haven’t tested the limits of this IMPORTXML formula but I’m able to track and sort through 20+ Facebook pages in one single spreadsheet but I’m sure it can handle more.
As promised, if you are simply interested in making a copy of my original work, you can head over to this link : https://docs.google.com/spreadsheets/d/1mVaCROebN-UXnLwzt5olBA0x6kIFSJuOnrptvyj0Wto/edit?usp=sharing and make a copy of the file by clicking on “File” and “Make a Copy” at the top-left of the Google Sheets interface.
This spreadsheet also contains a way to track Facebook, Youtube, Pinterest, Twitter and Twitch accounts so you might want to delete the tabs you do not need to free up some calculation memory.
And that is it for this article. I hope you found it helpful and that it will serve you right in your activity. If you liked this content and are working in Marketing, I highly suggest you join my newsletter that focuses on Marketing Technology and Automation. I share every week jobs postings, news and other interesting tidbits about the industry which I’m sure you will find interesting. You can find a form in the link right below this paragraph.
If you have any questions, feel free to drop me an email or to leave a comment in the section down below and I will get back to you as soon as I can.
Have a great time,