Expand my Community achievements bar.

SOLVED

Creating Visit segment for Data feed raw data

Avatar

Level 3

Hi Team,

 

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

1 Accepted Solution

Avatar

Correct answer by
Community Advisor and Adobe Champion

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

View solution in original post

9 Replies

Avatar

Community Advisor and Adobe Champion

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

Avatar

Correct answer by
Community Advisor and Adobe Champion

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

Avatar

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

Avatar

Community Advisor and Adobe Champion

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.

Avatar

Level 3

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
    )

Avatar

Community Advisor and Adobe Champion

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'

Avatar

Level 3

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.

DineshRkumar_0-1728376076514.png

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
DineshRkumar_1-1728376857167.png

 

Avatar

Community Advisor and Adobe Champion

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.

Avatar

Level 3

Thanks @leocwlau , @Jennifer_Dungan  for your valuable suggestions & codes. I am able to get numbers matching with Adobe WS.