SEO/social reporting with Google Spreadsheets
Ian Lurie Sep 23 2011
I was sure I’d get crushed by a falling NASA satellite today. It didn’t happen, but it was very motivating. I completed my legacy to the marketing world: A Google Spreadsheet that automatically grabs everything from Linkscape data to Google Analytics numbers to Twitter follower counts. It’s purty:
If you want to see how it all works, you can look at the Utilities tab in the spreadsheet, and look at the code in Tools >> Script Editor.
What it does
This sheet uses triggers to run every night, while I sleep peacefully (or thrashing around like a lunatic, which is apparently more the norm for me). At night, the sheet:
- Grabs your Twitter user count.
- Takes a count of Twitter lists you’re on, and times you’ve been favorited.
- Grabs your Facebook fan count.
- Fetches your Klout score.
If it’s the end of the month, then it also collects and records:
- Linkscape inlinking domains
- Linkscape domain authority
- Majestic SEO link count
- Google Analytics organic visitor count
- Non-branded Google Analytics organic visitor count
- Blekko incoming links
- Keyword diversity, which is very important to me
You have to add the Google link and page counts on your own, sorry.
How it works
I coded most of this at 2 AM during my 45 minutes of free time each day. Do you think I have any idea how it works?
If you want to see how it works, check out the code and the sheet tab labeled ‘utilities’. It’s safer than me trying to explain it.
Feel free to steal it
If you want to rip off my work and be a lazy bum, feel free. Just save a copy.
You’ll need to create triggers for:
- RunSched every night at midnight
- FetchSocial every night at midnight
- CheckTwitter every hour
All I ask is that you send me any changes/improvements you make, so I can share them.
Oh, and you can’t get all snotty if you find and correct my mistakes. Be nice.
Credit where credit is due
No way in hell I could’ve written this without help from:
Tom Critchlow from Distilled helped me figure out how to connect to LinkScape without going completely insane.
CEO & Founder
Ian Lurie is CEO and founder of Portent and the EVP of Marketing Services at Clearlink. He's been a digital marketer since the days of AOL and Compuserve (25 years, if you're counting). He's recorded training for Lynda.com, writes regularly for the Portent Blog and has been published on AllThingsD, Smashing Magazine, and TechCrunch.Ian speaks at conferences around the world, including SearchLove, MozCon, Seattle Interactive Conference and ad:Tech. He has published has published several books about business and marketing: One Trick Ponies Get Shot, available on Kindle, The Web Marketing All-In-One Desk Reference for Dummies, and Conversation Marketing.Follow him on Twitter at portentint, and on LinkedIn at LinkedIn.com/in/ianlurie. Read More