Unable to align Visits for Referrer Type Dimension in UI to data from Data feed

[cj_mouse1]

Hi all,

I've been attempting to try to match the visits for a data feed to the adobe workspace UI while applying the Referrer Type Dimension and have been unsuccessful. I know that the UI only exposes the four referrer types and the data feed has additional, but my understanding is that the four that are exposed should still match. I've included a screenshot with our UI numbers, example query against our datafeed data, and our datafeed results.

Is there any custom logic I should be applying to match the UI? The number of visits matches exactly to the UI, so I believe I am pulling the correct window of data.

with static_values as (
  SELECT data.a as referrer_type, b as unique_visitors, c as visits
    FROM VALUES ('Search Engines', 2170246, 2232362),
    ('Social Networks', 216465, 231600),
    ('Typed/Bookmarked', 651960, 824445),
    ('Other Web Sites', 492606,520595) AS data(a, b, c)
), visit_level_referrer_derived as (select distinct computed_visit_id, computed_visitor_id,
visit_ref_type as referrer_type,
   first_value( visit_ref_type, True) over (partition by computed_visit_id order by date_time, referrer desc nulls last) as visit_ref_type
            from
     (select concat(visid_high, visid_low, visit_num) as computed_visit_id, case 
            when visit_ref_type = 1 then 'Inside Your Site'
            when visit_ref_type = 2 then 'Other Web Sites'
            when visit_ref_type = 3 then 'Search Engines'
            when visit_ref_type = 4 then 'Hard Drive'
            when visit_ref_type = 5 then 'USENET (newsgroups)'
            when visit_ref_type = 6 then 'Typed/Bookmarked'
            when visit_ref_type = 7 then 'Email'
            when visit_ref_type = 8 then 'No JavaScript' 
            when visit_ref_type = 9 then 'Social Networks'
            else concat(visit_ref_type, ' no match') end
            as visit_ref_type, date_time, referrer, concat(visid_high, visid_low) as computed_visitor_id
   FROM our_table
where year = 2024 and month = 07 and day between 14 and 16
and to_date(date_time) = '2024-07-15'
and suite='datafeed_suite'
and exclude_hit in (0,'') )
),
 referrer_agged as (
select referrer_type,  count(distinct computed_visitor_id) as unique_visitors, count(distinct computed_visit_id) as visits
 from visit_level_referrer_derived
 group by 1)

select r.referrer_type, s.visits as UI_Visits, r.visits as data_feed_visits, round((s.visits - r.visits)  / s.visits,4) * 100 * -1 as variance, sum(s.visits) over (partition by null) as ui_total_referral_visits,
format_number(sum(case when r.referrer_type in ('Other Web Sites','Typed/Bookmarked','Social Networks','Search Engines') then r.visits else 0 end) over (partition by null), 0) as data_feed_visits_match_ui_types, format_number(sum(r.visits) over (partition by null), 0) as actual_total_visits from referrer_agged r left join static_values s on r.referrer_type=s.referrer_type
order by 2 desc

I've read through this post but didn't see an answer for this scenario here. https://experienceleaguecommunities.adobe.com/t5/adobe-analytics-questions/how-to-get-the-quot-refer...