GSP407

Ringkasan

BigQuery adalah database analisis Google yang terkelola sepenuhnya, tanpa pengoperasian (NoOps), dan hemat biaya. Dengan BigQuery, Anda dapat melakukan kueri pada data berukuran terabyte tanpa harus memiliki administrator database atau infrastruktur apa pun untuk mengelolanya. BigQuery menggunakan SQL dan dapat memanfaatkan model bayar sesuai penggunaan. Dengan BigQuery, Anda dapat berfokus menganalisis data untuk menemukan insight yang bermakna.

Kami memiliki set data e-commerce baru yang berisi jutaan data Google Analytics untuk Google Merchandise Store yang dimuat ke dalam tabel di BigQuery. Di lab ini, Anda akan menggunakan salinan dari set data tersebut. Tersedia skenario contoh tempat Anda melihat data dan cara menghapus informasi duplikat. Lab ini kemudian akan memandu Anda menganalisis data lebih lanjut.

Untuk mengikuti dan bereksperimen dengan kueri BigQuery yang disediakan guna menganalisis data, lihat Referensi Sintaksis Kueri BigQuery.

Tujuan

Di lab ini, Anda akan menggunakan BigQuery untuk:

• Mengakses set data e-commerce
• Melihat metadata set data
• Menghapus entri duplikat
• Menulis dan menjalankan kueri

Penyiapan dan persyaratan

Sebelum mengklik tombol Start Lab

Baca petunjuk ini. Lab memiliki timer dan Anda tidak dapat menjedanya. Timer yang dimulai saat Anda mengklik Start Lab akan menampilkan durasi ketersediaan resource Google Cloud untuk Anda.

Lab interaktif ini dapat Anda gunakan untuk melakukan aktivitas lab di lingkungan cloud sungguhan, bukan di lingkungan demo atau simulasi. Untuk mengakses lab ini, Anda akan diberi kredensial baru yang bersifat sementara dan dapat digunakan untuk login serta mengakses Google Cloud selama durasi lab.

Untuk menyelesaikan lab ini, Anda memerlukan:

• Akses ke browser internet standar (disarankan browser Chrome).

Catatan: Gunakan jendela Samaran (direkomendasikan) atau browser pribadi untuk menjalankan lab ini. Hal ini akan mencegah konflik antara akun pribadi Anda dan akun siswa yang dapat menyebabkan tagihan ekstra pada akun pribadi Anda.

• Waktu untuk menyelesaikan lab. Ingat, setelah dimulai, lab tidak dapat dijeda.

Catatan: Hanya gunakan akun siswa untuk lab ini. Jika Anda menggunakan akun Google Cloud yang berbeda, Anda mungkin akan dikenai tagihan ke akun tersebut.

Cara memulai lab dan login ke Google Cloud Console

1. Klik tombol Start Lab. Jika Anda perlu membayar lab, dialog akan terbuka untuk memilih metode pembayaran. Di sebelah kiri ada panel Lab Details yang berisi hal-hal berikut:

   • Tombol Open Google Cloud console
   • Waktu tersisa
   • Kredensial sementara yang harus Anda gunakan untuk lab ini
   • Informasi lain, jika diperlukan, untuk menyelesaikan lab ini

2. Klik Open Google Cloud console (atau klik kanan dan pilih Open Link in Incognito Window jika Anda menjalankan browser Chrome).

   Lab akan menjalankan resource, lalu membuka tab lain yang menampilkan halaman Sign in.

   Tips: Atur tab di jendela terpisah secara berdampingan.

   Catatan: Jika Anda melihat dialog Choose an account, klik Use Another Account.

3. Jika perlu, salin Username di bawah dan tempel ke dialog Sign in.

   {{{user_0.username | "Username"}}}

   Anda juga dapat menemukan Username di panel Lab Details.

4. Klik Next.

5. Salin Password di bawah dan tempel ke dialog Welcome.

   {{{user_0.password | "Password"}}}

   Anda juga dapat menemukan Password di panel Lab Details.

6. Klik Next.

   Penting: Anda harus menggunakan kredensial yang diberikan lab. Jangan menggunakan kredensial akun Google Cloud Anda. Catatan: Menggunakan akun Google Cloud sendiri untuk lab ini dapat dikenai biaya tambahan.

7. Klik halaman berikutnya:

   • Setujui persyaratan dan ketentuan.
   • Jangan tambahkan opsi pemulihan atau autentikasi 2 langkah (karena ini akun sementara).
   • Jangan mendaftar uji coba gratis.

Setelah beberapa saat, Konsol Google Cloud akan terbuka di tab ini.

Catatan: Untuk mengakses produk dan layanan Google Cloud, klik Navigation menu atau ketik nama layanan atau produk di kolom Search.

Tugas 1. Menyematkan project lab di BigQuery

Di bagian ini, Anda akan menambahkan project data-to-insights ke resource lingkungan.

1. Klik Navigation menu > BigQuery.

Kotak pesan Welcome to BigQuery pada Konsol Cloud akan terbuka.

Catatan: Kotak pesan Welcome to BigQuery pada Konsol Cloud menampilkan link ke panduan memulai dan update UI.

2. Klik Done.

Set data publik BigQuery tidak ditampilkan secara default di UI web BigQuery. Untuk membuka project set data publik, salin "data-to-insights".

3. Klik + Add > Star a project by name, lalu setel nama ke data-to-insights. Klik STAR.

Bagian Explorer kini menampilkan daftar project data-to-insights.

Tugas 2. Menjelajahi data e-commerce dan mengidentifikasi data duplikat

Skenario: Tim analis data Anda mengekspor log Google Analytics untuk situs e-commerce ke BigQuery dan membuat tabel baru yang berisi semua data mentah sesi pengunjung e-commerce.

Jelajahi data tabel all_sessions_raw:

1. Klik ikon Expand node di dekat data-to-insights untuk memperluas project.
2. Perluas ecommerce.
3. Klik all_sessions_raw.

Di panel kanan, bagian yang memberikan 3 tampilan data tabel akan terbuka:

• Tab Schema: Nama Field, Type, Mode, dan Description; batasan logis digunakan untuk mengatur data
• Tab Details: Metadata tabel
• Tab Preview: Pratinjau tabel

4. Klik tab Details untuk melihat metadata tabel.

Pertanyaan:

Mengidentifikasi baris duplikat

Dengan melihat contoh jumlah data, Anda dapat mengetahui garis besar dari apa yang disertakan dalam set data.

1. Untuk melihat pratinjau baris contoh dari tabel tanpa menggunakan SQL, klik tab preview.

2. Lihat sekilas baris yang ditampilkan. Tidak ada kolom tunggal yang mengidentifikasi baris secara unik, sehingga Anda memerlukan logika lanjutan untuk mengidentifikasi baris duplikat.

3. Kueri yang Anda gunakan (yang ada di bawah) memakai fungsi GROUP BY SQL pada setiap kolom dan menghitung (COUNT) jika ada baris yang memiliki nilai sama di setiap kolom:

• Jika setiap kolom bersifat unik, COUNT akan menampilkan nilai 1 karena tidak ada pengelompokan baris lain yang memiliki nilai sama persis untuk semua kolom.
• Jika ada beberapa baris dengan nilai sama untuk semua kolom, baris ini akan dikelompokkan dan COUNT akan menampilkan nilai yang lebih besar dari 1.

Bagian terakhir kueri adalah filter agregasi menggunakan HAVING untuk hanya menampilkan hasil dengan COUNT duplikat lebih besar dari 1. Jadi, jumlah data yang memiliki duplikat akan sama dengan jumlah baris di tabel yang dihasilkan.

4. Salin dan tempel kueri berikut ke Editor kueri, lalu RUN kueri untuk menemukan data duplikat di semua kolom.

#standardSQL SELECT COUNT(*) as num_duplicate_rows, * FROM `data-to-insights.ecommerce.all_sessions_raw` GROUP BY fullVisitorId, channelGrouping, time, country, city, totalTransactionRevenue, transactions, timeOnSite, pageviews, sessionQualityDim, date, visitId, type, productRefundAmount, productQuantity, productPrice, productRevenue, productSKU, v2ProductName, v2ProductCategory, productVariant, currencyCode, itemQuantity, itemRevenue, transactionRevenue, transactionId, pageTitle, searchKeyword, pagePathLevel1, eCommerceAction_type, eCommerceAction_step, eCommerceAction_option HAVING num_duplicate_rows > 1;

Catatan: Di set data Anda sendiri, meskipun Anda memiliki kunci unik, sebaiknya konfirmasi keunikan baris dengan COUNT, GROUP BY, dan HAVING sebelum memulai analisis.

Klik Check my progress untuk memverifikasi tujuan. Mengidentifikasi baris duplikat

Menganalisis tabel all_sessions baru

Di bagian ini, Anda akan menggunakan tabel yang telah dihapus duplikatnya bernama all_sessions.

Skenario: Tim analis data Anda menyediakan kueri ini, dan pakar skema Anda telah mengidentifikasi kolom kunci yang harus bersifat unik untuk setiap data per skema Anda.

1. Jalankan kueri tersebut untuk mengonfirmasi ada atau tidak adanya duplikat, kali ini di tabel all_sessions:

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=en SELECT fullVisitorId, # the unique visitor ID visitId, # a visitor can have multiple visits date, # session date stored as string YYYYMMDD time, # time of the individual site hit (can be 0 to many per visitor session) v2ProductName, # not unique since a product can have variants like Color productSKU, # unique for each product type, # a visitor can visit Pages and/or can trigger Events (even at the same time) eCommerceAction_type, # maps to ‘add to cart', ‘completed checkout' eCommerceAction_step, eCommerceAction_option, transactionRevenue, # revenue of the order transactionId, # unique identifier for revenue bearing transaction COUNT(*) as row_count FROM `data-to-insights.ecommerce.all_sessions` GROUP BY 1,2,3 ,4, 5, 6, 7, 8, 9, 10,11,12 HAVING row_count > 1 # find duplicates

Kueri menampilkan nol data.

Catatan: Di SQL, Anda dapat GROUP BY (mengelompokkan) atau ORDER BY (mengurutkan) indeks kolom seperti menggunakan GROUP BY 1, alih-alih GROUP BY fullVisitorId.

Tugas 3. Menulis SQL dasar pada data e-commerce

Di bagian ini, Anda akan membuat kueri untuk insight pada set data e-commerce.

Menulis kueri yang menunjukkan jumlah total pengunjung unik

Kueri Anda menentukan tampilan total dengan menghitung product_views, dan jumlah pengunjung unik dengan menghitung fullVisitorID.

1. Klik ikon "+" (Tulis Kueri Baru).
2. Tulis kueri ini di editor:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(DISTINCT fullVisitorId) AS unique_visitors FROM `data-to-insights.ecommerce.all_sessions`;

3. Untuk memastikan sintaksis Anda benar, konfirmasi bahwa validator kueri real-time menampilkan ikon Centang hijau.
4. Klik Run. Baca hasil untuk melihat jumlah pengunjung unik.

Hasil:

5. Sekarang, tulis kueri yang menunjukkan total pengunjung unik (fullVisitorID) menurut situs pengarah (channelGrouping):

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS unique_visitors, channelGrouping FROM `data-to-insights.ecommerce.all_sessions` GROUP BY channelGrouping ORDER BY channelGrouping DESC;

Hasil:

6. Tulis kueri untuk mencantumkan semua nama produk unik (v2ProductName) secara alfabetis:

#standardSQL SELECT (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` GROUP BY ProductName ORDER BY ProductName

Tips: Di SQL, klausa ORDER BY memiliki default Menaik (ASC) A ke Z. Jika ingin kebalikannya, coba ORDER BY field_name DESC.

Hasil:

Kueri ini menampilkan total 633 produk (baris).

7. Tulis kueri untuk mencantumkan lima produk paling banyak dilihat (product_views) dari semua pengunjung (termasuk orang yang telah melihat produk lebih dari sekali). Kueri Anda menghitung berapa kali produk (v2ProductName) dilihat (product_views), memasukkan daftar dalam urutan menurun, dan mencantumkan 5 entri teratas:

Tips: Di Google Analytics, pengunjung dapat "melihat" produk selama jenis interaksi berikut: 'page', 'screenview', 'event', 'transaction', 'item', 'social', 'exception', 'timing'. Untuk tujuan kita, cukup filter untuk jenis = 'PAGE' saja.

#standardSQL SELECT COUNT(*) AS product_views, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Hasil:

8. Bonus: Sekarang, saring kueri agar tidak lagi menghitung dua kali penayangan produk bagi pelanggan yang telah melihat produk beberapa kali. Setiap penayangan produk yang berbeda hanya boleh dihitung sekali per pengunjung:

WITH unique_product_views_by_person AS ( -- find each unique product viewed by each visitor SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- aggregate the top viewed products and sort them SELECT COUNT(*) AS unique_view_count, ProductName FROM unique_product_views_by_person GROUP BY ProductName ORDER BY unique_view_count DESC LIMIT 5

Tips: Anda dapat menggunakan klausa WITH SQL untuk membantu memecah kueri yang kompleks menjadi beberapa langkah. Di sini, pertama-tama, kita akan membuat kueri yang menemukan setiap produk unik per pengunjung dan menghitungnya sekali. Lalu kueri kedua melakukan agregasi di seluruh pengunjung dan produk.

Hasil:

9. Selanjutnya, perluas kueri Anda sebelumnya agar menyertakan jumlah total produk berbeda yang dipesan dan jumlah total unit yang dipesan (productQuantity):

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Hasil:

Pertanyaan:

10. Perluas kueri agar menyertakan jumlah rata-rata produk per pesanan (jumlah total unit yang dipesan/jumlah total pesanan, atau SUM(productQuantity)/COUNT(productQuantity)):

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS orders, SUM(productQuantity) AS quantity_product_ordered, SUM(productQuantity) / COUNT(productQuantity) AS avg_per_order, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY v2ProductName ORDER BY product_views DESC LIMIT 5;

Hasil

Pertanyaan:

Botol Minum Infuser 650 ML YouTube memiliki avg_per_order tertinggi dengan 9,38 unit per pesanan.

Klik Check my progress untuk memverifikasi tujuan. Menulis SQL dasar pada data e-commerce

Selamat!

Selamat! Di lab ini, Anda telah menggunakan BigQuery untuk melihat dan mengkueri data guna mendapatkan insight yang bermakna di berbagai aspek pemasaran produk. Anda telah mempelajari cara mengakses set data e-commerce, melihat metadata set data, menghapus entri duplikat, serta menulis dan menjalankan kueri.

Langkah berikutnya/Pelajari lebih lanjut

• Pelajari Set Data Publik BigQuery.
• Punya akun Google Analytics dan ingin melakukan kueri set data Anda sendiri di BigQuery? Ikuti panduan ekspor ini.
• Pelajari 15 hal menarik yang mungkin tidak Anda ketahui tentang BigQuery.
• Ikuti lab lainnya untuk mempelajari BigQuery lebih lanjut:
  • Memecahkan Masalah Error SQL Umum dengan BigQuery
  • Data Cuaca di BigQuery

Sertifikasi dan pelatihan Google Cloud

...membantu Anda mengoptimalkan teknologi Google Cloud. Kelas kami mencakup keterampilan teknis dan praktik terbaik untuk membantu Anda memahami dengan cepat dan melanjutkan proses pembelajaran. Kami menawarkan pelatihan tingkat dasar hingga lanjutan dengan opsi on demand, live, dan virtual untuk menyesuaikan dengan jadwal Anda yang sibuk. Sertifikasi membantu Anda memvalidasi dan membuktikan keterampilan serta keahlian Anda dalam teknologi Google Cloud.

Manual Terakhir Diperbarui pada 02 April 2024

Lab Terakhir Diuji pada 02 April 2024

Hak cipta 2025 Google LLC. Semua hak dilindungi undang-undang. Google dan logo Google adalah merek dagang dari Google LLC. Semua nama perusahaan dan produk lain mungkin adalah merek dagang masing-masing perusahaan yang bersangkutan.