Menggabungkan Google Analytics dan Shopify: Membuat Gudang Data eCommerce di BigQuery

Google Analytics adalah alat hebat yang memungkinkan Anda memahami perilaku pengguna dari mereka yang mengunjungi situs web Anda. Padahal, Shopify luar biasa dalam memberikan cakupan kinerja yang mendetail dari perspektif eCommerce. Keduanya hebat dalam fungsinya, tetapi ada titik di mana data dari Shopify dan Google Analytics (GA) secara terpisah gagal memberikan wawasan lebih dalam yang mungkin Anda inginkan. Sementara masing-masing memiliki kemampuan untuk memberikan informasi yang kuat, ada tingkat wawasan yang jauh lebih besar yang dapat diperoleh dengan menggabungkan data.

Dalam contoh khusus ini, kami akan membahas bagaimana kami mencapai ini untuk teman-teman kami di Pooch & Mutt, sebuah perusahaan yang berbasis di Inggris yang memproduksi makanan anjing yang alami dan sehat. Kami ingin meningkatkan wawasan dengan mengambil data yang dikumpulkan di Google Analytics dan menggunakannya bersamaan dengan data eCommerce dari Shopify, serta data langganan dari Recharge dan menggabungkannya dengan data langganan lama dari Bold. Dengan menggunakan berbagai sumber data ini, kami dapat membangun gambaran yang lebih kaya dan lengkap.

Blog ini akan berusaha untuk memberikan ikhtisar tentang bagaimana kami mengumpulkan data ini, dan kemudian menguraikan proses pemikiran di balik penggabungannya untuk mengekstraksi dan memvisualisasikan wawasan berbasis data ini. Secara khusus, kami akan menjelaskan bagaimana kami menjawab tiga pertanyaan berikut;

1. Saluran mana yang mendorong pendaftaran langganan?
2. Kombinasi keranjang di luar GA; keranjang apa yang paling populer untuk pelanggan dan non-pelanggan dan mana yang paling menguntungkan?
3. Berapa lama dan berapa rata-rata pembelian yang dibutuhkan sebelum pengguna menjadi pelanggan?

Metodologi

Sebelum kita menjawab pertanyaan-pertanyaan ini, pertama-tama mari kita bahas bagaimana kita mengumpulkan semua data dari platform masing-masing ke lokasi terpusat dan membuat tabel yang dapat kita gunakan untuk menyusun data ini.

Struktur data

Kami memilih untuk menggunakan BigQuery (BQ) untuk memusatkan data karena keserbagunaan dan kemampuannya untuk menangani kombinasi kumpulan data. Bagan alur di bawah menyoroti hubungan umum antara data dan cara pencapaiannya:

Jahitan

Stitch adalah alat yang terjangkau yang memungkinkan Anda untuk mengekstrak dan memuat data ke gudang data yang sudah dibuat sebelumnya.

Kami dengan mudah terhubung ke Shopify dan Recharge melalui Stitch, di mana kami cukup memilih tabel yang kami inginkan. Dalam hal ini, tabel yang paling penting adalah 'pelanggan' dan 'langganan' dari Recharge, dan 'pesanan' dari Shopify.

Informasi ini kemudian disinkronkan dengan BQ setiap 6 jam melalui proses penjadwalan otomatis Stitch.

Berani

Untuk data pelanggan historis, kami melakukan ekspor akhir dan mengunggahnya ke BQ melalui Google Sheets.

Google Analytics

Sementara Stitch juga dapat digunakan untuk ini, kami memilih untuk menggunakan RStudio untuk memasukkan data ini ke dalam BQ. Jika Anda tertarik untuk mempelajari lebih lanjut tentang cara melakukannya, Anda dapat membaca blog oleh pakar R Studio kami Danny Smith tentang cara menggunakan GA API dengan R.

Membangun Meja Master

Setelah kami memiliki semua data yang diperlukan di BQ, kami dapat mulai menggabungkannya. Langkah-langkah yang kami ambil untuk melakukannya adalah sebagai berikut:

1. Kami menggabungkan dan menyelaraskan data pelanggan lama dari Bold dengan data pelanggan saat ini dari Recharge.
2. Kami kemudian mengambil data eCommerce dari GA yang mencakup ID pesanan (mis. SPFYXXXXX), bersama dengan sumber, media, dan saluran.
3. Kami membuat kolom baru berdasarkan tabel GA dan data pelanggan gabungan sebelum bergabung ke data pesanan Shopify. Dalam hal ini, beberapa kolom tambahan ini termasuk;
   1. Pengenal pelanggan 'Aktif'
   2. Tanggal asli berlangganan
   3. Sumber, media & saluran pesanan
   4. Pertama atau ulangi pesanan pelanggan
4. Selanjutnya, kami memilih bidang yang relevan dari data pesanan Shopify yang ingin kami gabungkan dengan data baru ini. Beberapa contoh utama adalah;
   1. ID Pelanggan
   2. Id pemesanan
   3. Tanggal pemesanan
   4. SKU
   5. Jumlah Barang
   6. Harga barang
   7. Jumlah Harga Pesanan
   8. Kode diskon
   9. Jumlah diskon
   10. Tag (Digunakan sebagai metode tambahan untuk mengkategorikan pelanggan vs non-pelanggan)
5. Tahap terakhir di sini adalah membuat beberapa kolom tambahan agar lebih mudah menangani baris duplikat untuk item berbeda dalam urutan yang sama;
   1. Harga rata-rata
   2. Diskon rata-rata
   3. Status Pelanggan (tag + jika ada di tabel Bold & Isi Ulang)

Kami kemudian menjadwalkan kueri ini untuk dijalankan sekali sehari dan menggunakan hasilnya sebagai tabel master untuk kueri tambahan. Melakukan hal ini berarti kami tidak perlu menjalankan kueri lengkap berkali-kali, karena ini adalah tabel statis yang hanya diperbarui saat dijadwalkan, bukan setiap kali permintaan dibuat melalui Dasbor Looker Studio.

Wawasan

Sekarang kita telah membahas metodologi dan menguraikan langkah-langkah yang diambil untuk mendapatkan semua data yang kita butuhkan, kita dapat melanjutkan untuk membahas bagaimana kita menggunakan data ini untuk menjawab pertanyaan kita.

Saluran mana yang mendorong pendaftaran langganan?

1. Untuk menghitungnya, kami melihat pesanan pertama yang dilakukan oleh pelanggan, menggunakan bidang yang kami buat di tabel master yang mengembalikan 'Pertama' atau 'Ulangi' berdasarkan tanggal pesanan pertama.
2. Dengan menggunakan kolom tambahan pengelompokan saluran & status pelanggan dalam tabel master, kami dapat melihat pesanan pertama orang yang kemudian menjadi pelanggan, baik pada pembelian pertama atau pembelian berulang, dan ke saluran apa mereka diatribusikan di GA .
3. Menghapus langkah untuk hanya melihat pelanggan, kami juga dapat melihat saluran mana yang secara dominan mendorong pembelian pertama.

Kombinasi keranjang di luar GA; keranjang apa yang paling populer untuk pelanggan dan non-pelanggan, dan mana yang paling menguntungkan?

1. Dengan menggunakan fungsi STRING_AGG, kami dapat menggabungkan berbagai produk dari satu pesanan dalam satu baris, dipisahkan dengan ' | '. Ini pada dasarnya membuat keranjang untuk setiap pesanan.
2. Bersamaan dengan informasi ini, kami menjumlahkan pendapatan untuk setiap item ini sebagai 'Total Keranjang', dengan tujuan untuk memiliki baris per pesanan dengan semua informasi yang relevan.
3. Tentu saja, ada banyak variasi antar produk (rasa, ukuran, dll.) jadi kami menambahkan kolom yang menghilangkan variasi ini untuk melihat kombinasi keranjang produk dasar.
4. Kami kemudian menggunakan 'ROW_NUMBER() OVER (PARTITION BY Transaction_ID) AS Identifier' untuk menetapkan nilai berurutan ke setiap pesanan. Ini kemudian digunakan sebagai filter di dasbor untuk mengisolasi satu baris yang masih berisi semua informasi.

Berapa lama dan berapa rata-rata pembelian yang dibutuhkan sebelum pengguna menjadi pelanggan?

1. Menggunakan tabel master kami, kami menarik semua ID pelanggan yang kami tahu telah menjadi pelanggan dan mengambil tanggal berlangganan pertama mereka.
2. Kami kemudian mengambil tanggal pesanan minimum dan menggunakan fungsi DATE_DIFF untuk menghitung selisih antara tanggal-tanggal tersebut dalam 'hari'.
3. Langkah terakhir menarik saluran dan jumlah hari rata-rata sebelum berlangganan, yang menghasilkan tabel yang menunjukkan jumlah hari rata-rata sebelum pelanggan biasanya menjadi pelanggan.
4. Untuk menghitung jumlah rata-rata pesanan yang dilakukan pelanggan sebelum menjadi pelanggan, kami hanya membagi jumlah ID pesanan unik dengan jumlah ID pelanggan unik di mana mereka adalah 'Pelanggan' dan mereka memiliki tanggal pesanan kurang dari tanggal berlangganan minimum.

Ini hanyalah beberapa contoh tingkat permukaan dari tampilan yang kami hasilkan menggunakan laporan ini. Dengan data, ada kemungkinan tak terbatas – contoh lain dari proyek ini termasuk melihat nilai seumur hidup pelanggan dari berbagai perspektif dan menilai penggunaan kode diskon dan dampaknya terhadap perilaku seumur hidup pelanggan. Dengan wawasan ini, mereka dapat digunakan untuk melengkapi keputusan bisnis. Misalnya, mengetahui bahwa dibutuhkan rata-rata 3 pesanan sebelum menjadi pelanggan, Anda dapat melihat untuk membagikan penawaran dan insentif yang mendorong pelanggan untuk menjadi pelanggan setelah 2 kali pembelian. Hasil ini kemudian dapat diukur di lain waktu.

Ringkasan

Studi kasus ini menunjukkan potensi wawasan berbasis data yang sangat berharga yang dapat diperoleh melalui kombinasi berbagai sumber data, dari GA hingga Shopify dan seterusnya. Setelah infrastruktur ini dibuat, dapat dipertahankan secara dinamis dan digunakan untuk melaporkan berbagai aspek bisnis.

Jika pertanyaan yang kami jawab di blog ini serupa dengan yang mungkin Anda minati untuk bisnis Anda sendiri, Anda dapat menghubungi kami dengan mengisi formulir kontak kami.