Query SQL di BigQuery e Google Analytics

Question

Sto cercando di creare una matrice da una tabella importata dai dati di Google Analytics in BigQuery. La tabella rappresenta gli hit su un sito Web che contengono session_ID insieme ad alcune proprietà come l'URL, il timestamp ecc. Inoltre, ci sono alcuni metadati basati su azioni definite dall'utente a cui ci riferiamo come eventi. Di seguito è riportato un esempio della tabella.

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

Il risultato previsto dovrebbe essere qualcosa di simile alla tabella sottostante.

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

Nota che qualsiasi evento che non porta a url_target dovrebbe essere indicato come zeri incluso il target. Ciò significa che la query dovrebbe esaminare il timestamp per verificare che eventuali eventi siano seguiti da url_target esaminando il loro timestamp. Ad esempio, event2 non era seguito da "url_target", ecco perché lo denotiamo come zeri. Stesso caso in session_id 3, poiché event2 non era seguito da url_target, annota il timestamp di url_target che era prima di event2, non dopo. Quindi indicato come zeri.

Apprezzerei qualsiasi aiuto nella costruzione della query SQL per produrre quella matrice. Sono stato in grado di raggruppare solo per session_id e quindi eseguire il conteggio degli eventi utilizzando "count", ma non sono stato in grado di trovare la query SQL di scrittura da confrontare con il timestamp e controllare altri campi.

Answer 1

Utilizza una sottoquery per calcolare il primo (o l'ultimo) tempo target. Quindi usa countif()e aggregazione:

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

Ritenere:

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