Level 3

Solved

Creating Visit segment for Data feed raw data

9 replies
1690 views
Translate

Hi Team,

Can you please help me Creating Visit Level segment for Data feed raw data? for instance: Segment visits having eVar9 = "campaign"

Analytics

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

leocwlauAccepted solution

New Participant

Hi @dineshrkumar , you need a subquery to find distinct visid_high, post_visid_low, visit_num, visit_start_time_gmt (which identifies a visit) with eVar9 = 'campaign' so as to further filter for any other condition.

select
  *  -- or any others
from
  hit_data
where
  (
    visid_high,
    post_visid_low,
    visit_num,
    visit_start_time_gmt
  ) in (
    select
      distinct visid_high,
      post_visid_low,
      visit_num,
      visit_start_time_gmt
    from
      hit_data
    where
      eVar9 = 'campaign'
  )
-- and any other conditions

leocwlauAccepted solution

New Participant

Hi @dineshrkumar , you need a subquery to find distinct visid_high, post_visid_low, visit_num, visit_start_time_gmt (which identifies a visit) with eVar9 = 'campaign' so as to further filter for any other condition.

select
  *  -- or any others
from
  hit_data
where
  (
    visid_high,
    post_visid_low,
    visit_num,
    visit_start_time_gmt
  ) in (
    select
      distinct visid_high,
      post_visid_low,
      visit_num,
      visit_start_time_gmt
    from
      hit_data
    where
      eVar9 = 'campaign'
  )
-- and any other conditions

Translate

DineshRkumarAuthor

Level 3

Thanks @leocwlau , We don't see

visid_high,
      post_visid_low,
      visit_num,
      visit_start_time_gmt

from data feed raw data file, however we have

"Post_Vis_High_Identifier,

Post_Vis_Low_Identifier,

Visit_Number,

Visit_Start_Gmt_Timestamp" so I am using the same. This giving me the count, but it is not matching with Adobe workspace numbers.

Query:

SELECT

SUM(CASE WHEN Post_Event_List_Text LIKE '%,202,%' THEN 1 ELSE 0 END) as e3,SUM(CASE WHEN Post_Event_List_Text LIKE '%,1,%' THEN 1 ELSE 0 END) as Order

FROM

table_name

WHERE

(Post_Vis_High_Identifier,

Post_Vis_Low_Identifier,

Visit_Number,

Visit_Start_Gmt_Timestamp

)

IN(

Select

DISTINCT Post_Vis_High_Identifier,

Post_Vis_Low_Identifier,

Visit_Number,

Visit_Start_Gmt_Timestamp

FROM table_name

WHERE

Exclude_Hit_Number = 0

AND Hit_Source_Identifier = 1

year(Visited_Timestamp) = 2024

AND month(Visited_Timestamp) = 8

AND Post_Event_List_Text LIKE "%,202,%"

AND Post_Event_List_Text LIKE "%,1,%"

)
Expected count is e3 = 465, actual = 538. Please suggest me here

Translate

leocwlau

New Participant

Hi @dineshrkumar, my bad that it should be "post_visid_high" in the original sample code.

The LIKE on post_event_list_text could be a reason for the mismatch, with the %,xxx,%, you are filtering 202 and 1 in the middle of the string and excluding any of them as the first or last one. I have a post, https://leocwlau.com/2022/converting-event_list-and-post_event_list-in-adobe-analytics-data-feed/, on how to extract events and even with values into corresponding columns for better data consumption.

Of course, for your particular case, you can try to use OR to check for cases where 202 and 1 are the first or last events in the event list.

Translate

Jennifer_Dungan

Level 2

Do you mean help write a SQL to return visits that have eVar9 = "campaign"?

Translate

Sign up

Login with SSO

Login to the community

Login with SSO