Question
Variation between Average Time spent on Site (Seconds) or Time spent per Visit (Sec) in Data feeds and Adobe workspace
Hi everyone! I´m trying to calculate the Time spent per Visit in Seconds or Avg Time on site in Seconds by month in Data Feed, using below query but I can´t find the same number in the Workspace. I appreciate the help to solve this problem! Thanks!
WITH TimeSpent AS (
SELECT
date_trunc('month', date_parse(SUBSTRING(hit_time_gmt_dt_key, 1, 8), '%Y%m%d')) AS month_start_date,
CONCAT(post_visid_high, '_', post_visid_low, '_', visit_num) AS visit_id,
CAST(post_cust_hit_time_gmt AS BIGINT) as hit_time,
LEAD(CAST(post_cust_hit_time_gmt AS BIGINT)) OVER (PARTITION BY CONCAT(post_visid_high, '_', post_visid_low, '_', visit_num) ORDER BY visit_page_num) - CAST(post_cust_hit_time_gmt AS BIGINT) AS time_spent_on_hit
FROM
"my Database"
WHERE
SUBSTRING(hit_time_gmt_dt_key, 1, 4) = '2024'
AND post_page_event = '0'
AND exclude_hit = '0'
AND hit_source NOT IN ('5', '7', '8', '9')
),
ValidVisits AS (
SELECT
month_start_date,
visit_id,
SUM(time_spent_on_hit) AS total_time_spent
FROM TimeSpent
WHERE time_spent_on_hit IS NOT NULL
GROUP BY month_start_date, visit_id
)
SELECT
month_start_date,
AVG(total_time_spent) AS avg_time_spent_per_visit
FROM ValidVisits
GROUP BY month_start_date
ORDER BY month_start_date;
| Adobe workspace | Data feeds | |
| 1/1/2024 | 263.8794549 | 198.5599481 |
| 1/2/2024 | 282.0308352 | 211.0674157 |
| 1/3/2024 | 279.1131643 | 207.1704737 |