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
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Data Validation

Avatar

Level 1

Hi All,

We recently began storing our adobe clickstream data in an AWS database. I am currently in the process of trying to match the data in our database with reports from Ad-Hoc Analysis. I am able to match the total number of visits in October 2016 to our website from our AWS database to the report from Ad-Hoc Analysis using this query:

select  dateday, count(distinct UID) as visits
from (
    select split_part(date_time,' ',1) as dateday,
    cast(@post_visid_high as varchar(200)) + '-' + cast(@post_visid_low as varchar(200)) + '-' + cast(@visit_num as varchar(200)) + '-' + cast(@visit_start_time_gmt as varchar(200)) as UID
    from all_2016_10
    Where exclude_hit <= 0 AND hit_source NOT IN ('5','7','8','9') and username = 'ourwebsite' and
    )
group by dateday
order by dateday

However, when I add filtering I am not able to match the results. For example, If I look at visits with Entry Page equals to 'our homepage' in Ad-hoc, I can't get this report to match my query from our database in AWS. I am using this query

select  dateday, count(distinct UID) as visits
from (
    select split_part(date_time,' ',1) as dateday,
    cast(@post_visid_high as varchar(200)) + '-' + cast(@post_visid_low as varchar(200)) + '-' + cast(@visit_num as varchar(200)) + '-' + cast(@visit_start_time_gmt as varchar(200)) as UID
    from all_2016_10
    Where exclude_hit <= 0 AND hit_source NOT IN ('5','7','8','9') and username = 'ourwebsite' and visit_start_page_url = 'our homepage'
    )
group by dateday
order by dateday

Does anyone know why these results aren't matching when I add filtering?

0 Replies