Looking for exact logic to calculate Bounces in Data Feed | Community
Skip to main content
A
AkshaySh15
January 9, 2025
Solved

Looking for exact logic to calculate Bounces in Data Feed

  • January 9, 2025
  • 2 replies
  • 704 views

Hi everyone.

I´m trying to calculate the Bounces in Data Feed, But the count are not matching from the adobe dashboard counts for bounces.

 

For Example: 

I have done the calculation for 1july2024 only one day data.

below is the count for Adobe Dashboard and the my logics count :

My logic count: 5864

Adobe dashboard count : 4319

 

Logic : 

SELECT
COUNT (DISTINCT CONCAT (post_visid_high, post_visid_low, visit_num, visit_start_time_gmt)) as Bounces,
date(date_time) Day,
FROM
table
WHERE exclude_hit = '0' AND
visit_page_num = '1'
AND visit_num = '1'
AND  page_event  NOT IN ('10','11','12')
AND post_page_event = '0'
AND duplicate_purchase ='0'
AND date_time BETWEEN ('2024-07-01 00:00:00') AND ('2024-07-01 23:59:59')
AND hit_source NOT IN ('5','7','8','9')  
GROUP BY Day
ORDER BY Day;
 

I appreciate the help to solve this problem. 

Regards

Akshay Shrivastava

Best answer by Harveer_SinghGi1

HI @akshaysh15 , couple of things that I noticed in your logic,

  • Why are you using visit_num = '1' clause? What this means is that you want to look at only the first visits of the users. A bounce could happen on any subsequent visits also, not only on the first visits? Can you confirm why you need this clause in your query, are you using the same logic in your AA report as well?
  • Why are you using page_event = '0' clause? This means you are only looking at page view type hits. A bounce is counted if a visit has only one hit, no matter a page load or not. Can you confirm if you are looking for bounces or single page visits?

Ideally the below query should give you the bounces,

 

SELECT date, COUNT(*) AS bounces FROM ( SELECT date(date_time) AS date, COUNT(*) AS bounce FROM `prj-dfdm-865-cfpro-p-865.bq_fpro_sfmc_aa_fdp_dmc_vw.fordpro_adobe_clisckstr_na_fpaa_raw_vw` WHERE exclude_hit = 0 AND duplicate_purchase = 0 AND date_time BETWEEN ('2024-07-01 00:00:00') AND ('2024-07-15 23:59:59') 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;

 

Given that you are not comparing it with any filtered or segmented data (like using a segmented VRS) in Adobe Analytics..

If you are still facing issues can you please share the report details that you are using in AA or you can DM me and we can discuss it in detail.

Cheers!

2 replies

Harveer_SinghGi1
Community Advisor
Harveer_SinghGi1Community Advisor
Community Advisor
January 9, 2025

Hi @akshaysh15 ,

The logic that you are using is expected to give you incorrect numbers as when you use visit_page_num = '1'  it will give you the first hit of the visit, regardless of whether there have been more hits in that visit.

I'd modify the query as below for getting the aggregated bounce count for a given period,

 

SELECT
COUNT(*) as bounces,
FROM
table
WHERE exclude_hit = '0' AND
AND duplicate_purchase ='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')
GROUP BY CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt)
HAVING SUM(visit_page_num) = 1

 

What this query will do is look at visits where sum of visit_page_num is 1 which is they have only one hit, exactly what we are looking for as a bounce.

You can modify this query to get the data on daily/weekly or any other granularity of your choice. To do that you'll have run above query as a sub query and in main query group the data by granularity of your choice.

Cheers!

    A
    AkshaySh15Author
    January 10, 2025

    Hello @harveer_singhgi1,

    Firstly, Really appretiate your quick reply with a really close logic.

    I have use the below logic for 1july2024 adobe data : But there is a slight difference still.

     

    SELECT date, COUNT(*) as bounces FROM
      (
        SELECT date(date_time) AS date,
               COUNT(*) AS bounce
        FROM `prj-dfdm-865-cfpro-p-865.bq_fpro_sfmc_aa_fdp_dmc_vw.fordpro_adobe_clisckstr_na_fpaa_raw_vw`
        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-15 23:59:59')  
        AND visit_num = '1'
        AND page_event = '0'
        AND duplicate_purchase ='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 1st July 2024:
    My logic : 4408
    Adobe:     4319

     

    Looking forward for you assistance.

     

    Thanks,

    Akshay Shrivastava

    Harveer_SinghGi1
    Community Advisor
    Harveer_SinghGi1Community AdvisorAccepted solution
    Community Advisor
    January 10, 2025

    HI @akshaysh15 , couple of things that I noticed in your logic,

    • Why are you using visit_num = '1' clause? What this means is that you want to look at only the first visits of the users. A bounce could happen on any subsequent visits also, not only on the first visits? Can you confirm why you need this clause in your query, are you using the same logic in your AA report as well?
    • Why are you using page_event = '0' clause? This means you are only looking at page view type hits. A bounce is counted if a visit has only one hit, no matter a page load or not. Can you confirm if you are looking for bounces or single page visits?

    Ideally the below query should give you the bounces,

     

    SELECT date, COUNT(*) AS bounces FROM ( SELECT date(date_time) AS date, COUNT(*) AS bounce FROM `prj-dfdm-865-cfpro-p-865.bq_fpro_sfmc_aa_fdp_dmc_vw.fordpro_adobe_clisckstr_na_fpaa_raw_vw` WHERE exclude_hit = 0 AND duplicate_purchase = 0 AND date_time BETWEEN ('2024-07-01 00:00:00') AND ('2024-07-15 23:59:59') 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;

     

    Given that you are not comparing it with any filtered or segmented data (like using a segmented VRS) in Adobe Analytics..

    If you are still facing issues can you please share the report details that you are using in AA or you can DM me and we can discuss it in detail.

    Cheers!

      SamuelPaulPeter
      SamuelPaulPeter
      Level 3
      January 10, 2025

      Kindly try the below code:

       

      SELECT COUNT(DISTINCT CONCAT(post_visid_high, post_visid_low, visit_num, visit_start_time_gmt)) AS Bounces, DATE(date_time) AS Day FROM table WHERE exclude_hit = '0' AND visit_page_num = '1' AND visit_num = '1' AND page_event NOT IN ('10', '11', '12') AND post_page_event = '0' AND duplicate_purchase = '0' AND date_time BETWEEN '2024-07-01 00:00:00' AND '2024-07-01 23:59:59' AND hit_source NOT IN ('5', '7', '8', '9') AND post_visid_high IS NOT NULL -- Ensure the visitor ID is valid AND post_visid_low IS NOT NULL GROUP BY Day ORDER BY Day;
      Terms & ConditionsAccessibility statement

      Loading footer...