Comment faire une requête BigQuery correspondant au segment Sequences dans Google Analytics ?

Dans Google Analytics, il est possible de créer facilement des segments de sequence.
Nous allons voir comment reproduire la requête BigQuery correspondante.
Nous allons prendre l'exemple d'une sequence avec "/page1/", puis "/page2/", puis "/page3/" (en acceptant qu'il ait pu y avoir d'autres pages entre chaque etape).

La méthode pour créer la requête BigQuery correspondante est de :

• Commencer par faire une requete de base ayant toutes les variables necessaires
• Puis, à partir de cette base, prendre les hits des sessions qui ont fait l'etape 1
• Ensuite, pour l'etape 2, on calcule le hitNumber minimum de l'etape 1 de chaque session. Et on filtre pour ne conserver que les sessions qui ont fait l'etape 2 après l'etape 1 (à l'aide du hitNumber de l'etape 2 et du hitNumber minimum calculé pour l'etape 1)
• Pour les étapes suivantes, il suffit de reproduire la même logique que pour l'etape 2

#standardSQL
WITH
raw_data as (
SELECT
date as r_date,
fullVisitorId AS r_visitor_id,
CONCAT(date,'-',fullVisitorId,'-',CAST(visitId AS STRING)) AS r_session_id,
hits.hitNumber as r_hitNumber,
hits.type as r_hitType,
hits.page.pagePath as r_page
FROM `moncompte.12345678.ga_sessions_*`, UNNEST(hits) as hits
WHERE _TABLE_SUFFIX BETWEEN '20191001' AND '20191031' 
),
q_sessions_et0 as (
SELECT *
FROM raw_data
WHERE r_hitType = 'PAGE'
),
q_sessions_et1 as (
SELECT *
FROM q_sessions_et0
WHERE REGEXP_CONTAINS(r_page, '/page1/') = TRUE
),
q_sessions_et2_aidePourMinHitNumber as (
SELECT 
r_session_id,
MIN(r_hitNumber) AS minHitNumber
FROM q_sessions_et1
WHERE REGEXP_CONTAINS(r_page, '/page1/') = TRUE
GROUP BY r_session_id
),
q_sessions_et2 as (
SELECT 
a.r_date,
a.r_session_id
FROM q_sessions_et1 a
LEFT JOIN q_sessions_et2_aidePourMinHitNumber b
ON a.r_session_id=b.r_session_id
WHERE a.r_hitnumber > b.minHitnumber AND REGEXP_CONTAINS(r_page, '/page2/') = TRUE
),
q_sessions_et3_aidePourMinHitNumber as (
SELECT 
r_session_id,
MIN(r_hitNumber) AS minHitNumber
FROM q_sessions_et1
WHERE REGEXP_CONTAINS(r_page, '/page2/') = TRUE
GROUP BY r_session_id
),
q_sessions_et3 as (
SELECT 
a.r_date,
a.r_session_id
FROM q_sessions_et2 a
LEFT JOIN q_sessions_et3_aidePourMinHitNumber b
ON a.r_session_id=b.r_session_id
WHERE a.r_hitnumber > b.minHitnumber AND REGEXP_CONTAINS(r_page, '/page3/') = TRUE
)

SELECT r_date, COUNT(DISTINCT r_session_id) as sessions
FROM q_sessions_et3
group by r_date
order by r_date