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;
Solved! Go to Solution.
Views
Replies
Total Likes
Ok, so when you take out the max date limitation, is the total for the data feeds greater than or less than your workspace total?
The other thing to check is to make sure that you've replicated any conditions in workspace in your data feeds. For example, if you're using a VRS, make sure you replicate that logic. Also, workspace might be excluding hits/visits based on bots, exclusions based on IP, etc. If your data feeds total is now greater than your workspace total, it is possible that workspace is filtering out some visits that the data feeds are including.
Views
Replies
Total Likes
Based on your code I think this is happening based on the max(date) function you're using.
For example, if you have three people come to your site
1 visits Monday afternoon
1 visits Monday night just before midnight and continues into Tuesday
1 visits Tuesday morning
When you get your table you get
visit max date
1 monday
2 tuesday
Even though technically one visit was on Monday and Tuesday. This is because it will take the largest value and attribute the entire visit to that data instead of splitting it across both dates. Your individual rows in a workspace dashboard will have the visit attributed to both days, and then deduplicated in the table grand total.
Instead of using max date, try just using a date value, it would return a table like
visit day
2 monday
2 tuesday
If we continue using my example from above. It's up to you to decide if that is how you want to count visits, or if you only want to attribute them to a single day.
weirdly enough if i just run the query below, without date, for that one day i still get the same wrong unique visitor count:
AS WITH InitialAggregation AS (
SELECT
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
CONCAT(post_visid_high, post_visid_low)
)
SELECT
COUNT(unique_visitor_id) AS total_daily_unique_visitors,
SUM(visits) AS total_daily_visits
FROM
InitialAggregation
Views
Replies
Total Likes
Ok, so when you take out the max date limitation, is the total for the data feeds greater than or less than your workspace total?
The other thing to check is to make sure that you've replicated any conditions in workspace in your data feeds. For example, if you're using a VRS, make sure you replicate that logic. Also, workspace might be excluding hits/visits based on bots, exclusions based on IP, etc. If your data feeds total is now greater than your workspace total, it is possible that workspace is filtering out some visits that the data feeds are including.
Views
Replies
Total Likes