5 Replies Latest reply: Jan 12, 2012 12:19 PM by Stefan Wühl RSS

    Time elapsed between two non-sequential actions.

      I am working with web analysis data. I am trying to figure out the average time elapsed between two pages that may or may not be in sequential order. Key peices of information I have are Timestamps, URLs, and User Names. In another table I have the URLs broken up into components. Which would make the data a little easier to work with.


      Thanks for your help guys.

        • Time elapsed between two non-sequential actions.
          Stefan Wühl

          Not really sure what you are after.


          If you want to analize per User, you could create a chart with User and Timestamp as dimension and then URL as expression and something like

          =interval(TIMESTAMP - above(TIMESTAMP))


          as expression for the time elapsed (Column should be ordered by User, TimeStamp asc).


          Anyway, I think you could probably achieve what you want by appropriate order your fields and use chart inter records like above() in a chart object or previous() or peek() in the load script.


          Hope this helps,


            • Time elapsed between two non-sequential actions.

              My primary need is to find the average time it takes to get from the search to the page that the answer is on. So if you click search, you will be given a list of search findings and then you have to click on one of the findings to find the answer. The two pages will generally not be one after the other.

                • Time elapsed between two non-sequential actions.
                  Stefan Wühl

                  Because the User may first click on a page where he can't find the correct answer? Is this somekind of quiz?

                  Not sure if I understand, but how do you know when analyzing which page provides the correct answer?


                  Can you upload a small data set, maybe just as inline table, that shows how your data looks like?

                    • Re: Time elapsed between two non-sequential actions.

                      People use our site to find information. We have defined a page in our data as the "answer page". So like when people would search in Google, 1.)they would place a search, 2.)get a list of results, and 3.)then click one of the results to find their answer. We want to find the time it took to place the search and get to the answer page. Unfortunately, I cannot post a data set to the community using our actual data, but I can create a small sample in Excel to give you an idea.

                        • Time elapsed between two non-sequential actions.
                          Stefan Wühl

                          You can start with something like



                          load * INLINE [

                          UserName,    TimeStamp,    URL

                          Bob,    09:16:46,    Homepage

                          Bob,    09:16:48,    Search

                          Bob,    09:16:49,    Results

                          Bob,    09:16:51,    Answer

                          Terri,    12:16:21,    Homepage

                          Terri,    12:16:23,    Search

                          Terri,    12:16:27,    Results

                          Terri,    12:16:28,    Answer



                          left join LOAD *,

                          if(URL='Answer' and Peek(URL)='Search', Interval(TimeStamp-peek(TimeStamp))) as SearchInterval


                          load UserName, TimeStamp, URL resident INPUT  where (URL='Search' or URL='Answer') order by UserName, TimeStamp asc;


                          Then you could e.g. just use



                          in a chart object to get average values.


                          Hope this gives you an idea,