Variation between Average Time spent on Site (Seconds) or Time spent per Visit (Sec) in Data feeds and Adobe workspace

[Chandra_Tp]

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

[MandyGeorge]

Data feeds can be a bit tricky to work with sometimes, as they often have more data than what you will have in workspace. First thing I would suggest checking, is the workspace you're using a VRS? If so, have you replicated those conditions in your data feeds table?

If you have, then the next thing I would look at are specific visits as an example. Find a visit with a customer ID that you can identify in workspace and in the data feeds. Look at the time stamps associated with that particular visit (or check a few visits), see if they are lining up. If they do, then it is likely that there are hits/visits being included in the data feeds that are being excluded from your report suite in workspace (bots, excluded IPs, etc.).

[Chandra_Tp]

Thanks for your response @MandyGeorge We are not using VRS at this time. 
what do you mean by customer ID, can you please provide an example.  If bots, excluded IPs, etc. hits/Visits are also included in data feeds shouldn't the Time spent per Visit increase than workspace?

[MandyGeorge]

By customer ID I mean a unique customer identifier. We capture a marketing cloud visitor ID (MCVISID) in an evar. Or if you have something unique to your site that you pass that can be used to identifier a unique visitor (like a hashed email or other log-in value).

As for including those increasing the values - not necessarily. If you have an entire visit that isn't in workspace because it's filtered based on IP or as a bot, and it's shorter than the average visit, it would reduce your average time on site instead of increasing it. The numbers in your examples, based on the code, appear to be average times. So if workspace has visit 1 at 10 seconds, and visit 2 and 15 seconds (average 12.5), but then data feeds also has visit 3 at 5 seconds, average becomes 10 seconds, lowering it. 

For total time on site (not average), yes excluded hits/visits should raise it, but not necessarily for averages.