Membangun aplikasi rekomendasi Customer 360 dengan BigQuery Graph

1. Pengantar

Dalam codelab ini, Anda akan mempelajari cara menggunakan BigQuery Graph untuk membangun tampilan Customer 360 dan mesin pemberi saran untuk Cymbal Pets, sebuah perusahaan retail fiktif. Anda akan memanfaatkan kecanggihan SQL untuk membuat, mengkueri, dan menganalisis data grafik langsung di BigQuery, serta menggabungkannya dengan penelusuran vektor untuk rekomendasi produk tingkat lanjut.

BigQuery Graph memungkinkan Anda membuat model hubungan antara entity data (seperti pelanggan, produk, dan pesanan) sebagai grafik, sehingga memudahkan untuk menjawab pertanyaan kompleks tentang perilaku pelanggan dan afinitas produk.

Diagram Kasus Penggunaan

Yang akan Anda lakukan

  • Membuat set data dan skema BigQuery untuk grafik Cymbal Pets
  • Memuat data sampel (Pelanggan, Produk, Pesanan, Toko) dari Cloud Storage
  • Membuat Graf Properti di BigQuery yang menghubungkan semua entity ini
  • Memvisualisasikan histori pembelian pelanggan menggunakan kueri graf
  • Membangun sistem rekomendasi produk menggunakan penelusuran vektor
  • Tingkatkan rekomendasi menggunakan relasi "Dibeli Bersama" pada graph dan metrik kemiripan Jaccard

Yang Anda butuhkan

  • Browser web seperti Chrome
  • Project Google Cloud yang mengaktifkan penagihan

Codelab ini ditujukan bagi developer dari semua level, termasuk para pemula.

2. Sebelum memulai

Membuat Project Google Cloud

  1. Di Konsol Google Cloud, pilih atau buat project Google Cloud.
  2. Pastikan penagihan diaktifkan untuk project Cloud Anda.

Mulai Cloud Shell

  1. Klik Activate Cloud Shell di bagian atas Konsol Google Cloud.
  2. Verifikasi autentikasi:
gcloud auth list
  1. Konfirmasi project Anda:
gcloud config get project
  1. Tetapkan jika diperlukan:
export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project $PROJECT_ID

Mengaktifkan API

Jalankan perintah ini untuk mengaktifkan BigQuery API yang diperlukan:

gcloud services enable bigquery.googleapis.com

3. Menentukan Skema

Pertama, Anda perlu membuat set data untuk menyimpan tabel terkait grafik dan menentukan skema untuk node dan edge.

  1. Untuk codelab ini, kita akan menjalankan perintah SQL. Anda dapat menjalankan perintah ini di BigQuery Studio > SQL Editor, atau menggunakan perintah bq query di Cloud Shell. Kueri SQL BaruKami akan mengasumsikan bahwa Anda menggunakan BigQuery SQL Editor untuk mendapatkan pengalaman yang lebih baik dengan pernyataan pembuatan multi-baris.
  2. Buat set data cymbal_pets_demo:
CREATE SCHEMA IF NOT EXISTS cymbal_pets_demo;
  1. Buat tabel untuk order_items, products, orders, stores, customers, dan co_related_products_for_angelica. Tabel ini akan berfungsi sebagai data sumber untuk grafik kita.
CREATE TABLE IF NOT EXISTS cymbal_pets_demo.order_items
(
  order_id INT64,
  product_id INT64,
  order_item_id INT64,
  quantity INT64,
  price FLOAT64,
  PRIMARY KEY (order_id, product_id, order_item_id) NOT ENFORCED
)
CLUSTER BY order_item_id;

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.products
(
  product_id INT64,
  product_name STRING,
  brand STRING,
  category STRING,
  subcategory INT64,
  animal_type INT64,
  search_keywords INT64,
  price FLOAT64,
  description STRING,
  inventory_level INT64,
  supplier_id INT64,
  average_rating FLOAT64,
  uri STRING,
  embedding ARRAY<FLOAT64>,
  PRIMARY KEY (product_id) NOT ENFORCED
)
CLUSTER BY product_id;

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.orders
(
  customer_id INT64,
  order_id INT64,
  shipping_address_city STRING,
  store_id INT64,
  order_date STRING,
  order_type STRING,
  payment_method STRING,
  PRIMARY KEY (order_id) NOT ENFORCED
);

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.stores
(
  store_id INT64,
  store_name STRING,
  address_state STRING,
  address_city STRING,
  latitude FLOAT64,
  longitude FLOAT64,
  opening_hours STRUCT<Monday STRING, Tuesday STRING, Wednesday STRING, Thursday STRING, Friday STRING, Saturday STRING, Sunday STRING>,
  manager_id INT64,
  PRIMARY KEY (store_id) NOT ENFORCED
)
CLUSTER BY store_id;

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.customers
(
  customer_id INT64,
  first_name STRING,
  last_name STRING,
  email STRING,
  gender STRING,
  address_city STRING,
  address_state STRING,
  loyalty_member BOOL,
  PRIMARY KEY (customer_id) NOT ENFORCED
)
CLUSTER BY customer_id;

CREATE TABLE IF NOT EXISTS cymbal_pets_demo.co_related_products_for_angelica
(
  angelica_product_id INT64,
  other_product_id INT64,
  co_purchase_count INT64,
  jaccard_similarity FLOAT64
);

Anda kini telah menentukan struktur untuk data grafik.

4. Memuat Data

Sekarang, isi tabel dengan data sampel dari Cloud Storage.

Jalankan pernyataan LOAD DATA berikut di BigQuery SQL Editor:

LOAD DATA INTO `cymbal_pets_demo.customers`
FROM FILES (
    format = 'AVRO',
    uris = ['gs://sample-data-and-media/cymbal-pets/tables/customers/*.avro'],
    enable_logical_types = true
);

LOAD DATA INTO `cymbal_pets_demo.order_items`
FROM FILES (
    format = 'AVRO',
    uris = ['gs://sample-data-and-media/cymbal-pets/tables/order_items/*.avro'],
    enable_logical_types = true
);

LOAD DATA INTO `cymbal_pets_demo.orders`
FROM FILES (
    format = 'AVRO',
    uris = ['gs://sample-data-and-media/cymbal-pets/tables/orders/*.avro'],
    enable_logical_types = true
);

-- 1. Create a new partitioned and clustered table from your current data
CREATE TABLE cymbal_pets_demo.orders_temp
PARTITION BY order_date
CLUSTER BY order_id
AS
SELECT
  customer_id,
  order_id,
  shipping_address_city,
  store_id,
  -- Parse the string to a DATE type. Adjust format string ('%Y-%m-%d') if necessary.
  PARSE_DATE('%Y-%m-%d', order_date) AS order_date,
  order_type,
  payment_method
FROM
  cymbal_pets_demo.orders;

-- 2. Drop the original, non-partitioned table
DROP TABLE cymbal_pets_demo.orders;

-- 3. Rename the temporary table to the original table name
ALTER TABLE cymbal_pets_demo.orders_temp RENAME TO orders;

LOAD DATA INTO `cymbal_pets_demo.products`
FROM FILES (
    format = 'AVRO',
    uris = ['gs://sample-data-and-media/cymbal-pets/tables/products/*.avro'],
    enable_logical_types = true
);

LOAD DATA INTO `cymbal_pets_demo.stores`
FROM FILES (
    format = 'AVRO',
    uris = ['gs://sample-data-and-media/cymbal-pets/tables/stores/*.avro'],
    enable_logical_types = true
);

Anda akan melihat konfirmasi bahwa baris telah dimuat ke dalam setiap tabel.

5. Membuat Graf Properti

Setelah data dimuat, Anda kini dapat menentukan grafik properti. Tindakan ini memberi tahu BigQuery tabel mana yang mewakili node (entity seperti Pelanggan, Produk) dan tabel mana yang mewakili edge (hubungan seperti "Visited", "Placed", "Has").

Skema Grafik

Jalankan pernyataan DDL berikut:

CREATE OR REPLACE PROPERTY GRAPH cymbal_pets_demo.PetsOrderGraph
NODE TABLES (
  cymbal_pets_demo.customers KEY(customer_id) LABEL Customer,
  cymbal_pets_demo.products KEY(product_id) LABEL Products,
  cymbal_pets_demo.stores KEY(store_id) LABEL Stores,
  cymbal_pets_demo.orders KEY(order_id) LABEL Orders
)
EDGE TABLES (
  cymbal_pets_demo.orders as customer_to_store_edge
    KEY (order_id)
    SOURCE KEY (customer_id) references customers(customer_id)
    DESTINATION KEY (store_id) references stores(store_id)
    LABEL Visited
    PROPERTIES ALL COLUMNS,

  cymbal_pets_demo.order_items
    KEY (order_item_id)
    SOURCE KEY (order_id) references orders(order_id)
    DESTINATION KEY (product_id) references products(product_id)
    LABEL Has
    PROPERTIES ALL COLUMNS,

  cymbal_pets_demo.orders as customer_to_orders_edge
    KEY (order_id)
    SOURCE KEY (customer_id) references customers(customer_id)
    DESTINATION KEY (order_id) references orders(order_id)
    LABEL Placed
    PROPERTIES ALL COLUMNS,

  cymbal_pets_demo.co_related_products_for_angelica
    KEY (angelica_product_id)
    SOURCE KEY (angelica_product_id) references products(product_id)
    DESTINATION KEY (other_product_id) references products(product_id)
    LABEL BoughtTogether
    PROPERTIES ALL COLUMNS
);

Tindakan ini akan membuat grafik PetsOrderGraph yang memungkinkan kita melakukan penjelajahan grafik menggunakan operator GRAPH_TABLE.

6. Memvisualisasikan Histori Pembelian semua pelanggan

Untuk bagian visualisasi dan rekomendasi codelab ini, kita akan menggunakan visualisasi grafik native di BigQuery Studio. Hal ini memungkinkan kita memvisualisasikan hasil grafik dengan mudah.

Atau, Anda dapat memvisualisasikan di BigQuery Graph Notebook menggunakan IPython Magics. Dengan menambahkan perintah ajaib %%bigquery dengan fungsi TO_JSON, Anda dapat memvisualisasikan hasilnya seperti yang ditunjukkan di bagian berikut.

Misalnya, Cymbal Pets ingin mendapatkan visualisasi 360 derajat dari semua pelanggan dan pembelian yang mereka lakukan dalam jangka waktu tertentu.

Jalankan perintah berikut di tab BigQuery Studio baru:

GRAPH cymbal_pets_demo.PetsOrderGraph
  # finds the customer node and then finds all
  # the Orders nodes that are connected to that customer through the
  # Placed relationship
  MATCH (customer:Customer)-[placed:Placed]->(ordr:Orders)-[has:Has]->(product:Products)
  # filters the Orders nodes to only include those where the
  # order_date is within the last 3 months.
  WHERE ordr.order_date >= date('2024-11-27')
  # # This line finds all the Products nodes that are connected to the
  # # filtered Orders nodes through the Has relationship.
  MATCH p=(customer:Customer)-[placed:Placed]->(ordr:Orders)-[has:Has]->(product:Products)
  LIMIT 40
  RETURN 
    TO_JSON(p) as paths

Untuk memvisualisasikan hasil, di panel Query results, klik Graph.

Histori Pembelian semua Pelanggan

7. Memvisualisasikan Histori Pembelian Angelica

Misalnya, Cymbal Pets ingin mempelajari lebih dalam tentang pelanggan bernama Angelica Russell. Mereka ingin menganalisis produk yang dibeli Angelica dalam 3 bulan terakhir, dan toko yang dikunjungi pelanggan tersebut.

GRAPH cymbal_pets_demo.PetsOrderGraph
  # finds the customer node with the name "Angelica Russell" and then finds all
  # the Orders nodes that are connected to that customer through the
  # Placed relationship and all the Products nodes that are connected to the
  # filtered Orders nodes through the Has relationship.
   MATCH p=(customer:Customer {first_name: 'Angelica', last_name: 'Russell'})-[placed:Placed]->(ordr:Orders)-[has:Has]->(product:Products)
  # filters the Orders nodes to only include those where the
  # order_date is within the last 3 months.
  WHERE ordr.order_date >= date('2024-11-27')
  # finds the Stores nodes where Angelica placed order from
  MATCH p2=(customer)-[visited:Visited]->(store:Stores)
  RETURN
    TO_JSON(p) as path, TO_JSON(p2) as path2

Histori Pembelian Angelica

8. Rekomendasi Produk menggunakan penelusuran vektor

Cymbal Pets ingin merekomendasikan produk kepada Angelica berdasarkan produk yang baru-baru ini dia beli. Kita dapat menggunakan penelusuran vektor untuk menemukan produk dengan embedding yang mirip dengan pembelian sebelumnya.

Jalankan skrip SQL berikut di sel Colab baru. Skrip ini:

  1. Mengidentifikasi produk yang baru-baru ini dibeli Angelica.
  2. Menggunakan VECTOR_SEARCH untuk menemukan 4 produk serupa teratas dari tabel products.

Catatan: Langkah ini mengasumsikan bahwa Anda telah menjalankan AI.GENERATE_EMBEDDINGS untuk membuat kolom embedding di tabel produk.

DECLARE products_bought_by_angelica ARRAY<INT64>;

-- 1. Get IDs of products bought by Angelica
SET products_bought_by_angelica = (
  SELECT ARRAY_AGG(product_id) FROM
   GRAPH_TABLE(
    cymbal_pets_demo.PetsOrderGraph
      MATCH (c:Customer {first_name: 'Angelica', last_name: 'Russell'})-[placed:Placed]->(o:Orders)
      WHERE o.order_date >= date('2024-11-27')
      MATCH (o)-[has_edge:Has]->(p:Products)
      RETURN DISTINCT p.product_id as product_id
  ));

-- 2. Find similar products using vector search
SELECT 
  query.product_name as AngelicaBought, 
  base.product_name as RecommendedProducts, 
  base.category
FROM
  VECTOR_SEARCH(
    TABLE cymbal_pets_demo.products,
    'embedding',
    (SELECT * FROM cymbal_pets_demo.products
     WHERE product_id IN UNNEST(products_bought_by_angelica)),
    'embedding',
    top_k => 4)
WHERE query.product_name <> base.product_name;

Anda akan melihat daftar produk yang direkomendasikan yang secara semantik mirip dengan yang dibeli Angelica.

Hasil Penelusuran Vektor

9. Rekomendasi menggunakan "Dibeli Bersama" dan Kemiripan Jaccard

Teknik rekomendasi yang efektif lainnya adalah "Penyaringan Kolaboratif" — merekomendasikan produk yang sering dibeli bersama oleh pengguna lain.

Kita dapat menemukan produk ini dengan melintasi grafik dari pelanggan ke produk yang mereka beli, lalu ke pelanggan lain yang membeli produk tersebut, dan terakhir ke produk lain yang dibeli pelanggan tersebut.

Mengatasi Bias Popularitas dengan Kemiripan Jaccard

Meskipun jumlah pembelian bersama mentah berguna, jumlah tersebut dapat bias terhadap produk populer. Produk yang sangat populer mungkin dibeli bersama banyak barang lain secara kebetulan.

Kemiripan Jaccard meningkatkan rekomendasi dengan menormalisasi jumlah pembelian bersama. Metrik ini mengukur kesamaan antara dua set (dalam hal ini, set pesanan yang berisi setiap produk).

Rumus untuk Jaccard Similarity adalah:

Dalam hal ini:

  • A beririsan dengan B adalah jumlah pesanan yang berisi produk A dan produk B (jumlah pembelian bersama).
  • A adalah jumlah total pesanan yang berisi produk A.
  • B adalah jumlah total pesanan yang berisi produk B.

Dalam contoh berikut, himpunan A = {b,c,e,f,g}, himpunan B = {a,d,b,g}, irisannya A⋂B = {b,g}, gabungannya A⋃B = {a,b,c,d,e,f,g}, sehingga kesamaan Jaccard antara A dan B adalah 2 / 7 = 0,285714

Pemilihan Kandidat dan Pemeringkatan Ulang

Dalam sistem rekomendasi di dunia nyata yang beroperasi pada set data yang sangat besar, sering kali tidak praktis untuk menghitung skor kesamaan yang kompleks (seperti Jaccard) untuk semua kemungkinan pasangan produk. Sebagai gantinya, pola yang umum adalah menggunakan pendekatan dua tahap:

  1. Pemilihan Kandidat: Gunakan metrik yang sederhana dan cepat (seperti jumlah pembelian bersama mentah) untuk memfilter ruang penelusuran dan menemukan jumlah kandidat yang dapat dikelola (misalnya, 10 teratas).
  2. Pemeringkatan ulang: Terapkan metrik yang lebih presisi, tetapi lebih berat secara komputasi (seperti Kemiripan Jaccard) untuk memberi peringkat pada kumpulan kecil kandidat tersebut dan memilih rekomendasi teratas akhir.

Dalam codelab ini, kita akan mengikuti pola berikut:

  • Tahap 1: Jalankan kueri untuk menemukan 10 produk teratas yang dibeli bersama untuk setiap produk, berdasarkan jumlah pembelian bersama mentah, dan simpan dalam tabel.
  • Tahap 2: Gunakan kueri grafik untuk mengambil kandidat ini, memberi peringkat berdasarkan kemiripan Jaccard, dan menampilkan 3 kandidat teratas.

[!WARNING] Kelemahan: Dengan memfilter jumlah mentah di Tahap 1, kita mungkin kehilangan "recall" pembelian bersama yang sangat spesifik, tetapi frekuensinya rendah. Jika suatu produk sangat mirip dengan produk lain, tetapi keduanya jarang dibeli, produk tersebut mungkin tidak masuk ke dalam 10 kandidat teratas dan akan terlewatkan.

Jalankan kueri berikut untuk menghitung jumlah pembelian bersama mentah dan kesamaan Jaccard, serta menyimpan 10 kandidat teratas berdasarkan jumlah mentah:

CREATE OR REPLACE TABLE cymbal_pets_demo.co_related_products_for_angelica AS
-- Calculate the total number of orders for each product
WITH ProductOrderCounts AS (
    SELECT product_id, COUNT(DISTINCT order_id) as total_count
    FROM cymbal_pets_demo.order_items
    GROUP BY product_id
),
-- Calculate the intersection of each product pairs
CoPurchases AS (
    SELECT
        angelicaProduct.product_id AS angelica_product_id,
        otherProduct.product_id AS other_product_id,
        count(DISTINCT otherOrder.order_id) AS co_purchase_count
    FROM
        GRAPH_TABLE (cymbal_pets_demo.PetsOrderGraph
          MATCH (angelica:Customer {first_name: 'Angelica', last_name: 'Russell'})-[:Placed]->(o:Orders)-[:Has]->(angelicaProduct:Products)
          WHERE o.order_date >= date('2024-11-27')
          WITH angelica, angelicaProduct
          MATCH (otherCustomer:Customer)-[:Placed]->(otherOrder:Orders)-[:Has]->(angelicaProduct)
          WHERE otherCustomer <> angelica
          WITH angelicaProduct, otherOrder
          MATCH (otherOrder)-[:HAS]->(otherProduct:Products)
          WHERE angelicaProduct <> otherProduct
          RETURN angelicaProduct, otherProduct, otherOrder
        )
    GROUP BY
        angelicaProduct.product_id, otherProduct.product_id
)
SELECT * FROM (
    SELECT
        cp.angelica_product_id,
        cp.other_product_id,
        cp.co_purchase_count,
        -- The Jaccard calculation, which is the intersection of A and B divided by (A + B - intersection)
        SAFE_DIVIDE(cp.co_purchase_count, (poc1.total_count + poc2.total_count - cp.co_purchase_count)) AS jaccard_similarity,
        ROW_NUMBER() OVER (PARTITION BY cp.angelica_product_id ORDER BY cp.co_purchase_count DESC) AS rn
    FROM CoPurchases cp
    JOIN ProductOrderCounts poc1 ON cp.angelica_product_id = poc1.product_id
    JOIN ProductOrderCounts poc2 ON cp.other_product_id = poc2.product_id
)
WHERE rn <= 10;

Logika Rekomendasi

Jalankan kueri ini untuk merekomendasikan 3 produk teratas untuk setiap pembelian Angelica, yang terhubung langsung melalui edge BoughtTogether, yang menampilkan jumlah pembelian bersama dan kemiripan Jaccard:

SELECT * FROM GRAPH_TABLE(
  cymbal_pets_demo.PetsOrderGraph
  MATCH (customer:Customer {first_name: 'Angelica', last_name: 'Russell'})-[placed:Placed]->(ordr:Orders)
  WHERE ordr.order_date >= date('2024-11-27')
  MATCH (ordr)-[has:Has]->(product:Products)
  MATCH (product)-[bought_together:BoughtTogether]->(recommended_product:Products)
  RETURN 
    product.product_name AS OriginalProduct,
    recommended_product.product_name AS Recommended,
    bought_together.co_purchase_count AS Strength,
    bought_together.jaccard_similarity AS JaccardSimilarity
)
-- Rank product recommendations by Jaccard Similarity
QUALIFY ROW_NUMBER() OVER (PARTITION BY OriginalProduct ORDER BY JaccardSimilarity DESC) <= 3
ORDER BY OriginalProduct;

Kueri ini melintasi dari Customer -> Order -> Product -> (BoughtTogether) -> Recommended Product, yang menampilkan rekomendasi berdasarkan perilaku pembelian kolektif dan mengambil skor kemiripannya.

Dibeli Bersama

10. Pembersihan

Agar tidak menimbulkan biaya berkelanjutan pada akun Google Cloud Anda, hapus resource yang dibuat selama codelab ini.

Hapus set data dan semua tabel:

DROP SCHEMA IF EXISTS cymbal_pets_demo CASCADE;

Jika Anda membuat project baru untuk codelab ini, Anda juga dapat menghapus project tersebut:

gcloud projects delete $PROJECT_ID

11. Selamat

Selamat! Anda telah berhasil membangun tampilan Customer 360 dan mesin pemberi saran menggunakan BigQuery Graph.

Yang telah Anda pelajari

  • Cara membuat graf properti di BigQuery.
  • Cara memuat data ke dalam node dan edge grafik.
  • Cara membuat kueri pola grafik menggunakan GRAPH_TABLE dan MATCH.
  • Cara menggabungkan kueri grafik dengan penelusuran vektor untuk rekomendasi campuran.

Langkah berikutnya