Solved
Unique visitors count is wrong
Hi, i made a query to get the total unique visitor and visits for a single day, when i compare my query result to the result on adobe dashboard i see that i am a couple unique visitors off. for example my query would get 345,369 unique visitors but on adobe dashboard i would get 345,375. How can I match the unique visitors on my adobe dashboard?
my query is below.
AS WITH InitialAggregation AS (
SELECT
MAX(date_time) AS date,
CONCAT(post_visid_high, post_visid_low) AS unique_visitor_id,
COUNT(DISTINCT CONCAT(post_visid_high, post_visid_low, CAST(visit_num AS VARCHAR), CAST(visit_start_time_gmt AS VARCHAR))) AS visits
FROM
[dbo].[Extracted_Adobe_Data_Test]
WHERE
exclude_hit = 0
AND hit_source NOT IN (5, 7, 8, 9)
GROUP BY
date_time,
CONCAT(post_visid_high, post_visid_low)
)
SELECT
date,
COUNT(unique_visitor_id) AS total_daily_unique_visitors,
SUM(visits) AS total_daily_visits
FROM
InitialAggregation
GROUP BY
date;