PageViews and Unique are ok, but how do I calculate visits?RSS

6 replies

Last post Feb 22, 2007 11:37 AM by Anonymous

  • PageViews and Unique are ok, but how do I calculate visits?

    Nov 07, 2006 06:22 AM|Anonymous|LINK

    Hi there,

    browsing through this forum I have found the answer to my questions ALMOST. But it's the almost part that I have been struggling with for a day and a half now. What I would like to measure is basically three things:

    1. pageviews: I used query below to do that (please correct me if I'm wrong)

    SELECT COUNT (*) AS PAGEVIEWS into PAGEVIEWS.txt FROM www3\*.log WHERE  EXTRACT_EXTENSION(cs-uri-stem) NOT IN ('css';'js';'gif';'bmp';'jpg';'jpeg';'png')

    2. unique visitors: query below (once again: I might be wrong....)

    SELECT COUNT(DISTINCT HASHSEQ(STRCAT(c-ip, [cs(User-Agent)]))) AS UNIQUE into UNIQUE.txt FROM www3\*.log

    3. and this is the hard one: VISITS a.k.a. user sessions

    We use the IISWSC logfiles, so I first thought: why not use the ASPSESSIONID for that. Since our pages are mainly ASP, the ASPSESSIONID field should be filled on almost every line of the logfile. BUT.... that means missing out on all files not being ASP and I have no idea what will happen once we migrate to ASPX.

    Then I thought something like:

    SELECT COUNT(DISTINCT HASHSEQ(STRCAT(c-ip, [cs(User-Agent)]))) from www3\*.log HAVING (MAX(date,time)-MIN(date,time)= etc. etc.

    But as you will understand from the etc. etc., the HAVING part is where I give up. I have no idea about the syntax or whether this can be done in the first place.

    I read somewhere that a VISIT or USER SESSION means that a visitor stays active for at least 20 minutes. But how do I query that OR how do the loganalyzer software makers do that?

    Any help appreciated!!

     

    Thanks

    Cees

    IIS Input Format

  • Re: PageViews and Unique are ok, but how do I calculate visits?

    Nov 07, 2006 06:44 AM|Anonymous|LINK

    Unfortunately, there isn't really any good way to do visit tracking in LP. It is only barely possible in a normal SQL engine using complicated self joins.

    I'd suggest looking into an actual web log analysis program such as AWStats for the visit tracking part..

    IIS Input Format

  • Re: PageViews and Unique are ok, but how do I calculate visits?

    Nov 07, 2006 06:49 AM|Anonymous|LINK

    I did think about looking at AWStats, but the first glimpse learned that it is not as flexible as Logparser is. That's why I love it, I just output a number, add it to an HTML table, throw in the company logo and export it to PDF. My manager loves me for it!!!

    Thanks for the reply Daniel.

     

    Cees

    IIS Input Format

  • Re: PageViews and Unique are ok, but how do I calculate visits?

    Nov 07, 2006 07:34 AM|Anonymous|LINK

    I will go back on my prior comments and say that if you have a session cookie stored in the logs that you can group by that in LP and get fair sessionization from that. Of course, it will still have holes for people who leave their browser open all day and people who have cookies turned off, but those are the trade-offs.

    IIS Input Format

  • Re: PageViews and Unique are ok, but how do I calculate visits?

    Nov 07, 2006 07:51 AM|Anonymous|LINK

    And you have to have dynamic pages, because IIS f.e. does not log the session cookie in case of an .HTM(L) file. If the same user visits an ASP page too then it is no problem, but if your website is mainly static pages.....

     

    Cees

    IIS Input Format

  • Re: PageViews and Unique are ok, but how do I calculate visits?

    Feb 17, 2007 09:48 PM|Anonymous|LINK

    nice querys tho.. very interesting for a newbie like me..

    WebLogExpert (Pro trial priod) gives me output like this
    The one called total visitors is users that has been away from the site more than 30 minutes (this is something that u set analyzer options panel)
    Unfortuneatle i could not disable it..to see if it gave the same result as your "time sesssion" - query. I could set it at 10000 minutes at max..but the two numbers were not that far from eachother.

    Hits
    Total Hits 744,422
    Average Hits per Day 9,667
    Average Hits per Visitor 25.05
    Cached Requests 67,743
    Failed Requests 13,257
    Page Views
    Total Page Views 37,320
    Average Page Views per Day 484
    Average Page Views per Visitor 1.26
    Visitors
    Total Visitors 29,716
    Average Visitors per Day 385
    Total Unique IPs 23,220
    Bandwidth
    Total Bandwidth 0 B
    Average Bandwidth per Day 0 B
    Average Bandwidth per Hit 0 B
    Average Bandwidth per Visitor 0 B

     

    If you have more sweet query lines throw them this way ^_^

    IIS Input Format

  • Re: PageViews and Unique are ok, but how do I calculate visits?

    Feb 22, 2007 11:37 AM|Anonymous|LINK

    The big problem with Log Parser and User level statistics is that Log Parser is designed to focus on a row of data and does not cross the row boundary.  When faced with a problem that goes cross rows, you really need to write a little code.  Just keep track of the first and last time for a "user" and clear/dump them when the last time is too far away from the "current" time (I have hacked similar using a pair of arrays in Java - nothing I would let anyone look at, but it can be done). 

    The hardest part is actually defining "user".  This is simple if you force authentication.  If you don't, then you have to worry about proxies verses users.

     

    IIS Input Format