How to calculate Bounces and Bounce rate from data Feed (clickstream data)

[martink96858192]

Hi all

With data feeds, one is required to calculate certain metrics (like visits, visitors and page views) that this document outlines: Calculating Metrics

But how would one use the data feed columns to calculate Bounces and then Bounce rate?

thanks

[Ishan_Sinha_ · Accepted solution]

Hello,

Bounces are defined as the number of visits with a single image request (or single hit). Custom links are included, meaning if a visit contains 1 page view and 1 custom link call, it won't be considered a bounce.

So, basically, any visits that have only one hit or one line can be considered as bounces.

You can consider the following columns:

visit_num - tells the visit number

visit_page_num - tell the page number of the visit (filter this column for 1)

page_event - tells the custom link calls - (filter to only include 0 - that is normal page view)

Lookout for visits in the data feed and that should give you Bounces.

Hope this helps!

[swetank26061992]

What do you mean by "Lookout for visits in the data feed and that should give you Bounces."

What is wrong with the below query?

SELECT TOP 10
visit_num,
visit_page_num,
post_page_event,
post_evar29,
COUNT(distinct concat(post_visid_high,post_visid_low,visit_num,visit_start_time_gmt)) as B
FROM DB_ERDL_Staging.STG_ADB_AGG_TEST main_data_table
WHERE TRY_CAST(visit_page_num AS INTEGER) = 1
AND TRY_CAST(post_page_event AS INTEGER) = 0
GROUP BY post_evar29,
visit_num,
visit_page_num,
post_page_event
ORDER BY B Desc;