Cookbook 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 set 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

Format yang salah: menggunakan operator karakter pengganti
SELECT *
FROM [table name];

 

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

Mengizinkan cache

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

Saat ini, hasil yang disimpan dalam cache tidak didukung untuk kueri terhadap beberapa tabel menggunakan karakter pengganti, meskipun jika opsi 'Use Cached Results' 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 mengklik Simpan sebagai Tabel di atas hasil kueri. Selanjutnya, Anda dapat mereferensikan tabel tersebut di bagian FROM pada kueri, yang akan mengurangi jumlah data yang harus diproses dan waktu pemrosesan yang dibutuhkan.

menggunakan tabel perantara
SELECT field1, field2
FROM [Dataset name.table name];

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 flag dryRun.

Kueri yang valid memiliki indikator berwarna hijau yang dapat Anda klik untuk melihat jumlah data yang diproses oleh kueri. Fitur ini memberi Anda kesempatan untuk mengoptimalkan data sebelum menjalankan kueri, sehingga Anda dapat menghindari pemrosesan data yang tidak perlu.

Query Debugging - Success

 

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

Query Debugging - Error

 

Tips dan praktik terbaik

Menggunakan set data sampel

Contoh berikut menggunakan set data sampel Google Analytics.

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

Menggunakan SQL Standar vs Legacy SQL

BigQuery mendukung dua dialek SQL:

Bermigrasi ke SQL Standar menjelaskan perbedaan antara dua 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 membuat kueri 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 Legacy SQL 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
Legacy SQL
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
Legacy SQL
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
Legacy SQL
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
Legacy SQL
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
Legacy SQL
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 (intrahari)

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

Legacy SQL

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

Legacy SQL

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 kunjungan halaman 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

Legacy SQL

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 kunjungan halaman produk menurut jenis pembeli (pembeli vs bukan pembeli)

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

SQL Standar

Jumlah rata-rata kunjungan halaman 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 )

Legacy SQL

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

SQL Standar

Jumlah rata-rata kunjungan halaman 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 )

Legacy SQL

Jumlah rata-rata kunjungan halaman 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 )

Legacy SQL

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 )

Legacy SQL

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 hit

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

Legacy SQL

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 hit menjadi PAGE agar tidak perlu melihat interaksi peristiwa atau transaksi. Setiap baris output mewakili kunjungan halaman dan ditunjukkan oleh urutan default kolom dalam pernyataan SELECT.

 

 

Beberapa dimensi kustom di tingkat hit atau sesi

dimensi kustom di tingkat hit
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 kustom 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:

  • Menampilkan dimensi khusus sebagai kolom baru. Anda dapat mengulangi fungsi ini untuk menampilkan 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 kustom, tetapi untuk setiap kondisi yang bukan index=1 (hits) atau index=2 (sessions), hasil yang ditampilkan adalah NULL.
  • Menampilkan nilai maksimum, yang merupakan nilai Dimensi Kustom 1 untuk hit atau Dimensi Kustom 2 untuk sesi karena semua nilai lain 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-commerce 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-commerce Klasik)
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. Pada baris pertama, Anda memilih semua item lainnya yang dibeli oleh pengguna dan fungsi agregat COUNT() digunakan untuk menghitung kuantitas setiap item lain yang dibeli. Hasilnya akan ditampilkan di kolom berlabel quantity, dengan itemnya yang terkait di kolom produk yang diberi label 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 'Product Item Name A').

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

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

produk yang dibeli oleh pelanggan yang membeli 'Brighton Metallic Pens (Set of 4)' pada 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 '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;

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

Produk yang dibeli oleh pelanggan yang membeli Produk A (Enhanced E-commerce)

Ini sama dengan kueri kerangka sebelumnya, namun berfungsi untuk Enhanced E-commerce. Contoh ini juga memanfaatkan TABLE_DATE_RANGE untuk membuat kueri data selama beberapa hari.

produk yang dibeli oleh pelanggan yang membeli produk A (Enhanced E-commerce)
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 melakukan pembelian

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

Di bawah ini adalah skrip kerangka untuk pertanyaan: Berapa jumlah rata-rata interaksi pengguna sebelum melakukan pembelian?

jumlah interaksi pengguna sebelum melakukan pembelian
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. Baris pertama menjalankan operasi matematika utama untuk menemukan rata-rata interaksi pengguna per produk, dan kueri ini menciptakan join antara dua subkueri yang disebut ‘Alias_Name_1’ dan ‘Alias_Name_2’.
  2. ‘Alias_Name_1’ digunakan untuk menghasilkan kolom yang menggunakan fungsi agregat SUM() untuk menjumlahkan semua jumlah hit yang dicatat untuk sebuah produk.
  3. ‘Alias_Name_2’ digunakan untuk menemukan jumlah hit yang dilakukan oleh pengguna per produk menggunakan fungsi COUNT().
  4. Baris terakhir menunjukkan kolom umum (hits.item.productSku) yang dibagikan antara dua set data pada join.

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

jumlah interaksi pengguna pada 10 September 2013 sebelum melakukan pembelian
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 set data, 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 [‘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. Baris pertama menghasilkan dua 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 menggabungkan baris dari dua set data berdasarkan kolom yang sama di antara keduanya. Dalam hal ini, kedua set data itu adalah [ ‘Imported_DataSet’ ] dan ‘Alias_Name’.
  3. [ ‘Imported_DataSet’ ] adalah data non-Analytics. Ini adalah set data 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 ditampilkan 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 set data dan tempat kedua set data digabungkan.

Banyak variabel dalam kueri ini memiliki nama set data yang dilampirkan sebagai awalan (misalnya, Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). Awalan ini untuk mengklarifikasi kolom yang Anda pilih dan untuk memperjelas set data 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 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.’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. 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 set data. 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 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 set data non-GA.

Profitabilitas riil setiap produk (memperhitungkan pengembalian dana)

Di bawah ini adalah 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 [ ‘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. Kueri ini sangat mirip dengan Berapa profitabilitas setiap produk? Hal yang berbeda hanyalah pada set data non-Analytics di subkueri abu-abu dan pada operasi matematika yang menghitung laba riil di baris pertama.
  2. Dalam set data 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 selengkapnya 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 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 dananya 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?

Perlu bantuan lain?

Coba langkah-langkah selanjutnya berikut:

Posting ke komunitas bantuan Dapatkan jawaban dari anggota komunitas
Hubungi kami Beri tahu kami selengkapnya dan kami akan membantu Anda
true
learning path
Memilih jalur pembelajaran Anda sendiri

Lihat google.com/analytics/learn, referensi baru untuk membantu Anda mendapatkan hasil maksimal dari Google Analytics 4. Situs baru ini berisi video, artikel, dan alur panduan, serta menyediakan link ke berbagai sumber informasi terkait Google Analytics (yaitu, Discord, Blog, channel YouTube, dan repositori GitHub).

Mulai belajar sekarang juga

Telusuri
Hapus penelusuran
Tutup penelusuran
Menu utama
6727513356255212320
true
Pusat Bantuan Penelusuran
true
true
true
true
true
69256
false
false