Hi @StefanBaumgart
There are probably a few ways this can be done, but what I would do is create a visit key to define the visit from the feed, then create an identifier for hit order, then a flag for the first condition and a flag the second condition then join visits that match the first condition then the second.
You can find a reference guide of the fields on this page:
https://experienceleague.adobe.com/en/docs/analytics/export/analytics-data-feed/data-feed-contents/d...
So the steps would be something like this:
Create a CTE and name it something like base_hits it can then be referenced for the other CTEs, this should be from your adobe data feed. This should include:
The visit ID:
CONCAT(post_visid_high, '-', post_visid_low, '-', visit_num, '-', visit_start_time_gmt) AS visit_id
Columns used for hit order:
hit_time_gmt,
hitid_high,
hitid_low,
Flag for Condition A
CASE
WHEN
/* <<< PUT STEP A CONDITIONS HERE >>> */
THEN 1
ELSE 0
END AS is_step_a,
Flag for Condition B
CASE
WHEN
/* <<< PUT STEP B CONDITIONS HERE >>> */
THEN 1
ELSE 0
END AS is_step_b,
Then create a CTE to order the hits & an other for the first condition hits.
Create an order hits CTE:
ordered_hits AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY visit_id
ORDER BY hit_time_gmt, hitid_high, hitid_low
) AS hit_ordinal
FROM base_hits
),
Create a CTE to identify the first condition
per_visit AS (
SELECT
visit_id,
MIN(CASE WHEN is_step_a = 1 THEN hit_ordinal END) AS first_a_ordinal
FROM ordered
GROUP BY visit_id
)
Then join the first-condition CTE to the ordered hits using the Visit ID, filtering to hits that satisfy the second condition and have a hit order greater than the first occurrence of the initial condition.
Join the first condition with the second
SELECT DISTINCT o.visit_id
FROM ordered o
JOIN per_visit v
ON o.visit_id = v.visit_id
WHERE
v.first_a_ordinal IS NOT NULL
AND o.is_step_b = 1
AND o.hit_ordinal > v.first_a_ordinal;
This should then produce a set of visits that satisfy the sequential segment.
Hope this helps,
Dan
