Unique Visitors

[jdcohen1990]

I am attempting to pull a unique visitors report directly from our Adobe Analytics data as opposed to using the online report generator. In doing so, I made sure to filter for the recommended values based on this page: Identifying Visitors

However, the results of my queries are showing about 10% more unique visitors than in the online tool. Are there any additional metrics I should be filtering on?

Thanks in advance,

Jason

Below is the query I used to pull the unique visitors:

SELECT post_pagename,

(COUNT(DISTINCT(post_visid_high))),

(COUNT(DISTINCT(post_visid_low))),

(COUNT(DISTINCT(post_visid_high))  + COUNT(DISTINCT(post_visid_low))) AS visitors

FROM DB.table

WHERE date_time BETWEEN ('2017-11-08 00:00:00') AND ('2017-11-08 11:59:59')

AND exclude_hit = 0

AND hit_source NOT IN (5,7,8,9)

GROUP BY post_pagename

ORDER BY visitors DESC;

[Abhijeet_Mishra · Accepted solution]

If you are just looking for Unique Visitors count, please try this:

SELECT COUNT(DISTINCT post_visid_high, post_visid_low) AS count_uv FROM

FROM DB.table

WHERE date_time BETWEEN ('2017-11-08 00:00:00') AND ('2017-11-08 11:59:59')

AND exclude_hit = 0

AND hit_source NOT IN (5,7,8,9);

[jdcohen1990]

Do you have any tips on weekly unique visitors via querying clickstream data feed? I was able to get the daily query above to work perfectly via the where clause:

WHERE date_time LIKE ('2017-11-18%'), but when I use something like BETWEEN ('2017-11-08 00:00:00') AND ('2017-11-08 23:59:59') for the weekly version, the counts come back lower than expected (~9%). Again, thanks for the help!

Jason

[jdcohen1990]

Do you have any tips on weekly unique visitors via querying clickstream data feed? I was able to get the daily query above to work perfectly via the where clause:

WHERE date_time LIKE ('2017-11-18%'), but when I use something like BETWEEN ('2017-11-08 00:00:00') AND ('2017-11-08 23:59:59') for the weekly version, the counts come back lower than expected (~9%). Again, thanks for the help!