Hi Team,
Can you please help me Creating Visit Level segment for Data feed raw data? for instance: Segment visits having eVar9 = "campaign"
Solved! Go to Solution.
Views
Replies
Total Likes
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
Do you mean help write a SQL to return visits that have eVar9 = "campaign"?
Views
Replies
Total Likes
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
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
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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.
Views
Replies
Total Likes
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'
Views
Replies
Total Likes
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:
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.
Thanks @leocwlau , @Jennifer_Dungan for your valuable suggestions & codes. I am able to get numbers matching with Adobe WS.