Scraping view count from Facebook and Youtube in real time

Note: The content of this article is now outdated, but the logic remain the same. I highly recommend Ben Collin’s article on the subject to help you achieve your data scraping goals.

Every production company wants to know if the content it produces is worth what it costs to make it. For that, there are a multitude of indicators, but the main ratio we’re looking at is of course views over budget.

Google sheets offer a simple solution to collect data from Facebook and Youtube, and I intend to share it with you!

Step 1: ♪, ♫ Do you want to build a spreadsheet? ♪, ♫

Of course, we have to start by entering our list of videos into a spreadsheet. Then, for each project, we need to write down the URLs where the videos are hosted.

For Youtube, you can copy the url from any location, wether it’s a thumbnail, or the page itself.

For Facebook, it’s a little more tricky. The only stable source (as far as I can tell from my experiments) comes from the “video” page, accessible from the left side menu. Once you are on this page, right click a thumbnail and select “copy link address”

The link is now in your clipboard, and all you have left to do is pasting it into the destination cell.

For now, our spreadsheet is as simple as this:

Step 2: Using ImportXML

One of Google Sheets’ function is called “ImportXML”. In short, it lets you import data from any website, as long as you point it in the right direction. The syntax for the formula is:

=ImportXML ( ” url ” , ” Xpath selector ” )

The “Xpath selector” is a piece of code that grabs a specific element on the page. In our case, it is going to be the number of views for these videos.

You can find this code by inspecting the source code of a page. Google Chrome is quite helpful for that, and you can find many tutorials about it online. For the sake of this articles, I will give you the solution.

Youtube Xpath:      "//div[@class='watch-view-count']"

Facebook Xpath:    "//span[@class='fcg']"

So let’s build these formulas in cells C2 and D2:

C2:     =ImportXML(B2,"//div[@class='watch-view-count']")

F2:     =importXML(D2,"//span[@class='fcg']")

Your spreadsheet should look something like this:

 Step 3: Cleaning up the data

As you can see, the data coming from Youtube has the “views” affix, and the data coming from Facebook has that as well, and a “K” for “thousands”. If the video has a million views, it will show a “M” and will be abbreviated (ie: “1.2M”)

So before we can add these views together, we need to remove all these letters that prevent us from doing math operations.

For Youtube, I am using the “LEFT” and “LEN” functions to remove the last 6 characters (there’s a space before the word “views”). I am actually going to change the formula we have in C2 to include that in there.

For Facebook, it’s more complex. Here are all the elements that could be displayed and need to be removed:

• “views”
• the “K” when views are in the thousands
• the “M” and the “.” when view are in millions

Removing the “views” will done using the same technique as for Youtube.

Then, I am using a “VLOOKUP” to switch the “K” and “M” to “,000” and “,000,000” respectively. I created a table and named it “ViewCounter”. You can see that this named range (see more about names ranged here) on the same sheet of my example, but I would of course suggest putting that on a separate tab and hiding it.

Finally, I am using the “SUBSTITUTE” function to remove the “.”

Here are the formulas I used:

C2:       =LEFT(IMPORTXML(B2,"//div[@class='watch-view-count']"),LEN(IMPORTXML(B2,"//div[@class='watch-view-count']"))-5)

E2:       =LEFT(importXML(D2,"//span[@class='fcg']"),LEN(importXML(D2,"//span[@class='fcg']"))-6)

F2:       =LEFT(SUBSTITUTE(LEFT(E2,LEN(E2)-1),".","")&VLOOKUP(RIGHT(E2,1),viewcounter,2,FALSE),7)

Your spreadsheet should now look something like this:

Step 4: Finishing touches

All we have left to do is adding up our Facebook and Youtube views, and calculating our view per dollar cost. This is the easy part!

I am adding 3 columns to total my views, enter my budget amount, and finally, divide the total views by the budget amount. That’s it, all done!

Notes:

Facebook is weird. You’ll notice pretty often that the data isn’t coming in anymore, even though nothing has changed. Just copy the URLs, delete the content of the cells containing them, and paste them back in. And magic, everything comes back to normal.

It’s probably a good thing to wrap all the formulas in an “IFERROR” functon, to avoid showing “#N/A” when there is no link. You can also wrap the formula in cell G2 in a conditional statement to only add values if they are larger than 0. Otherwise you’ll have a lot of zeros in your document.

Thumbnail designed by slidesgo / Freepik