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