The Easiest Reputation Management Tool (Google Spreadsheet)

UPDATE: The spreadsheet is currently down. We are working on it right now. Please check back!

If you have read my previous articles, you are likely to know that I love tools and tables. If you haven’t, just trust my word or read further.

I’ve been an earlier adopter of Google Spreadsheets for the same reason: they are almost as great as Excel for making cool handy tables and they can be turned into fun-to-use and easy-to-share free tools!

What I am going to share today is one Google Spreadsheet which will make your daily reputation monitoring task a breeze: all you need to do is to copy the spreadsheet, bookmark it and open it daily. Even if you haven’t yet started monitoring your brand name mentions, you will do that from now on. Guaranteed. Just because it’s that easy and fun!

Now let’s start playing.

What Does The Document Do?

The “Easy Reputation Management Spreadsheet” generates search results from three sources:

• Generic Google search results (30 top results)
• Google search results for the past 24 hours (30 results)
• Twitter search results (top 100 tweets)

Each page found in top 30 Google search results for your name has the following data:

• Tweets (according to Tweetmeme);
• Facebook Shares; Facebook LIKES; Facebook comments + Facebook total interaction score.

This way you will see how well your brand name does in Google and how actively it is being discussed in social media simply by clicking through Google Spreadsheets tabs. As easy as that!

To use the spreadsheet, simply provide your brand name in the yellow cell in each tab (1, 2, 3):

You may want to play with different search operators for more interesting results:

• Exact match [“brand name”] (works great for the first tab)
• In-body mentions [“brand name” -intitle:”brand name” OR “brandname” -intitle:”brandname”] (work best for both generic and recent Google search results – first two tabs). In-body mentions are often unlinked, so these often turn into fabulous link opportunity.
• All search tricks from this old post of mine(“Twitter search results” tab):
  • Filter out RTs [“brand name” -rt]
  • Filter out links to find *real* opinions and discussions [“brand name” -filter:links]
  • Filter out your own Tweets [“brand name” -from:username]

Troubleshooting

Tweet count is lower than what your page button shows: Unlike Facebook, Twitter makes it almost impossible to tell  exactly how many times each page has been shared so far. Official Twitter API caches the number and returns some absolutely useless data. Tweetmeme is more reliable but it’s still somewhat inaccurate. That being said, that’s not an exact science. The most important thing is, if there was any interaction at all and how it compares to other sources.

Tweet count shows “Service invoked too many times in a short time: urlfetch. Try Utilities.sleep(1000) between calls.” or other errors: You’ll see errors now and then because there is no perfect way to update scripts or schedule requests from the cells, so if you see such an error try searching for something else. Or re-save the “tweetMemeCount” and “FacebookLikes” scripts by going: Tools -> Script Manager -> Select the script and click “edit” and then “Save” in the new window. This will force the script to re-run.

There are some weird results saying “images for..” or “related to…”: Our scraper may include blended search results, so just ignore them.

Something else is not working? Google changes search results mark-up now and then to kill scrapers (unlike our scraper, there are quite a few malicious ones). Facebook and Tweetmeme API are subject to change as well. So if you want anything to be updated in the document, please let me know by commenting here. I’ll do my best to update the document once anything stops working!

Credits

I am not that geeky to create the scripts myself. So here’s what I’ve been using:

• tweetMemeCount script was created on my request by PHP Climber
• FacebookLikes script is courtesy of Martin Hassman

Updates:

• 2012/2/22: Updated to fit the new Google results. We had to come up with the additional parser to get actual URLs from Google redirects – feel free to copy it for free!
• GetTweets script is courtesy of Mikael Thuneberg