Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2006
    Posts
    55

    Unanswered: query needed for path navigation through a web site

    hi experts,
    i'm working in building new reports from an existing database. the report i'm working in is to save the path of the visitor through a web site(this is neede for the statistics web site), i have the siteId, commid, maintab, subtab.
    the site id is dtored in site table, maintab and subtab are stored in article(they are mixed in one columns called title) i have also sessionid stored in session table.
    i want a query that show the flow of the visitor through a web site, which tab he clicked first then second tab then third tab and in this tab he clicked subtab and the last tab he clicked on before leaving the web page.
    is this possible and if not, what are the other approches that can i make to get the report i want.
    also i want to ask if it is possible to create the report where it will show you the visitors for the first time and the returned visitors.
    thanks
    Peace out
    Mr joka

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How do you determine the order of the pages visited? Are you storing timestamps or incrementally increasing IDs?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2006
    Posts
    55
    hi,
    thanks for the answer, actually i'm storing also the timestamp, and i think you have a point there, a timestamp is a good way to know the order, but i think a lot of information will be stored in the database if i decided to store the timestamp, do you have any other approach because at this time we can not afford tos tore a lot of information?
    thanks
    Peace out
    Mr joka

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A datetime column is not going to take up a lot of disk space. I can't think of any solution that would be cheaper in terms of bytes except an auto-incrementing identity surrogate key, and this is not the intended use of surrogate keys.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    there are plenty of 3rd party apps that do all this - you are reinventing the wheel if you code it yourself.

    have you looked at google analytics?
    http://www.google.com/analytics/

  6. #6
    Join Date
    Oct 2006
    Posts
    55
    ok.
    blindman, how can i get the path order according to tmestamp do you have an idea or a query that can do that?
    jezemine, can you explain more.

    what about the report for showing the returning visitors?
    thanks
    Peace out
    Mr joka

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I use google analytics to analyze traffic on my website. I find it very good, and it's free. You can do all sorts of stuff, like see where people are referred from, most common navigation paths they take through your site, etc.

    Did you look at the link I provided?

    also there are a ton of 3rd party apps for parsing web logs, many are free and the ones that aren't almost always have trial versions. all you need to do is look for them:

    http://www.google.com/search?q=web+log+analysis

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by mrjoka
    ok.
    blindman, how can i get the path order according to tmestamp do you have an idea or a query that can do that?
    jezemine, can you explain more.

    what about the report for showing the returning visitors?
    thanks
    Okay, assuming you have the timestamp that will give you the order in which the pages were visited. Then you just need a way to differentiate between the different people/logins/connections that visit each page. What is your method of accomplishing this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Oct 2006
    Posts
    55
    thanks for your answers and sorry for being late to answer back.
    i have all th information stored about the user, i have sessionid,siteid, i did not think of it yet, but in your point of view which one is the best?
    any ideas?!!!
    i will take a look also to the link you give me.
    thanks
    Peace out
    Mr joka

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So can't you just sort your data by sessionid and timestamp to get the order of the pages visited?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Oct 2006
    Posts
    55
    hi,
    can you give me an example or a query how to do it?
    thanks
    Peace out
    Mr joka

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select * from YourData order by SessionID, TimeStamp

    What is the problem here?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Oct 2006
    Posts
    55
    it seems too easy but i think you are right.
    i will work on the report and i will post back if there is a problem, thaks for your help.
    what about the report to store the IP's any idea about that.
    thanks again and have a nice week end
    Peace out
    Mr joka

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •