BigQuery + GA4: Cách để lấy sự kiện đầu tiên hoặc gần đây nhất cho một người dùng.

Khi làm việc với dữ liệu Google Analytics 4 trong BigQuery, việc phân tích hoạt động đầu tiên hoặc gần đây nhất của một người dùng là rất phổ biến. Ví dụ:

• Thời gian đã trôi qua kể từ lần đăng nhập cuối cùng của mỗi người dùng là bao lâu?
• Đối với mỗi người dùng, khoảng thời gian đã trôi qua giữa phiên đầu tiên và gần đây nhất của họ là bao lâu?

Mục đích của bài viết ngắn này là chia sẻ cách làm điều này bằng cách sử dụng câu lệnh OVER clause.

Ví dụ

Hãy để tôi bắt đầu bằng việc chia sẻ một ví dụ đầy đủ, sau đó tôi sẽ giải thích cú pháp:

# For each user_id, pull data from the most recent login event

SELECT
    user_id,
    most_recent_login
FROM (
    SELECT
        user_id,
        event_date AS most_recent_login,
        ROW_NUMBER() OVER(
            PARTITION BY user_id 
            ORDER BY event_timestamp DESC
        ) AS row_num
    FROM `<project id>.<dataset>.events_<date>`
    WHERE event_name="login")
WHERE row_num=1

Mục tiêu của truy vấn này là tìm ngày đăng nhập gần đây nhất cho mỗi người dùng. Để làm được điều này, truy vấn thực hiện các bước sau:

1. Trích xuất tất cả các sự kiện có tên sự kiện là “login” (WHERE event_name = “login”).
2. Nhóm chúng theo user_id (PARTITION BY user_id).
3. Sử dụng event_timestamp để sắp xếp các sự kiện theo thứ tự diễn ra (ORDER BY event_timestamp DESC) và gán cho chúng một số hàng (row number)
4. Loại bỏ tất cả các hàng ngoại trừ hàng có số hàng bằng 1

Cú pháp của mệnh đề OVER

Mệnh đề OVER rất hữu ích để phân tích dữ liệu cấp sự kiện được tạo ra bởi Google Analytics 4, bởi vì nó giúp bạn áp dụng một hàm chỉ vào các hàng mà bạn chỉ định, và theo thứ tự mà bạn chỉ định.

Dưới đây là cú pháp của nó:

<function> OVER (   [PARTITION BY]
                    [ORDER BY]  )

PARTITION BY = Đây là cột mà bạn muốn sử dụng để tạo các phân nhóm con của dữ liệu để phân tích (tương tự như việc sử dụng GROUP BY).

Trong ví dụ của tôi, tôi đã phân chia theo user_id (định danh Google Analytics 4 cho người dùng đã đăng nhập), nhưng nếu bạn phân tích dữ liệu cho người dùng chưa đăng nhập, bạn nên thay thế điều này bằng user_pseudo_id.

ORDER BY = Đúng như tên gọi, điều này cho phép bạn điều khiển thứ tự các hàng sẽ được đánh giá bởi hàm của bạn.

Trong trường hợp của tôi, tôi đã đặt thứ tự sử dụng event_timestamp DESC để thời gian gần đây nhất được đặt lên đầu, nhưng bạn có thể thay thế “DESC” bằng “ASC” để liệt kê các hàng của bạn theo thứ tự tăng dần.

Nguồn: https://www.ken-williams.com/guide/reporting-analysis/bigquery-ga4-how-to-get-the-first-or-most-recent-event-for-a-user