Best way to track impressions/clicks in a bespoke advertisement system?

Question

I've been asked to create a bespoke advertisement system despite suggesting open source alternatives such as OpenX and DoubleClick for Publishers (the former Google Ad Manager).

I've got the basics of the system set up, i.e. uploading creatives, creating positions and a mechanism to place creatives within positions; however, the area I'm stuck with is impression and click tracking.

At the moment an impression and click is stored with the creative, but this then means impressions/clicks can't be queried. For example, we can't find how many impressions were in position x between date y and date z.

How would I go about storing that kind of data? My theory was store the creative ID, position ID and timestamp in a database table, but given the amount of traffic the site has this would produce a very large database very quickly.

If any one could give me a pointer or two, that would be great.

Answer 1

What do you mean by 'a very large database very quickly'? 4 bytes each for two int unsigned and one timestamp field is 12 million bytes for a million rows.

If your concern is a number of rows in the tens or hundreds of millions, then a few suggestions:

1. Don't put integrity constraints on the id fields - ensure integrity in the app instead
2. You can write a stored procedure to archive (and delete) older data into archive tables in your primary database or a secondary reporting database
3. Do not ever run an update on the data in the primary tracking table
4. If you're going to archive (as in point 2), do it in a way that moves/deletes one row at a time

I wouldn't imagine trying to do this any other way than a database table. Even if you generated a log file, you would still need to import it to a database to be able to do any meaningful analysis with it.

btw - add a unique id field, and an IP address field too. This takes you up to 20 million bytes for a million rows