There are three types of cookies that contain information of value: __utma, __utmb and __utmz. The four main (debatable, I know) browsers store them differently. Internet Explorer stores them in a text file, Firefox and Chrome in an SQLite database, and Safari in a plist file.
The values in the data base look something like this:
- __utma: 191645736.1125870631.1349411172.1349411172.1349411172.1
- __utmb: 1400295184.108.40.2069409002
- __utmz: 140029553.1349409002.1.1.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=php%20email%20throttling
I have written one tool that will parse the Google Analytics cookies for these four browsers, GA Cookie Cruncher:
Internet Explorer - point the tool to the folder containing the cookies (either export out the cookies folder, or mount the image). The tool will read each cookie within the folder, determine if it has these values and parse them accordingly.
Chrome – point the tool to the cookies sqlite database (either exported from your image, or mounted). The tool will query the database for all the Google Analytics values and parse accordingly.
Safari (Mac) – point the tool the the cookies.plist file. It will parse the plist file and the the Google Analytics cookies within.
Firefox- The Firefox cookies are stored in an SQLite database. Unfortunately, the wrapper library I used can not access this SQLite Database. I also tried to test the Firefox cookies database with the free SQLite Browser which could not read it either. So far, the only tool I have been able to access this database with is the SQLite Manager plugin for Firefox.
I have included a little hint in the "Browser Information" box to remind you where the default location of these cookies are for whatever browser you select. I cant event remember where I put my keys, so I thought this might be helpful.
The program creates 3 files in CSV format: %Browesername%_UTMA, %Browesername%_UTMB and %Browesername%_UTMZ
Here is some sample output from Internet Explorer from a __utmz cookie:
Now, I haven’t tested this on every browser version out there, and I have seen some variations on the way the cookies are stored. Some initial tests indicate that IE 9 does not seem to track these values, but more research will need to be done to confirm (thanks to cheeky4n6monkey for the testing). If the tool does not read your cookie file, I'm happy to help, just shoot me an email.
Download the GA Cookie Cruncher here.
Great information. Two things:ReplyDelete
1. I receive a compatibility error on my 32-bit Win7 machine. I will try my 64-bit machine later.
2. In reading over the DFI article, what "hash type" is being used to decode the domain hash? Maybe I missed that piece of information somewhere.
Thanks for the input! I did not have a 32 bit system handy to test it on. I'll see if I can fix that.ReplyDelete
As for the Hash, I didn't see it either...I'll have to look into that.
Great work, Mari!ReplyDelete
Thanks for such a great tool!
Exporting from SQLite manager plugin in Firefox and parsing the file with GA Cookie Cruncher gives me the error after I click the process button:
Unable to process file. Please verify file format: Conversion from string "1367551553637:1" to type 'Integer' is not valid.
What can cause this issue? Some special settings in the export dialog of SQLite manager plugin ? I tried different combinations of check-boxes but still no luck. Please help.
Thanks for the feedback.
I have that error as a catch all if the program is not able to parse the file due to multiple reasons. That number looks like an epoch time stamp that contains milliseconds.
Can you open the CSV file and search for that row and send it to me, or tell me what column contains that data? column 7 should be have 10 digits, and column 8 should have 16.
In the meantime, if that is the only value that contains the timestamp in that format, you can remove that row and it should process ok.
My email is arizona4n6 at gmail.com
The SANS webcast link is dead, but I found the preso on youtube for anyone interested.ReplyDelete
Not So Private Browsing
Thanks for your posts on GA cookies, and blog in general.
Does the GA Cookie Cruncher can running on Linux?
Does the GA Cookie Cruncher can running on Linux ?
I have a similar toot that is python based that should run on Linux. Check my downloads page for the Google Analytics ParserReplyDelete
Does this tool work with FF v36?ReplyDelete
Not sure.. give it a try and let me know. If not, I'll be happy to update the code :-)ReplyDelete