SQL Standar di Google BigQuery: Keuntungan dan Contoh Penggunaan dalam Pemasaran

Diterbitkan: 2022-04-12

Pada tahun 2016, Google BigQuery memperkenalkan cara baru untuk berkomunikasi dengan tabel: SQL Standar. Sampai saat itu, BigQuery memiliki bahasa kueri terstrukturnya sendiri yang disebut BigQuery SQL (sekarang disebut Legacy SQL).

Sekilas, tidak ada banyak perbedaan antara Legacy dan Standard SQL: nama tabel ditulis sedikit berbeda; Standar memiliki persyaratan tata bahasa yang sedikit lebih ketat (misalnya, Anda tidak dapat menempatkan koma sebelum FROM) dan lebih banyak tipe data. Tetapi jika Anda perhatikan lebih dekat, ada beberapa perubahan sintaks kecil yang memberi banyak keuntungan bagi pemasar.

Dalam artikel ini, Anda akan mendapatkan jawaban atas pertanyaan-pertanyaan berikut:

Apa kelebihan SQL Standar dibandingkan SQL Legacy?
Apa saja kemampuan SQL Standar dan bagaimana penggunaannya?
Bagaimana saya bisa pindah dari Legacy ke Standard SQL?
Layanan, fitur sintaks, operator, dan fungsi lain apa yang kompatibel dengan SQL Standar?
Bagaimana saya bisa menggunakan kueri SQL untuk laporan pemasaran?

Apa kelebihan SQL Standar dibandingkan SQL Legacy?

Tipe data baru: array dan bidang bersarang

SQL standar mendukung tipe data baru: ARRAY dan STRUCT (array dan bidang bersarang). Artinya, di BigQuery, bekerja dengan tabel yang dimuat dari file JSON/Avro menjadi lebih mudah, yang sering kali berisi lampiran multi-level.

Bidang bersarang adalah tabel mini di dalam tabel yang lebih besar:

Pada diagram di atas, bilah biru dan kuning adalah garis di mana tabel mini disematkan. Setiap baris adalah satu sesi. Sesi memiliki parameter umum: tanggal, nomor ID, kategori perangkat pengguna, browser, sistem operasi, dll. Selain parameter umum untuk setiap sesi, tabel klik dilampirkan ke baris.

Tabel klik berisi informasi tentang tindakan pengguna di situs. Misalnya, jika pengguna mengklik spanduk, membolak-balik katalog, membuka halaman produk, memasukkan produk ke keranjang, atau memesan, tindakan ini akan dicatat di tabel klik.

Jika pengguna memesan di situs, informasi tentang pesanan juga akan dimasukkan di tabel klik:

transactionId (nomor yang mengidentifikasi transaksi)
transaksiRevenue (nilai total pesanan)
transaksiPengiriman (biaya pengiriman)

Tabel data sesi yang dikumpulkan menggunakan OWOX BI memiliki struktur yang serupa.

Misalkan Anda ingin mengetahui jumlah pesanan dari pengguna di New York City selama sebulan terakhir. Untuk mengetahuinya, Anda perlu merujuk ke tabel hit dan menghitung jumlah ID transaksi unik. Untuk mengekstrak data dari tabel tersebut, SQL Standar memiliki fungsi UNNEST:

    #standardSQL SELECT COUNT (DISTINCT hits.transaction.transactionId) -- count the number of unique order numbers; DISTINCT helps to avoid duplication FROM `project_name.dataset_name.owoxbi_sessions_*` -- refer to the table group (wildcard tables) WHERE ( _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTHS)) -- if we don't know which dates we need, it's better to use the function FORMAT_DATE INTERVAL AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) ) AND geoNetwork.city = 'New York' -- choose orders made in New York City
#standardSQL SELECT COUNT (DISTINCT hits.transaction.transactionId) -- count the number of unique order numbers; DISTINCT helps to avoid duplication FROM `project_name.dataset_name.owoxbi_sessions_*` -- refer to the table group (wildcard tables) WHERE ( _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTHS)) -- if we don't know which dates we need, it's better to use the function FORMAT_DATE INTERVAL AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) ) AND geoNetwork.city = 'New York' -- choose orders made in New York City

Jika informasi pesanan dicatat dalam tabel terpisah dan bukan dalam tabel bersarang, Anda harus menggunakan GABUNG untuk menggabungkan tabel dengan informasi pesanan dan tabel dengan data sesi untuk mengetahui di mana urutan sesi dibuat.

Opsi subkueri lainnya

Jika Anda perlu mengekstrak data dari bidang bertingkat multi-level, Anda dapat menambahkan subkueri dengan SELECT dan WHERE. Misalnya, dalam tabel streaming sesi OWOX BI, subtabel lain, produk, ditulis ke subtabel hits. Subtabel produk mengumpulkan data produk yang dikirimkan dengan larik E-niaga yang Disempurnakan. Jika e-niaga yang disempurnakan diatur di situs dan pengguna telah melihat halaman produk, karakteristik produk ini akan dicatat dalam subtabel produk.

Untuk mendapatkan karakteristik produk ini, Anda memerlukan subquery di dalam kueri utama. Untuk setiap karakteristik produk, subkueri SELECT terpisah dibuat dalam tanda kurung:

    SELECT column_name1, -- list the other columns you want to receive column_name2, (SELECT productBrand FROM UNNEST(hits.product)) AS hits_product_productBrand, (SELECT productRevenue FROM UNNEST(hits.product)) AS hits_product_productRevenue, -- list product features (SELECT localProductRevenue FROM UNNEST(hits.product)) AS hits_product_localProductRevenue, (SELECT productPrice FROM UNNEST(hits.product)) AS hits_product_productPrice, FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD`
SELECT column_name1, -- list the other columns you want to receive column_name2, (SELECT productBrand FROM UNNEST(hits.product)) AS hits_product_productBrand, (SELECT productRevenue FROM UNNEST(hits.product)) AS hits_product_productRevenue, -- list product features (SELECT localProductRevenue FROM UNNEST(hits.product)) AS hits_product_localProductRevenue, (SELECT productPrice FROM UNNEST(hits.product)) AS hits_product_productPrice, FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD`

Berkat kemampuan SQL Standar, lebih mudah untuk membangun logika kueri dan menulis kode. Sebagai perbandingan, di Legacy SQL, Anda perlu menulis jenis tangga ini:

    SELECT column_name1, column_name2, column_name3 FROM ( SELECT table_name.some_column AS column1… FROM table_name )
SELECT column_name1, column_name2, column_name3 FROM ( SELECT table_name.some_column AS column1… FROM table_name )

Permintaan ke sumber eksternal

Menggunakan SQL Standar, Anda dapat mengakses tabel BigQuery langsung dari Google Bigtable, Google Cloud Storage, Google Drive, dan Google Spreadsheet.
Artinya, alih-alih memuat seluruh tabel ke BigQuery, Anda dapat menghapus data dengan satu kueri, memilih parameter yang Anda butuhkan, dan mengunggahnya ke penyimpanan cloud.

Lebih banyak fungsi pengguna (UDF)

Jika Anda perlu menggunakan rumus yang tidak didokumentasikan, Fungsi Buatan Pengguna (UDF) akan membantu Anda. Dalam praktik kami, ini jarang terjadi, karena dokumentasi SQL Standar mencakup hampir semua tugas analitik digital.

Dalam SQL Standar, fungsi yang ditentukan pengguna dapat ditulis dalam SQL atau JavaScript; SQL lama hanya mendukung JavaScript. Argumen dari fungsi-fungsi ini adalah kolom, dan nilai yang diambilnya adalah hasil dari manipulasi kolom. Dalam SQL Standar, fungsi dapat ditulis di jendela yang sama dengan kueri.

Lebih banyak kondisi GABUNG

Di Legacy SQL, kondisi GABUNG dapat didasarkan pada kesetaraan atau nama kolom. Selain opsi ini, dialek SQL Standar mendukung GABUNG dengan ketidaksetaraan dan ekspresi arbitrer.

Misalnya, untuk mengidentifikasi mitra BPA yang tidak adil, kami dapat memilih sesi yang sumbernya diganti dalam waktu 60 detik dari transaksi. Untuk melakukan ini di SQL Standar, kita dapat menambahkan ketidaksetaraan ke kondisi GABUNG:

    #standardSQL SELECT * FROM ( SELECT traff.clientId AS clientId, traff.page_path AS pagePath, traff.traffic_source AS startSource, traff.traffic_medium AS startMedium, traff.time AS startTime, aff.evAction AS evAction, aff.evSource AS finishSource, aff.evMedium AS finishMedium, aff.evCampaign AS finishCampaign, aff.time AS finishTime, aff.isTransaction AS isTransaction, aff.pagePath AS link, traff.time-aff.time AS diff FROM ( SELECT fullVisitorID AS clientId, h.page.pagePath AS page_path, trafficSource.source AS traffic_source, trafficSource.medium AS traffic_medium, trafficSource.campaign AS traffic_campaign, date, SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time FROM `demoproject.google_analytics_sample.ga_sessions_20190301`, UNNEST (hits) AS h WHERE trafficSource.medium != 'cpa' ) AS traff JOIN ( SELECT total.date date, total.time time, total.clientId AS clientId, total.eventAction AS evAction, total.source AS evSource, total.medium AS evMedium, total.campaign AS evCampaign, tr.eventAction AS isTransaction, total.page_path AS pagePath FROM ( SELECT fullVisitorID AS clientId, h.page.pagePath AS page_path, h.eventInfo.eventAction AS eventAction, trafficSource.source AS source, trafficSource.medium AS medium, trafficSource.campaign AS campaign, date, SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time FROM `demoproject.google_analytics_sample.ga_sessions_20190301`, UNNEST(hits) AS h WHERE trafficSource.medium ='cpa' ) AS total LEFT JOIN ( SELECT fullVisitorID AS clientId, date, h.eventInfo.eventAction AS eventAction, h.page.pagePath pagePath, SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time FROM `demoproject.google_analytics_sample.ga_sessions_20190301`, UNNEST(hits) AS h WHERE h.eventInfo.eventAction = 'typ_page' AND h.type = 'EVENT' GROUP BY 1, 2, 3, 4, 5 ) AS tr ON total.clientId=tr.clientId AND total.date=tr.date AND tr.time>total.time -- JOIN tables by inequality. Pass the additional WHERE clause that was needed in Legacy SQL WHERE tr.eventAction = 'typ_page' ) AS aff ON traff.clientId = aff.clientId ) WHERE diff> -60 AND diff<0 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 ORDER BY clientId, finishTime
#standardSQL SELECT * FROM ( SELECT traff.clientId AS clientId, traff.page_path AS pagePath, traff.traffic_source AS startSource, traff.traffic_medium AS startMedium, traff.time AS startTime, aff.evAction AS evAction, aff.evSource AS finishSource, aff.evMedium AS finishMedium, aff.evCampaign AS finishCampaign, aff.time AS finishTime, aff.isTransaction AS isTransaction, aff.pagePath AS link, traff.time-aff.time AS diff FROM ( SELECT fullVisitorID AS clientId, h.page.pagePath AS page_path, trafficSource.source AS traffic_source, trafficSource.medium AS traffic_medium, trafficSource.campaign AS traffic_campaign, date, SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time FROM `demoproject.google_analytics_sample.ga_sessions_20190301`, UNNEST (hits) AS h WHERE trafficSource.medium != 'cpa' ) AS traff JOIN ( SELECT total.date date, total.time time, total.clientId AS clientId, total.eventAction AS evAction, total.source AS evSource, total.medium AS evMedium, total.campaign AS evCampaign, tr.eventAction AS isTransaction, total.page_path AS pagePath FROM ( SELECT fullVisitorID AS clientId, h.page.pagePath AS page_path, h.eventInfo.eventAction AS eventAction, trafficSource.source AS source, trafficSource.medium AS medium, trafficSource.campaign AS campaign, date, SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time FROM `demoproject.google_analytics_sample.ga_sessions_20190301`, UNNEST(hits) AS h WHERE trafficSource.medium ='cpa' ) AS total LEFT JOIN ( SELECT fullVisitorID AS clientId, date, h.eventInfo.eventAction AS eventAction, h.page.pagePath pagePath, SAFE_CAST(visitStartTime+h.time/1000 AS INT64) AS time FROM `demoproject.google_analytics_sample.ga_sessions_20190301`, UNNEST(hits) AS h WHERE h.eventInfo.eventAction = 'typ_page' AND h.type = 'EVENT' GROUP BY 1, 2, 3, 4, 5 ) AS tr ON total.clientId=tr.clientId AND total.date=tr.date AND tr.time>total.time -- JOIN tables by inequality. Pass the additional WHERE clause that was needed in Legacy SQL WHERE tr.eventAction = 'typ_page' ) AS aff ON traff.clientId = aff.clientId ) WHERE diff> -60 AND diff<0 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 ORDER BY clientId, finishTime

Satu-satunya batasan SQL Standar sehubungan dengan GABUNG adalah tidak mengizinkan semi-gabung dengan subkueri formulir WHERE kolom IN (PILIH ...):

    #legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE -- such a construction cannot be used in Standard SQL state IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE -- such a construction cannot be used in Standard SQL state IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC

Lebih sedikit kemungkinan kesalahan

Beberapa fungsi di Legacy SQL mengembalikan NULL jika kondisinya salah. Misalnya, jika pembagian dengan nol telah merayap ke dalam perhitungan Anda, kueri akan dieksekusi dan entri NULL akan muncul di baris tabel yang dihasilkan. Ini mungkin menutupi masalah dalam kueri atau data.

Logika SQL Standar lebih mudah. Jika suatu kondisi atau input data salah, kueri akan menghasilkan kesalahan, misalnya «pembagian dengan nol,» sehingga Anda dapat dengan cepat memperbaiki kueri tersebut. Pemeriksaan berikut disematkan dalam SQL Standar:

Nilai yang valid untuk +, -, ×, SUM, AVG, STDEV
Pembagian dengan nol

Permintaan berjalan lebih cepat

Kueri GABUNG yang ditulis dalam SQL Standar lebih cepat daripada yang ditulis dalam SQL Legacy berkat penyaringan awal data yang masuk. Pertama, kueri memilih baris yang cocok dengan kondisi GABUNG, lalu memprosesnya.
Di masa mendatang, Google BigQuery akan berupaya meningkatkan kecepatan dan performa kueri hanya untuk SQL Standar.

Tabel dapat diedit: menyisipkan dan menghapus baris, memperbarui

Fungsi Data Manipulation Language (DML) tersedia di SQL Standar. Ini berarti Anda dapat memperbarui tabel dan menambahkan atau menghapus baris dari tabel tersebut melalui jendela yang sama tempat Anda menulis kueri. Misalnya, menggunakan DML, Anda dapat menggabungkan data dari dua tabel menjadi satu:

    #standardSQL MERGE dataset.Inventory AS T USING dataset.NewArrivals AS S ON T.ProductID = S.ProductID WHEN MATCHED THEN UPDATE SET quantity = T.quantity + S.quantity WHEN NOT MATCHED THEN INSERT (ProductID, quantity) VALUES (ProductID, quantity)
#standardSQL MERGE dataset.Inventory AS T USING dataset.NewArrivals AS S ON T.ProductID = S.ProductID WHEN MATCHED THEN UPDATE SET quantity = T.quantity + S.quantity WHEN NOT MATCHED THEN INSERT (ProductID, quantity) VALUES (ProductID, quantity)

Kode lebih mudah dibaca dan diedit

Dengan SQL Standar, kueri kompleks dapat dimulai tidak hanya dengan SELECT tetapi juga dengan WITH, membuat kode lebih mudah dibaca, dikomentari, dan dipahami. Ini juga berarti lebih mudah untuk mencegah kesalahan Anda sendiri dan memperbaiki kesalahan orang lain.

    #standardSQL WITH total_1 AS ( -- the first subquery in which the intermediate indicator will be calculated SELECT id, metric1, SUM(metric2) AS total_sum1 FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD` GROUP BY id, metric ), total_2 AS ( -- the second subquery SELECT id, metric1, SUM(metric2) AS total_sum2 FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD` GROUP BY id, metric1 ), total_3 AS ( -- the third subquery SELECT id, metric, SUM(metric2) AS total_sum3 FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD` GROUP BY id, metric ) SELECT *, ROUND(100*( total_2.total_sum2 - total_3.total_sum3) / total_3.total_sum3, 3) AS difference -- get the difference index: subtract the value of the second subquery from the value of the third; divide by the value of the third FROM total_1 ORDER BY 1, 2
#standardSQL WITH total_1 AS ( -- the first subquery in which the intermediate indicator will be calculated SELECT id, metric1, SUM(metric2) AS total_sum1 FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD` GROUP BY id, metric ), total_2 AS ( -- the second subquery SELECT id, metric1, SUM(metric2) AS total_sum2 FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD` GROUP BY id, metric1 ), total_3 AS ( -- the third subquery SELECT id, metric, SUM(metric2) AS total_sum3 FROM `project_name.dataset_name.owoxbi_sessions_YYYYMMDD` GROUP BY id, metric ) SELECT *, ROUND(100*( total_2.total_sum2 - total_3.total_sum3) / total_3.total_sum3, 3) AS difference -- get the difference index: subtract the value of the second subquery from the value of the third; divide by the value of the third FROM total_1 ORDER BY 1, 2

Lebih mudah untuk bekerja dengan operator WITH jika Anda memiliki perhitungan yang dilakukan dalam beberapa tahap. Pertama, Anda dapat mengumpulkan metrik perantara di subkueri, lalu melakukan penghitungan akhir.

Google Cloud Platform (GCP), yang mencakup BigQuery, adalah platform siklus penuh untuk bekerja dengan data besar, mulai dari mengatur gudang data atau awan data hingga menjalankan eksperimen ilmiah serta analitik prediktif dan preskriptif. Dengan diperkenalkannya SQL Standar, BigQuery memperluas audiensnya. Bekerja dengan GCP menjadi lebih menarik bagi analis pemasaran, analis produk, ilmuwan data, dan tim spesialis lainnya.

Kemampuan SQL Standar dan contoh kasus penggunaan

Di OWOX BI, kami sering bekerja dengan tabel yang dikompilasi menggunakan ekspor Google Analytics 360 standar ke Google BigQuery atau Pipeline OWOX BI. Dalam contoh di bawah ini, kita akan melihat spesifikasi kueri SQL untuk data tersebut.

Jika Anda belum mengumpulkan data dari situs Anda di BigQuery, Anda dapat mencoba melakukannya secara gratis dengan OWOX BI versi uji coba.

DAPATKAN UJI COBA

1. Pilih data untuk interval waktu

Di Google BigQuery, data perilaku pengguna untuk situs Anda disimpan dalam tabel karakter pengganti (tabel dengan tanda bintang); meja terpisah dibentuk untuk setiap hari. Tabel-tabel ini memiliki nama yang sama: hanya sufiksnya yang berbeda. Sufiksnya adalah tanggal dalam format YYYYMMDD. Misalnya, tabel owoxbi_sessions_20190301 berisi data tentang sesi untuk 1 Maret 2019.

Kita dapat merujuk langsung ke sekelompok tabel tersebut dalam satu permintaan untuk mendapatkan data, misalnya, dari 1 Februari hingga 28 Februari 2019. Untuk melakukan ini, kita perlu mengganti YYYYMMDD dengan * di FROM, dan di WHERE, kita perlu menentukan sufiks tabel untuk awal dan akhir interval waktu:

    #standardSQL SELECT sessionId, FROM `project_name.dataset_name.owoxbi_sessions_*` WHERE _TABLE_SUFFIX BETWEEN �' AND �'
#standardSQL SELECT sessionId, FROM `project_name.dataset_name.owoxbi_sessions_*` WHERE _TABLE_SUFFIX BETWEEN �' AND �'

Tanggal spesifik yang kami ingin kumpulkan datanya tidak selalu kami ketahui. Misalnya, setiap minggu kita mungkin perlu menganalisis data selama tiga bulan terakhir. Untuk melakukan ini, kita dapat menggunakan fungsi FORMAT_DATE:

    #standardSQL SELECT <enumerate field names> FROM `project_name.dataset_name.owoxbi_sessions_*` WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTHS)) AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
#standardSQL SELECT <enumerate field names> FROM `project_name.dataset_name.owoxbi_sessions_*` WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTHS)) AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

Setelah BETWEEN, kami mencatat sufiks dari tabel pertama. Frasa TANGGAL_JALAN (), INTERVAL 3 BULAN berarti «pilih data untuk 3 bulan terakhir dari tanggal saat ini.» Akhiran tabel kedua diformat setelah AND. Ini diperlukan untuk menandai akhir interval seperti kemarin: CURRENT_DATE (), INTERVAL 1 DAY.

2. Ambil parameter dan indikator pengguna

Parameter dan metrik pengguna di tabel Ekspor Google Analytics ditulis ke tabel klik bersarang dan ke subtabel Dimensi kustom dan metrik kustom. Semua dimensi khusus dicatat dalam dua kolom: satu untuk jumlah parameter yang dikumpulkan di situs, yang kedua untuk nilainya. Berikut tampilan semua parameter yang ditransmisikan dengan satu pukulan:

Untuk membongkarnya dan menulis parameter yang diperlukan di kolom terpisah, kami menggunakan kueri SQL berikut:

    -- Custom Dimensions (in the line below index - the number of the user variable, which is set in the Google Analytics interface; dimension1 is the name of the custom parameter, which you can change as you like. For each subsequent parameter, you need to write the same line: (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension1, -- Custom Metrics: the index below is the number of the user metric specified in the Google Analytics interface; metric1 is the name of the metric, which you can change as you like. For each of the following metrics, you need to write the same line: (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metric1
-- Custom Dimensions (in the line below index - the number of the user variable, which is set in the Google Analytics interface; dimension1 is the name of the custom parameter, which you can change as you like. For each subsequent parameter, you need to write the same line: (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension1, -- Custom Metrics: the index below is the number of the user metric specified in the Google Analytics interface; metric1 is the name of the metric, which you can change as you like. For each of the following metrics, you need to write the same line: (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metric1

Begini tampilannya dalam permintaan:

    #standardSQL SELECT <column name1>, <column_name2>, -- list column names (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS page_type, (SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customDimensions)) AS visitor_type, -- produce the necessary custom dimensions (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metric1 -- produce the necessary custom metrics <column_name3> -- if you need more columns, continue to list FROM `project_name.dataset_name.owoxbi_sessions_20190201`
#standardSQL SELECT <column name1>, <column_name2>, -- list column names (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS page_type, (SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customDimensions)) AS visitor_type, -- produce the necessary custom dimensions (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metric1 -- produce the necessary custom metrics <column_name3> -- if you need more columns, continue to list FROM `project_name.dataset_name.owoxbi_sessions_20190201`

Pada tangkapan layar di bawah, kami telah memilih parameter 1 dan 2 dari data demo Google Analytics 360 di Google BigQuery dan menyebutnya page_type dan client_id. Setiap parameter dicatat dalam kolom terpisah:

3. Hitung jumlah sesi berdasarkan sumber lalu lintas, saluran, kampanye, kota, dan kategori perangkat

Perhitungan tersebut berguna jika Anda berencana untuk memvisualisasikan data di Google Data Studio dan memfilter menurut kota dan kategori perangkat. Ini mudah dilakukan dengan fungsi jendela COUNT:

    #standardSQL SELECT <column_name 1>, -- choose any columns COUNT (DISTINCT sessionId) AS total_sessions, -- summarize the session IDs to find the total number of sessions COUNT(DISTINCT sessionId) OVER(PARTITION BY date, geoNetwork.city, session.device.deviceCategory, trafficSource.source, trafficSource.medium, trafficSource.campaign) AS part_sessions -- summarize the number of sessions by campaign, channel, traffic source, city, and device category FROM `project_name.dataset_name.owoxbi_sessions_20190201`
#standardSQL SELECT <column_name 1>, -- choose any columns COUNT (DISTINCT sessionId) AS total_sessions, -- summarize the session IDs to find the total number of sessions COUNT(DISTINCT sessionId) OVER(PARTITION BY date, geoNetwork.city, session.device.deviceCategory, trafficSource.source, trafficSource.medium, trafficSource.campaign) AS part_sessions -- summarize the number of sessions by campaign, channel, traffic source, city, and device category FROM `project_name.dataset_name.owoxbi_sessions_20190201`

4. Gabungkan data yang sama dari beberapa tabel

Misalkan Anda mengumpulkan data tentang pesanan yang telah selesai di beberapa tabel BigQuery: satu mengumpulkan semua pesanan dari Toko A, yang lain mengumpulkan pesanan dari Toko B. Anda ingin menggabungkannya menjadi satu tabel dengan kolom berikut:

client_id — nomor yang mengidentifikasi pembeli unik
transaction_created — waktu pembuatan pesanan dalam format TIMESTAMP
id_transaksi — nomor pesanan
is_approved — apakah pesanan telah dikonfirmasi
transaksi_pendapatan — jumlah pesanan

Dalam contoh pesanan kami dari 1 Januari 2018, hingga kemarin harus ada di tabel. Untuk melakukannya, pilih kolom yang sesuai dari setiap grup tabel, beri nama yang sama, dan gabungkan hasilnya dengan UNION ALL:

    #standardSQL SELECT cid AS client_id, order_time AS transaction_created, order_status AS is_approved, order_number AS transaction_id FROM `project_name.dataset_name.table1_*` WHERE ( _TABLE_SUFFIX BETWEEN �' AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) ) UNION ALL SELECT userId AS client_id, created_timestamp AS transaction_created, operator_mark AS is_approved, transactionId AS transaction_id FROM `project_name.dataset_name.table1_*` WHERE ( _TABLE_SUFFIX BETWEEN �' AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) ) ORDER BY transaction_created DESC
#standardSQL SELECT cid AS client_id, order_time AS transaction_created, order_status AS is_approved, order_number AS transaction_id FROM `project_name.dataset_name.table1_*` WHERE ( _TABLE_SUFFIX BETWEEN �' AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) ) UNION ALL SELECT userId AS client_id, created_timestamp AS transaction_created, operator_mark AS is_approved, transactionId AS transaction_id FROM `project_name.dataset_name.table1_*` WHERE ( _TABLE_SUFFIX BETWEEN �' AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) ) ORDER BY transaction_created DESC

5. Buat kamus grup saluran lalu lintas

Saat data masuk ke Google Analytics, sistem secara otomatis menentukan grup tempat transisi tertentu berada: Langsung, Penelusuran Organik, Penelusuran Berbayar, dan seterusnya. Untuk mengidentifikasi sekelompok saluran, Google Analytics melihat tag transisi UTM, yaitu utm_source dan utm_medium. Anda dapat membaca selengkapnya tentang grup saluran dan aturan definisi di Bantuan Google Analytics.

Jika klien OWOX BI ingin menetapkan nama mereka sendiri ke grup saluran, kami membuat kamus, transisi mana yang dimiliki saluran tertentu. Untuk melakukan ini, kami menggunakan operator CASE bersyarat dan fungsi REGEXP_CONTAINS. Fungsi ini memilih nilai di mana ekspresi reguler yang ditentukan muncul.

Sebaiknya ambil nama dari daftar sumber Anda di Google Analytics. Berikut ini contoh cara menambahkan kondisi tersebut ke badan permintaan:

    #standardSQL SELECT CASE WHEN (REGEXP_CONTAINS (source, 'yandex') AND medium = 'referral' THEN 'Organic Search' WHEN (REGEXP_CONTAINS (source, 'yandex.market')) AND medium = 'referral' THEN 'Referral' WHEN (REGEXP_CONTAINS (source, '^(go.mail.ru|google.com)$') AND medium = 'referral') THEN 'Organic Search' WHEN medium = 'organic' THEN 'Organic Search' WHEN (medium = 'cpc') THEN 'Paid Search' WHEN REGEXP_CONTAINS (medium, '^(sending|email|mail)$') THEN 'Email' WHEN REGEXP_CONTAINS (source, '(mail|email|Mail)') THEN 'Email' WHEN REGEXP_CONTAINS (medium, '^(cpa)$') THEN 'Affiliate' WHEN medium = 'social' THEN 'Social' WHEN source = '(direct)' THEN 'Direct' WHEN REGEXP_CONTAINS (medium, 'banner|cpm') THEN 'Display' ELSE 'Other' END channel_group -- the name of the column in which the channel groups are written FROM `project_name.dataset_name.owoxbi_sessions_20190201`
#standardSQL SELECT CASE WHEN (REGEXP_CONTAINS (source, 'yandex') AND medium = 'referral' THEN 'Organic Search' WHEN (REGEXP_CONTAINS (source, 'yandex.market')) AND medium = 'referral' THEN 'Referral' WHEN (REGEXP_CONTAINS (source, '^(go.mail.ru|google.com)$') AND medium = 'referral') THEN 'Organic Search' WHEN medium = 'organic' THEN 'Organic Search' WHEN (medium = 'cpc') THEN 'Paid Search' WHEN REGEXP_CONTAINS (medium, '^(sending|email|mail)$') THEN 'Email' WHEN REGEXP_CONTAINS (source, '(mail|email|Mail)') THEN 'Email' WHEN REGEXP_CONTAINS (medium, '^(cpa)$') THEN 'Affiliate' WHEN medium = 'social' THEN 'Social' WHEN source = '(direct)' THEN 'Direct' WHEN REGEXP_CONTAINS (medium, 'banner|cpm') THEN 'Display' ELSE 'Other' END channel_group -- the name of the column in which the channel groups are written FROM `project_name.dataset_name.owoxbi_sessions_20190201`

Cara beralih ke SQL Standar

Jika Anda belum beralih ke SQL Standar, Anda dapat melakukannya kapan saja. Hal utama adalah untuk menghindari pencampuran dialek dalam satu permintaan.

Opsi 1. Beralih di antarmuka Google BigQuery

SQL lama digunakan secara default di antarmuka BigQuery lama. Untuk beralih di antara dialek, klik Perlihatkan Opsi di bawah bidang masukan kueri dan hapus centang pada kotak Gunakan SQL Lama di sebelah Dialek SQL.

Antarmuka baru menggunakan SQL Standar secara default. Di sini, Anda perlu membuka tab Lainnya untuk beralih dialek:

Opsi 2. Tulis awalan di awal permintaan

Jika Anda belum mencentang pengaturan permintaan, Anda dapat memulai dengan awalan yang diinginkan (#standardSQL atau #legacySQL):

    #standardSQL SELECT weight_pounds, state, year, gestation_weeks FROM `bigquery-public-data.samples.natality` ORDER BY weight_pounds DESC LIMIT 10;
#standardSQL SELECT weight_pounds, state, year, gestation_weeks FROM `bigquery-public-data.samples.natality` ORDER BY weight_pounds DESC LIMIT 10;

Dalam hal ini, Google BigQuery akan mengabaikan setelan di antarmuka dan menjalankan kueri menggunakan dialek yang ditentukan di awalan.

Jika Anda memiliki tampilan atau kueri tersimpan yang diluncurkan pada jadwal menggunakan Apps Script, jangan lupa untuk mengubah nilai useLegacySql menjadi false dalam skrip:

    var job = { configuration: { query: { query: 'INSERT INTO MyDataSet.MyFooBarTable (Id, Foo, Date) VALUES (1, \'bar\', current_Date);', useLegacySql: false }
var job = { configuration: { query: { query: 'INSERT INTO MyDataSet.MyFooBarTable (Id, Foo, Date) VALUES (1, \'bar\', current_Date);', useLegacySql: false }

Opsi 3. Transisi ke SQL Standar untuk tampilan

Jika Anda bekerja dengan Google BigQuery bukan dengan tabel tetapi dengan tampilan, tampilan tersebut tidak dapat diakses dalam dialek SQL Standar. Artinya, jika presentasi Anda ditulis dalam SQL Lama, Anda tidak dapat menulis permintaannya dalam SQL Standar.

Untuk mentransfer tampilan ke SQL standar, Anda perlu menulis ulang kueri yang digunakan untuk membuatnya secara manual. Cara termudah untuk melakukannya adalah melalui antarmuka BigQuery.

1. Buka tampilan:

2. Klik Detail. Teks kueri akan terbuka, dan tombol Edit Kueri akan muncul di bawah:

Sekarang Anda dapat mengedit permintaan sesuai dengan aturan SQL Standar.
Jika Anda berencana untuk terus menggunakan permintaan sebagai presentasi, klik Simpan Tampilan setelah Anda selesai mengedit.

Kompatibilitas, fitur sintaks, operator, fungsi

Kesesuaian

Berkat penerapan SQL Standar, Anda dapat langsung mengakses data yang disimpan di layanan lain langsung dari BigQuery:

File log Penyimpanan Google Cloud
Catatan transaksi di Google Bigtable
Data dari sumber lain

Ini memungkinkan Anda menggunakan produk Google Cloud Platform untuk tugas analitis apa pun, termasuk analisis prediktif dan preskriptif berdasarkan algoritme pembelajaran mesin.

Sintaks kueri

Struktur kueri dalam dialek Standar hampir sama dengan di Legacy:

Nama tabel dan tampilan dipisahkan dengan titik (titik), dan seluruh kueri diapit dengan aksen serius: `project_name.data_name_name.table_name``bigquery-public-data.samples.natality`

Sintaks lengkap kueri, dengan penjelasan tentang apa yang dapat disertakan di setiap operator, dikompilasi sebagai skema dalam dokumentasi BigQuery.

Fitur sintaks SQL Standar:

Koma diperlukan untuk mencantumkan bidang dalam pernyataan SELECT.
Jika Anda menggunakan operator UNNEST setelah FROM , koma atau JOIN ditempatkan sebelum UNNEST.
Anda tidak dapat menempatkan koma sebelum FROM.
Koma di antara dua kueri sama dengan CROSS JOIN, jadi berhati-hatilah dengannya.
BERGABUNG dapat dilakukan tidak hanya dengan kolom atau kesetaraan tetapi dengan ekspresi dan ketidaksetaraan yang sewenang-wenang.
Dimungkinkan untuk menulis subquery kompleks di bagian mana pun dari ekspresi SQL (dalam SELECT, FROM, WHERE, dll.). Dalam praktiknya, ekspresi seperti WHERE column_name IN (SELECT ...) belum bisa digunakan seperti yang Anda bisa di database lain.

Operator

Dalam SQL Standar, operator menentukan tipe data. Misalnya, array selalu ditulis dalam tanda kurung []. Operator digunakan untuk perbandingan, pencocokan ekspresi logis (NOT, OR, AND), dan dalam perhitungan aritmatika.

Fungsi

SQL standar mendukung lebih banyak fitur daripada Legacy: agregasi tradisional (jumlah, angka, minimum, maksimum); fungsi matematika, string, dan statistik; dan format langka seperti HyperLogLog ++.

Dalam dialek Standar, ada lebih banyak fungsi untuk bekerja dengan tanggal dan TIMESTAMP. Daftar lengkap fitur disediakan dalam dokumentasi Google. Fungsi yang paling umum digunakan adalah untuk bekerja dengan tanggal, string, agregasi, dan jendela.

1. Fungsi agregasi

COUNT (DISTINCT column_name) menghitung jumlah nilai unik dalam kolom. Misalnya, kita perlu menghitung jumlah sesi dari perangkat seluler pada 1 Maret 2019. Karena nomor sesi dapat diulang pada baris yang berbeda, kita hanya ingin menghitung nilai nomor sesi unik:

    #standardSQL SELECT COUNT (DISTINCT sessionId) AS sessions FROM `project_name.dataset_name.owoxbi_sessions_20190301` WHERE device.deviceCategory = 'mobile'
#standardSQL SELECT COUNT (DISTINCT sessionId) AS sessions FROM `project_name.dataset_name.owoxbi_sessions_20190301` WHERE device.deviceCategory = 'mobile'

SUM (column_name) — jumlah nilai dalam kolom

    #standardSQL SELECT SUM (hits.transaction.transactionRevenue) AS revenue FROM `project_name.dataset_name.owoxbi_sessions_20190301`, UNNEST (hits) AS hits -- unpacking the nested field hits WHERE device.deviceCategory = 'mobile'
#standardSQL SELECT SUM (hits.transaction.transactionRevenue) AS revenue FROM `project_name.dataset_name.owoxbi_sessions_20190301`, UNNEST (hits) AS hits -- unpacking the nested field hits WHERE device.deviceCategory = 'mobile'

MIN (nama_kolom) | MAX (column_name) — nilai minimum dan maksimum dalam kolom. Fungsi-fungsi ini nyaman untuk memeriksa penyebaran data dalam tabel.

2. Fungsi jendela (analitis)

Fungsi analitis mempertimbangkan nilai bukan untuk seluruh tabel tetapi untuk jendela tertentu — sekumpulan baris yang Anda minati. Artinya, Anda dapat menentukan segmen di dalam tabel. Misalnya, Anda dapat menghitung SUM (pendapatan) tidak untuk semua lini tetapi untuk kota, kategori perangkat, dan sebagainya. Anda dapat mengubah fungsi analitik SUM, COUNT, dan AVG serta fungsi agregasi lainnya dengan menambahkan kondisi OVER (PARTITION BY column_name) ke dalamnya.

Misalnya, Anda perlu menghitung jumlah sesi menurut sumber lalu lintas, saluran, kampanye, kota, dan kategori perangkat. Dalam hal ini, kita dapat menggunakan ekspresi berikut:

    SELECT date, geoNetwork.city, t.device.deviceCategory, trafficSource.source, trafficSource.medium, trafficSource.campaign, COUNT(DISTINCT sessionId) OVER(PARTITION BY date, geoNetwork.city, session.device.deviceCategory, trafficSource.source, trafficSource.medium, trafficSource.campaign) AS segmented_sessions FROM `project_name.dataset_name.owoxbi_sessions_20190301` t
SELECT date, geoNetwork.city, t.device.deviceCategory, trafficSource.source, trafficSource.medium, trafficSource.campaign, COUNT(DISTINCT sessionId) OVER(PARTITION BY date, geoNetwork.city, session.device.deviceCategory, trafficSource.source, trafficSource.medium, trafficSource.campaign) AS segmented_sessions FROM `project_name.dataset_name.owoxbi_sessions_20190301` t

OVER menentukan jendela untuk perhitungan yang akan dibuat. PARTITION BY menunjukkan baris mana yang harus dikelompokkan untuk perhitungan. Dalam beberapa fungsi, perlu untuk menentukan urutan pengelompokan dengan ORDER BY.

Untuk daftar lengkap fungsi jendela, lihat dokumentasi BigQuery.

3. Fungsi string

Ini berguna saat Anda perlu mengubah teks, memformat teks dalam satu baris, atau merekatkan nilai kolom. Misalnya, fungsi string sangat bagus jika Anda ingin membuat pengidentifikasi sesi unik dari data ekspor Google Analytics 360 standar. Mari kita pertimbangkan fungsi string yang paling populer.

SUBSTR memotong bagian dari string. Dalam permintaan, fungsi ini ditulis sebagai SUBSTR (string_name, 0.4). Angka pertama menunjukkan berapa banyak karakter yang harus dilewati dari awal baris, dan angka kedua menunjukkan berapa banyak digit yang harus dipotong. Misalnya, Anda memiliki kolom tanggal yang berisi tanggal dalam format STRING. Dalam hal ini, tanggalnya terlihat seperti ini: 20190103. Jika Anda ingin mengekstrak satu tahun dari baris ini, SUBSTR akan membantu Anda:

    #standardSQL SELECT SUBSTR(date,0,4) AS year FROM `project_name.dataset_name.owoxbi_sessions_20190301`
#standardSQL SELECT SUBSTR(date,0,4) AS year FROM `project_name.dataset_name.owoxbi_sessions_20190301`

CONCAT (column_name, dll.) merekatkan nilai. Mari kita gunakan kolom tanggal dari contoh sebelumnya. Misalkan Anda ingin semua tanggal dicatat seperti ini: 03-01-2019. Untuk mengonversi tanggal dari format saat ini ke format ini, dua fungsi string dapat digunakan: pertama, potong bagian string yang diperlukan dengan SUBSTR, lalu rekatkan melalui tanda hubung:

    #standardSQL SELECT CONCAT(SUBSTR(date,0,4),"-",SUBSTR(date,5,2),"-",SUBSTR(date,7,2)) AS date FROM `project_name.dataset_name.owoxbi_sessions_20190301`
#standardSQL SELECT CONCAT(SUBSTR(date,0,4),"-",SUBSTR(date,5,2),"-",SUBSTR(date,7,2)) AS date FROM `project_name.dataset_name.owoxbi_sessions_20190301`

REGEXP_CONTAINS mengembalikan nilai kolom tempat ekspresi reguler muncul:

    #standardSQL SELECT CASE WHEN REGEXP_CONTAINS (medium, '^(sending|email|mail)$') THEN 'Email' WHEN REGEXP_CONTAINS (source, '(mail|email|Mail)') THEN 'Email' WHEN REGEXP_CONTAINS (medium, '^(cpa)$') THEN 'Affiliate' ELSE 'Other' END Channel_groups FROM `project_name.dataset_name.owoxbi_sessions_20190301`
#standardSQL SELECT CASE WHEN REGEXP_CONTAINS (medium, '^(sending|email|mail)$') THEN 'Email' WHEN REGEXP_CONTAINS (source, '(mail|email|Mail)') THEN 'Email' WHEN REGEXP_CONTAINS (medium, '^(cpa)$') THEN 'Affiliate' ELSE 'Other' END Channel_groups FROM `project_name.dataset_name.owoxbi_sessions_20190301`

Fungsi ini dapat digunakan di SELECT dan WHERE. Misalnya, di WHERE, Anda dapat memilih halaman tertentu dengannya:

    WHERE REGEXP_CONTAINS(hits.page.pagePath, 'land[123]/|/product-order')
WHERE REGEXP_CONTAINS(hits.page.pagePath, 'land[123]/|/product-order')

4. Fungsi tanggal

Seringkali, tanggal dalam tabel dicatat dalam format STRING. Jika Anda berencana untuk memvisualisasikan hasil di Google Data Studio, tanggal dalam tabel harus dikonversi ke format DATE menggunakan fungsi PARSE_DATE.

PARSE_DATE mengonversi STRING dari format 1900-01-01 ke format DATE.
Jika tanggal di tabel Anda terlihat berbeda (misalnya, 19000101 atau 01_01_1900), Anda harus terlebih dahulu mengonversinya ke format yang ditentukan.

    #standardSQL SELECT PARSE_DATE('%Y-%m-%d', date) AS date_new FROM `project_name.dataset_name.owoxbi_sessions_20190301`
#standardSQL SELECT PARSE_DATE('%Y-%m-%d', date) AS date_new FROM `project_name.dataset_name.owoxbi_sessions_20190301`

DATE_DIFF menghitung berapa banyak waktu yang telah berlalu antara dua tanggal dalam hari, minggu, bulan, atau tahun. Ini berguna jika Anda perlu menentukan interval antara saat pengguna melihat iklan dan melakukan pemesanan. Begini tampilan fungsinya dalam permintaan:

    #standardSQL SELECT DATE_DIFF( PARSE_DATE('%Y%m%d', date1), PARSE_DATE('%Y%m%d', date2), DAY ) days -- convert the date1 and date2 lines to the DATE format; choose units to show the difference (DAY, WEEK, MONTH, etc.) FROM `project_name.dataset_name.owoxbi_sessions_20190301`
#standardSQL SELECT DATE_DIFF( PARSE_DATE('%Y%m%d', date1), PARSE_DATE('%Y%m%d', date2), DAY ) days -- convert the date1 and date2 lines to the DATE format; choose units to show the difference (DAY, WEEK, MONTH, etc.) FROM `project_name.dataset_name.owoxbi_sessions_20190301`

Jika Anda ingin mempelajari lebih lanjut tentang fungsi yang tercantum, baca Fitur Google BigQuery — Tinjauan Mendetail.

Kueri SQL untuk laporan pemasaran

Dialek SQL Standar memungkinkan bisnis untuk mengekstrak informasi maksimum dari data dengan segmentasi mendalam, audit teknis, analisis KPI pemasaran, dan identifikasi kontraktor yang tidak adil dalam jaringan CPA. Berikut adalah contoh masalah bisnis di mana kueri SQL pada data yang dikumpulkan di Google BigQuery akan membantu Anda.

1. Analisis ROPO: mengevaluasi kontribusi kampanye online terhadap penjualan offline. Untuk melakukan analisis ROPO, Anda perlu menggabungkan data tentang perilaku pengguna online dengan data dari CRM, sistem pelacakan panggilan, dan aplikasi seluler Anda.

Jika ada kunci di satu dan basis kedua — parameter umum yang unik untuk setiap pengguna (misalnya, ID Pengguna) — Anda dapat melacak:
pengguna mana yang mengunjungi situs sebelum membeli barang di toko
bagaimana pengguna berperilaku di situs
berapa lama waktu yang dibutuhkan pengguna untuk membuat keputusan pembelian
kampanye apa yang memiliki peningkatan terbesar pada pembelian offline.

2. Segmentasikan pelanggan berdasarkan kombinasi parameter apa pun, mulai dari perilaku di situs (halaman yang dikunjungi, produk yang dilihat, jumlah kunjungan ke situs sebelum membeli) hingga nomor kartu loyalitas dan barang yang dibeli.

3. Cari tahu mitra CPA mana yang bekerja dengan itikad buruk dan mengganti tag UTM.

4. Analisis kemajuan pengguna melalui saluran penjualan.

Kami telah menyiapkan pilihan kueri dalam dialek SQL Standar. Jika Anda sudah mengumpulkan data dari situs Anda, dari sumber iklan, dan dari sistem CRM Anda di Google BigQuery, Anda dapat menggunakan template ini untuk memecahkan masalah bisnis Anda. Cukup ganti nama proyek, set data, dan tabel di BigQuery dengan milik Anda. Dalam koleksi, Anda akan menerima 11 kueri SQL.

Untuk data yang dikumpulkan menggunakan ekspor standar dari Google Analytics 360 ke Google BigQuery:

Tindakan pengguna dalam konteks parameter apa pun
Statistik tentang tindakan pengguna utama
Pengguna yang melihat halaman produk tertentu
Tindakan pengguna yang membeli produk tertentu
Siapkan corong dengan langkah-langkah yang diperlukan
Efektivitas situs pencarian internal

Untuk data yang dikumpulkan di Google BigQuery menggunakan OWOX BI:

Konsumsi yang diatribusikan menurut sumber dan saluran
Biaya rata-rata untuk menarik pengunjung menurut kota
ROAS untuk laba kotor menurut sumber dan saluran
Jumlah pesanan di CRM berdasarkan metode pembayaran dan metode pengiriman
Waktu pengiriman rata-rata menurut kota

Jika Anda memiliki pertanyaan tentang kueri data Google BigQuery yang tidak Anda temukan jawabannya di artikel ini, tanyakan di komentar. Kami akan mencoba membantu Anda.