Consulta SQL do BigQuery e do Google Analytics

Question

Estou tentando construir uma matriz de uma tabela que é importada de dados do Google Analytics para o BigQuery. A tabela representa hits em um site que contém session_IDs ao lado de algumas propriedades, como url, timestamp etc. Além disso, existem alguns metadados com base em ações definidas pelo usuário que chamamos de eventos. Abaixo está um exemplo da tabela.

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

O resultado pretendido deve ser algo como a tabela abaixo.

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

Observe que qualquer evento que não leve a url_target deve ser denotado como zeros incluindo o destino. Isso significa que a consulta deve olhar para o carimbo de data / hora para verificar se todos os eventos são seguidos por url_target olhando para seu carimbo de data / hora. Por exemplo, event2 não foi seguido por "url_target", é por isso que o estamos denotando como zeros. Mesmo caso em session_id 3, como event2 não foi seguido por url_target, observe o carimbo de data / hora de url_target que estava antes do event2, não depois dele. Portanto, denotado como zeros.

Eu apreciaria qualquer ajuda na construção da consulta SQL para produzir essa matriz. Eu só consegui agrupar por session_id e, em seguida, realizar eventos de contagem usando "count", mas não consegui encontrar a consulta SQL de gravação para corresponder ao carimbo de data / hora e verificar outros campos.

Answer 1

Use uma subconsulta para calcular o primeiro (ou último) tempo alvo. Em seguida, use countif()e agregação:

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

Considerar:

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