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;
- console.cloud.google.com/bigquery adresine gidin
- Çalışacağınız projeyi seçin.
- Son olarak Google Analytics e bağlı olan tablonuzu açın.
İ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
Ç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 🙂
Görüşmek üzere.
