Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You can start with something like
INPUT:
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
=Interval(avg(SearchInterval))
in a chart object to get average values.
Hope this gives you an idea,
Stefan
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,
Stefan
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.
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?
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.
You can start with something like
INPUT:
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
=Interval(avg(SearchInterval))
in a chart object to get average values.
Hope this gives you an idea,
Stefan