Monitoring Youtube Channels Subscribers and Data with Google Sheets

While browsing through Quora, I realized that there was a demand for some kind of Google Sheets that could track the number of subscribers of a variety of Youtube Channels and other interesting information related to them.

I figured I’d give this a shot and teach you how you can create your own “Youtube Channel Monitoring Google Sheets Spreadsheet”. This could be helpful for all of you Social Media Marketers or agencies that want to keep an eye on your clients or competitors’ subscribers all through your own spreadsheets.

The data we are going to extract that is going to power our spreadsheet will come from the website Social Blade. Social Blade tracks user statistics for YouTube, Twitch, and Twitter. For our case, we are just going to use their extremely advanced Youtube Tracker although we are only going to monitor some basic metric such as the number of subscribers, videos uploaded, the total number of views, where the channel is from and what category type is the channel about. All of this data, in a single spreadsheet.

If you’re interested in doing this but for Instagram, I also created a tutorial for this specific platform at this address: https://martechwithme.com/monitoring-instagram-accounts-followers-and-engagement-rate-with-google-sheets/

The Spreadsheet

If you are not interested in learning how to create this spreadsheet on your own, you can also simply make a copy of this spreadsheet by going to this Youtube Tracker Spreadsheet, click on “File” and then “Make a Copy” so you can start using it straight away. The only thing you need to do is add the username of the Twitch channel you are interested in in one empty cell in the A column and the other columns will populate themselves automatically.

Now, if you want to learn how to do the same spreadsheet yourself, I separated this article into multiple sections that explain how to extract each data point from our website source into the spreadsheet. So let’s get into it.

Using =IMPORTXML To Extract Web-Page Content

People tend to underestimate how powerful Google Sheets can be. One of the features of Google Sheets that is often missed is its ability to web-scrape. While not being on par to Python’s capabilities, Google Sheets is still a very powerful tool in this regard and I am going to show you how to use it.

So the way this function works is by taking two parameters: The complete URL of the page to extract the data from and the Xpath Query which is basically an indicator of where the content you’re after is located. It might look scary but I promise you, it is not.

Once you’re done setting the columns, we want to open a dummy web page and inspect its code to determine what we need.

In our case, we are going to head over to Linus Tech Tips’ Social Blade’s profile. Click here. This is going to be the URL that is going to be referenced in the first part of the =IMPORTXML function.

And here are the information we are interested in:

Now, we want to right-click on the number of subscribers this channel has and click on the field “Inspect“. This will open a window in our window with the code that generated the present webpage.

The part we are interested in is the HTML Tag that contains the exact field we are interested in, which is this one:

To use IMPORTXML, we need three elements from this information:

• The HTML Tag. So whether it’s an <a>, a <span> or something else…
• Whether the identifier is an “id” or a “class”
• The name of the “id” or the “class”. If you have the choice between an “id” or a “class”, always refer to the “id” as it’s more unique and less prone to mistakes.

In our case, the HTML Tag is a <span>, the identifier is an “id” and the name of the “id” is “youtube-stats-header-subs”. WIth all of these parameters saved up, it is time to build our Xpath Query.

So this is how your XPath Query should look like:

//span[@id='youtube-stats-header-subs']

In this example, you can see where we put the HTML Tag, the identifier and the name of the identifier. This is basically it.

In conjunction with our URL, this is how it should look like in Google Sheets now:

We could already wrap up the tutorial right here but I really want to show you how I decided to organize my own Youtube Channels Monitor Spreadsheet so here we go.

Organizing the Information

The first thing we want to do is set up the spreadsheet’s columns. Let’s just create what we need:

• Youtube Username is going to be our input to generate the data. This is where we are going to write “PewDiePie” or “Tseries”.
• Country, Channel Type, # Subscribers, # Uploads and # Views are all fields that are going to get generated by our formulas.
• Socialblade’s Profile is the URL where all of the stats are going to get extracted from. This is also going to be automatically generated based on the Channel’s name we are going to input in column A.

Creating the Formulas

To create the Socialblade’s profile cell, we need to concatenate a base URL from the website and a Youtube username. To find the Youtube username of a specific channel, just head to someone’s channel:

Click on their “About” section and find the username in the URL:

Sometimes, the Youtube Username is completely different to the Youtube Channel’s Name so be aware of these inconsistencies.

Now that we have our username, we need to concatenate the base URL of a Youtube Profile and the username like so:

Giving us this result:

The next step is to start creating our IMPORTXML Functions.

We already created the formula to get the number of subscribers of the channel in the first part of this tutorial. All is left here is to make it a bit more dynamic. To do so, we just need to replace the URL string by its cell reference that we just created. This way, we can just drag the formula down and it will create multiple URLs instantly.

This is how I proceeded:

=IMPORTXML(B13,"//span[@id='youtube-stats-header-subs']")

And this is the code I used for the rest of the columns:

For the number of uploads:

=IMPORTXML(B13,"//span[@id='youtube-stats-header-uploads']")

For the number of views:

=IMPORTXML(B13,"//span[@id='youtube-stats-header-views']")

For the country of origin:

=IMPORTXML(B13,"//a[@id='youtube-user-page-channeltype']")

Funny thing to notice here is that I did not use “span” but “a” instead. The reason for that is that by inspecting the webpage, I found this:

As you can see, this is not a “span” HTML tag but an “a” tag, hence the edit in the =IMPORTXML formula.

Same goes for the Channel Type:

=IMPORTXML(B13,"//a[@id='youtube-user-page-channeltype']")

And that’s it. After that, you just have to drag down the formula and you can accomplish something like this:

Conclusion

This is just the first version of this spreadsheet. Although this spreadsheet will update itself every time you will close it and open it again (Provided the stats on Social Blade have also been updated), this does not make it easy to track trends and follow the evolution of the channels you’re monitoring.

In the next article about this spreadsheet, I will try to go in this direction. A bit of scripting might be necessary to automate this process but this should not be anything that is going to be scary 🙂

Also, as specified earlier, you can find the same kind of article but for Instagram at this address : https://martechwithme.com/monitoring-instagram-accounts-followers-and-engagement-rate-with-google-sheets/ . It’s a bit more complicated than the Youtube one but I explained everything in details so everyone should be able to follow 🙂

If you liked what you read, consider subscribing to the newsletter and as always, I’m available in the comments section down below if you have any questions.

Thank you for your time!