Fashion Channel Case Study Spreadsheet Formulas

Google Sheets has a powerful and versatile set of IMPORT formulas that can import social media statistics.

This article looks at importing social media statistics from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.

The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.

Caveats: these formulas occasionally stop working when the underlying website changes, but I will try to keep this post updated with working versions for the major social media statistics.

Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy: .

Contents

  1. Facebook
  2. Twitter
  3. Instagram
  4. Google+
  5. Youtube
  6. Pinterest
  7. Alexa rank
  8. Quora
  9. Reddit
  10. Spotify
  11. Soundcloud
  12. GTmetrix
  13. Bitly
  14. Linkedin
  15. Sites that don’t work and why not
  16. Closing thoughts
  17. Resources

How to import social media statistics into Google Sheets with formulas


Import Facebook data

Start with the Facebook page URL in cell A1, e.g.

Here is the formula to extract page likes:

Alternative formula:

Here is an alternative formula to extract the page metadata, which includes the likes:

the formula to extract likes is:

and to extract the “talking about” number:

The following screenshot shows these formulas:

See the Facebook Import Sheet.

^ Back to Contents


Import Twitter data

Start with the Twitter handle URL in cell A1, e.g.

Here is the formula to extract follower count:

Pro-tip: If you find this formula gives an error, wrap it in an IFERROR function and call the IMPORTXML function a second time, but add a “/” to the end of the Twitter handle. This should then pull out the follower number for you (it requires that your Twitter handle in cell A1 does NOT have a “/” at the end):

The following screenshot shows these formulas (this is an older screenshot and all references to “Col2” should be changed to “Col3”):

Note 1: These Twitter formulas seem to be particularly volatile, working fine one minute, then not at all the next. I have two Sheets open where it’ll work in one, but not the other!

Note 2: For Twitter handles with extremely large followings, in the millions, this will return a result with millions shown as “M” (e.g. 5,650,000 = 5.65M):

(This is an older screenshot and all references to “Col2” should be changed to “Col3”.)

We can use a quick SUBSTITUTE formula in C1 to convert to the equivalent million number:

Of course, this introduces a degree of inaccuracy as it records the number to the nearest ten thousand.

See the Twitter Import Sheet.

^ Back to Contents


Import Instagram data

Start with the Instagram page URL in cell A1:

Then, this formula in cell B1 to extract the follower metadata:

To extract details from this is now relatively simple. Here’s the number of followers:

Note, for large numbers of followers, the number will be in the format 250.2k for hundreds of thousands or 32.7m for millions, in which case we need to use some IFs and SUBSTITUTEs to deal with this, so our formula becomes:

Here’s the number following:

Similar to the number of followers above, you may need to wrap with IFs and SUBSTITUTE formulas if the number is of the thousand or million format.

Here’s the number of posts:

The following screenshot shows these formulas:

If this causes an error, try these alternative methods:

Oo-er, this one’s a bit funky!

I wasn’t able to import the precise “followed_by” class so I’ve used some REGEX to extract the followers count tag from the data I’ve imported.

In a nutshell, the REGEXEXTRACT formula extracts this snippet: . The second REGEXEXTRACT formula then extracts the the digits and finally VALUE converts to a number.

One further datapoint we can grab from the instagram page is the link, with this formula:

See the Instagram Import Sheet.

^ Back to Contents


Import Google+ data

With the URL in cell A1:

this formula in cell B1, again using REGEX to extract the specific datapoint, will get the followers of the Google+ account:

The following screenshot shows this formula (actually shows a prior version of this formula):

Pro-tip: If you find this formula gives an error, wrap it in an IFERROR function and call the IMPORTXML function a second time, but add a “/” to the end of the Google+ handle. This should then pull out the follower number for you (it requires that your Google+ handle in cell A1 does NOT have a “/” at the end):

See the Google+ Import Sheet.

Alternative formula to try for Google+, submitted by one of the students of my online Google Sheets and Data Studio dashboard course:

^ Back to Contents


Import YouTube data

Start with the YouTube channel URL in cell A1:

To get the number of subscribers to a YouTube channel, use this formula in cell B1:

To get a channel’s total views, you need to use the about page as your URL in cell A1:

Then use this formula to extract channel views:

The following screenshot shows these formulas:

See the YouTube Import Sheet.

^ Back to Contents


Import Pinterest data

In cell A1, enter the following URL, again replacing benlcollins with the profile you’re interested in:

Then in the adjacent cell, B1, enter the following formula:

to get the following output (screenshot shows older version of the formula, latest one is above and in the template file):

Note, you can also get hold of the profile metadata with the import formulas, as follows:

See the Pinterest Import Sheet.

^ Back to Contents


Import Alexa ranking data

Here there are two metrics I’m interested in – a site’s Global rank and a site’s US rank.

Global Rank

To get the Global rank for your site, enter your URL into cell A1 (replace benlcollins.com):

and use the following helper formula in cell B1:

and then extract the rank in cell C1:

US Rank

Assuming you have the Alexa URL in cell A1 again, then the US rank is extracted with this helper formula:

and this formula to extract the actual rank value:

The following screenshot shows these formulas:

See the Alexa Ranking Import Sheet.

^ Back to Contents


Import Quora data

In this instance, I’ve imported the number followers Barack Obama has on Quora.

Quora is a little bit different because I need to use the URL and the profile name in my formula, so I’ve kept them in separate cells for that purpose. So in cell A1, add the generic Quora URL:

/

And then in cell B1, add the profile name:

Then the formula in C1 to get the number of followers is:

The following screenshot shows this formula:

See the Quora Import Sheet.

^ Back to Contents


Import Reddit data

Here, I’m using the funny subreddit as my example.

In A1:

To get the number of followers of this subreddit, use this formula in cell B1:

Bonus: To get the number of active viewers of this subreddit:

The following screenshot shows these formulas:

See the Reddit Import Sheet.

^ Back to Contents


Import Spotify Artist followers

Update mid-2017: Unfortunately, Spotify have changed their API access and these formulas no longer work.

Here’s a method for extracting the number of followers an artist has on the music streaming site Spotify.

First, find your favorite artist on Spotify and right-click their name. From the menu that pops up, choose “Copy Spotify URI”. Paste that into your Google Sheet in column B, and delete the “spotify:artist:” string to leave just the ID, as shown in the image further down this post.

So you should have an artist name in cell A1:

and the artist’s unique URI in cell B1:

Add this URL (the API endpoint) into cell C1:

and then combine the artist URI and API endpoint (columns C & B) with this formula, to put the full URL into column D:

Then put the following formula into column E to extract the followers:

The following screenshot shows these formulas (click to view larger version):

Note, for the technical amongst you, copy the full API url (column D) and paste it into a new tab of your web browser. There you’ll see the raw JSON data before we import into Google Sheets, and you can clearly see the followers count:

Read more about the Spotify API and find other end points here:
https://developer.spotify.com/web-api/endpoint-reference/

And the detailed information on the get artist information end point we’ve used in this example:
https://developer.spotify.com/web-api/get-several-artists/

See the Spotify Import Sheet.

^ Back to Contents


Import Soundcloud data

Start with the Soundcloud page URL in cell A1, e.g.

Here is the formula to extract page likes:

Alternative formula:

Here is an alternative formula to extract the page metadata, which includes the likes:

the formula to extract likes is:

and to extract the “talking about” number:

The following screenshot shows these formulas:

See the Soundcloud Import Sheet.

^ Back to Contents


Import GTmetrix data

GTmetrix is a website that analyzes website performance.

You need to grab the correct URL before you can start scraping the data. So navigate to the GTmetrix site and enter the URL and hit analyze. You’ll end up with a URL like this:

Those last 8 characters (“BcHv78bP”) appear to be unique each time you run an analysis, so you’ll have to do this step manually.

Then in column B, I use this formula to extract the Page Speed Score and YSlow Score, into cells B1 and B2:

and this formula in cell B3, to get the page details (Fully Loaded Time, Total Page Size and Requests) in cells B3, B4 and B5:

The following screenshot shows these formulas:

See the GTmetrix Import Sheet.

^ Back to Contents


Import Bitly click data

Bitly is a service for shortening urls. They provide metrics for how many clicks you’ve had on each bitly link, e,g.

Taking a standard Bitly link (e.g. http://bitly.com/2mmW1lr) and appending a “+” to it will take you to the dashboard page, with the metrics. Then we can use the import data function, a query function and a REGEX function to extract the click metrics.

User clicks are:

and global clicks are:

Clicks from the Bitly network are then simply the user clicks subtracted from the global clicks.

The following screenshot shows these formulas:

See the Bitly Import Sheet.

^ Back to Contents


Import Linkedin data

This formula is no longer working for extracting Linkedin followers and I have not found an alternative.

In cell A1:

This formula used to work to get the number of Linkedin followers, but no longer:

and the output:

There is no example sheet for Linkedin since the formula is no longer working.

^ Back to Contents


Sites that don’t work and why not

I’ve tried the following sites but the IMPORT formulas are unable to extract the social media statistics:

  • Linkedin (see above)
  • Similar Web
  • Twitch
  • Mobcrush
  • Crunchbase
  • Angel.co
  • Majestic SEO

These are all modern sites built using front-end, client-side Javascript frameworks, so the IMPORT formulas can’t extract any data because the page is built dynamically in browser as it’s loaded up. The IMPORT formulas work fine on sites built in the traditional fashion, with lots of well formed HTML tags, where the social media statistics are embedded into the site markup that is passed from the server.

Compare this screenshot of the source code for Mobcrush, built using Angular JS it looks like (click to enlarge):

versus what the source code looks for this page on my website (click to enlarge):

You can see the code for my site has lots of tags which the IMPORT formulas can parse, whereas the other site’s code does not.

If anyone knows of any clever way to get around this, do share!

Otherwise, you’re next option is to venture down the API route. Yes, this involves coding, but it’s not as hard as you think.

I’ll be posting some API focussed articles soon. In the meantime, check out my post on how to get started with APIs, or for a peak at what’s coming, take a look at my Apps Script + API repo on GitHub.

Loading error

Also, even when these formulas are working, they can be temperamental. If you work with them a lot, sooner or later you’ll find yourself hitting this loading issue all the time, where the formulas stop displaying any results:

^ Back to Contents


Closing thoughts

These formulas are unstable and will sometimes display an error message.

I’ve found that adding or removing the final “/” from the URL can sometimes get the formula working again (the issue is to do with caching).

I can make no guarantee that these will work for you or into the future. Whilst researching this article, I came across several older articles where many of the formulas no longer work. So things change!

To summarize: Caveat Emptor!

^ Back to Contents


Resources

^ Back to Contents


As always, leave any comments, corrections or request other social media statistics below.

Icons from Freepik.

This action might not be possible to undo. Are you sure you want to continue?

One thought on “Fashion Channel Case Study Spreadsheet Formulas

Leave a Reply

Your email address will not be published. Required fields are marked *