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 14, 2024

    Thanks @leocwlau , I tried it, dint got any output. However if you see below screen shot,  ",1," ",202," are appearing at the begging of the event list...  I just want to recite my query again here...  trying to build visit level segment having event3, Order event & v13 in the post_v13 because v13 is firing on Home page then on order conf page e3 & order events are firing.

    Resharing my SQL statement for better understanding: 

    SELECT
    SUM(CASE WHEN (Post_Event_List_Text LIKE '%,202,%' AND Post_Event_List_Text LIKE '%,1,%' AND Post_cart_abandonment_campaign_eVar13 ='Cart Abandonment campaign') THEN 1 ELSE 0 END) as e3

    FROM
        table-name
    WHERE -- Segmenting the visit 
        (Post_Vis_High_Identifier,
        Post_Vis_Low_Identifier,
        Visit_Number,
        Visit_Start_Gmt_Timestamp  -- hoping something needs to be added here
        )
    IN(
       Select
       DISTINCT Post_Vis_High_Identifier,
        Post_Vis_Low_Identifier,
        Visit_Number,
        Visit_Start_Gmt_Timestamp -- hoping something needs to be added here
        FROM  table-name
    WHERE
        Exclude_Hit_Number = 0
        AND Hit_Source_Identifier = 1
        AND Duplicate_Purchase_Flag = 0
        AND year(Visited_Timestamp) = 2024
        AND month(Visited_Timestamp) = 8
      --  AND day(Visited_Timestamp) = 12
        AND (CONCAT(',',Post_Event_List_Text,',') LIKE "%,202,%"
        AND CONCAT(',',Post_Event_List_Text,',') LIKE "%,1,%")
        AND Post_B2bi_Status_Text = FALSE
        AND Post_cart_abandonment_campaign_eVar13 = 'Cart Abandonment campaign'
        AND Order_Identifier IS NOT NULL
        AND Order_Identifier NOT LIKE '1%'
     )
    When I run above query, getting e3 = 538,,  however in Adobe WS, I am getting 442

     


    It is always tricky to reproduce the exact results with Data Feed raw data against the AA WS UI, as we need to make sure to have the exact segments/metrics logic.

    From the latest screen, I noted the segment name mentioned "(v13) Instances exists" which I don't see in the SQL.

    Can you share the screenshot of those segments/metrics definitions with me? Please DM me if there is anything sensitive and you don't want to share publicly.

      Terms & ConditionsAccessibility statement

      Loading footer...