Requête SQL BigQuery et Google Analytics - question développée

Nov 19 2020

J'essaye de développer ma question répondue ici . donc, compte tenu des données:

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

Et le résultat actuel comme ci-dessous:

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

Je voudrais développer le résultat donné pour réfléchir aux cas où la cible est égale à zéro. Pourrais-je également annoter ces cas avec le nombre d'événements, quelles que soient les dates de vérification?

Ainsi, le nouveau résultat attendu serait le suivant:

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

Je suis particulièrement intéressé par session_id = 2 où il y a un certain nombre d'événements qui se produisent, sans url_target visité. Enfin, session_id = 3 est également un autre cas où je ne sais pas comment gérer! Comme il a un événement (event2), mais cela a été fait après avoir visité le url_target. Je devrais peut-être le désigner par target = 2, comme étant un cas particulier. Mais, si cela est difficile avec SQL, je le supprimerais du résultat et le garderais sous forme de zéros, comme le tableau de résultats prévu ci-dessus.

Merci beaucoup à l'avance pour vos contributions.

Réponses

1 GordonLinoff Nov 19 2020 at 22:51

D'après ce que vous décrivez, vous voulez une logique conditionnelle. Cela devrait fonctionner:

select session_id,
       countif((target_hit_timestamp > hit_timestamp or target_hit_timestamp is null) and category = 'event1') as event1,
       countif((target_hit_timestamp > hit_timestamp or target_hit_timestamp is null) > 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

Le target_hit_timestampest NULLs'il n'y a pas d'URL cible.