Reporting Interface vs Data feeds - Visits metrics

[quickenloansinc]

Hello We are noticing inconsistencies when comparing the visits metrics on day level in the reporting interface and visit metrics calculated from data feeds for that day.

Day: 2020-11-01

Reporting Interface: 617615 

Data Feeds:  616346 ( for exclude hits = 0)  

For data feeds, We used the below query

select count(*) from (select distinct post_visid_high,post_visid_low,visit_num,visit_start_time_gmt from adobe_data_feed where datekey='2020-11-01') a

How can we match these numbers?

[user95087 · Accepted solution]

Hi, this is the code we used and it gave us correct numbers. I see you are missing the exclusion of hit_source 5,7,8&9.

SELECT count(distinct concat(post_vis_id_high,
post_vis_id_low,
CAST(visit_num AS VARCHAR),
CAST(visit_start_time_gmt AS VARCHAR)))Visits, batch_date FROM "adobe_data_feed"
WHERE exclude_hit = 0
AND hit_source NOT IN (5,7,8,9))