How to Monitor Twitch Channels’ Stats in Google Sheets

After having done one article about how to monitor Youtube Channels and Instagram accounts, I decided to keep the series going with an article about how to track Twitch’s channels stats within Google Sheets.

In this article, I am going to demonstrate how you can easily display various statistics about the Twitch channels you are interested in, all in one easily sortable Google Sheets’ spreadsheet.

My goal with this spreadsheet is to be able to compare at a glance the amount of followers each streamer has, the amount of channel view they got over their lifetime, how many new followers they get daily and monthly on average and which is the last game they streamed. This can be an extremely helpful tool if you’re preparing an Influencer Marketing campaign and wants to compare multiple streamers and choose the one that are getting the most engagement lately.

At the end of this tutorial, you will have a spreadsheet that will look like this:

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 Twitch 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.

Introduction

The first thing we want to understand is our data source. For the purpose of this tutorial, all of the data is going to be extracted from the Social Media Analytics platform SocialBlade.com .

If you are not aware, Google Sheets has a formula called =IMPORTXML that allows the parsing of websites’ contents in order to analyse or display it into a spreadsheet. This is an extremely convenient formula and the basis of this tutorial.

If you would like to learn how to use this formula, I have a full article about how to extract the content of a website in Google Sheets here.

The First Two Columns

The first two columns we want to build are the “Username” column and the “SocialBlade URL” column. The basis to this is that we are going to need Google Sheets to extract the data from individual pages on SocialBlade each corresponding to one streamer/channel.

Each row is going to correspond to one streamer and the data in the other columns is going to be extracted from the SocialBlade’s URL that refers to the Streamer/Channel data.

So for instance, if we want to extract data from the ESL CSGO twitch channel from this address, we are going to need the username in the column A and the URL of the Socialblade’s profile in the column B like so:

Now, an easy way to automatically generate the B column would be to use a formula that just completes the SocialBlade’s URL from the username input in A. Basically something like this:

Because we know that every Twitch profile on SocialBlade starts with ” https://socialblade.com/twitch/user/ ” we simply have to input the username in the column A and the formula in column B will add the value of the A column to the “prefix” URL.

Now that we are able to “build URLs” programmatically, we can start extracting data from the URLs in column B.

How to Extract Number of Twitch Followers into Google Sheets

If we navigate to the page of the ESL CSGO Twitch Channel on SocialBlade and disable Javascript, we can get a complete understanding of which parts of the website we can extract and which parts we can not.

As shown in these two screenshots, we can see the amount of followers, channel views, latest game streamed and daily/monthly averages in the same page, which is perfect for us.

So let’s start by inspecting the code where the amount of followers and channel views is located. Simply right-click on the number of followers at the top of the page and click on “Inspect“.

From then on, we are able to build our IMPORTXML formula like this:

=IMPORTXML(B3,"//p[@style='color:#aaa; font-size: 10pt;']")

As you can see from the formula description and the code example above, the first argument required for this formula to work is the URL from where the data will be extracted. In our case, we built the URLs in the column B so we therefore reference a non empty cell in the column B.

After that, we need to specify what is the Xpath of the data we are looking to collect. If it all sounds complicated again, I invite you to explore the article I mentioned at the beginning of this tutorial that refers to how to use the ImportXML formula in Google Sheets.

In our case, by inspecting the followers and channel views’ HTML fields, we noticed that this data is encompassed within a <p> tag that has a ‘style‘ value of ‘color:#aaa; font-size: 10pt;‘, which is what we put as the second argument of the formula.

With this first formula, this is what Google Sheets gives us back:

As you can see, not only do we get the amount of followers, but we also get the amount of channel views as both of these share the same p tag and style value.

In order to only select the followers, we need to put an INDEX formula around the IMPORTXML like so:

=INDEX(IMPORTXML(B3,"//p[@style='color:#aaa; font-size: 10pt;']"),1,1)

The INDEX formula asks the cell in Google Sheets to only retrieve the first row and column that has been generated by the IMPORTXML formula.

Finally, we need to get rid of all of the text surrounding the number of followers so we can easily sort the column once we have multiple channels to compare. For this purpose, we will use the formula REGEXREPLACE and SUBISTITUTE like so:

=SUBSTITUTE(REGEXREPLACE(INDEX(IMPORTXML(B3,"//p[@style='color:#aaa; font-size: 10pt;']"),1,1),"[A-Za-z]+","")," ","")

It looks a bit long but it does the job :). And this is how we are able to extract the amount of followers of a Twitch Channel SocialBlade’s profile in Google Sheets.

How to Extract Number of Twitch Channel Views into Google Sheets

The second column “Channel views” is going to be really easy to extract now that we did all of the heavy lifting in the section above.

In order to extract the Channel Views value from the SocialBlade’s URL to the Google spreadsheet we are working on, we just need to change the INDEX value of the formula we use beforehand to 2,1 instead of 1,1 like so:

=SUBSTITUTE(REGEXREPLACE(INDEX(IMPORTXML(B3,"//p[@style='color:#aaa; font-size: 10pt;']"),2,1),"[A-Za-z]+","")," ","")

And just like that, we easily took care of the Channel Views column. We can now jump onto the Daily and Monthly Averages.

How to Know Number of Twitch Channel Daily and Monthly New Followers into Google Sheets

We are getting near the end of this tutorial now. The next thing we want to extract is the amount of new followers the streamer/channel is getting on average daily and monthly. Thankfully for us, SocialBlade is presenting us with this data straight into their URL:

Now we can proceed the same way we extracted the amount of followers using the IMPORTXML and apply what we learned there to extract these figures.

We start by “Inspect“ing the HTML content:

We can then see that all of the content we need is encompassed within <div> tags that share the same ‘style’ value “width: 240px; height: 40px; line-height: 40px; float: left;“. We can then build our IMPORTXML formula like this:

=IMPORTXML(B3,"//div[@style='width: 240px; height: 40px; line-height: 40px; float: left;']")

As you can see, the output of this formula gives us both the Daily average and the Monthly average all in one go. In order to display the data in two columns instead of two rows, we can now use the formula TRANSPOSE over the IMPORTXML formula so that the data automatically switches from getting displayed in columns instead of rows like so:

=TRANSPOSE(IMPORTXML(B3,"//div[@style='width: 240px; height: 40px; line-height: 40px; float: left;']"))

And just like that, we are done extracting those two values and displaying them in a friendly manner within our spreadsheet.

How to Know Which is the Last Game a Twitch Channel Streamed in Google Sheets

The last data point we are interested in getting to round up this tutorial is “Last Game Streamed“. It might not be extremely important to some marketers that wants to compare multiple audiences but I figured I could scrape this value and so I did. Same process as usual, right-click “Inspect” on the part of the page with the Latest Game value and building the IMPORTXML formula just as before.

By default, my IMPORTXML formula was capturing the Latest Game but also the Latest Twitch Channel Status that the streamer/channel used when they were live. We can simply apply the INDEX formula around it to focus our output on the Latest Game value like so:

=INDEX(IMPORTXML(B3,"//p[@style='font-size: 1.2em; color:#41a200; font-weight: 600;']"),1,1)

Conclusion

And this is just. Now you can simply drag all of the formulas created down and start filling the Username column with the streamers and channels you are interested in comparing with each other and the rest of the data will automatically be extracted from SocialBlade’s website and into your spreadsheet for easy filtering :).

If you have enjoyed this tutorial and wish to learn more, I highly encourage you to check out the rest of my Google Sheets related tutorials and maybe join my newsletter to be made aware of my future projects.

Thank you taking the time to read through this, and I hope this helped :).