User tracking table design and method

Question

I have some questions about database design for user tracking (analytics)

My website will have 3 classes of user:

• non user
• user with incomplete regstration progress
• registered user

For each of these classes I have different events I want to track. Ie: for a non-user I want to track if they reach registration. For registered I want to track if they make it to a certain page etc...

At the start of each visit I will record their landing page and referrer, then I want to track each of these funnels. So for the table design I am thinking:

1. A table with their session id (stored in a cookie) and user id if they ever log in=one row per user + one row per guest
2. A table for visit details- referrer, landing page, time etc... so If a user from table 1 visits 10 times they will have 10 entries in this table. Maybe have another table with a list of browsers?
3. 3 tables for each of the user types with tracking flags. Each row will link to the visit in table 2. So there is a 1:1 relation between these 3 tables and table 2

Is this a logical design for tracking users? Does anyone have any links with good articles on user tracking?

Also, how reliable is the http-referrer header for tracking where users came from? Is there any other method I can use to see where people came from?

Also also, how scalable will this table design be?

Thanks, M

Answer 1

Not having done that before, I find your approach not bad - propably improvable, but ok. Just wanted to add that - depending on your requirements - you could be very lazy and let Google Analytics do that for you!

With different tracking rules, you can set up Google Analytics to track differently depending on user cookies, which you can set in the registration/login-process (or not for the non-user).

Downside of this idea is that you'd rely on javascript and you won't have seperate data for each user, just a total of the user-class.

Here's the GA Resource how to set up exceptions (see the lower description). Then create three different GA tracking codes and on each only accept user with either no-cookie, logged-in cookie or registration-process cookie.

Hope I could help, anyway good luck!