Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Unique Visitors

jdcohen1990
Level 1
Level 1

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;

1 Accepted Solution
Abhijeet_Mishra
Correct answer by
Employee
Employee

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);

View solution in original post

3 Replies
Abhijeet_Mishra
Correct answer by
Employee
Employee

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);

View solution in original post

jdcohen1990
Level 1
Level 1

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
Level 1
Level 1

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!