How can I calculate Bounces and Bounce Rate in Data Feed?

[RodrigoPan]

Hi everyone! I´m trying to calculate the Bounces and Bounce Rate in Data Feed, but I´m not finding the right way.

I´ve tried to combine the visit_num, visit_page_num, page_event and post_page_event fields, but I can´t find the same number of the Workspace Analytics.

@Ishan_Sinha_ had a post with this question (https://experienceleaguecommunities.adobe.com/t5/adobe-analytics-questions/how-to-calculate-bounces-...), but I can´t solve anyway.

I appreciate the help to solve this problem! Thanks!

[PratheepArunRaj · Accepted solution]

Dear RodrigoPan,

You need to filter only the Visits with Visit Depth 1 (visit_page_num) irrespective of the HIT type. This will give you the Bounce Number. Then you can divide the same by Visits to get the Bounce Rate.

However, this will work only for the overall #s and not broken down by any dimensions.

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

[RodrigoPan]

Hi @PratheepArunRaj , thanks for your answer.

So, if i need to breakdown for days or months (for example), I can´t reach the real number of bounces?

In Workspace Analytics and Report Builder I got the same number of Bounces, but I can´t get this number in Data Feed.

[PratheepArunRaj]

Dear RodrigoPan,

Breaking down by Time Series is fine. If that's the case, you are missing something. Until we see the data, it is tougher for us to comment.

I would suggest you to take a shorter time frame and try to replicate the numbers. See if that helps and then you can calculate it for higher time frame.

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

[RodrigoPan]

Hm, understand!

I included a preview of my data, comparing the data from Workspace Analytics or Report Builder and the script from Data Feed:

Workspace Analytics or Report Builder:

Data Feed:

The visits, unique visitors, page views and page views / visits are ok (I followed the rules of the calculate metrics documentation: https://experienceleague.adobe.com.mcas.ms/docs/analytics/export/analytics-data-feed/data-feed-conte...), but the number and % of bounces are different.

The field “Bounces” I use this logic: count only the records with visit_page_num = '1' (the same that you said to me).

The field “Bounces2” I use this logic: count only the records with visit_page_num = '1' and post_page_event = '0'.

But both fields are very different from the Workspace Analytics. I don´t know If I have to include one more condition in my script to receive the correct data.

[PratheepArunRaj]

Dear RodrigoPan,

Guess you are just putting a filter with visit_page_num = '1' and calculating the count of the record. If you do, you are including all the visits that has visit_page_num = '1', but not the record with only visit_page_num = '1'. Can you double check?

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

[PratheepArunRaj]

Or else, try this.

You have concatenated the post_visid_high, post_visid_low, visit_num, and visit_start_time_gmt for the visit count right? Just count the concatenated value which has only one count.

[RodrigoPan]

I did a case condition for both columns (column Bounces: case when visit_page_num = '1' then 1 else 0 end and column Bounces2: case when visit_page_num = '1' and post_page_event = '0' then 1 else 0 end) and in the end I sum each columns values.

But I have to keep the distinct record based in what column or what rule?

Yes, for visit when i group the values i use this rule: 

1. Concatenate post_visid_high, post_visid_low, visit_num, and visit_start_time_gmt.
2. Count the unique number of values.

[RodrigoPan]

This time, I chose one day (“2021-01-01”):

Workspace/Report Builder: 5.166

Data Feed: I selected all the records from the day “2021-01-01” (28.129), I concatenated the columns “post_visid_high || post_visid_low || visit_num || visit_start_time_gmt” and, after the concatenated, I identified how many distinct records appears only one time and how many are in the “visit_page_num” = 1, but the numbers are different again (6.376).

Do you have a practical example with the Bounce calculate? With the example I can see where are my mistakes.

[PratheepArunRaj]

Dear RodrigoPan,

Glad that the numbers are closer now unlike earlier.

Can you also add one more condition to check 'post_page_event = '0' and check?

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

[RodrigoPan]

Hello @PratheepArunRaj   ! Thanks for another reply!

I tried with this another condition, but the number only decreased 21 records (6.355):

I selected all the records from the day “2021-01-01” (28.129), I concatenated the columns “post_visid_high || post_visid_low || visit_num || visit_start_time_gmt” and, after the concatenated, I identified how many distinct records appears only one time and how many are in the “visit_page_num” = 1 (6.376) and how many are in the post_page_event = '0' (6.355).

I´m doing this analyze in Microsoft Excel, using pivot table and excluding duplicate values in the end.

[PratheepArunRaj]

Dear RodrigoPan,

Then try to identify the descrepency using Visitor ID if you have.

I try to calculate this for my dummy data and share you if i can match the bounces.

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

[RodrigoPan]

Hi @PratheepArunRaj 

I already did this when I concatenated the columns “post_visid_high || post_visid_low || visit_num || visit_start_time_gmt”, right?

There is no visitor_id field in the feed.

[AkshaySh15]

Hello @RodrigoPan/ @PratheepArunRaj,

Hope you guys are doing well, Are you guys able to find the exact logic forthe bounce field as i am also facing issues while calculating the exact logic. Can you assist here.

Thanks

Akshay Shrivastava

[PratheepArunRaj]

Hello @AkshaySh15 ,

The conversation was during 2021.

Try calculating the bounce for a visitor ID and see whether the numbers are matching between Workspace and Data Feed. If not, reach out to Customer Care.

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

[AkshaySh15]

Have used the below query: 

SELECT date, COUNT(*) as bounces FROM

  (

    SELECT date(date_time) AS date,

           COUNT(*) AS bounce

    FROM `datafeedtable`

    WHERE

    exclude_hit = '0'

    AND hit_source NOT IN ('5','7','8','9')

    AND date_time BETWEEN ('2024-07-01 00:00:00') AND ('2024-07-01 23:59:59')  

    AND visit_num = '1'

    AND  page_event = '0'

    AND evar15 like '%ford%'

    GROUP BY date,

             CONCAT(post_visid_high,post_visid_low,visit_num,visit_start_time_gmt)

    HAVING SUM(cast(visit_page_num as int)) = 1

 ) AS subquery

 GROUP BY date

 order by date;

For July 1 2024:

logic : 4361

Adobe : 4319

There is a slight deviation. Can you assist here