Expand my Community achievements bar.

The Analytics Community Lens news letter for February 2024 is now here! Come see upcoming events and what's been going on in the community!

Calculate Entry Page Visits from Clickstream Data

Avatar

Level 1

Hello There,

I'm trying to calculate the entry page visits for clickstream data using SQL, but I'm receiving inconsistent results when attempting to reconcile with Adobe Analytics data. Please find below SQL query for the same,

select
    dd,
    count(
        distinct (
            concat (
                post_visid_high,
                post_visid_low,
                visit_num,
                visit_start_time_gmt
            )
        )
    ) as visits
from
    `clickstream`
where
    yy = 2023
    and mm = 06
    and entry_page_type = 'product page'
    and visit_page_num = = 1
group by
    dd


I also tried using prev_page fields but still the results are different. Can someone help me to correct this ?  Thanks

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

2 Replies

Avatar

Community Advisor

Did you factor in the exclude_hit column in your raw data? If there are hits that shouldn't be included, you may still be counting them in your SQL?

Thanks Jennifer. No I am not counting any extra hits in any of the case.