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.
Ian Lurie is CEO and founder of Portent Inc. He's recorded training for Lynda.com, writes regularly for the Portent Blog and has been published on AllThingsD, Forbes.com and TechCrunch. Ian speaks at conferences around the world, including SearchLove, MozCon, SIC and ad:Tech. Follow him on Twitter at portentint. He also just published a book about strategy for services businesses: One Trick Ponies Get Shot, available on Kindle. Read More