การสืบค้น BigQuery และ Google Analytics SQL

Question

ฉันกำลังพยายามสร้างเมทริกซ์จากตารางที่นำเข้าจากข้อมูล Google Analytics ไปยัง BigQuery ตารางนี้แสดงถึง Hit บนเว็บไซต์ที่มี session_ID ร่วมกับคุณสมบัติบางอย่างเช่น url การประทับเวลาเป็นต้นนอกจากนี้ยังมีข้อมูลเมตาบางส่วนที่อิงตามการกระทำที่ผู้ใช้กำหนดเองซึ่งเราอ้างถึงว่าเป็นเหตุการณ์ ด้านล่างนี้เป็นตัวอย่างของตาราง

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

ผลลัพธ์ที่ตั้งใจไว้ควรมีลักษณะดังตารางด้านล่าง

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

โปรดทราบว่าเหตุการณ์ใด ๆ ที่ไม่นำไปสู่ ​​url_target ควรแสดงเป็นศูนย์รวมถึงเป้าหมายด้วย ซึ่งหมายความว่าข้อความค้นหาควรพิจารณาถึงการประทับเวลาเพื่อตรวจสอบว่าเหตุการณ์ใด ๆ ตามมาด้วย url_target โดยดูจากการประทับเวลา ตัวอย่างเช่นเหตุการณ์ 2 ไม่ได้ตามด้วย "url_target" นั่นคือเหตุผลที่เราระบุว่าเป็นศูนย์ กรณีเดียวกันใน session_id 3 เนื่องจาก event2 ไม่ได้ตามด้วย url_target โปรดสังเกตการประทับเวลาของ url_target ซึ่งอยู่ก่อน event2 ไม่ใช่ตามหลัง ดังนั้นจึงแสดงว่าเป็นศูนย์

ฉันขอขอบคุณสำหรับความช่วยเหลือในการสร้างแบบสอบถาม SQL เพื่อสร้างเมทริกซ์นั้น ฉันสามารถจัดกลุ่มตาม session_id เท่านั้นจากนั้นดำเนินการนับเหตุการณ์โดยใช้ "count" แต่ไม่พบการเขียนแบบสอบถาม SQL เพื่อจับคู่กับการประทับเวลาและตรวจสอบช่องอื่น ๆ

Answer 1

ใช้แบบสอบถามย่อยเพื่อคำนวณเวลาเป้าหมายแรก (หรือสุดท้าย) จากนั้นใช้countif()และการรวม:

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

พิจารณา:

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