Among the best Google products I love playing with are Google Docs, especially Google Spreadsheets. They are feature-rich, highly flexible, reliable, and, on top of that, absolutely free.
I've been using Google Spreadsheets for ages... for campaign tracking, reporting, automating, and aggregating some of the most complex search engine optimization (SEO) and social media tasks.
In this article, I'll share the three most useful, yet easy-to-implement, Google spreadsheet tips any online marketer would find useful.
1. Google Spreadsheet Functions and Scripts
The beauty of Google Spreadsheets is its flexibility. You can be as creative as you want (or, at least, as "your geekiness" permits). If you are not ready to spend hours trying to figure out Google functions and Google scripts, here's a quick look at the three most interesting ones:
Script* / function
What does it do?
Google spreadsheet to demonstrate it
Courtesy / further instructions
Works great for scraping any Web data (e.g., search results)
Counts Facebook "Likes," shares, and comments for any URL. It's great to use in combination with =importxml function to automatically extract social media interaction for the scraped search results.
Counts tweets for any URL (based on Tweetmeme). It's great to use in combination with =importxml function to automatically extract social media interaction for the scraped search results
*Though functions will work as soon as you copy-paste into the cell, a script requires additional effort. To install a new script to your Google spreadsheet, do the following:
- Go to Tools > Script gallery.
- Find the script, and click "Install."
Or, if the script you are using is not in the gallery, do the following:
- Go to Tools > Script manager.
- Click "New."
- Copy-paste the script code there.
- Click "Save."
In both cases, the subsequent steps are the following:
- Go to Tools > Script manager.
- Select the script, and click "Run" to authorize it.
If any of the scripts are not working or show a "too many connections" error message, go to the script manager, click "edit" the script, and "save" in the new window. Resaving a script forces it to rerun the requests.
2. Scraping Anything to Google Spreadsheets Is Easy
To use the Scraper tool, you'll need the Google Chrome browser installed on your computer. (I am pretty sure a Firefox version of Scraper does not exist.) Using Google Chrome, install the Scraper extension.
Scraper is the easiest tool for data mining that I am aware of; it grabs any data from the page and imports it into Google Spreadsheets. The tool is invaluable when you want to analyze and structure (or restructure) text, huge link lists, etc. Scraper works well for importing Google search results.
To use Scraper, just highlight any part of the page you want to import into Google Spreadsheets, and select "Scrape similar" from the context menu:
There you go!
Now, use the scripts and functions to play with the data you have exported.
Note: If you are planning to use Scraper to analyze search results, be mindful that Google Instant shows the default 10 results per page. If you want to export more results, you'll have to disable Google Instant and choose up to 100 results per page via "Search Settings" in the top-right corner of your screen.
Note: Though this method is so much easier than using the =importxml function (see item No. 1), the results from this method won't be updated each time you open the document. (The =importxml function allows you to use the spreadsheet to search!).
3. Aggregating RSS Feeds
Using spreadsheets as an RSS reader provides a few huge benefits:
- Spreadsheets can arrange the news in tables (which saves plenty of time going through the updates).
- Spreadsheets have plenty of cool features built in (e.g., charts, color coding).
- Spreadsheets have plenty of scripts developed that can add some interesting data to your updates (we have already looked at some of them above).
You can get your copy here. Just make a copy of that document (via File > Make a copy), and change the feed URLs in the yellow cells to update the spreadsheet with your feed details.
Here are a few bright ideas on using the aggregator:
- Get focused. Collect the feeds of your very favorite blogs.
- Monitor reputation. Create RSS feeds using Google Alerts, Twitter search, Google News, and unite them here.
- Color-code. Use the "Conditional formatting" option to color-code news by interest, name, etc., and you'll instantly see your favorite topics as the RSS feed loads.
The spreadsheet is courtesy of this helpful tutorial.
* * *
Do you love playing with Google Spreadsheets? Share your tips in the comments!
Enter your email address to keep reading ...
Know someone who would enjoy it too? Share with your friends, free of charge, no sign up required! Simply share this link, and they will get instant access…
Metrics & ROI Articles
You may like these other MarketingProfs articles related to Metrics & ROI:
- How to Design a Customer Health Score: Seven Focus Areas
- The Five Key Metrics Every CMO Should Be Tracking [Infographic]
- Why Event Organizers Are Ditching Vanity Metrics—And Choosing These Metrics Instead
- Top 5 Marketing Agency Metrics to Measure
- A B2B Marketing Deep Dive on AI Foundations, the Future, and More: Christopher Penn on Marketing Smarts [Podcast]
- Stepping Out of the Dark Funnel: How to Shed Light on What Is Not Registered by Your Pixels (Article 2 of 2)