Noob MySQL question - handling hostorical data (website stats)

[Jog On]

I'm building a MySQL database to store information on my websites such as traffic stats (from Google analytics), lead conversions and affiliate sales.

I've been searching and searching but can't find any useful info on how to best handle the historical data (I probably don't even know the search terms I should be using).

I want to update the DB each week and be able to keep the historical data so I can measure ongoing performance and draw my own graphs etc just like Google analytics but with conversion and revenue data as well.

What's the most effective and efficient way to update the DB each week and retain the previous data in each table? Some fields won't be updated but some will.

[yorkshireman]

I'm building a MySQL database to store information on my websites such as traffic stats (from Google analytics), lead conversions and affiliate sales.

I've been searching and searching but can't find any useful info on how to best handle the historical data (I probably don't even know the search terms I should be using).

I want to update the DB each week and be able to keep the historical data so I can measure ongoing performance and draw my own graphs etc just like Google analytics but with conversion and revenue data as well.

What's the most effective and efficient way to update the DB each week and retain the previous data in each table? Some fields won't be updated but some will.

Hi Jog On,

I would have a field in the table called resultsdate make it a timestamp field. This will auto populate with the current time/date whenever you load data.

Write a script to get your results preferably in csv format then call mysqlimport to import into your table.

Use cron to run your script weekly

[Spacecadet]

All I can answer that with is a lot of questions!

To save me shooting in the dark, I'll give you a break down of what you should be doing

Your best method will be to start off with building some prototype charts and graphs.
From these you can extract what you want to measure and the attributes which those measured will be grouped against.
Attributes will build into members - logical groups of members and from there you can build a hierarchy
By now you should be getting towards something that looks like a dimensionalised database.

Once you've got a semi decent logic and physical model you can then start to think about historical attributes.

Sounds like a lot of work but if you do it properly you'll see the benefits

[Jog On]

Hi Jog On,

I would have a field in the table called resultsdate make it a timestamp field. This will auto populate with the current time/date whenever you load data.

Thanks for that. Will it create a new row for each week and leave the previous rows as they were?

[minestrone]

I would have an audit table and set a trigger on update of the table to insert a new insert on the audit table. Probably the way I would do it.

[yorkshireman]

Thanks for that. Will it create a new row for each week and leave the previous rows as they were?

It will import a new row each time the script is run.

Depending how much of a programmer you are, you may be better looking at a free tool for web stats like AWSTATS. It can automatically query webserver and write stats away to mysql.

[Freamon]

Is this currently an operational database where the data is just updated whenever it changes, rather than a new row being inserted?

If so, you could create a separate table and then take a "snapshot" of all the data by running a weekly script to copy the data into that table and add a timestamp.