Panduan BigQuery

Fitur ini hanya tersedia di Analytics 360, bagian dari Google Marketing Platform.
Pelajari Google Marketing Platform lebih lanjut.

Artikel ini berisi contoh cara membuat kueri dari data Analytics yang Anda ekspor ke BigQuery. Kami telah menyediakan kumpulan data sampel sehingga Anda dapat berlatih dengan beberapa kueri dalam artikel ini.

Dalam artikel ini:

Pengoptimalan kueri

Setiap kueri yang Anda jalankan berkontribusi terhadap kuota pemrosesan data bulanan Anda. Jika Anda memilih kolom yang tidak relevan, Anda meningkatkan jumlah data yang perlu diproses, dan akibatnya, Anda menggunakan lebih banyak kuota bulanan daripada yang dibutuhkan. Kueri yang dioptimalkan membuat penggunaan kuota pemrosesan data bulanan Anda menjadi efisien.

Pelajari lebih lanjut penetapan harga.

Hanya pilih yang dibutuhkan

Ketika merumuskan kueri, pilih kolom yang relevan dalam pernyataan SELECT. Dengan mengecualikan kolom yang tidak relevan, Anda mengurangi jumlah data, dan waktu yang dibutuhkan untuk memproses kueri.

Contoh: hindari penggunaan operator karakter pengganti

Bentuk yang salah: menggunakan operator karakter pengganti
SELECT *
FROM [nama tabel];

 

Bentuk yang lebih baik: menggunakan nama kolom untuk menghindari pemrosesan yang tidak perlu
SELECT field1, field2
FROM [nama tabel];

Izinkan cache

Bila memungkinkan, hindari menggunakan fungsi sebagai kolom. Fungsi (seperti NOW() atau TODAY()) mengembalikan hasil variabel, yang mencegah kueri disimpan ke cache dan karena itu dikembalikan lebih cepat. Sebagai gantinya, gunakan waktu dan tanggal spesifik.

Saat ini, hasil yang disimpan dalam cache tidak dukung untuk kueri terhadap beberapa tabel menggunakan karakter pengganti meskipun opsi Gunakan Hasil yang Disimpan dalam Cache dicentang. Jika menjalankan kueri karakter pengganti yang sama beberapa kali, Anda akan ditagih untuk setiap kueri. Pelajari lebih lanjut

Menggunakan tabel perantara untuk subkueri yang biasa digunakan

Jika ternyata Anda berulang kali menggunakan kueri tertentu sebagai subkueri, Anda dapat menyimpan kueri tersebut sebagai tabel perantara dengan mengeklik Simpan sebagai Tabel di atas hasil kueri. Selanjutnya Anda dapat merujuk pada tabel tersebut di bagian FROM pada kueri Anda, yang akan mengurangi jumlah data yang harus diproses dan waktu pemrosesan yang dibutuhkan.

menggunakan tabel perantara
SELECT field1, field2
FROM [Nama dataset.nama tabel];

Proses debug kueri

BigQuery melakukan debug terhadap kode Anda ketika Anda membuatnya. Pada jendela komposisi, proses debug ditunjukkan tepat di bawah kueri. Proses debug juga tersedia melalui API dengan bendera dryRun.

Kueri yang valid memiliki indikator berwarna hijau yang dapat diklik untuk melihat jumlah data yang diproses oleh kueri. Fitur ini memberikan kesempatan untuk mengoptimalkan data sebelum Anda menjalankan kueri sehingga pengolahan data yang tidak perlu dapat dihindari.

Query Debugging - Success

 

Kueri yang tidak valid memiliki indikator berwarna merah yang dapat diklik untuk melihat informasi tentang kesalahan, serta menemukan baris dan kolom yang berisi kesalahan tersebut. Pada contoh di bawah ini, pernyataan GROUP BY kosong, dan kesalahan ditunjukkan.

Query Debugging - Error

 

Tips dan praktik terbaik

Menggunakan kumpulan data sampel

Contoh berikut menggunakan kumpulan data sampel Google Analytics.

Untuk menggunakan kueri pada data Anda sendiri, cukup ganti nama project dan kumpulan data dalam contoh dengan nama project dan kumpulan data Anda sendiri.

Menggunakan SQL Standar vs SQL Lama

BigQuery mendukung 2 dialek SQL:

Bermigrasi ke SQL Standar menjelaskan perbedaan antara 2 dialek tersebut.

SQL Standar sekarang menjadi dialek SQL pilihan untuk membuat kueri pada data yang tersimpan di BigQuery.

Lihat Mengaktifkan SQL Standar untuk informasi tentang cara mengaktifkan SQL Standar di UI BigQuery, CLI, API, atau antarmuka mana pun yang Anda gunakan.

Cara termudah untuk memulai adalah menyertakan komentar "standardSQL" di bagian teratas kueri SQL Standar seperti yang ditunjukkan pada contoh berikut.

>Dengan Legacy SQL, data Google Analytics 360 diteruskan ke tabel baru setiap hari. Untuk mengkueri beberapa tabel sekaligus, Anda dapat menggunakan koma untuk memisahkan nama tabel, menggunakan fungsi karakter pengganti tabel TABLE_DATE_RANGE, atau menggunakan beberapa fungsi TABLE_DATE_RANGE yang dipisahkan koma, seperti dalam contoh berikut.

Membuat kueri beberapa tabel

Contoh berikut menunjukkan kueri SQL Standar dan SQL Lama untuk data yang sama.

3 hari

SQL Standar
3 Hari menggunakan UNION ALL
#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
SQL Lama
3 hari menggunakan nama tabel yang dipisahkan koma
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

 

1095 Hari Terakhir

SQL Standar
1095 Hari Terakhir menggunakan _TABLE_SUFFIX
#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
SQL Lama
1095 hari terakhir menggunakan TABLE_DATE_RANGE
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

 

36 Bulan Terakhir

SQL Standar
36 Bulan Terakhir menggunakan _TABLE_SUFFIX
#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
SQL Lama
36 bulan terakhir menggunakan TABLE_DATE_RANGE
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

 

3 tahun terakhir

SQL Standar
3 Tahun Terakhir menggunakan _TABLE_SUFFIX
#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
SQL Lama
3 tahun terakhir menggunakan TABLE_DATE_RANGE
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

 

Rentang tanggal tertentu

SQL Standar
Rentang tanggal tertentu menggunakan _TABLE_SUFFIX
#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
SQL Lama
Rentang tanggal tertentu menggunakan TABLE_DATE_RANGE
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

3 tahun terakhir ditambah data hari ini (dalam 1 hari)

SQL Standar
3 tahun terakhir ditambah data hari ini (dalam 1 hari) menggunakan UNION ALL & _TABLE_SUFFIX
Catatan: contoh kueri ini tidak akan berfungsi dengan kumpulan data publik Google Analytics karena saat ini tidak ada tabel dalam 1 hari.
#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

SQL Lama

3 tahun terakhir ditambah data hari ini (dalam 1 hari) menggunakan beberapa TABLE_DATE_RANGE
Catatan: contoh kueri ini tidak akan berfungsi dengan kumpulan data publik Google Analytics karena saat ini tidak ada tabel dalam 1 hari.
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

 

 

Contoh kueri dasar

Bagian ini menjelaskan cara membuat kueri dasar menggunakan metrik dan dimensi dari contoh data Analytics.

Total [metrik] per [dimensi]?

Di bawah ini adalah contoh skrip untuk pertanyaan: Berapa jumlah total transaksi yang dihasilkan per browser perangkat pada Juli 2017?

SQL Standar

Total transaksi per browser perangkat pada Juli 2017
#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

SQL Lama

Total transaksi per browser perangkat pada Juli 2017
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

 

 

Rasio pantulan rata-rata per [ dimensi ]?

Rasio pantulan yang sesungguhnya didefinisikan sebagai persentase kunjungan dengan pageview tunggal. Berikut ini contoh skrip untuk pertanyaan: Berapa rasio pantulan yang sesungguhnya per sumber traffic?

SQL Standar

Rasio pantulan per sumber traffic pada Juli 2017
#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

SQL Lama

Rasio pantulan per sumber traffic pada Juli 2017
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

 

 

Jumlah rata-rata pageview produk menurut jenis pembeli (pembeli vs non-pembeli)

Di bawah ini adalah contoh skrip untuk pertanyaan: Berapa jumlah rata-rata pageview produk untuk pengguna yang melakukan pembelian pada Juli 2017?

SQL Standar

Jumlah rata-rata pageview produk untuk pengguna yang melakukan pembelian pada Juli 2017
#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 )

SQL Lama

Jumlah rata-rata pageview produk untuk pengguna yang melakukan pembelian pada Juli 2017
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 )

 

 

Berikut ini contoh skrip untuk pertanyaan: Berapa jumlah rata-rata pageview produk untuk pengguna yang tidak melakukan pembelian pada Juli 2017?

SQL Standar

Jumlah rata-rata pageview produk untuk pengguna yang tidak melakukan pembelian pada Juli 2017
#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 )

SQL Lama

Jumlah rata-rata pageview produk untuk pengguna yang tidak melakukan pembelian pada Juli 2017
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 )

 

 

Jumlah rata-rata transaksi per pembeli

Di bawah ini adalah contoh skrip untuk pertanyaan: Berapa total transaksi rata-rata per pengguna yang melakukan pembelian pada Juli 2017?

SQL Standar

Jumlah rata-rata transaksi per pengguna yang melakukan pembelian pada Juli 2017
#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 )

SQL Lama

Jumlah rata-rata transaksi per pengguna yang melakukan pembelian pada Juli 2017
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 )

 

 

Jumlah rata-rata uang yang dibelanjakan per sesi

Di bawah ini adalah contoh skrip untuk pertanyaan: Berapa jumlah rata-rata uang yang dibelanjakan per sesi pada Juli 2017?

SQL Standar

Jumlah rata-rata uang yang dibelanjakan per sesi pada Juli 2017
#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 )

SQL Lama

Jumlah rata-rata uang yang dibelanjakan per sesi pada Juli 2017
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 )

 

 

Urutan klik

Di bawah ini adalah contoh skrip untuk pertanyaan: Apa urutan halaman yang dilihat?.

SQL Standar

Urutan halaman yang dilihat oleh pengguna pada Juli 2017
#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

SQL Lama

Urutan halaman yang dilihat oleh pengguna pada Juli 2017
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

Dalam kueri ini, Anda membatasi jenis klik menjadi PAGE supaya tidak melihat interaksi peristiwa atau transaksi. Setiap baris hasil menggambarkan tayangan halaman dan ditunjukkan oleh urutan default kolom di pernyataan SELECT.

 

 

Beberapa dimensi khusus di tingkat klik atau sesi

dimensi khusus di tingkat klik
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

 

dimensi khusus di tingkat sesi
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

Di setiap kueri:

Pernyataan SELECT mengajukan kueri untuk kolom dimensi dan metrik yang relevan.

Fungsi MAX:

  • Mengembalikan dimensi khusus sebagai kolom baru. Anda dapat mengulangi fungsi ini untuk mengembalikan beberapa dimensi khusus sebagai kolom baru.
  • WITHIN hits dan WITHIN RECORD mengevaluasi kondisi di dalam kolom berulang di BigQuery.
  • Kondisi di dalam MAX dievaluasi untuk setiap dimensi khusus, tetapi untuk yang bukan index=1 (hits) atau index=2 (sessions), NULL akan dikembalikan.
  • Mengembalikan nilai maksimum, yaitu nilai Dimensi Khusus 1 untuk klik atau Dimensi Khusus 2 untuk sesi karena semua nilai lainnya NULL.

Contoh kueri lanjutan

Setelah memahami kueri sederhana, sekarang Anda dapat membuat kueri menggunakan fungsi dan fitur lanjutan yang tersedia di BigQuery.

Produk yang dibeli oleh pelanggan yang membeli produk A (E-niaga Klasik)

Di bawah ini adalah skrip kerangka untuk pertanyaan: Apa produk lain yang dibeli oleh pelanggan yang membeli produk A?

produk yang dibeli oleh pelanggan yang membeli produk A (E-niaga Klasik)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [‘Nama Dataset' ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Nama Dataset’ ]
  WHERE hits.item.productName CONTAINS 'Nama Item Produk A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId
)
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Nama Item Produk A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. Pada baris pertama, Anda memilih semua item lainnya yang dibeli oleh pengguna dan fungsi penjumlahan COUNT() digunakan untuk menghitung kuantitas setiap item lain yang dibeli. Hasilnya kemudian ditampilkan di kolom berlabel quantity, dengan itemnya yang terkait di kolom produk yang dilabeli sebagai other_purchased_products.
  2. Di subkueri abu-abu, Anda hanya memilih pengguna unik (fullVisitorId) yang telah melakukan transaksi (totals.transactions>=1) dan selama transaksi membeli produk A (WHERE hits.item.productName CONTAINS 'Nama Item Produk A').

Aturan (pernyataan WHERE dan AND) di kueri tingkat teratas (hijau) mengabaikan nilai di hits.item.productName yang nihil dan berisi produk A.

Berikut ini contoh dari kueri Jika pelanggan membeli Pulpen Metalik Brighton - Set berisi 4, apa produk lainnya yang dia beli?

produk yang dibeli oleh pelanggan yang membeli 'Pulpen Metalik Brighton (Set berisi 4)' pada tanggal 24 Juni 2013
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 'Pulpen Metalik Brighton - Set berisi 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId
)
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Pulpen Metalik Brighton - Set berisi 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Di Dremel/BigQuery, menggunakan WHERE expr IN memicu JOIN, dan pembatasan ukuran berlaku, terutama, ukuran sisi kanan JOIN (dalam hal ini jumlah pengunjung) harus kurang dari 8 MB. Di Dremel, ini disebut broadcast JOIN. Bila ukurannya melebihi 8 MB, Anda harus memicu shuffled JOIN, yang dapat dilakukan menggunakan sintaks JOIN EACH. Sayangnya, hal ini tidak dapat dilakukan menggunakan IN, tetapi kueri yang sama dapat ditulis ulang dengan JOIN.

Produk yang dibeli oleh pelanggan yang membeli Produk A (E-niaga yang Disempurnakan)

Ini sama dengan kueri kerangka sebelumnya, namun berfungsi untuk E-niaga yang Disempurnakan. Ini juga memanfaatkan TABLE_DATE_RANGE untuk membuat kueri data selama beberapa hari.

produk yang dibeli oleh pelanggan yang membeli produk A (E-niaga yang Disempurnakan)
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;

Jumlah rata-rata interaksi pengguna sebelum membeli

Ini adalah contoh kueri perintah JOIN() [...] ON, yang hanya bergantung pada data Analytics.

Berikut ini skrip kerangka untuk pertanyaan: Berapa jumlah rata-rata interaksi pengguna sebelum membeli?

jumlah interaksi pengguna sebelum membeli
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 [‘Nama Dataset GA’ ]
 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 [‘Nama Dataset GA’ ]
 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. Baris pertama menjalankan operasi matematika utama untuk menemukan interaksi pengguna rata-rata per produk, dan kueri ini menciptakan pertalian antara dua subkueri yang disebut ‘Alias_Name_1’ dan ‘Alias_Name_2’.
  2. Alias_Name_1’ digunakan untuk menghasilkan bidang yang menggunakan fungsi penjumlahan SUM() untuk menjumlahkan semua jumlah klik yang dicatat untuk sebuah produk.
  3. Alias_Name_2’ digunakan untuk menemukan jumlah klik yang dilakukan oleh pengguna per produk dengan menggunakan fungsi COUNT().
  4. Baris terakhir menunjukkan kolom umum (hits.item.productSku) yang dibagi di antara 2 dataset pada pertalian tersebut.

Berikut ini contoh dari kueri Pada tanggal 10 September 2013, berapa jumlah rata-rata interaksi pengguna sebelum membeli?

jumlah interaksi pengguna pada tanggal 10 September 2013 sebelum membeli
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;

Persentase stok yang terjual per produk

Ini adalah contoh kueri yang tidak hanya bergantung pada data Analytics, tetapi juga data non-Analytics. Dengan menggabungkan kedua dataset, Anda dapat mulai memahami perilaku pengguna pada tingkat yang lebih tersegmentasi. Anda bisa mengimpor data non-Analytics ke BigQuery, tetapi perlu diingat bahwa ini akan memengaruhi tagihan penyimpanan data bulanan Anda.

Di bawah ini adalah skrip kerangka untuk pertanyaan: Berapa persentase stok terjual per produk?

persentase stok yang terjual per produk
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 [‘Dataset GA’ ]
  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. Baris pertama menghasilkan 2 kolom: Satu berisi semua ID produk, dan satu lagi berisi operasi matematika yang menunjukkan persentase stok yang terjual untuk ID produk tersebut.
  2. Karena kueri ini bergantung pada dua set data, Anda perlu menggunakan fungsi JOIN() ... ON. Perintah ini menghubungkan baris dari 2 dataset berdasarkan kolom yang sama di antara baris tersebut. Dalam hal ini, kedua dataset itu adalah [ ‘Imported_DataSet’ ] dan ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] adalah data non-Analytics. Ini adalah dataset yang berisi kolom metrik untuk jumlah stok yang tersisa (Imported DataSet.’stock_left_field’) dan kolom dimensi ID produk (Imported_DataSet.’productId_field’).
  4. ‘Alias_Name’ adalah nama yang diberikan ke data yang dikembalikan oleh subkueri abu-abu. Subkueri ini menggunakan data Analytics untuk mengetahui total kuantitas item yang terjual per produk.
  5. Baris terakhir menggunakan pernyataan ON untuk menunjukkan kolom yang sama di antara kedua dataset dan tempat kedua dataset dihubungkan.

Banyak variabel di kueri ini memiliki nama dataset yang dilampirkan sebagai awalan (misalnya, Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Ini untuk mengklarifikasi kolom yang Anda pilih dan untuk memperjelas dataset asal kolom tersebut.

Berikut ini contoh dari kueri Berapa persentase stok yang terjual per produk pada tanggal 28 Juli 2013?

persentase stok yang terjual per produk pada tanggal 28 Juli 2013
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;

Profitabilitas setiap produk

Berikut ini skrip kerangka untuk pertanyaan: Berapa profitabilitas setiap produk?

laba berdasarkan produk
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’kolom laba produk’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’kolom laba produk’
  FROM [ ‘Dataset yang Diimpor’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘Nama Dataset GA’ ]
    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. Baris pertama berisi operasi matematika untuk menghitung total laba yang dihasilkan pada setiap produk.
  2. Subkueri abu-abu menggunakan data non-Analytics, yang mengumpulkan data tentang jumlah laba yang dihasilkan ketika produk terjual.
  3. Subkueri merah adalah subkueri data Analytics, yang akan digabungkan dengan data non-Analytics. Ini menghitung kuantitas item yang terjual per produk.
  4. Baris terakhir menggunakan pernyataan ON untuk memperjelas kolom yang sama dari kedua dataset. Dalam contoh ini, kolom tersebut adalah nomor ID produk.

Berikut ini contoh dari kueri Berapa profitabilitas setiap produk pada tanggal 28 Juli 2013?

laba berdasarkan produk pada tanggal 28 Juli 2013
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
);

Laba dihitung dengan mencari selisih antara harga jual produk dan biaya produksi. Informasi ini disimpan pada dataset non-GA.

Profitabilitas riil setiap produk (memperhitungkan pengembalian dana)

Berikut ini skrip kerangka untuk pertanyaan: Berapa profitabilitas riil setiap produk?

laba riil berdasarkan produk
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 [ ‘Nama Dataset yang Diimpor’ ] AS 'Imported_DataSet'
    JOIN (
      
SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘Nama Dataset GA’ ]
      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. Kueri ini sangat mirip dengan Berapa profitabilitas setiap produk? Hal yang berbeda hanyalah pada dataset non-Analytics di subkueri abu-abu dan pada operasi matematika yang menghitung laba riil di baris pertama.
  2. Dalam dataset non-Analytics, Anda juga menghitung jumlah total uang yang dihabiskan untuk pengembalian dana (dalam pernyataan SELECT subkueri merah).
  3. Kemudian Anda menjalankan operasi matematika pada baris 1 untuk menemukan laba riil dengan mengurangi pendapatan yang dibelanjakan untuk pengembalian dana dari laba kotor Anda.

Untuk informasi lebih lanjut tentang kueri, lihat bagian profitabilitas setiap produk.

Berikut ini contoh dari kueri berikut Berapa profitabilitas riil setiap produk pada tanggal 28 Juli 2013?

laba riil berdasarkan produk pada tanggal 28 Juli 2013
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 )
);

Laba riil memperhitungkan profitabilitas suatu produk setelah memperhitungkan produk yang dikembalikan. Untuk menghitung total pendapatan yang dikembalikan untuk suatu produk:

total pendapatan yang dikembalikan untuk suatu produk = (harga produk + harga pengiriman pengembalian untuk produk) * kuantitas produk yang dikembalikan

Apakah ini membantu?
Bagaimana cara meningkatkannya?