Unique visitors count is wrong

[EnsarDo]

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;

[MandyGeorge]

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. 

[EnsarDo]

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

[MandyGeorge · Accepted solution]

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.