Requête SQL BigQuery et Google Analytics

Question

J'essaie de créer une matrice à partir d'une table importée de données Google Analytics dans BigQuery. Le tableau représente les hits sur un site Web qui contiennent des identifiants de session à côté de certaines propriétés telles que l'url, l'horodatage, etc. De plus, il existe des métadonnées basées sur des actions définies par l'utilisateur que nous appelons des événements. Voici un exemple du tableau.

session_id  hit_timestamp   url event_category
1           11:12:23        url134      event1
1           11:14:23        url2234     event2
1           11:16:23        url_target  null
2           03:12:11        url2344     event1
2           03:14:11        url43245    event2
3           09:10:11        url5533     event2
3           09:09:11        url_target  null
4           08:08:08        url64356    event2
4           08:09:08        url56456    event2
4           08:10:08        url_target  null

Le résultat attendu devrait être quelque chose comme le tableau ci-dessous.

session_id  event1  event2  target
1           1       1       1
2           0       0       0
3           0       0       0
4           0       2       1

Notez que tout événement ne conduisant pas à url_target doit être noté comme des zéros, y compris la cible. Cela signifie que la requête doit examiner l'horodatage pour vérifier que tous les événements sont suivis par url_target en examinant leur horodatage. Par exemple, event2 n'a pas été suivi de "url_target", c'est pourquoi nous le désignons par des zéros. Même cas dans session_id 3, car event2 n'a pas été suivi par url_target, notez l'horodatage de url_target qui était avant event2, pas après. Par conséquent désigné par des zéros.

J'apprécierais toute aide dans la construction de la requête SQL pour produire cette matrice. Je n'ai pu regrouper que par session_id, puis effectuer le comptage des événements en utilisant "count", mais je n'ai pas été en mesure de trouver la requête SQL d'écriture pour correspondre à l'horodatage et vérifier d'autres champs.

Answer 1

Utilisez une sous-requête pour calculer la première (ou la dernière) heure cible. Ensuite, utilisez countif()et agrégation:

select session_id,
       countif(target_hit_timestamp > hit_timestamp and category = 'event1') as event1,
       countif(target_hit_timestamp > hit_timestamp and category = 'event2') as event2,
       countif(url like '%target') as target
from (select t.*,
             min(case when url like '%target' then hit_timestamp end) over (partition by session_id) as target_hit_timestamp
      from t
     ) t
group by session_id

Answer 2

Considérer:

select session_id,
    countif(cnt_url_target > 0 and event_category = 'event1') event1,
    countif(cnt_url_target > 0 and event_category = 'event2') event2,
    countif(url = 'url_target') target
from (
    select t.*,
        countif(url = 'url_target') over(partition by session_id order by hit_timestamp desc) cnt_url_target
    from mytable t
) t
group by session_id