Creating Visit segment for Data feed raw data | Community
Skip to main content
DineshRkumar
DineshRkumar
Level 3
October 1, 2024
Solved

Creating Visit segment for Data feed raw data

  • October 1, 2024
  • 2 replies
  • 1696 views

Hi Team,

 

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

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

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

2 replies

Jennifer_Dungan
Community Advisor and Adobe Champion
Jennifer_DunganCommunity Advisor and Adobe Champion
Community Advisor and Adobe Champion
October 2, 2024

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

leocwlau
Community Advisor and Adobe Champion
leocwlauCommunity Advisor and Adobe ChampionAccepted solution
Community Advisor and Adobe Champion
October 2, 2024

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
    DineshRkumar
    DineshRkumarAuthor
    Level 3
    October 3, 2024

    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
    leocwlau
    Community Advisor and Adobe Champion
    leocwlauCommunity Advisor and Adobe Champion
    Community Advisor and Adobe Champion
    October 8, 2024

    Hi @leocwlau , both event3 & Order event 1 fire on order confirmation page, so it's okay about there placement. I think there is something needs to be added in the below visit segment like adding event list or eVar instances..  I have tired it but getting output mismatch error.

    WHERE
        (Post_Vis_High_Identifier,
        Post_Vis_Low_Identifier,
        Visit_Number,
        Visit_Start_Gmt_Timestamp
        )

    please try

    select post_event_list_text from table_name where post_event_list_text like '202,%' or post_event_list_text like '%,202' or post_event_list_text like '1,%' or post_event_list_text like '%,1'
    Terms & ConditionsAccessibility statement

    Loading footer...