Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

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

martink96858192
Level 1
Level 1

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

1 Accepted Solution
ishans52004352
Correct answer by
Employee
Employee

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!

View solution in original post

2 Replies
ishans52004352
Correct answer by
Employee
Employee

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!

View solution in original post

swetank26061992
Level 1
Level 1

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;