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:
More important, I’ve provided a link to the public version, here. Save a copy to see the scripts.
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.
Mikael Thuneberg, whose work with Google Analytics and Google Spreadsheets made the Google portion of this possible. You should buy his GA Data Fetch tool. Really.
This is so awesome, will hurry away and download this soon.
I’m working on a little coding myself this weekend. Though I’m still new to the whole thing. If I can scrape something together I am going to offer it to the public.
My life is easier now. Thanks!
Ok this looks really awesome, but is there anyway we can get some more info on setting this up completely? I input my twitter and facebook and google, but I’m unsure about my klout “id” and the “profile id” from the google code website. is this profile id just the permalink that google gives? is there anything else we have to do in the google code website to set up the data feed?
also, how do we setup the runsched, fetchsocial, and checktwitter? sorry i hope these aren’t stupid questions. i’ll definitely share on linkedin once i get this thing figured out!!
Hi Danny,
This is really an example for folks to learn from, so you’ll need to check out Klout, and try the link that I put in the sheet next to the profile ID field.
For setting up the triggers, look in the Google App Script help under ‘triggers’. Or, click Tools >> Script Editor. Then, when you’re in the script editor, click Triggers >> Current script. You’ll need to set up triggers for the three scripts there.
Hope this helps,
Ian
Awesome post, you pretty well just gave the groundwork to do pretty well literally anything in Google Spreadsheets, thanks…
This goes way beyond SEO and web reporting, there are so many API’s, pretty sweet.