BigQuery Kullanarak Cohort Analizi Nasıl Yapılır?

Cohort analizi, belirli bir özelliğe sahip kişilerin oluşturduğu grupların (aynı yılda doğanlar, aynı işte çalışanlar, aynı tarihte üye olanlar) zaman içindeki davranışlarını analiz etmek için kullanılır.

Bu yazıda, GA4 veri setini kullanarak BigQuery ile cohort analizi yapmayı adım adım anlatacağız. Kullanıcıların ilk ziyaretleri ve tekrar eden ziyaretlerini inceleyerek geri dönüş oranlarını hesaplayacağız.

GA4 ve BigQuery kullanarak cohort analizi yaparken izlenecek adımlara Cohort Analizi Nedir ve Nasıl Yapılır? GA4’da Cohort Analizi yazımızdan ulaşabilirsiniz

1. Veri Setinde Kullanacağımız Temel Bilgiler

Veri seitnde kullanıcı kimliğini ve zaman bilgisini alacağınız parametrelerin neler olduğunu belirlememiz gerekir.
GA4 veri setinde user_pseudo_id alanı kullanıcı kimliği event_timestamp alanı ise kullanıcıların zaman bilgilerini hesaplamamız için kullanacağımız parametreler olacaktır.

Herkese açık bir GA4 veri seti kullanıyoruz. Bu veri setinde sadece 2020 Ekim – 2021 Ocak arası dolu. Siz kod bloğundaki veriyi kendi veri setinize göre düzenleyebilirsiniz.

2. Cohort Analizinde Kullanılan Sütunlar

Tablodaki verileri doğru yorumlayabilmek için Query’de nitelendirdiğimiz sütunların anlamlarını bilmelisiniz:

📌 cohort_date: Kullanıcının ilk kez siteye giriş yaptığı tarih.
📌 day sütunları: Kullanıcının aktivite tarihi ile cohort_date arasındaki gün farkı.

3. Cohort Analizi için BigQuery Sorgusu

Aşağıdaki BigQuery sorgusu, GA4 veri setinde cohort analizi yapmak için kullanılabilir.

WITH user_activity AS (
  SELECT
    DISTINCT DATE(TIMESTAMP_MICROS(event_timestamp)) as activity_date, user_pseudo_id
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE TIMESTAMP_MICROS(event_timestamp) < TIMESTAMP_ADD(PARSE_TIMESTAMP("%Y%m%d", '20250131'), INTERVAL 1 DAY)
  AND _TABLE_SUFFIX BETWEEN '20241001' AND '20250131'
 
),
user_cohorts AS (
  SELECT
    activity_date,
    FIRST_VALUE(activity_date) OVER (PARTITION BY user_pseudo_id ORDER BY activity_date) as cohort_date,
    user_pseudo_id
  FROM user_activity
),
cohort_retention AS (
  SELECT
    cohort_date,
    DATE_DIFF(activity_date, cohort_date, DAY) AS days_since_first_seen,
    COUNT(DISTINCT user_pseudo_id) AS users
  FROM user_cohorts
  GROUP BY cohort_date, days_since_first_seen
)
SELECT
  cohort_date,
  SUM(CASE WHEN days_since_first_seen = 0 THEN users END) AS day_0,
  SUM(CASE WHEN days_since_first_seen = 1 THEN users END) AS day_1,
  SUM(CASE WHEN days_since_first_seen = 2 THEN users END) AS day_2,
  SUM(CASE WHEN days_since_first_seen = 3 THEN users END) AS day_3,
  SUM(CASE WHEN days_since_first_seen = 4 THEN users END) AS day_4,
  SUM(CASE WHEN days_since_first_seen = 5 THEN users END) AS day_5,
  SUM(CASE WHEN days_since_first_seen = 6 THEN users END) AS day_6,
  SUM(CASE WHEN days_since_first_seen = 7 THEN users END) AS day_7,
  SUM(CASE WHEN days_since_first_seen = 14 THEN users END) AS day_14,
  SUM(CASE WHEN days_since_first_seen = 30 THEN users END) AS day_30,
  SUM(CASE WHEN days_since_first_seen = 60 THEN users END) AS day_60
FROM cohort_retention
GROUP BY cohort_date
ORDER BY cohort_date

Bu sorgunun işleyişi:
1) İlk adımda, user_activity tablosu oluşturulur ve benzersiz kullanıcı ziyaretleri belirlenir.
2) Sonrasında, user_cohorts tablosu oluşturularak her kullanıcının ilk aktivite tarihi bulunur.
3) Son adımda, kullanıcıların belirli zaman aralıklarında geri dönüş oranları hesaplanır.

4. Cohort Analizi Sonuçlarını Yorumlama

BigQuery’den elde edilen cohort retention verilerini Google Sheets veya Looker Studio’ya aktarırsanız görselleştirme işlemini kolaylaştırabilirsiniz.

Elde edilen verilerle şu soruların yanıtlarını bulabilirsiniz:
İlk kez gelen kullanıcılar hangi günlerde geri dönüyor?
Hangi günlerde kullanıcı bağlılığı en yüksek?
Kullanıcılar ortalama kaç gün sonra tekrar ziyaret ediyor?

Sonuç

BigQuery ile cohort analizi yaparak kullanıcıların geri dönüş oranlarını analiz edebilir, pazarlama stratejinizi geliştirebilirsiniz.

---

Referanslar

1. BigQuery Kohort Analizini Keşfetme
2. BigQuery Kohort Analizini kullanarak veri analisti olarak analiz edebileceğiniz sorgular
3. BigQuery ve Looker Studio’yu kullanarak Kohort Analizine İlişkin Tam Kılavuz
4. Cohort Analizi Nedir ve Nasıl Yapılır? GA4’da Cohort Analizi?