Data Validation

natel22471794

14-12-2016

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?

Accepted Solutions (0)

Answers (0)