Hi All,
I'm trying to calculate Time Spent Per Page View in our Downstream Data Lake. I was referencing the Data Columns reference to try to understand this, but unsure how to calculate it in SQL. It's my understand each row is a hit. How would I look at the average time spent on a particular page to exclude single page visits and also to average the time on a specific page, for example "Page A"? I'm trying to replicate this from my calculated metric that contains a segment as well downstream. I posted it here to reference.
I am also trying to combine this with this time spent code: Sample SQL queries to query Adobe analytics Data feeds
SELECT CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt) as visit_id,
visit_page_num,
post_cust_hit_time,
CASE
WHEN LEAD(visit_id) OVER (ORDER BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt), visit_page_num) = visit_id AND LEAD(visit_page_num) OVER (ORDER BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt), visit_page_num) > visit_page_num
THEN LEAD(post_cust_hit_time) OVER (ORDER BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt), visit_page_num) - post_cust_hit_time
ELSE NULL
END as time_spent
FROM your_data_feed_name
WHERE date >= 'your_start_date'
AND date <= 'your_end_date'
Solved! Go to Solution.
Views
Replies
Total Likes
@skatofiabah, here is my SQL for the calculation. I break that into two for easy understanding.
The first one get time spent on each hit, no matter what hit type it is.
CREATE
OR REPLACE TEMP VIEW time_per_hit AS
select
date_time,
concat(
post_visid_high,
post_visid_low,
visit_num,
visit_start_time_gmt
) as visit_id,
visit_page_num,
page_event, -- to know if the hit is page view or not
hit_time_gmt, -- using hit_time_gmt as many 0 in cust_hit_time_gmt
lead(hit_time_gmt, 1) over (
partition by concat(
post_visid_high,
post_visid_low,
visit_num,
visit_start_time_gmt
)
order by
visit_page_num
) as next_hit_time_gmt, -- move the next hit time up for time spent calculation
next_hit_time_gmt - hit_time_gmt as timespent,
pagename
from
hit_data
where
date_time >= 'start_date'
and date_time < 'end_date'
order by
visit_id,
visit_page_num
The second one groups by page and gets the number of page views of each page and the total time spent on them. It can easily be converted to get the total time spent and page views for the overall average.
select
pagename,
SUM(timespent) AS total_timespent,
COUNT(
CASE
WHEN page_event = '0' THEN 1
END
) AS pageviews -- get number of page views
from
time_per_hit
group by
pagename
While I don't have any queries to help support this.. from a high level:
Lets assume:
Page A's time will be "Time Stamp of Page B" minus "Time Stamp of Page A"
Page B's time will be "Time Stamp of Page C" minus "Time Stamp of Page B"
Page C's time will be "Time Stamp of Page D" minus "Time Stamp of Page C"
Page D cannot be determined as there is no subsequent timestamps (however, in theory, if a user triggered an "exit" click, i.e. they clicked on an external link that trigger an automatic exit link tracking, you might be able to use that timestamp.... but if the user closed their tab or browser, or just let the session expire there will be nothing.
Hi @Jennifer_Dungan,
This makes sense. Thank you. I did just add the sample code and resource link in my edited post on how to calculate the time spent between 2 hits. How can this be done on the page level to only include time spent on a page with more than 1 page view and add that up and take the average to get average time spent on a given page?
Views
Replies
Total Likes
@skatofiabah, a side question on why you want to exclude single-page view visits? The "total second spent" metric, https://experienceleague.adobe.com/en/docs/analytics/components/metrics/total-seconds-spent, actually considers both page view and link tracking hits, so if there is just one-page view in the visit but some link tracking, such as exit link click, Adobe will give you a total second spent as well.
If a visit has only one single hit, the lead() calculation will automatically exclude that visit from the calculation.
Views
Replies
Total Likes
@skatofiabah, here is my SQL for the calculation. I break that into two for easy understanding.
The first one get time spent on each hit, no matter what hit type it is.
CREATE
OR REPLACE TEMP VIEW time_per_hit AS
select
date_time,
concat(
post_visid_high,
post_visid_low,
visit_num,
visit_start_time_gmt
) as visit_id,
visit_page_num,
page_event, -- to know if the hit is page view or not
hit_time_gmt, -- using hit_time_gmt as many 0 in cust_hit_time_gmt
lead(hit_time_gmt, 1) over (
partition by concat(
post_visid_high,
post_visid_low,
visit_num,
visit_start_time_gmt
)
order by
visit_page_num
) as next_hit_time_gmt, -- move the next hit time up for time spent calculation
next_hit_time_gmt - hit_time_gmt as timespent,
pagename
from
hit_data
where
date_time >= 'start_date'
and date_time < 'end_date'
order by
visit_id,
visit_page_num
The second one groups by page and gets the number of page views of each page and the total time spent on them. It can easily be converted to get the total time spent and page views for the overall average.
select
pagename,
SUM(timespent) AS total_timespent,
COUNT(
CASE
WHEN page_event = '0' THEN 1
END
) AS pageviews -- get number of page views
from
time_per_hit
group by
pagename
Views
Replies
Total Likes
The one problem with taking actions into account it that you need to find the last action on that page; or try to add all incremental times together which can be tedious...
Example:
It you have a page load, followed by something like an overlay impression, followed by tracking on the dismissal, followed by expanding/contracting a section... etc... the user hasn't left the page...
Hi @leocwlau,
Thank you for the prompt response. I want to exclude visitors who viewed more than 1 page during their visit. The reason being that our bounce rate is off and we are trying to mitigate that. We essentially want to know if a user goes to a next page or not. For Example, if a user lands on "Page A" but doesn't advanced (even if it has non-page view hits) don't include it. In another Example, if a user lands on "Page A" and continues to multiple pages, sum the time spent on "Page A" (whether they viewed that page 1 time and continued to other pages in the view and/or they also viewed "Page A" again in the same visit. I think your code below will need changed to skip over that first landing page hits timestamps to not include single page visits.
Here is the segment from the calculated field for visual aid at what I'm alluding to:
Views
Replies
Total Likes
Hi @skatofiabah, to exclude single-page visits in the calculation, add a sub-query in the first SQL finding all visits with only one pageview and exclude those hits from the time_per_hit.
However, the result will be different from the calculated metric in your very first post, where "total second spent" is un-segmented, which will include time on a single page visit calculated by page view and subsequence non-page view hits.
Thank you so much @leocwlau! Your answers were super thorough and make complete sense to me!
Views
Replies
Total Likes