Consulta SQL de BigQuery y Google Analytics

Question

Estoy tratando de crear una matriz a partir de una tabla que se importa desde los datos de Google Analytics a BigQuery. La tabla representa visitas en un sitio web que contienen session_IDs junto con algunas propiedades como la URL, la marca de tiempo, etc. Además, hay algunos metadatos basados ​​en acciones definidas por el usuario a las que nos referimos como eventos. A continuación se muestra un ejemplo de la tabla.

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

El resultado deseado debería ser similar a la tabla siguiente.

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

Tenga en cuenta que cualquier evento que no lleve a url_target debe indicarse con ceros, incluido el destino. Esto significa que la consulta debe buscar en la marca de tiempo para verificar que cualquier evento sea seguido por url_target examinando su marca de tiempo. Por ejemplo, event2 no fue seguido por "url_target", por eso lo estamos denotando como ceros. El mismo caso en session_id 3, como event2 no fue seguido por url_target, tenga en cuenta la marca de tiempo de url_target que estaba antes del event2, no después. Por lo tanto denotado como ceros.

Agradecería cualquier ayuda en la construcción de la consulta SQL para producir esa matriz. Solo pude agrupar por session_id y luego realizar eventos de conteo usando "count", pero no pude encontrar la consulta SQL de escritura para que coincida con la marca de tiempo y verifique otros campos.

Answer 1

Utilice una subconsulta para calcular el primer (o último) tiempo objetivo. Luego usa countif()y agregación:

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