Export Google Trends to Google Docs Script

Feeling inspired from the success of the script that I wrote to export from Google Analytics to Google Docs , I decided to see if I could reuse most of the code for other Google CSV exports.  My first sub-project was to tackle Google Trends.

I made the necessary front end changes to put in the new links and collect the correct export URLs.  Generally everything worked on the backend as well after only a few minor tweaks.  However, I kept receiving a lot of junk in the export and the content was not legible.

There seems to be some issue with the format of the CSV file being delivered by Google Trends.  It opens fine is a pure text editor like Notepad++ , but the browser recognized thousands of "invalid" characters in the export.  Basically, the CSV contains a null value in between every other valid character and it included rare form feed characters at the beginning and end of the file.  These invisible characters were blowing up the import to Google Docs so that while I would receive a "success" message for the upload, everything came out as gobbledegoop .

After multiple attempts to try to request a different file format, I finally decided to work a little string magic using RegEx.  I stripped out all of the junk characters before sending it up to Google Docs and voila! Everything works fine now.

Unfortunately, Google Trends only allows exports for keyword comparisons at the moment, but not for website data.  In the meantime though, for anyone doing SEO Keyword research, this could be of some valuable use to you.

Here are the links:
Download GM script directly
Greasemonkey Script Page

Enjoy.

Google Analytics Bug - The Sum of its Parts is More Than the Whole

Google Analytics is a great product, in general, but sometimes I come across things that are totally head-scratching.

Their relatively new feature for Advanced Segmentation will be very very useful once it reaches some semblance of reliability. As for now, though, I cannot rely on it.

The image does not lie.  I have 2 segments applied:

1. All Visits (basically, unfiltered)
2. True Non-Paid (all visits that are not CPC)

GA tells me that yesterday I had about 110 more conversions coming from my non-cpc traffic than all of my traffic combined?????

I know that the little red tag says "Beta", but we have come to expect very reliable behavior from a lot of other "beta" products from Google (eg., Gmail , Google Earth , Maps for the longest time, etc...).

This is something that needs to get fixed quickly.  The worst thing that GA can do is put doubt in the minds of its users about the accuracy of the data.

New Version of Analytics Export to Google Spreadsheet Script

Newly updated script (v0.3.6) for exporting from Google Analytics to Google Docs / Spreadsheets.

Some of the improvements:

• Exports now properly handle applied filters, changes to dates, segments and any other AJAX-style update made to the report (previously only went off of the query in the URL)
• Enables user to name the report
• Cleaner UI that blocks the screen during loading

New links:
Download GM script directly
Greasemonkey Script Page

Thanks to sallymander for the input!

Original post

Cheers.

Watching UCLA Hoops from Barcelona using Orb, FFMPEG, and Zune

I am a big UCLA Bruins basketball fan. I started following the Bruins when I was probably 11 or 12 years old. This was during the Trevor Wilson/Pooh Richardson era. My brother was attending UCLA and I was really starting to get more interested in sports of all kinds.  Even when I first attended UW for my first 2 quarters out of high school, I found myself rooting for the Bruins against the Huskies in Pac-10 play.  After I transfered to UCLA, I could see the games in person as a student as well as always catch them on local TV.

After graduating, I still followed the games and other events closely, which was quite easy in the LA area between local cable (Fox Sports West) and the LA Times . Once moving to Spain, however, it has been a much bigger challenge to catch games that are:

1. Broadcast regionally only in the USA
2. Scheduled in the evenings (California time .... 9 hours behind Spain)

The first 2 years I had used a Tivo  connected to a Slingbox  at my parents' house.  It worked well enough but the quality was not great and the hard drive of the Tivo finally died this summer.

I now have a setup that is working quite well for the 2008-2009 season.  I bought a Haupauge 1600 TV card for my parents' PC and installed Orb.  Orb is a fantastic service that principally turns any computer into a powerful Slingbox with a built in PVR.

It is very easy to use and runs in the background.  I schedule the recordings using the web interface here in Spain.

I just look up the channel and the time (I have the schedule setup as an RSS feed from Google Calendar to have it all on the same page) and I click record:

Once the game is scheduled, I just wait.  Orb records video in dvr-ms format, which uses MPEG-2 video.  Although, very high quality, MPEG-2 makes just way too big of files to download (average size of 1 game is 3.2GB!).  

I could stream the games directly using Orb, but the PC at my parents house is not very powerful and the Internet connections are just too slow and far away between Spain and CA to maintain a stable streaming connection for 2 hours.  Additionally, since I have 2 young children, it is rare that I ever get 2 whole hours to sit and watch something, let alone 2 times per week during the season.

So, generally I watch the games partially on my PC and partially on my Zune (when it is working ).  To make the dvr-ms file, small enough to download (700MB) and is in a format able to be viewed on the Zune (WMV), I run a script to transcode  the video using FFMPEG.  It runs nightly and checks if a newly recorded video exists and if has not already converted that video, it quietly makes a copy in wmv.

I wake up morning following the game and initiate the download via Orb:

After the multiple hour download, I can start watching it on the PC or sync the game to my Zune to watch later.

This has worked so far after a lot of trial and error, especially with the file conversion (tried DVRMSToolbox and MCEBuddy before writing my own script with ffmpeg) and syncing.  I am about 3/4 done watching the UCLA vs. USC game and will finish watching it later today.  

Do not tell me who won please!

Hopefully, Orb will eventually be able to record directly to other formats like wmv or H.264 and soon I plan on changing my Zune for a Nokia 5800 XpressMusic.

Go Bruins! 

Our First Snow in Spain

This is now our 3rd winter in Spain and it finally snowed. It had snowed the previous 3 years straight in the town where we live, but there was nothing since late 2005/early 2006. We woke up this morning to find the whole neighborhood blanketed in white.

• 
• 
• 
• 
• 
• 
•