Join the top marketers who read our newsletter each week:

Monitoring Youtube Channels Subscribers and Data with Google Sheets

Table of Contents

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!

Join the top marketers who read our newsletter each week.

Yaniss Illoul

Share on twitter
Share on linkedin
Share on facebook
Share on reddit

You might also like these posts:

24 Responses

  1. I copied the LinusTechTips one and it still doesn’t work. It shows me this:

    #ERROR!
    Error
    Formula parse error.

    My formula is this one:
    =IMPORTXML(“https://socialblade.com/youtube/user/FaZeClan”,”//span[@id=’youtube-stats-header-subs’]”)

    1. Hey thanks for the comment !

      So this is extremely weird. Looking at your formula it should definitely work. But when I pasted it in one of my spreadsheets somehow it did not recognize that the two arguments were string…

      I wrote the formula manually afterwards and it worked though… Have you tried refreshing your page ? An other solution I found was removing the quotation marks and putting them again around your strings…

      This is definitely a very weird behavior. Anyway here’s the content of my cell that made it work : =IMPORTXML(“https://socialblade.com/youtube/user/FaZeClan”,”//span[@id=’youtube-stats-header-subs’]”)

      It’s definitely the same as yours so it’s very strange.

      Lastly, take a look at your locale settings It’s in File -> Spreadsheet Settings at the bottom. Some countries like Germany require to use “;” between arguments. Might be worth resetting it 🙂

      Please email me if that did not solve your problem, I could share my spreadsheet with you and you could make a copy of it!

    2. Hey I also had the same issue.
      But I found out the mistake I did.
      you have to put the SocialBlade’s Profile Link separately in one column, then you have to put the =IMPORTXML formula in another column and link the SocialBlade profile link followed by a , then the ”//span[@id=’youtube-stats-header-subs’]”)

      This way it will work.

  2. Hi Yannis, The formula jus switches to this =IMPORTXML(B2,”//span[@id=’youtube-stats-header-subs’]”)”) and not the one you have given

  3. Hi Yannis,

    Very helpful article! Is there a link to the follow up article going more in depth with how to track trends etc?

    1. Hey thanks for taking the time to comment! Unfortunately, I haven’t had the time to work on this specific evolution.. But please send me an email at yaniss@martechwithme.com and I will make sure to send you a quick message as soon as I get to this next stage!

      Thanks again for taking the time 🙂

  4. It has been working out great for me except the upload function. This is the formula I am using:
    =IMPORTXML(A65,”//span[@id=’youtube-stats-header-uploads’]”)

    Can someone tell me if theirs is working, if so can I see the formula you are using and if I am wrong, what I am doing wrong?

    Thanks,
    Leo

    1. Hey Leo ! Thanks for reaching out !

      Can you try this formula: =IMPORTXML(A2,”//span[@id=’youtube-stats-header-uploads’]”)

      Just make sure the first parameter (The Cell reference) faces a correct URL and for me it works on my spreadsheets.

      Please let me know if that solves it for you or not 🙂 If not just let me know and I will send you an email so I can take a look at it 🙂

  5. Hi, It work great with the exception of my own channel (was hiding the suscribers, now it’s on again)
    I’ve +25k and it’s bringing 5.3k. Do you know why it can be doing that?
    this is really helpful

  6. First off, this post is EPIC! Exactly what I was looking for. One quick question, I’m trying to setup a Google Sheet Dashboard for our YT channel using your formula: =IMPORTXML(“https://socialblade.com/youtube/user/crxnamja”,”//span[@id=’youtube-stats-header-subs’]”).

    However, I’m really looking to add a column for daily subs. Is there a way I can adjust the formula to pull in subscribers each day?

    Thanks!

    1. Hey Mitchell! Thanks so much for reaching out! I’m really glad you liked the post 😀

      So about the daily subs formula. I looked at how they coded their table and it’s really unconventional, to say the least, making it challenging to parse :(.

      But, eventually, I think I managed to get a formula that would retrieve the table of dates and daily subscribers count that you can find if you go to the “Detailed Statistics” tab of SocialBlade.

      Anyway, if you input this formula:

      =ARRAYFORMULA({IMPORTXML("https://socialblade.com/youtube/user/crxnamja","//div[@style='float: left; width: 95px;']"),INDEX(ARRAYFORMULA(IMPORTXML("https://socialblade.com/youtube/user/crxnamja","//div[@style='float: left; width: 205px;']")),0,1)})

      You should get something usable. Then you can add a VLOOKUP to only have today’s value like this:

      =VLOOKUP(TODAY(),ARRAYFORMULA({IMPORTXML("https://socialblade.com/youtube/user/crxnamja","//div[@style='float: left; width: 95px;']"),INDEX(ARRAYFORMULA(IMPORTXML("https://socialblade.com/youtube/user/crxnamja","//div[@style='float: left; width: 205px;']")),0,1)}),2,FALSE)

      And you will only get the subscriber’s daily value of the day 🙂

      Hope that helps!

  7. Hello!! Thanks for sharing your work!

    Are there any chance to get this stats directly from youtube instead socialblade?

    Thanks in advance!!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

HELLO!

Get more tutorials, guides and curated content !

In your inbox, once a week.

wait!

Get more tutorials, guides and curated content !

In your inbox, once a week.