Google Bigquery ile Google Analytics Uygulamaları

Merhaba VBO okuyucuları!

Bir önceki yazımda Google Bigquery hakkında temel bilgiler vermiş olup, yazının sonunda Google Analytics’e de değinmiştim. Bu yazımda Google Analytics datası üzerinde dijital pazarlama metriklerini Bigquery üzerinde kodlayarak göreceğiz.

Google Analytics nedir?

Kısaca, Google Analytics e-ticaret sitesi/ web sitesine gelen ziyaretçiler hakkında detaylı analizler yapabilmeyi sağlayan web analiz aracıdır. Örneğin, son 30 günde, uygulamadan gelen ziyaretçilerin hangi davranışları gösterdiğini, ne kadar alışveriş yaptıklarını gözlemleyebiliriz. 

Google Analytics hangi bilgileri içeriyor? Bigquery’de de bunlar var mı?

Google Analytics verilerini boyutlar ve metrikler olarak inceleyebiliriz.

Boyutlar, ziyaretçiler hakkında bilgiler içerir. Demografi, ilgi alanlar, coğrafi, davranış, teknoloji vs. gibi başlıklar altında şu sorulara cevap verilebilir:

  • Kullanıcıların hangi cihazları kullanıldığı,
  • Kullanıcıların hangi kanaldan e-ticaret sitesine geldikleri,
  • Gezilen sayfalar,
  • Kullanıcının cinsiyeti, yaşı gibi bilgiler Analytics’in içinde mevcut olan bilgilerdir.

Metrikler ise, bu boyutlara bağlı olarak verilen sayısal sonuçları ifade eder. Belirli bir davranışı gerçekleştiren kullanıcı sayısı, belirli kanaldan gelen oturumların sayısı gibi sayısal sonuçları elde etmemizi sağlar.

Bu hazır boyut ve metrikler dışında daha detaylı analizler yapılmak istendiğinde “Özel Boyut ve Metrikler” devreye giriyor. Bigquery, bütün bu boyut ve metrikleri içermekte olup, daha detaylı analizlerin yapılmasını ve daha farklı boyutları analize katabilmemizi sağlar.

Peki, “özel boyut ve metrikler” nedir?

Analytics üzerinde hazır olan boyut ve metriklerin ihtiyacımızı karşılayamayacağı durumlarda “özel boyut ve metrikler” oluşturulur. Örneğin, büyük beden kıyafetler satan bir alışveriş sitemiz olsun, bedenlerin kullanıcılara göre dağılımı illaki işimize yarayacaktır. Ürünlerin bedenlerini bir boyut olarak tanıtabilirsek, buradaki kullanıcı davranışlarını da gözlemliyor oluruz.

Bu genel bilgileri verdikten sonra biraz Bigquery’de Analytics kullanımına ve bazı önemli bilgilere değinmek istiyorum.

  • Gölgelendirilmiş tablolar (Shaded tables)

“Gölgelendirilmiş tablo” (shaded tables) denilen bir yapıda oluşturulan Google Analytics datası, bir tabloya yazmak yerine, Şekil-1’deki gibi bir aynı tablo ismi tarih değişerek farklı bir tabloya atanır.

Şekil-1: Gölgelendirilmiş Tablo
  • Tarih filtrelemesi

Tek bir tarihe bakmak istiyorsak şu şekilde filtreleme yapabiliriz:

SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170718`

Eğer belirli bir tarih aralığına bakmak istiyorsak, _TABLE_SUFFIX işlemi uygulamamız gerekir.

SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` 
WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801'

Burada 30 gün öncesini görmek istediğimizde dinamik hale de getirebiliriz. Konuya devam etmek adına bu bilgiyi burada bırakıyorum 😊

  • İç içe geçmiş tablolar (Nested tables)

Burada Şekil-2’deki bir örnek üzerinden gidelim. Aşağıdaki tabloda bir ziyaretçinin yaptığı oturumla ilgili bilgiler yer almakta, ancak burada sadece ilk satırı gerçek satır olarak değerlendirebiliriz, diğer satırlar iç içe geçmiş satırlardır. Bu içiçe geçmiş satırlar üzerinde yer alan veriler elde edebilmemiz için bazı işlemler uygulamamız gerekiyor.

Şekil -2: İç içe Geçmiş Tablolar

Bu yazıda hangi metrikleri inceleyeceğiz?

Toplam oturum sayısı: Siteye yapılan ziyaret sayısıdır. Bir kullanıcının  web sitesi ile belli bir zaman aralığındaki etkileşimi olarak düşünebiliriz. Şekil-3’teki gibi bir sonuç elde ediyoruz.

SELECT sum(totals.visits) FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170718`
Şekil-3: Toplam ziyaret sorgu sonucu

Toplam kullanıcılar: Oturumu gerçekleştirmiş kullanıcı sayısıdır.

SELECT count(distinct fullvisitorId) FROM `project-id.dataset-id.ga_sessions_20190101`

Huni Analizi (Funnel Analysis)

Bu temel metrik sorgularından sonra “funnel analysis” dediğimiz “huni analizi” ile satın almaya giden yolculuğa bir göz atalım.

(SELECT
    distinct 
    format_timestamp("%Y-%m-%d %H:%M:%S", timestamp_seconds(safe_CAST(visitStartTime AS INT64)), "Turkey") AS date,
    fullVisitorId,
    visitid,
    CONCAT(CAST(fullvisitorid AS string),CAST(visitid AS string)) as session_visitor,
    hits.eCommerceAction.action_type as type2,
    CASE WHEN hits.eCommerceAction.action_type = '1' THEN 'ProductClick'  
    WHEN hits.eCommerceAction.action_type = '3' THEN 'AddtoCart' 
    WHEN hits.eCommerceAction.action_type = '6' THEN 'Purchase'  ELSE NULL END as ActType
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
    UNNEST(customDimensions) AS cd,
    UNNEST(hits) AS hits,
    UNNEST(product) AS Product
 WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801' and 
 totals.visits = 1 and (isImpression IS NULL  OR isImpression = FALSE) 
 order by 4,1 asc)
Şekil-4: Sorgu sonucu

Bu kod bloğunu inceleyecek olursak, Şekil-4’te görmüş olduğumuz üzere, tarih bilgisini dönüştürerek alıyoruz, fullvisitorId dediğimiz kullanıcı id’sini, oturum id’sini alıyoruz. Kullanıcı bilgisi ve oturum id’sini birleştirerek alıyoruz ki, o oturumdaki bilgilere erişebilelim. Burada action_type dediğimiz “ürün tıklama, sepete atma, satın alma” gibi davranış tiplerinin kodlanmış hali. Biz de şu linkten bu bilgilerin ne demek olduğunu öğrenmiş olduğumuz için 1 ise ProductClick, 3 ise AddtoCart, 6 ise Purchase eventi olduğunu işaretliyoruz.

İçiçe geçmiş bilgileri UNNEST() ile açmış oluyoruz. Tarih kısıtlarımızı giriyoruz. totals.visits = 1 ile interaction oturumları alıyoruz ve isImpression filtreleri ile ürün listesi görünümünü almamış oluyoruz.

(SELECT ActType fullVisitorId, date,session_visitor,type2,
  LEAD (ActType, 1) OVER (PARTITION BY fullVisitorId, session_visitor 
    ORDER BY date,session_visitor,type2) AS next_event,
  LEAD (ActType, 2) OVER (PARTITION BY fullVisitorId, session_visitor 
    ORDER BY date,session_visitor,type2) AS third_event,
  LEAD (ActType, 3) OVER (PARTITION BY fullVisitorId, session_visitor
    ORDER BY date,session_visitor,type2) AS fourth_event,
  LEAD (ActType, 4) OVER (PARTITION BY fullVisitorId, session_visitor 
  ORDER BY date,session_visitor,type2) AS fifth_event
  FROM 
(SELECT
    distinct 
    format_timestamp("%Y-%m-%d %H:%M:%S", timestamp_seconds(safe_CAST(visitStartTime AS INT64)), "Turkey") AS date,
    fullVisitorId,
    visitid,
    CONCAT(CAST(fullvisitorid AS string),CAST(visitid AS string)) as session_visitor,
    hits.eCommerceAction.action_type as type2,
    CASE WHEN hits.eCommerceAction.action_type = '1' THEN 'ProductClick'  
    WHEN hits.eCommerceAction.action_type = '3' THEN 'AddtoCart' 
    WHEN hits.eCommerceAction.action_type = '6' THEN 'Purchase'  ELSE NULL END as ActType
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
    UNNEST(customDimensions) AS cd,
    UNNEST(hits) AS hits,
    UNNEST(product) AS Product
 WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801' and 
 totals.visits = 1 and (isImpression IS NULL  OR isImpression = FALSE) 
 order by 4,1 asc)
WHERE ActType IN ("ProductClick", "AddtoCart", 
 "Purchase")
ORDER BY fullVisitorId, date,session_visitor,type2 ASC)
Şekil-5: Diğer sorgu sonucu

Şekil-5’te gördüğümüz gibi, burada gerçekleşen LEAD() fonksiyonu ile bir kullanıcının sonraki davranışlarını sütun olarak eklemiş olduk.

SELECT COUNT(DISTINCT user_1) as users_1,
  COUNT(DISTINCT user_2) as users_2,
  COUNT(DISTINCT user_3) as users_3
FROM 
(SELECT IF(ActType = "ProductClick", fullVisitorId, NULL) AS user_1,
  IF(ActType = "ProductClick" AND next_event = "AddtoCart", fullVisitorId, NULL) AS 
    user_2,
  IF(ActType = "ProductClick" AND next_event = "AddtoCart" AND 
    third_event = "Purchase", fullVisitorId, NULL) AS user_3
from 
(SELECT ActType,fullVisitorId, date,session_visitor,type2,
  LEAD (ActType, 1) OVER (PARTITION BY fullVisitorId, session_visitor 
    ORDER BY date,session_visitor,type2) AS next_event,
  LEAD (ActType, 2) OVER (PARTITION BY fullVisitorId, session_visitor 
    ORDER BY date,session_visitor,type2) AS third_event,
  LEAD (ActType, 3) OVER (PARTITION BY fullVisitorId, session_visitor
    ORDER BY date,session_visitor,type2) AS fourth_event,
  LEAD (ActType, 4) OVER (PARTITION BY fullVisitorId, session_visitor 
  ORDER BY date,session_visitor,type2) AS fifth_event
  FROM 
(SELECT
    distinct 
    format_timestamp("%Y-%m-%d %H:%M:%S", timestamp_seconds(safe_CAST(visitStartTime AS INT64)), "Turkey") AS date,
    fullVisitorId,
    visitid,
    CONCAT(CAST(fullvisitorid AS string),CAST(visitid AS string)) as session_visitor,
    hits.eCommerceAction.action_type as type2,
    CASE WHEN hits.eCommerceAction.action_type = '1' THEN 'ProductClick'  
    WHEN hits.eCommerceAction.action_type = '3' THEN 'AddtoCart' 
    WHEN hits.eCommerceAction.action_type = '6' THEN 'Purchase'  ELSE NULL END as ActType
  FROM
    `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
    UNNEST(customDimensions) AS cd,
    UNNEST(hits) AS hits,
    UNNEST(product) AS Product
 WHERE _TABLE_SUFFIX BETWEEN '20170701' AND '20170801' and 
 totals.visits = 1 and (isImpression IS NULL  OR isImpression = FALSE) 
 order by 4,1 asc)
WHERE ActType IN ("ProductClick", "AddtoCart", 
 "Purchase")
ORDER BY fullVisitorId, date,session_visitor,type2 ASC)
WHERE ActType != next_event OR next_event IS NULL)
Şekil-6: Huni analizi sonucu

Burada ise her bir aşamayı gerçekleştiren kullanıcı sayısını bularak Şekil-6’daki sonucu elde ediyoruz: Ürün tıklaması gerçekleştiren 8401 kullanıcı mevcut, bunların 3113’ü sepete ürün atmış, sepete ürün atanların 743’ü ie ürün satın alımı yapmış demek oluyor. Burada farklı event’ler tanımlanarak farklı huni analizleri gerçekleştirilebilir.

Bir sonraki yazımda ise yine Google Analytics ve Bigquery üzerinde tahmin çalışması yapmayı planlıyorum. Umarım faydalı bir yazı olmuştur. Yorumlarınız için şimdiden teşekkür ederim.

Yazar Hakkında
Toplam 9 yazı
Sena Merter Dereli
Sena Merter Dereli
Pazarlama & Müşteri Analitiği Araştırmacısı / n11.com'da Pazarlama Analitiği Yöneticisi
Yorumlar (Yorum yapılmamış)

Bir cevap yazın

E-posta hesabınız yayımlanmayacak.

×

Bir Şeyler Ara