Find Your Most Popular Web Pages on Twitter with Google Docs
If you were to measure the popularity of your website content on Twitter, the most effective way would be that you count the number of tweets (and re-tweets) that are linking to your web pages.
For instance, I can go to backtweets.com and it will instantly show me a list of twitter messages (and their total count) that mention any of my blog articles.
Which articles from your site are getting popular on Twitter?
The web interface of backtweets is pretty awesome for tracking popularity of web pages on Twitter one-by-one but imagine if you could use the same service to measure popularity of dozens of web pages or even your entire site in one go?
Well, here’s a Google Docs spreadsheet PS:that does exactly the same thing. It consists of two sheets - one for tracking RSS feeds and other for tracking multiple URLs.
PS:If you have trouble viewing the sheet, please open this read-only HTML version of the Google sheet. And here’s a video demo:
The text in the video will be more readable if you watch it in full-screen mode.
Here’s how you get started - just type the URL of any RSS feed in cell B1 and Google Docs will automatically compute the tweet count of all articles that are syndicated via that feed.
If you like to measure the same data but for your entire site, open the sheet titled “Web Pages” and copy-paste the list of all your URLs in column A. The tweet count will be reflected in the adjacent column and you can then sort by this column to find web pages that are most popular on Twitter.
And this computation happens in real-time so the Twitter numbers on your Google Docs will change if more people tweet that URL or if you some new content gets added to your RSS feed.
The Technical Details
You don’t need to read this for using the spreadsheet but if you are really curious to know how Google Docs could get the Twitter numbers for your blog feed, here’s what happens behind the scenes.
Cell B1 is the place where you type the web URL of an RSS feed.
Column A grabs the title of the 15 latest stories from the RSS feed using this formula:
\=importfeed(B1, “Items Title” , , 15)
Column B displays the URL of these 15 stories using this formula:
\=importfeed(B1, “Items URL” , , 15)
Now that we have the URL of your pages, we can use the BackTweets API to calculate the number of incoming links on Twitter for those web pages. BackTweets sends the data in an XML format so we use another Google Docs formula to extract the relevant information.
\=importxml(http://backtweets.com/search.xml?key=XYZ&q=” &B3, “//totalresults”)
You need to replace the string XYZ with your own unique API key and that can be requested at this page. It’s free for non-commercial use and you can make 1000 requests per day.
See the tutorial on Tracking Web Pages with Google Docs for a more detailed explanation of the ImportXML function.
Google Developer Expert, Google Cloud Champion
Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.