Cleaning the CAM database

I'm now pulling nice big piles of data into my CAM database, but I'm finding it rather messy. I'm parsing log files, and there are over 100 distinct URL patterns I need to handle. This obviously brings in a lot of places I can make mistakes - mis-parsing URLs, overly broad matching of a URL pattern, missing variants in a particular URL pattern. So I'm now working on cleaning that all up. The main items I'm looking at are called PBLs and TAs, and they have clearly defined patterns regarding what they are.

It's slow going - I'm gradually fixing up and improving those 100 patterns, and finding all sorts of ways they can be wrong. I'm also finding "errors" in the data - which appear to be incorrect URLs formed by users playing with things in the address bar of their browser, or occasionally from javascript incorrectly accessing unusual URLs. I'm not sure what to do with these events; it's probably the case that I should just delete those URLs from my analysis database, as they will just make analysis harder, with no real benefit.

I'm interacting directly with the database here, using the SQL console. It's the quickest and most trustworthy way to get at the data; but I'm starting to think having some other visualization tools might come in handy at this point to confirm that I'm getting the patterns I expect.
Here's some of what I've done:

Phase 1: add a "pattern" tag to events, to allow mapping of parsing patterns to items in the database. This will let me check that each pattern is catching the right items, and that the code isn't catching and misinterpreting any false positive matches

Phase 2: Look through every single pattern and ensure that it catches URLs that will be parsed correctly by its parsing rule. Use commands like:

select distinct followedlink from event where tags='pattern60';

This will take a long time to do.

Phase 3: Look at items, ensure that they are being mapped correctly. Initially, I was getting PBLs with values like "4635" and TAs with values like "4.04" - I had mis-mapped items. Use commands like:

select  item.title,item.itemid,count(*) from item,eventitem where type='TA' and item.itemid=eventitem.itemid and not item.title ~ '^[0-9]+$' group by item.title,item.itemid;

To find non-correct TAs, and

select  item.title,item.itemid,count(*) from item,eventitem where type='pbl' and item.itemid=eventitem.itemid  group by item.title,item.itemid order by count;

to find all the PBLs and work out which ones are OK.

Some results!

OK, admittedly, they're not very exciting results, and they don't tell me a whole lot that I didn't already know, but I've finally got my data into a kind of shape that allows me to analyze it.

My CAM structured database now has about 12 million Event records in it (12,383,032, to be precise, drawn from 18,764,699 lines of log files). From that, I have created a graph that show how long each resource is used (specifically, the number of downloads each day after it is downloaded).

Average downloads per item by day after posting. Click to embiggen.

What it's showing us that the vast bulk of the downloads of each resource happen in the first week after it's posted, (see that red spike at the start there - that's an average of 50 downloads for each resource in the day it's uploaded). In the second week there's an average of one download per day, and after three weeks the downloads pretty much drop to zero.

This is to be expected on a course built around a weekly timetable like this. Once the week is over, students no longer (a) need that resource any more, and (b) won't see it through the interface any more, since the links to the resource are in the previous week's section of the site, which they aren't visiting any more.

There is more detail in the following graph, which shows the download stats for all the student resources (each as a separate line with randomly generated colours):

Downloads per item by day after posting. Click to embiggen.

It's a lot messier and harder to read, but it's interesting in that there are two specific resources that stand out - one (represented by a red line) became popular at about 125 days after it was uploaded, and remained popular for about six weeks; and one (represented by a aqua-ish* line) became popular about 300 days after it was uploaded, and remained populat for about three weeks. I'm not sure what was special about these - whether the became relevant to the curriculum for some reason, or whether it was a UI change that I made that threw them into the limelight, prompting students to download them. But I think they warrant further investigation.


* The problem with randomly generated colours is that they don't lend themselves to easy naming. XKCD proved it.

The lure of shiny toys

As mentioned previously, I have a shiny new machine to do my PhD work. It's delightful - the latest 27" iMac, with i7 processors, 16 gigs of RAM, and a fusion hard drive. It's delightfully fast.

It's especially nice for analysis work. Prior to the upgrade I had to run my analysis on a separate machine, wile I used my study machine. It was slow and painful - analysis runs took hours (close to a day in some cases). They now take minutes - I can import log data into my database at the rate of 1500 records per second.

The main advantage, though, is the pleasure I get from sitting down in front of it. With the previous machine, the lag that would happen when I wanted to do anything slowed me down, and caused me to avoid doing too much - sometimes the 20 seconds it would take to log in and get my application running meant that I would avoid doing it at all. Now, everything happens straight away.

It's another lesson - it's worth removing those little barriers to productivity. In my case, working full time means I can afford to throw money at the problem - making my working environment nicer by buying a new machine means the hours I can afford to spare for my PhD are that little bit more productive and pleasant.