Time Spent Per Page View - Downstream

Question

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 feedsSELECT 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'

leocwlau · Accepted Answer

@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

Jennifer_Dungan · Answer

While I don't have any queries to help support this.. from a high level:

First, you need to identify the users and visits for those users.
Then you need to figure out which visits have only a single page view... (I don't know if the timestamp from "exit clicks" are taken into account in the Adobe calculations, for now, let's assume not... that only pages are factored in)
Now that you have visits with at least 2 pages, you will need to look at the timestamps for those page views...

Lets assume:

Page A
Page B
Page C
Page D

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.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded