Creating Visit Segment in SQL

[DineshRkumar]

Hi All, 

I am trying to create a visit segment to filter out visit having event3(,202,), event 35(,234,) and eVar13 exist. 

@Jen

[Jennifer_Dungan]

Hi @DineshRkumar,

So you want the visit to include event3 and event35 and eVar13 to exist? Do all of these need to be on the same hit? Or any time in the Visit?

Examples:

Scenario 1:

• Page A
  • eVar13 set to "something"
  • eVar13 instance counted
• Page B
  • eVar13 retains value "something"
  • eVar13 instance is not counted
• Page C
  • eVar13 retains value "something"
  • eVar13 instance is not counted
  • event3 counted
• Page D
  • eVar13 retains value "something"
  • eVar13 instance is not counted
  • event35 counted

Scenario 2:

• Page A
• Page B
• Page C
  • eVar13 = "something"
  • eVar13 instance counted
  • event3 counted
  • event35 counted
• Page D
  • eVar13 retains value "something"
  • eVar13 instance is not counted

Scenario 3:

• Page A
• Page B
  • eVar13 = "something"
  • eVar13 instance counted
• Page C
  • eVar13 retains value "something"
  • eVar13 instance is not counted
  • event3 counted
  • event35 counted
• Page D
  • eVar13 retains value "something"
  • eVar13 instance is not counted

Also, depending on the attribution settings of eVar13 (if it's set to something greater than Visit, maybe it's set to Week or Month, etc), you might have:

Scenario 4:

Visit 1

• Page A
• Page B
  • eVar13 set to "something"
  • eVar13 instance counted
• Page C
  • eVar13 retains value "something"
  • eVar13 instance is not counted

Visit 2

• Page D
  • eVar13 retains value "something"
  • eVar13 instance is not counted
• Page E
  • eVar13 retains value "something"
  • eVar13 instance is not counted
  • event3 counted
• Page F
  • eVar13 retains value "something"
  • eVar13 instance is not counted
  • event35 counted

So in all the scenarios the visit contains all three, but only scenario 2 has them on the same hit.... scenario 3 has a value and the events on the same hit (but not the instance), and scenario 4, has the value of eVar13 included, but not the instance (the actual setting of the value) and the two events on different hits within the visit.

Your sample segment would return scenarios 1 and 2 and 3, but would not return Visit 2 in Scenario 3, since there is no "instance" being counted in the visit.

If the same hit matters, and it needs to be the instance of the eVar, as in you only want scenario 2 to be returned, you can use:

VISIT [

    HIT Container [

        event3 exists

        AND

        event35 exists

        AND

        eVar13 Instance exists

    ]

]

If you need to return visits where your eVar has a value (but not necessarily the instance), and the events are on the same hit, so scenarios 2 and 3, you can use:

VISIT [

    HIT Container [

        event3 exists

        AND

        event35 exists

        AND

        eVar13 exists

    ]

]

(note this is very similar to above, but I am not looking at the instance, only at if eVar13 has a value.)

If you are looking for Visits that have a value in eVar13 (but not necessarily the instance during the visit), but the three don't have to be on the same hit... so returning Scenarios 1, 2, 3 and 4 (visit 2), then the segment will be similar to yours, but looking at the value of eVar13 instead of the instance:

VISIT [

    event3 exists

    AND

    event35 exists

    AND

    eVar13 exists

]

I hope this helps.

[Jennifer_Dungan]

Oops, sorry, I missed the "SQL" in the title, I only noticed it after I submitted....

I don't generally write the SQL on my team... but I would think if you were replicating something like you have above... looking at the visit having event3, event35 and the instance of the eVar13 in the same visit... you would probably need to use some nested logic....

Identify visits based on visit num and unique users, then looking at the hits inside of those vlsits to contain the three specific events... 

I am no SQL engineer, but I might approach it but running an inner query to create a named column for each of the events, one to identify event3, one for event35 and one for the instance of eVar13... if within the visit, all three columns have "true" then return the hits for the visit?

Someone who writes SQL on a daily basis may be more help....

Or maybe this article could help:

https://trevorwithdata.com/using-adobe-analytics-data-feeds-and-sql-for-basic-reporting/

[DineshRkumar]

Thanks @Jennifer_Dungan  for your prompt response. I have been to those sites & have used it. However, still my SQL query is not giving the desired output.

[Jennifer_Dungan]

Can you share your SQL statement? There might be something we can see that might help?

[DineshRkumar]

Here you  go... trying to match e3 Adobe workspace count 465,  but getting 538..

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-masked

WHERE

    (Post_Vis_High_Identifier,

    Post_Vis_Low_Identifier,

    Visit_Number,

    Visit_Start_Gmt_Timestamp

    )

IN(

   Select

    Post_Vis_High_Identifier,

    Post_Vis_Low_Identifier,

    Visit_Number,

    Visit_Start_Gmt_Timestamp

    FROM table-name-masked

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 (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 != ''

    AND Order_Identifier IS NOT NULL

    AND Order_Identifier NOT LIKE '1%'

)

[Jennifer_Dungan]

I'm not sure, but this kind of looks like your query is checking for all the conditions on the same hit? As opposed to being across different hits in the same visit.

Also, your SQL is looking at the post_value of eVar13, and not the "eVar13 Instance" as you had in your segment....