After having written an article for Youtube, Instagram and Twitch, it is now time to start investigating how to collect a few data points of many Twitter accounts in one Google Sheets spreadsheet.
I’ve been pretty inactive on Twitter for a certain amount of years but it’s hard to ignore how powerful a tool it is for many businesses and personal brands. As such, I decided to create a spreadsheet template where you could track many stats about tens of twitter accounts at once, including:
- Amount of Followers
- Amount of Accounts Followed
- Amount of Media and Tweets Liked
- Amount of Tweets
- Age of the Twitter Account
- Average of New Followers Daily and Monthly
If you are just interested in having access to the spreadsheet template, you can scroll down to the link of the template by clicking on “The Spreadsheet” in the table of contents on the left-side of your screen. But if you’re interested in learning how to build your own, feel free to tag along as I explain how I created it.
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 account of Rand Fishkin which stats are revealed at this address: https://socialblade.com/twitter/user/randfish
By disabling JavaScript, we can have a better idea of the kind of data we will be able to extract using this IMPORTXML formula.
As you can see in the screenshot below, we will be able to retrieve the amount of followers, the amount of following, the amount of likes, of tweets and also the date of when this account was created fairly easily. So let’s start with that.
By right-clicking on the amount of followers 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 followers Rand Fishkin has is stored under the HTML tag <span style=”font-weight: bold;”>XXX</span>. And that’s all we need to build the second part of our IMPORTXML formula.
As mentioned, the first parameter of 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 “span“, the “style” and the “font-weight: bold;” elements in the second parameter.
Fortunately, it turns out that all of the values mentioned in the first screenshot I shared are all stored under the same HTML tag and are therefore all extracted at once. You can then use the TRANSPOSE formula around the IMPORTXML formula in order to display the results in a row instead of a column like so:
Now, we can jump on the last two data points we were considering displaying in our spreadsheet at the beginning of this article: Average of New Followers Daily and Monthly.
If we slightly scroll down at the bottom of the SocialBlade profile of Rand Fishkin, we can see these two data points in the flesh:
The same way we got the first few data points, we can right-click on one of these data points and collect its elements like so:
Once again, we have the results displayed in a column instead of a row and we also get both the Daily and Monthly averages in one formula, therefore making our job easier :). A simple TRANSPOSE formula and we can finally drag all of our formulas down like I did in my spreadsheet:
I haven’t tested the limits of this IMPORTXML formula but I’m able to track and sort at the moment 20+ Twitter accounts in one single spreadsheet but I’m sure it can handle more.
The Spreadsheet
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 and Twitch accounts so you might want to delete the tabs you do not need to free up some calculation memory.
Conclusion
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. 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,
