BigQuery ve Google Analytics SQL sorgusu

Question

Google Analytics verilerinden BigQuery'ye aktarılan bir tablodan bir matris oluşturmaya çalışıyorum. Tablo, url, zaman damgası vb. Gibi bazı özelliklerin yanı sıra session_ID'ler içeren bir web sitesindeki isabetleri temsil eder. Ayrıca, olay olarak adlandırdığımız kullanıcı tanımlı eylemlere dayalı bazı meta veriler de vardır. Aşağıda tabloya bir örnek verilmiştir.

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

Amaçlanan sonuç aşağıdaki tablo gibi olmalıdır.

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

Url_target'a yol açmayan herhangi bir olayın hedef dahil sıfır olarak gösterilmesi gerektiğini unutmayın. Bu, sorgunun herhangi bir olayın zaman damgasına bakarak url_target tarafından takip edilip edilmediğini kontrol etmek için zaman damgasına bakması gerektiği anlamına gelir. Örneğin, event2'nin ardından "url_target" gelmedi, bu yüzden onu sıfır olarak gösteriyoruz. Session_id 3'te de aynı durum, event2'nin ardından url_target gelmediğinden, url_target zaman damgasını event2'den önce değil sonra not edin. Bu nedenle sıfır olarak belirtilir.

Bu matrisi oluşturmak için SQL sorgusunun oluşturulmasında her türlü yardımdan memnun olurum. Yalnızca session_id'ye göre gruplayabildim ve ardından "count" kullanarak olayları sayabildim, ancak SQL yazma sorgusunu zaman damgasıyla eşleşecek ve diğer alanları kontrol edecek şekilde bulamadım.

Answer 1

İlk (veya son) hedef zamanı hesaplamak için bir alt sorgu kullanın. Sonra kullanın countif()ve bir araya getirin:

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

Düşünmek:

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