Get your AOL data on

Now that you’ve downloaded the AOL data (if not, find a mirror), get the stuff in a database – you’re not going to be able to open these puppies in Excel. Josh uses PostgreSQL, and has posted how he imported the AOL data here. I’m more of a MySQL guy myself, so I plagarized him a bit to do my own import.

First, I got the ten unzipped files into one big ol’ file:

cat user-ct-test-collection-*.txt | grep -v “AnonID” | grep -v “\\\.” > aoldb.txt

Then I popped open the MySQL shell:

mysql -u root -p none-of-your-business

Then, the following:

create database aolsearch;
use aolsearch;
create table aolquery (anonid integer, query varchar(1024), querytime datetime, itemrank integer, clickurl varchar(1024));
load data local infile ‘aoldb.txt’ into table aolquery;

Bingo:

Query OK, 36389532 rows affected, 65535 warnings (7 min 52.56 sec)
Records: 36389532 Deleted: 0 Skipped: 0 Warnings: 16946940

(The warnings are from the records without click-through URLs.)

Thank god for the below:

mysql> select * from aolquery where query = ‘greg yardley’;
Empty set (1 min 40.79 sec)

I’m not going to really get a chance to dig in until tonight, but there’s a lot to learn about user behavior from the data set. For instance, this query tells me there’s 329 unique people in the data set who were looking to refinance their homes:

mysql> select count(distinct anonid) from query where query like ‘%refinance%’;