Looking for exact logic to calculate Bounces in Data Feed

[AkshaySh15]

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

[Harveer_SinghGi1]

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!

[AkshaySh15]

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]

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]

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;