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...

[Damonwhall]

Hmmm, 

It looks like you're trying to match the visits from Adobe Analytics' Workspace UI to what you're getting from the data feed using the Referrer Type dimension. The visits total matches perfectly, so you're confident you're pulling the correct data window. But the breakdown into the four UI categories—Search Engines, Typed/Bookmarked, Other Web Sites, and Social Networks—isn't matching up.

I have run into referrer issues before too.  

I explained to ChatGPT your problem and it said: 

• Isolate the Problem

  • Compare the total visits across all referrer types to see if they match. If the totals match, the issue is likely with the breakdown (not the overall logic).
  • Run your query for a single day (e.g., to_date(date_time) = '2024-07-15') to make debugging easier.

• Look for Sessions with Discrepancies

  • Identify sessions where the visit_ref_type in your data feed doesn’t match what you see in the UI. Log or output these sessions to find patterns.

• Check Variances

  • Your variance calculation is a good start. Focus on categories with high variance to figure out where the mismatch is occurring.

• Adjust Referrer Logic

  • If the UI applies a specific prioritization within a session, you may need to modify the first_value logic. For example, if Adobe prioritizes Search Engines over Other Web Sites when both occur in a session, you could tweak your query.

Try this: 

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 = 6 then 'Typed/Bookmarked'
                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
  where visit_ref_type in (2, 3, 6, 9) -- Exclude non-UI categories
  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 () 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 (), 0) as data_feed_visits_match_ui_types, 
       format_number(sum(r.visits) over (), 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;