Google Bigquery ile Google Analytics Uygulamaları
(No Ratings Yet)
Loading...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.
- 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.
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`
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)
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’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)
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.
![](https://www.veribilimiokulu.com/wp-content/uploads/2020/05/logo-e1590873495165.png"){kind=logo}