Yazımıza başlamadan önce kullanacağımız Toolu’u kısaca tanıyalım 🙂

 

BigQuery Nedir ?

 

  • BigQuery, Google’ın ürünü olan Cloud tabanlı bir Big Data Management tool’udur.
  • NoSQL yapıya sahiptir. Yani bir çok veri tabanı gibi ilişkisel bir yapıya sahip değildir. Fakat sorgu dili ilişkisel veri tabanlarına çok benzer yapıdadır.
  • Sorgularında Server SQL dilini kullanır. (T-SQL)
  • Sorgu başına ücretlendirmesi bulunmaktadır. TB başına 5$ ücretlendirilir. Her ay ilk 1TB boyutlu query ücretsizdir.
  • Analytics Property ayarlarından BigQuery ile linkleme işlemi yapılır. Her Property için yalnızca bir View’ı BigQuery’e aktarma hakkımız bulunduğundan, en fazla dataya sahip View’ı linklersek BigQuery’de o kadar fazla veriyi manipüle etme imkanına sahip oluruz.

 

Toolumuzu tanıdık 🙂 Eğer bu bilgileri daha önceden biliyorsanız, Google Analytics hesabınızdaki bir Property’yi BigQuery ye bağladıysanız ve Analytics raporlarınızı BigQuery üzerinden almak istiyorsanız doğru yerdesiniz.

Bu yazıda Analytics’te bulunan Metric ve Dimensionları BigQuery üzerinden nasıl hesaplanıyor ve hangi field ne anlama geliyor sorularına cevap bulacağız.

Bu yazının sonunda artık BigQuery üzerinden temel raporları çıkartabiliyor hale geleceksiniz. Bu sayede Google Analytics’teki sample problemi, 4 segment sınırı, uzun cevap süresi hatası vs. gibi problemleri ortadan kaldırmış olacağız.

Sorgularımızı aşağıdaki görselde bulunan Query Editor alanında çalıştıracağız. Bu ekrana ulaşmak için şu adımları takip edin;

 

İlk olarak vazgeçilmez metriklerimiz olan; Users, Sessions ve Pageviews dan başlayalım.

 

Users

BigQuery üzerinde her user için atanan eşsiz bir Id bulunmakta buna fullVisitorId deniyor. Eğer bu fullVisitorId leri sayarsak toplam user sayısını almış oluruz? Diyeceksiniz muhtemelen fakat BigQuery üzerinde bulunan her bir satır tek bir hit i temsil ettiğinden dolayı her satırdaki fullVisitorId’yi sayarsak arada aynı olan fullVisitorId leri de almış olacağız. Bundan dolayı fullVisitorId leri eşsiz bir şekilde saymamız gerekiyor. Nasıl mı? Şöyle;

SELECT COUNT(DISTINCT fullVisitorId) AS Users FROM `tablo-adi`

 

Sessions

Normal koşullarda Session hesaplamak için totals.visits kullanılıyor fakat Ülke bazlı bir rapor almak istediğimizde durumlar değişiyor. Neden mi? Çünkü her ülkenin zaman dilimi farklıdır ve totals.visits ile aldığımızda tek bir zaman dilimini kullandığı için arada kaçırdığımız sessionlar olacaktır. Bundan dolayı aşağıdaki sorguyu kullanmak daha doğru oluyor. Bu sorguda Users hesaplarken kullandığımız fullVisitorId ile ziyaret başlangıç zamanı olan visitStartTime birleştiriyoruz. Bu sayede bize eşsiz bir sessionId vermiş oluyor.

SELECT

COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitStartTime AS STRING))) AS Sessions

FROM `tablo-adi`

WHERE totals.visits = 1

 

Pageviews

Pageview hesaplamak için yapmamız gereken tüm satırları saymak. Fakat bu satırların sayfa olduğunu belirtmemiz gerekiyor. O da şu şekilde;

SELECT COUNT(*) AS Pageviews FROM `tablo-adi` WHERE hits.type = ‘PAGE’

Biraz da E-Ticaret metriklerine bakalım;

 

Transactions

Transactions hesaplarken de aynı Users ta yaptığımız gibi toplam eşsiz transactionId leri sayacağız.

SELECT COUNT(DISTINCT hits.transaction.transactionId) AS Transactions FROM `tablo-adi` WHERE totals.visits = 1

 

Ecommerce Conversion Rate

İlk olarak Conversion Rate nasıl hesaplanıyordu? Sorusunun cevabı bizde olmalı. Tabiki de Transaction/Session dediğinizi duyar gibiyim. O halde sahip olduğumuz bilgilere göre hesaplamayı yapalım.

SELECT

COUNT(DISTINCT hits.transaction.transactionId) / COUNT(DISTINCT CONCAT(CAST(fullVisitorId AS STRING), CAST(visitStartTime AS STRING))) AS Ecommerce_Conversion_Rate

FROM `tablo-adi` WHERE totals.visits = 1

 

Transaction Per User

İsminde de yazdığı gibi Transaction sayısını User sayısına bölersek bu metriği elde edeceğiz.

SELECT

COUNT(DISTINCT hits.transaction.transactionId) / COUNT(DISTINCT fullVisitorId) AS Transactions_Per_User

FROM `tablo-adi` WHERE totals.visits = 1

 

Quantity

1 Transaction hit’i içerisindeki ürünlerin adetlerini toplarsak Quantity’i elde etmiş olacağız. Bu durumda itemQuantity leri toplayalım.

SELECT

SUM(hits.item.itemQuantity) AS Quantity

FROM `tablo-adi` WHERE totals.visits = 1

 

via GIPHY

Çok Metric oldu biraz da dimension 🙂

 

Full Referrer

BigQuery de Full Referrer parametresine direkt olarak ulaşamıyoruz. Onun yerine traffic source ile referral path i birleştiriyoruz. Birleştirmek için CONCAT() adındaki fonksiyonu kullanacağız.

SELECT

CONCAT(trafficSource.source, trafficSource.referralPath) AS Full_Referrer FROM `tablo-adi` WHERE totals.visits = 1

 

Source / Medium

Bu parametreye de direkt olarak ulaşamıyoruz fakat isminden de anlaşılacağı gibi iki parametre arasına / ekleyerek ulaşabiliriz.

SELECT

CONCAT(trafficSource.source,” / “,trafficSource.medium) AS Source_Medium

FROM `tablo-adi` WHERE totals.visits = 1

 

Tarih ve Zaman Parametreleri

Bu parametreleri tek tek açıklamayacağım. Çünkü hepsi aynı fonksiyonlar ile yazdırılıyor. Bu fonksiyonlar FORMAT_DATE ve PARSE_DATE.

FORMAT_DATE, adı üstünde içerisine yazılan tarihi istediğiniz bir formata getiriyor. Örneğin Yıl almak için %Y yazmamız gerekiyor.

PARSE_DATE ise içerisine yazılan Timestamp veya UNIX Time’ı istediğiniz bir tarih biçimine getiriyor. Örneğin; Ben tarihi 2020–07–16 olarak görmek istiyorum. Bunun için %Y-%m-%d yazmam gerekiyor.

O zaman parametrelerimize geçelim.

SELECT

FORMAT_DATE(‘%Y’, PARSE_DATE(“%Y%m%d”, date)) AS Year,

FORMAT_DATE(‘%G’, PARSE_DATE(“%Y%m%d”, date)) AS ISO_Year,

FORMAT_DATE(‘%Y%m’, PARSE_DATE(“%Y%m%d”, date)) AS Month_of_Year,

FORMAT_DATE(‘%m’, PARSE_DATE(“%Y%m%d”, date)) AS Month_of_the_Year,

FORMAT_DATE(‘%Y%U’, PARSE_DATE(“%Y%m%d”, date)) AS Week_of_Year,

FORMAT_DATE(‘%U’, PARSE_DATE(“%Y%m%d”, date)) AS Week_of_the_Year,

FORMAT_DATE(‘%W’, PARSE_DATE(“%Y%m%d”, date)) AS ISO_Week_of_the_Year,

FORMAT_DATE(‘%G%W’, PARSE_DATE(“%Y%m%d”, date)) AS ISO_Week_of_ISO_Year,

FORMAT_DATE(‘%d’, PARSE_DATE(“%Y%m%d”, date)) AS Day_of_the_Month,

FORMAT_DATE(‘%w’, PARSE_DATE(“%Y%m%d”, date)) AS Day_of_Week,

FORMAT_DATE(‘%A’, PARSE_DATE(“%Y%m%d”, date)) AS Day_of_Week_Name

FROM `tablo-adi` WHERE totals.visits = 1

İlk bölümümüzün sonuna geldik. Umarım faydalı ve ileride kullanacağınız bilgiler almış olursunuz. Bu bölümde çok kafa karışıklığı olmaması adına hesaplanması ve bulunması en kolay olan parametreleri seçmeye çalıştım. Bir sonraki yazılarda daha hesaplanması zor ve kompleks yapıda parametreler ile kaldığımız yerden devam edeceğiz 🙂

 

via GIPHY

Görüşmek üzere.

Leave a Reply

Hype

Esentepe Mahallesi
Ecza Sokak 6/1
Levent, İstanbul

+90 (532) 744 4943
[email protected]