Author(s): Kirsten Jiayi Pan
Awalnya diterbitkan di Menuju AI.
Sebagai analis data, kita semua tahu bahwa dasbor yang cantik dapat menceritakan kisah kumpulan data, dan wawasan bisnis yang bermakna dapat mengubah data menjadi dolar. Jadi bagaimana memanfaatkan data yang terbatas dan membantu bisnis untuk mencapai tujuan mereka?
Artikel ini akan mengajari Anda cara memanipulasi data dengan SQL (Structured Query Language) dan menggali wawasan bisnis yang bermakna dari data yang Anda terima seperti seorang analis data profesional. Anda dapat mempelajari ETL data sederhana, desain metrik, analisis data, menghasilkan wawasan bisnis, dan visualisasi data untuk mencapai tujuan bisnis dari siklus hidup penuh proyek analisis data.
Agar lebih deskriptif, saya akan membuat laporan perilaku pengguna dengan Anda berdasarkan pesanan pengguna dari Alibaba antara 25 November 2017 hingga 3 Desember 2017 di platform Alibaba. Ada sekitar 1 juta aktivitas pelanggan di kumpulan data kami.
Gambar di bawah ini adalah pratinjau dasbor perilaku pengguna Alibaba:
Lihat dasbor menggunakan tautan ini: https://public.tableau.com/app/profile/jiayi.pan4997/viz/alibaba_user_analysis/Dashboard1?publish=yes
Sekarang, mari kita mulai siklus hidup penuh analisis data kita…
Dataset: https://github.com/kirstenpan/Personal_Project/blob/96493ec3bed25a6862ffd69cbf4770667cdb08f5/alibaba_user.csv.zip
Kami hanya memiliki satu tabel untuk analisis. Tabel tersebut awalnya memiliki satu juta baris (peristiwa) dan lima kolom (tidak termasuk id kolom yang kami buat sebagai kunci utama). Berikut adalah deskripsi singkat dari meja kami:
· id adalah kolom primary key untuk tabel ini. Karena dataset asli tidak memiliki primary key, maka kita harus membuat primary key auto-increment untuk tabel.
· user_id adalah id untuk setiap pengguna.
· product_id adalah id untuk setiap produk.
· product_category_id adalah id untuk setiap kategori produk
· type_of_behavior menjelaskan tindakan pengguna, yang meliputi “pv”, “buy”, “cart”, dan “fav”
· timestamp menjelaskan timestamp dari setiap peristiwa
Langkah 1: Buat Tabel ALI_USER_STAGE dan ALI_USER di Database MySQL:
Catatan: Mengapa tabel pementasan diperlukan dalam data ETL? Gudang Data, Area Pementasan Data, melayani fungsi ganda menyimpan data yang belum diproses dari Sistem Sumber dan menyediakan penyimpanan untuk tabel sementara yang dihasilkan selama proses transformasi. Dengan kata lain, semua informasi yang diperlukan untuk memperbarui atau memasukkan ke database target disimpan dalam tabel pementasan (tabel sementara) sebelum dimasukkan ke dalam gudang data.
BUAT TABEL ‘ALI_USER_STAGE” (
“user_id’ numerik,
numerik “product_id”,
numerik “product_category_id”,
“type_of_behavior” varchar(255)
“timestamp” numerik) CREATE TABLE ‘ALI_USER’ (
“id” int NOT NULL AUTO INCREMENT,
numerik “user_id”,
numerik “product_id”,
numerik “product_category_id”,
“type_of_behavior” varchar(255),
tanggal “cap waktu”,
KUNCI UTAMA (“id”))
Langkah 2: Setelah mengimpor data ke ALI_USER_STAGE di Database MySQL, masukkan data ke ALI_USER:
MASUKKAN KE ALI_USER(user_id, product_id, product_category_id, type_of_behavior, timestamp)
PILIH user_id, product_id, product_category_id, type_of_behavior, FROM_UNIXTIME(timestamp, ‘Y-%m-%d %H:%m:%S’)
DARI_ALI_USER_STAGE
Langkah 3: Desain Metrik
Langkah 4: Analisis Data
Hipotesis №1: Pelanggan cenderung lebih aktif di luar jam kerja mereka (dalam zona waktu lokal sumber data)
SELECT HOUR(timestamp) AS jam, COUNT (user_id) AS hourly_total_customer
DARI ALI_USER
KELOMPOKKAN BERDASARKAN jam
PESAN BERDASARKAN jam
Berdasarkan metrik Customer Count by Hours, puncak jumlah pelanggan yang mengunjungi platform adalah antara pukul 20.00 hingga 22.00 dalam sehari, dan terendah pada tengah malam, yaitu sekitar pukul 02.00 hingga 05.00. Jelas bahwa pelanggan cenderung mengunjungi platform di luar jam kerja mereka, dan jam 8 malam hingga 10 malam adalah waktu di mana pelanggan memiliki lebih banyak waktu luang untuk fokus berbelanja online. Untuk alasan ini, Alibaba harus menargetkan kampanye pemasarannya pada waktu yang tepat karena waktu yang dihabiskan pelanggan di platform sangat berharga.
Hipotesis №2: Pelanggan cenderung menghabiskan lebih banyak waktu di platform selama akhir pekan
SELECT DATE_FORMAT(timestamp, “%m-%d-%Y %W”) SEBAGAI tanggal, COUNT
jumlah_kunjungan AS
DARI ALI_USER
DI MANA stempel waktu ANTARA ‘2017-11-24’ DAN ‘2017-12-03’
KELOMPOK BERDASARKAN tanggal
ORDER BY visit_count DESC
Dari area Tableau di atas, ada tren peningkatan jumlah kunjungan mulai dari 1 Desember 2017. Hal ini karena diskon seluruh situs Alibaba yang akan datang “12/12” (12 Desember 2017) mengaktifkan banyak silent customer. Sebelum 2 Desember 2017, tren keseluruhan grafik menunjukkan secara datar, dan kami dapat menyimpulkan bahwa pelanggan tidak menghabiskan lebih banyak waktu di platform selama akhir pekan secara teratur. Dengan demikian, hipotesis kami tidak diterima.
Jumlah Pelanggan berdasarkan Kategori:
PILIH product_category_id, COUNT(user_id) AS category_total_customer
DARI ALI_USER
DI MANA type_of_behavior = “beli”
GROUP BY product_category_id
PESAN BERDASARKAN DESC kategori_total_pelanggan
product_category_id = 4801426 memiliki pembelian terbanyak, sedangkan 181182, 194104, 245030, dan 344221 memiliki pembelian paling sedikit.
Daya Beli Pelanggan:
PILIH user_id,
KASUS
KETIKA jumlah_pelanggan = 1 ATAU jumlah_pelanggan = 0 KEMUDIAN “Perunggu”
KAPAN customer_count = 2 LALU “Perak”
KETIKA customer_count = 3 MAKA “Emas”
LAINNYA “Platinum”
AKHIR SEBAGAI daya_pembelian_pelanggan
DARI(
PILIH user_id, COUNT(user_id) AS customer_count
DARI ALI_USER
DI MANA type_of_behavior = “beli”
GROUP OLEH user_id
ORDER BY customer_count DESC) t
Catatan:
· Perunggu = pelanggan yang belum pernah melakukan pembelian atau membeli satu kali di platform
· Perak = pelanggan yang membeli dua kali di platform
· Emas = pelanggan yang membeli tiga kali di platform
· Platinum = pelanggan yang membeli 4 kali atau lebih di platform
Menurut daftar pelanggan rahasia yang dihasilkan dari kueri dan diagram lingkaran di atas, sebagian besar pengguna telah melakukan pembelian setidaknya dua kali antara 25 November 2017 hingga 3 Desember 2017, dari Alibaba. Dalam hal ini, rekomendasi lebih lanjut mendorong pelanggan pada daftar “Perunggu” dan mempertahankan pelanggan lainnya dengan menjaga keinginan pembelian mereka.
Tingkat Pembelian Kembali:
DENGAN cte AS (
PILIH user_id
DARI ALI_USER
DI MANA type_of_behavior = “beli”)
PILIH COUNT(t.user_id) / COUNT(cte.user_id) * 100 AS repurchase_rate
DARI(
PILIH user_id
DARI ALI_USER
DI MANA type_of_behavior = “beli”
GROUP OLEH user_id
MEMILIKI COUNT(user_id) >= 2) t
GABUNG KANAN cte
MENGGUNAKAN(user_id)
Tingkat pembelian kembali (pelanggan yang membeli setidaknya dua kali/pelanggan yang membeli) adalah 4,2989%.
Tingkat Konversi Pelanggan menurut Kategori:
PILIH product_category_id, ctvisit, ctbuy,concat(round(ctbuy*100/ctvisit,3),’%’) AS conversion_rate, product_number
DARI
(PILIH product_category_id, hitung
sebagai ctvisit,
SUM(KASUS KETIKA type_of_behavior=’beli’ lalu 1 lainnya 0 berakhir) sebagai ctbuy,
COUNT(DISTINCT product_id) sebagai product_number
DARI ALI_USER
GROUP BY product_category_id) t
ORDER BY conversion_rate
Ada beberapa kategori produk yang memiliki tingkat konversi tinggi, yang terlihat pada grafik di atas. Untuk menyelidiki alasan di balik fakta tersebut, kami menelusuri detailnya dengan menambahkan jumlah produk di setiap kategori dalam grafik kami. Grafik tersebut menunjukkan bahwa semakin tinggi tingkat konversi pelanggan, semakin rendah jumlah produk dalam kategori tersebut. Hal ini karena ketika pelanggan memiliki terlalu banyak pilihan atau substitusi, mereka mungkin memerlukan lebih banyak waktu untuk membandingkan produk, yang menyebabkan tingkat konversi pelanggan menjadi lebih rendah. Di sisi lain, untuk kategori produk dengan pilihan atau substitusi yang lebih sedikit, pelanggan cenderung menghabiskan lebih sedikit waktu untuk memilih produk, sehingga menghasilkan tingkat konversi pelanggan yang lebih tinggi. Singkatnya, kategori produk dengan pembelian terbanyak diberi peringkat berdasarkan kombinasi tingkat konversi pelanggan dan kunjungan halaman.
Referensi: https://beeketing.com/blog/connection-customer-choice-conversion/
Langkah 5: Kesimpulan dan Rekomendasi
Sebagian besar pelanggan aktif di planform Alibaba sekitar pukul 20.00 hingga 22.00; kami merekomendasikan agar departemen pemasaran Alibaba meningkatkan kampanye pemasaran dari jam 8 malam sampai jam 10 malam dengan berfokus pada dorongan produk yang disarankan. Karena jumlah pelanggan yang aktif selama akhir pekan sangat mirip dengan tanggal lain secara reguler, kami tidak menyarankan platform untuk melakukan upaya ekstra pada kampanye pemasaran untuk akhir pekan. Namun, departemen pemasaran harus mengetahui puncak kunjungan yang akan datang untuk diskon seluruh situs Alibaba “12/12”. Beberapa kategori produk memiliki tingkat pembelian yang rendah, seperti 181182, 194104, 245030, dan 344221. Kami menyarankan departemen pemasaran meningkatkan paparan produk untuk kategori ini kepada target pelanggan mereka dan bersiaplah untuk diskon di seluruh situs Alibaba “12/12”. Dengan menganalisis daya beli pelanggan, sebagian besar pelanggan berada di level “Bronze” dan “Silver”. Dalam hal ini, sebaiknya departemen pemasaran menganalisis keranjang belanja pelanggan dan menyempurnakan algoritme produk yang direkomendasikan untuk meningkatkan tingkat pembelian pelanggan. Sebagian besar bisnis yang termasuk dalam kategori produk tingkat konversi pelanggan rendah berada di pasar yang kompetitif. Setelah menggali ke dalam “Corong Konversi eCommerce”, kami menemukan bahwa pelanggan lebih suka memilih di antara merek teratas ketika mereka memiliki terlalu banyak pilihan. Pasar yang kompetitif seperti itu tidak ramah bagi usaha kecil dalam kategori produk yang sama. Dalam hal ini, kami mendorong usaha kecil untuk menerobos dari kategori produk asli mereka dan mencakup kategori produk dengan tingkat konversi pelanggan yang tinggi dengan menggunakan beberapa produk mereka.
https://www.convertcart.com/blog/ecommerce-conversion-funnel
Langkah 6: Visualisasi Data
Dasbor Tableau: https://public.tableau.com/app/profile/jiayi.pan4997/viz/alibaba_user_analysis/Dashboard1?publish=yes
Pratinjau Dasbor Tableau
Analisis Data Komprehensif dengan SQL dan Visualisasi Data: Investigasi Perilaku Pengguna Alibaba awalnya diterbitkan di Menuju AI di Medium, di mana orang melanjutkan percakapan dengan menyoroti dan menanggapi cerita ini.Diterbitkan melalui Menuju AI
