unique visitor count from data feeds dont match the dashboard

[EnsarDo]

Hi,

i have about 2 years of hit data from adobe, I am trying to match the daily unique visitor count from the hit data with the dashboard. my problem is that some days for the unique visitors count match and others dont, why is this? for example some days they would both be 300,003 unique visitors and other days the hit data would show 300,000 and the dash board would show 300,003.

below is my query to get daily unique visitor count from the hit data:

AS WITH InitialAggregation AS (
    SELECT
        date_time AS date,
        CONCAT(post_visid_high, post_visid_low) AS unique_visitor_id,
    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 unique_visitors,
FROM
    InitialAggregation
GROUP BY
    date;

[Jennifer_Dungan]

Given the complexity of the rules that Adobe applies to the data (and that how to process raw data exactly the same way isn't really disclosed); I would say if your numbers are off by 3 on approximately 300K users (0.001% off) I would take that as a win and call it a day to be honest.

I am not sure that anyone here would be able to find that tiny variance in logic to get those counts to match 100% all the time, since we all deal with minor mis-matches....

[EnsarDo]

sometimes the unique visitors calculated from my hit data is less than what is showing on the dashboard, why is that? 

[MandyGeorge · Accepted solution]

When you're calculating unique visitor/visit/hit IDs, it's a good idea to also bring in "first_hit_time_gmt", reason being that sometimes Adobe reuses values in post_visid_high and post_visid_low. It doesn't happen often, but I have seen it happen. It's possible that might be your 3 visitors.

Like Jen said though, that small of a difference is nothing to be concerned about.