Formulas to show trends for website reports

[Chuma]

I am currently trying to work out a way to provide statistics for my company's website
in a way that can show trends like you see on stock market reports so the weekly site
report that I send to my boss will be more informative than just saying what day had the
most visitors or something else that you can find out from an automatic reporting system.

The following is what I have so far -
Stats:
sum all recored visits = total visits
sum visits for past 7 days = visits this week
sum of total/total number of days = mean average visits
sum of total for past 7 days/7 = this week's mean average visits
this week's mean average visits - mean average visits = change in average
(this week's mean average visits/mean average visits) * 100 = % change in average for week
(sum visits for past 7 days/sum all recored visits) * 100 = % of total visits that occured this week

Used on Graphs (My friend sent me these):
SUM(last seven days)/7 = 7 day average
SUM(last 14 of 7 day average)/14 = 14 day average
(Day1*1)+(Day2*2)+(Day3*3)+(Day4*4)+(Day5*5)+(Day6*6)+(Day7*7))/23 = Weighted Moving Average
SUM(Last 10 days)/10 = Exponential Moving Average

Does anyone have any other suggestions?

Thanks.

[StatCounterWebmaster]

what data do you have to work with?

Do you just have the totals for each day? Or do you have more?

[Chuma]

I am using data that has all the visits to a particular page (or all the pages for one section of the website) that has been collated from 321 days of website logs.

The totals are worked out using a query that excludes the internal IP for my company and counts the number of records returned for each particular day in the log so each IP is recorded uniquely.

Thanks.

[StatCounterWebmaster]

do you still have access to the raw website logs for those 321 days, or do you just have the totals?

[Chuma]

I still have access to the raw logs and have put them into a database so I could query the logs easier. As I have found out the database tends to get quite large rather quickly so I delete it each month and keep the raw logs.

The following is an example of a query that I use to check the number of visitors to a particular page:
SELECT UsageData.[c-ip] FROM UsageData GROUP BY UsageData.[c-ip], UsageData.[cs-uri-stem], UsageData.[cs-uri-query], UsageData.date HAVING UsageData.[c-ip]<>"10.0.0.1" AND Left(UsageData.[cs-uri-stem],12)="Default.htm" AND UsageData.[cs-uri-query]="-" AND UsageData.date=#26-Mar-2003 0:00:00#

I then do a recordcount on the query results to check the number of visitors.

Thanks.

[StatCounterWebmaster]

I'm just curious now, there are some pretty advanced log analysis software tools out there at the moment, why not just use one of them?

They'd provide a variety of stats for your boss, and you could take an early day ;)

[Chuma]

I have tried a variety of log analysis tools but for some reason none of them seem to provide the level tracking I need, such as tracking visits to an individual page over a certain period of time.

This is useful when you place an advertisement listing a particular page and want to track visitors (I was asked to check the visitors to a position description page on my company's site once which is why I started using a database to analyse the logs.)

Thanks.

[aspdaddy]

Dont know if this is the kind of thing you are after, but in Excel you can add trend lines to your traffic graphs to show growth over time.

Looks impressive showing 2 campaigns over time on a line graph and if youre really going to town you can plot a curve of cumalative payback and use built in functions for rate of return.