BigQuery çözüm kitabı

Bu özellik yalnızca Google Marketing Platform kapsamında sunulan Analytics 360 ürününde kullanılabilir.
Google Marketing Platform hakkında daha fazla bilgi

Bu makalede, Analytics verilerini BigQuery'ye aktarma sorgularını nasıl oluşturacağınıza dair örnekler verilmektedir. Ayrıca, verilen sorgu örneklerinden bazılarıyla alıştırma yapabilmeniz için örnek bir veri kümesi kullanımınıza sunulmuştur.

Bu makalede ele alınan konular:

Sorgu optimizasyonu

Yaptığınız her sorgu işlemi, aylık veri işleme ödeneğinize eklenir. Fazla alan seçerseniz işlenmesi gereken veri miktarını arttırmış ve dolayısıyla aylık ödeneğinizin ihtiyacınızdan daha fazla kısmını kullanmış olursunuz. Optimize edilmiş sorgular, aylık veri işleme ödeneğinizi verimli kullanmanıza yardımcı olur.

Fiyatlandırma hakkında daha fazla bilgi edinin.

Yalnızca ihtiyacınız olanı seçin

Bir sorguyu formüle ederken SELECT ifadesi içindeki alakalı alanları seçin. Fazla alan seçmeyerek sorgunun işlenmesi için gereken veri miktarını ve süreyi azaltmış olursunuz.

Örnek: Joker karakter operatörü kullanmaktan kaçınma

Kötü biçim: joker karakter operatörü kullanma
SELECT *
FROM [table name];

 

İyi biçim: Gereksiz işlemlerden kaçınmak için alan adları kullanma
SELECT field1, field2
FROM [table name];

Önbelleğe alma izni sağlayın

Mümkünse, fonksiyonları alan olarak kullanmaktan kaçının. (NOW() veya TODAY()) gibi fonksiyonlar değişken sonuçlar getirir ve bu, sorguların önbelleğe alınmasını ve dolayısıyla daha hızlı getirilmesini engeller. Bunun yerine, belirli saatler ve tarihler kullanın.

Şu anda, Önbelleğe Alınmış Sonuçları Kullan seçeneği işaretli olsa bile, birden fazla tabloya yöneltilen ve joker karakter kullanılan sorgularda önbelleğe alınmış sonuçlar desteklenmez. Aynı joker karakter sorgusunu birden çok kez çalıştırırsanız her bir sorgu için faturalandırılırsınız. Daha fazla bilgi

Sık kullanılan alt sorgular için orta seviye tablolar kullanın

Belirli bir sorguyu sürekli olarak alt sorgu olarak kullandığınızı fark ederseniz, bu sorguyu sorgu sonuçlarının üzerindeki Tablo Olarak Kaydet öğesini tıklayarak orta seviye bir tablo olarak kaydedebilirsiniz. Daha sonra bu tabloyu sorgunuzdaki FROM bölümünde belirterek hem işlenmesi gereken veri miktarını hem de işlem süresini azaltabilirsiniz.

orta seviye tablo kullanma
SELECT field1, field2
FROM [Dataset name.table name];

Sorguda hata ayıklama

BigQuery, kodunuzu oluşturduğunuz sırada kodda hata ayıklama işlemi gerçekleştirir. Oluşturma penceresinde, hata ayıklama işlemi sorgunun hemen altında belirtilir. Hata ayıklama ayrıca dryRun işaretli API aracılığıyla da kullanılabilir.

Geçerli sorgularda, sorgu tarafından işlenen veri miktarını görmek için tıklayabileceğiniz yeşil bir gösterge bulunur. Bu özellik, sorguyu çalıştırmadan önce verilerinizi optimize etme olanağı sunar. Böylece gereksiz veri işlemlerinden kaçınabilirsiniz.

Query Debugging - Success

 

Geçersiz sorgularda, hataya ilişkin bilgileri görmek ve hatanın meydana geldiği satırı ve sütunu bulmak için tıklayabileceğiniz kırmızı bir gösterge bulunur. Aşağıdaki örnekte GROUP BY ifadesi boştur ve hatanın yeri gösterilmiştir.

Query Debugging - Error

 

İpuçları ve en iyi uygulamalar

Örnek veri kümesini kullanma

Aşağıdaki örneklerde Google Analytics örnek veri kümesi kullanılır.

Sorguları kendi verilerinizde kullanmak için proje ve veri kümesi adlarını kendi proje ve veri kümelerinizin adlarıyla değiştirmeniz yeterlidir.

Standart SQL veya Eski SQL'yi kullanma

BigQuery iki SQL dilini destekler:

İki dil arasındaki farklar, Migrating to Standard SQL (Standart SQL'e geçiş) başlıklı makalede açıklanmıştır.

BigQuery'de depolanan verileri sorgulamak için tercih edilen SQL dili artık standart SQL'dir.

BigQuery kullanıcı arayüzü, komut satırı arayüzü, API'si ya da kullandığınız farklı bir arayüzde standart SQL'i etkinleştirme hakkında daha fazla bilgi edinmek için Enabling Standard SQL (Standart SQL'i etkinleştirme) başlıklı makaleyi inceleyebilirsiniz.

Etkinleştirme işlemine başlamanın en kolay yöntemi, "standardSQL" yorumunu standart SQL sorgularınızın üst kısmına, aşağıdaki örneklerde gösterildiği gibi eklemektir.

Eski SQL ile, Google Analytics 360 verileri her gün yeni bir tabloya aktarılır. Birden çok tabloyu aynı anda sorgulamak için aşağıdaki örneklerde olduğu gibi tablo adlarını virgülle ayırabilir, TABLE_DATE_RANGE tablo joker karakter işlevini veya virgülle ayrılmış birden çok TABLE_DATE_RANGE işlevini kullanabilirsiniz.

Birden çok tabloyu sorgulama

Aşağıda, aynı veriler için Standart SQL ve Eski SQL sorgularına örnekler verilmiştir.

3 gün

Standart SQL
UNION ALL kullanarak 3 gün
#standardSQL
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160802`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160803`
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
Eski SQL
Virgülle ayrılmış tablo adları kullanılarak 3 gün
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
[bigquery-public-data.google_analytics_sample.ga_sessions_20160801],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160802],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160803]
GROUP BY
date
ORDER BY
date ASC

 

Son 1.095 gün

Standart SQL
_TABLE_SUFFIX kullanarak son 1.095 gün
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Eski SQL
TABLE_DATE_RANGE kullanarak son 1.095 gün
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -1095, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Son 36 ay

Standart SQL
_TABLE_SUFFIX kullanarak son 36 ay
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Eski SQL
TABLE_DATE_RANGE kullanarak son 36 ay
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -36, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Son 3 yıl

Standart SQL
_TABLE_SUFFIX kullanarak son 3 yıl
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Eski SQL
TABLE_DATE_RANGE kullanarak son 3 yıl
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

Belirli bir tarih aralığı

Standart SQL
_TABLE_SUFFIX kullanarak belirli bir tarih aralığı
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
Eski SQL
TABLE_DATE_RANGE kullanarak belirli bir tarih aralığı
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2016-08-01'), TIMESTAMP('2017-07-31')))
GROUP BY
date
ORDER BY
date ASC

Son 3 yılın artı bugünün verileri (gün içi)

Standart SQL
UNION ALL ve _TABLE_SUFFIX kullanarak Son 3 yılın artı bugünün verileri (gün içi)
Not: Bu örnek sorgu, şu anda gün içi tablo bulunmadığı için Google Analytics herkese açık veri kümesiyle kullanılamaz.
#standardSQL
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

Eski SQL

Birden çok TABLE_DATE_RANGE kullanarak Son 3 yılın artı bugünün verileri (gün içi)
Not: Bu örnek sorgu, şu anda gün içi tablo bulunmadığı için Google Analytics herkese açık veri kümesiyle kullanılamaz.
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_intraday_],
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

 

 

Temel sorgu örnekleri

Bu bölümde, örnek Analytics verilerindeki metrik ve boyutlar kullanılarak nasıl temel sorgular oluşturulacağı açıklanmaktadır.

[Boyut] başına toplam [metrik]

Aşağıda, Temmuz 2017'de cihaz tarayıcısı başına oluşturulan toplam işlemlerin sayısı kaçtır? sorusu için örnek komut dosyaları verilmiştir:

Standart SQL

Temmuz 2017'de cihaz tarayıcısı başına toplam işlem sayısı
#standardSQL
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

Eski SQL

Temmuz 2017'de cihaz tarayıcısı başına toplam işlem sayısı
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
device.browser
ORDER BY
total_transactions DESC

 

 

[ Boyut ] başına ortalama hemen çıkma oranı

Gerçek hemen çıkma oranı, sayfanın bir defa görüntülendiği ziyaretlerin yüzdesi olarak tanımlanır. Aşağıda, Trafik kaynağı başına gerçek hemen çıkma oranı kaçtır? sorusu için örnek komut dosyaları verilmiştir.

Standart SQL

Temmuz 2017'de trafik kaynağı başına hemen çıkma oranı
#standardSQL
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

Eski SQL

Temmuz 2017'de trafik kaynağı başına hemen çıkma oranı
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
source )
ORDER BY
total_visits DESC

 

 

Alıcı türüne göre ortalama ürün sayfası görüntüleme sayısı (satın alma işlemi gerçekleştirenler ve gerçekleştirmeyenler)

Aşağıda, Temmuz 2017'de satın alma işlemi gerçekleştiren kullanıcılar için ortalama ürün sayfası görüntüleme sayısı kaçtır? sorusuna örnek komut dosyaları verilmiştir:

Standart SQL

Temmuz 2017'de satın alma işlemi gerçekleştiren kullanıcılar için ortalama ürün sayfası görüntüleme sayısı
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

Eski SQL

Temmuz 2017'de satın alma işlemi gerçekleştiren kullanıcılar için ortalama ürün sayfası görüntüleme sayısı
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions >=1
GROUP BY
users )

 

 

Aşağıda, Temmuz 2017'de satın alma işlemi gerçekleştirmeyen kullanıcılar için ortalama ürün sayfası görüntüleme sayısı kaçtır? sorusuna örnek komut dosyaları verilmiştir.

Standart SQL

Temmuz 2017'de satın alma işlemi gerçekleştirmeyen kullanıcılar için ortalama ürün sayfası görüntüleme sayısı
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

Eski SQL

Temmuz 2017'de satın alma işlemi gerçekleştirmeyen kullanıcılar için ortalama ürün sayfası görüntüleme sayısı
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

Alıcı başına ortalama işlem sayısı

Aşağıda, Temmuz 2017'de satın alma işlemi gerçekleştiren kullanıcı başına ortalama toplam işlem sayısı kaçtır? sorusu için örnek komut dosyaları verilmiştir:

Standart SQL

Temmuz 2017'de satın alma işlemi gerçekleştiren kullanıcı başına ortalama işlem sayısı
#standardSQL
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

Eski SQL

Temmuz 2017'de satın alma işlemi gerçekleştiren kullanıcı başına ortalama işlem sayısı
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

 

 

Oturum başına harcanan ortalama para miktarı

Aşağıda, Temmuz 2017'de oturum başına harcanan ortalama paranı miktarı nedir? sorusu için örnek komut dosyaları verilmiştir:

Standart SQL

Temmuz 2017'de oturum başına harcanan ortalama para miktarı
#standardSQL
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

Eski SQL

Temmuz 2017'de oturum başına harcanan ortalama para miktarı
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

 

 

İsabetler dizisi

Aşağıda, "Görüntülenen sayfaların dizisi nedir?" sorusu için örnek komut dosyaları verilmiştir:

Standart SQL

Temmuz 2017'de kullanıcılar tarafından görüntülenen sayfaların dizisi
#standardSQL
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

Eski SQL

Temmuz 2017'de kullanıcılar tarafından görüntülenen sayfaların dizisi
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

Bu sorguda isabet türleri, etkinlik veya işlem etkileşimlerinin görünmemesi için PAGES ile sınırlandırılır. Her çıktı satırı bir sayfa görüntülemeyi ifade eder ve SELECT ifadesindeki alanlarda varsayılan sırada gösterilir.

 

 

İsabet veya oturum düzeyinde birden çok özel boyut

isabet düzeyinde özel boyut
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

oturum düzeyinde özel boyut
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

Her sorguda:

SELECT ifadesi alakalı boyut ve metrik alanlarını sorgular.

MAX işlevi:

  • Özel boyutu yeni bir sütun olarak getirir. Birden çok özel boyutları yeni sütunlar olarak getirmek için fonksiyonu tekrarlayabilirsiniz.
  • WITHIN hits ve WITHIN RECORD, BigQuery'deki tekrarlanan alanlar içindeki koşulu değerlendirir.
  • MAX içindeki koşul her özel boyut için değerlendirilir ancak index=1 (hits) veya index=2 (sessions) olmayan tüm özel boyutlar için NULL sonucunu döndürür.
  • Diğer tüm değerler NULL olduğundan, isabetler için Özel Boyut 1 veya oturumlar için Özel Boyut 2'nin değeri olan maksimum değeri döndürür.

Gelişmiş sorgu örnekleri

Temel sorguları öğrendiğinize göre, BigQuery'de sunulan gelişmiş fonksiyonları ve özellikleri kullanarak sorgular oluşturabilirsiniz.

A Ürününü satın alan müşteriler tarafından satın alınan ürünler (Klasik E-ticaret)

Aşağıda, A ürününü satın alan müşteriler başka hangi ürünleri satın almıştır? sorusu için taslak bir komut dosyası verilmiştir.

A ürününü satın alan bir müşteri tarafından satın alınan ürünler (Klasik E-ticaret)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [‘Dataset Name’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Dataset Name’ ]
  WHERE hits.item.productName CONTAINS 'Product Item Name A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Product Item Name A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. İlk satırda, bir kullanıcı tarafından satın alınmış tüm diğer öğeleri seçersiniz ve toplama işlevi COUNT(), satın alınmış diğer her öğenin miktarını hesaplamak için kullanılır. Sonuç daha sonra quantity etiketli bir alanda, other_purchased_products olarak etiketlenmiş ürün alanındaki ilişkili öğesiyle birlikte gösterilir.
  2. Gri alt sorguda, yalnızca işlem gerçekleştirmiş (totals.transactions>=1) ve bir işlem sırasında A ürününü satın almış (WHERE hits.item.productName CONTAINS 'Product Item Name A') benzersiz kullanıcıları (fullVisitorId) seçersiniz.

En üst düzey (yeşil) sorgudaki kurallar (WHERE ve AND ifadeleri), hits.item.productName kısmında bulunan, boş olan ve A ürününü içeren değerleri gözardı eder.

Bir müşteri Brighton Metallic Pens - 4 Set ürününü satın alırsa, diğer hangi ürün veya ürünleri satın almıştır? sorgusuna bir örnek aşağıda verilmiştir:

24 Haziran 2013 tarihinde "Brighton Metallic Pens (4 Set)" satın alan bir müşterinin satın aldığı ürünler
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Dremel/BigQuery'de WHERE expr IN kullanıldığında bir JOIN tetiklenir ve boyut kısıtlamaları uygulanır. Özellikle JOIN komutunun sağ tarafının boyutunun (bu durumda ziyaretçi sayısı) 8 MB'dan az olması gerekir. Dremel'de buna broadcast JOIN denir. Boyut 8 MB'ı geçtiğinde, JOIN EACH söz dizimi kullanılarak yapılan bir shuffled JOIN tetiklemeniz gerekir. Maalesef bu işlem IN kullanılarak yapılamaz ancak aynı sorgu bir JOIN ile yeniden yazılabilir.

A Ürününü satın alan müşteriler tarafından satın alınan ürünler (Geliştirilmiş E-ticaret)

Bu, önceki taslak sorguya benzer fakat Geliştirilmiş E-ticaret için çalışır. Birden çok güne yayılan verileri sorgulamak için TABLE_DATE_RANGE işlevinin kullanılmasını da sağlar.

A ürününü satın alan bir müşteri tarafından satın alınan ürünler (Geliştirilmiş E-ticaret)
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity
FROM SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14'))
WHERE hits.product.productSKU CONTAINS '10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY fullVisitorId )
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Satın almadan önceki ortalama kullanıcı etkileşimi sayısı

Bu, yalnızca Analytics verilerine bağlı olan bir JOIN() [...] ON komutu sorgusuna örnektir.

Aşağıda şu soru için taslak bir script verilmiştir: Satın almadan önceki ortalama kullanıcı etkileşimleri sayısı kaçtır?

satın almadan önceki kullanıcı etkileşimlerinin sayısı
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;
  1. İlk satır, ürün başına ortalama kullanıcı etkileşimlerini bulmak için ana matematik işlemini gerçekleştirir ve bu sorgu, "Alias_Name_1" ve "Alias_Name_2" adı verilen iki alt sorgu arasında bir bağlantı oluşturur.
  2. "Alias_Name_1", bir ürün için kaydedilen tüm isabet sayılarını toplamak için SUM() toplama işlevini kullanan bir alan oluşturmak için kullanılır.
  3. "Alias_Name_2", COUNT() işlevini kullanarak ürün başına kullanıcılar tarafından yapılan isabetlerin sayısını bulmak için kullanılır.
  4. Son satır bağlantıdaki iki veri kümesi arasında paylaşılan ortak alanı (hits.item.productSku) gösterir.

10 Eylül 2013 tarihinde bir satın alım öncesindeki ortalama kullanıcı etkileşimlerinin sayısı kaçtır? sorgusuna bir örnek aşağıda verilmiştir:

10 Eylül 2013 tarihinde bir satın alım öncesindeki kullanıcı etkileşimlerinin sayısı
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku  ) AS one
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS two
ON one.hits.item.productSku = two.hits.item.productSku;

Ürün başına satılan stok yüzdesi

Bu, yalnızca Analytics verilerine değil, aynı zamanda Analytics dışı verilere de bağlı olan bir sorguya örnektir. İki veri kümesini birleştirerek kullanıcı davranışlarını daha bölümlendirilmiş bir düzeyde anlamaya başlayabilirsiniz. Analytics dışı verileri BigQuery içine aktarabilirsiniz ancak bunun aylık veri depolama ücretinize ekleneceğini unutmayın.

Aşağıda, Ürün başına satılan stok yüzdesi kaçtır? sorusu için taslak bir komut dosyası verilmiştir.

ürün başına satılan stok yüzdesi
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold
FROM [ ‘Imported_DataSet’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘GA Dataset’ ]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS ‘Alias_Name’
ON Imported_DataSet.’productId_field’ = Alias_Name.hits.item.productSku;
  1. İlk satır iki alan sonucu getirir: tüm ürün kimliklerini içeren bir alan ve o ürün kimliği için satılan stokun yüzdesini gösteren matematik işlemi olan alan.
  2. Bu sorgu iki veri kümesine bağlı olduğundan JOIN() ... ON işlevini kullanmanız gerekir. Bu komut, iki veri kümesi arasındaki ortak alana bağlı olarak, bunlarda yer alan satırları birleştirir. Bu durumda, [ ‘Imported_DataSet’ ] ve 'Alias_Name' şeklinde iki veri kümesi olur.
  3. [Imported_DataSet] Analytics dışı veridir. Bu, kalan stok miktarıyla ilgili metrik alanını (Imported DataSet.’stock_left_field’) ve ürün kimliği boyut alanını (Imported_DataSet.’productId_field’) içeren veri kümesidir.
  4. ‘Alias_Name’, gri alt sorgunun döndürdüğü verilere atanan addır. Bu alt sorgu, ürün başına satılan toplam öğe miktarını bulmak için Analytics verilerini kullanır.
  5. Son satır, iki veri kümesi arasındaki ortak alanı ve bunların nerede birleştirildiğini göstermek için ON ifadesini kullanır.

Bu sorguda bulunan değişkenlerin çoğu, kendilerine ön ek olarak eklenmiş (ör. Imported_DataSet.’productId_field’, Alias_Name.quantity_sold) veri gruplarına sahiptir. Bu, hangi alanı seçtiğinizi netleştirmek ve değişkenin hangi veri kümesine ait olduğunu açık hale getirmek içindir.

28 Temmuz 2013 tarihinde ürün başına satılan stok yüzdesi kaçtır? sorgusuna bir örnek aşağıda verilmiştir:

28 Temmuz 2013 tarihinde ürün başına satılan stok yüzdesi
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold
FROM AnalyticsImport.product_data_20130728
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM GoogleStore.ga_sessions_20130728
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS one
ON AnalyticsImport.product_data_20130728.productId = one.hits.item.productSku
ORDER BY percentage_of_stock_sold DESC;

Her bir ürünün kârlılığı

Aşağıda şu soru için taslak bir komut dosyası verilmiştir: Her bir ürünün kârlılığı nedir?

ürün başına kâr
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘Imported Data Set’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘GA Dataset Name’ ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku );
  1. İlk satır, her üründen elde edilen toplam kârı hesaplamak için yapılan matematik işlemini içerir.
  2. Gri alt sorgu, bir ürün satıldığında ne kadar kâr elde edildiği hakkında veri toplayan Analytics dışı verileri kullanır.
  3. Kırmızı alt sorgu, Analytics dışı verilerle birleştirilecek olan Analytics verileri alt sorgusudur. Ürün başına satılan parça miktarını hesaplar.
  4. Son satır, iki veri kümesinin paylaştığı alanı netleştirmek için ON ifadesini kullanır. Bu durumda, ürün kimliği numarasıdır.

28 Temmuz 2013 tarihinde her bir ürünün kârlılığı nedir? sorgusuna bir örnek aşağıda verilmiştir:

28 Temmuz 2013 tarihinde ürün başına kâr
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit
FROM (
  SELECT two.hits.item.productSku, AnalyticsImport.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice
  FROM AnalyticsImport.product_data_20130728
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM GoogleStore.ga_sessions_20130728
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS two
  ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku );

Kâr, ürünün satış fiyatı ile üretim maliyeti arasındaki fark hesaplanarak bulunur. Bu bilgi, GA dışı veri kümesinde saklanır.

(Geri ödemeler de dikkate alınarak) her bir ürünün gerçek kârlılığı

Aşağıda şu soru için taslak bir komut dosyası verilmiştir: Her bir ürünün gerçek kârlılığı nedir?

ürün başına gerçek kâr
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT Alias_Name.hits.item.productSku, ( ( Imported_DataSet.productprice - Imported_DataSet.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Imported_DataSet.refunddeliveryprice + Imported_DataSet.productprice ) * Imported_DataSet.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT Alias_Name.hits.item.productSku, Imported_DataSet.productcost, Alias_Name.quantity, Imported_DataSet.productprice, Imported_DataSet.refunddeliveryprice, Imported_DataSet.refundquantity
    FROM [ ‘Imported DataSet Name’ ] AS 'Imported_DataSet'
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘GA Dataset Name’ ]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS 'Alias_Name'
    ON Imported_DataSet.productId = Alias_Name.hits.item.productSku ) );
  1. Bu, Her ürünün kârlılığı nedir? sorgusuna çok benzer. Tek fark, gri alt sorgudaki Analytics dışı veri kümesinde ve ilk satırda gerçek kârı hesaplamak için kullanılan matematik işlemindedir.
  2. Analytics dışı veri kümesinde, geri ödemeler için harcanan toplam para miktarını da hesaplarsınız (kırmızı alt sorgunun SELECT ifadesinde).
  3. Ardından, 1. satırdaki matematik işlemiyle brüt kârınızdan geri ödemelere harcanan gelir tutarını çıkararak gerçek kârı bulursunuz.

Sorgu hakkında daha fazla bilgi edinmek için her bir ürünün kârlılığı bölümüne göz atın.

28 Temmuz 2013 tarihinde her bir ürünün gerçek kârlılığı nedir? sorgusuna bir örnek aşağıda verilmiştir:

28 Temmuz 2013 tarihinde ürün başına gerçek kâr
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT two.hits.item.productSku, ( ( AnalyticsImport.product_data_20130728.productprice - AnalyticsImport.product_data_20130728.productcost ) * two.quantity ) AS gross_profit, ( ( AnalyticsImport.product_data_20130728.refunddeliveryprice + AnalyticsImport.product_data_20130728.productprice ) * AnalyticsImport.product_data_20130728.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT two.hits.item.productSku, Analytics.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice, AnalyticsImport.product_data_20130728.refunddeliveryprice, AnalyticsImport.product_data_20130728.refundquantity
    FROM AnalyticsImport.product_data_20130728
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM GoogleStore.ga_sessions_20130728
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS two
    ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku ) );

Gerçek kâr, bir ürünün kârlılığının geri ödeme yapılan ürünler dikkate alınarak hesaplanmasıyla belirlenir. Bir ürün için toplam geri ödeme tutarını hesaplamak için:

ürünün toplam geri ödeme tutarı = ( ürün fiyatı + ürünün geri ödeme teslim fiyatı ) * geri ödeme yapılan ürün sayısı

Bu size yardımcı oldu mu?

Bunu nasıl iyileştirebiliriz?

Daha fazla yardıma mı ihtiyacınız var?

Bir sonraki adımları deneyin:

Yardım topluluğunda yayınlayın Topluluk üyelerinden sorularınıza cevap alın
Bize ulaşın Bize daha fazla bilgi verin, size yardımcı olalım
true
learning path
Kendi öğrenme rotanızı seçin

Google Analytics 4'ten en iyi şekilde yararlanmanıza yardımcı olacak yeni bir kaynak olan google.com/analytics/learn adresine göz atın. Videolar, makaleler ve rehberli akışlar içeren yeni web sitesi Google Analytics Discord, Blog, YouTube kanalı ve GitHub deposuna bağlantılar da sunuyor.

Hemen öğrenmeye başlayın.

Arama
Aramayı temizle
Aramayı kapat
Ana menü
1003869640972631928
true
Yardım Merkezinde Arayın
true
true
true
true
true
69256
false
false