Creare applicazioni di suggerimenti Customer 360 con BigQuery Graph

1. Introduzione

In questo codelab imparerai a utilizzare BigQuery Graph per creare una visualizzazione a 360° del cliente e un motore di suggerimenti per Cymbal Pets, un'azienda di vendita al dettaglio fittizia. Sfrutterai la potenza di SQL per creare, eseguire query e analizzare i dati del grafico direttamente in BigQuery, combinandoli con la ricerca vettoriale per suggerimenti avanzati sui prodotti.

BigQuery Graph ti consente di modellare le relazioni tra le entità di dati (come clienti, prodotti e ordini) come un grafico, semplificando la risposta a domande complesse sul comportamento dei clienti e sulle affinità dei prodotti.

Attività previste

• Crea un set di dati e uno schema BigQuery per il grafico Cymbal Pets
• Carica dati di esempio (Clienti, Prodotti, Ordini, Negozi) da Cloud Storage
• Crea un grafico delle proprietà in BigQuery che colleghi queste entità
• Visualizzare la cronologia degli acquisti dei clienti utilizzando le query grafiche
• Crea un sistema di suggerimenti per i prodotti utilizzando la ricerca vettoriale
• Migliora i consigli utilizzando le relazioni del grafico "Acquistati insieme" e la similarità di Jaccard

Che cosa ti serve

• Un browser web come Chrome
• Un progetto cloud Google Cloud con la fatturazione abilitata

Questo codelab è rivolto a sviluppatori di tutti i livelli, inclusi i principianti.

2. Prima di iniziare

Crea un progetto Google Cloud

1. Nella console Google Cloud, seleziona o crea un progetto Google Cloud.
2. Verifica che la fatturazione sia attivata per il tuo progetto Cloud.

Avvia Cloud Shell

1. Fai clic su Attiva Cloud Shell nella parte superiore della console Google Cloud.
2. Verifica l'autenticazione:

gcloud auth list

3. Conferma il progetto:

gcloud config get project

4. Impostalo se necessario:

export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project $PROJECT_ID

Abilita API

Esegui questo comando per abilitare l'API BigQuery richiesta:

gcloud services enable bigquery.googleapis.com

3. Definisci lo schema

Innanzitutto, devi creare un set di dati in cui archiviare le tabelle correlate al grafo e definire lo schema per i nodi e gli archi.

1. Per questo codelab, eseguiremo comandi SQL. Puoi eseguire questi comandi in BigQuery Studio > Editor SQL oppure utilizzare il comando bq query in Cloud Shell. Per una migliore esperienza con le istruzioni create su più righe, presupporremo che tu stia utilizzando l'editor SQL di BigQuery.
2. Crea il set di dati cymbal_pets_demo:

CREATE SCHEMA IF NOT EXISTS cymbal_pets_demo;

3. Crea le tabelle per order_items, products, orders, stores, customers e co_related_products_for_angelica. Queste tabelle fungeranno da dati di origine per il nostro grafico.

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 DATE,
  order_type STRING,
  payment_method STRING,
  PRIMARY KEY (order_id) NOT ENFORCED
)
PARTITION BY order_date
CLUSTER BY order_id;

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
);

Ora hai definito la struttura dei dati del grafico.

4. Carica i dati

Ora, compila le tabelle con dati di esempio da Cloud Storage.

Esegui le seguenti istruzioni LOAD DATA nell'editor SQL di BigQuery:

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
);

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
);

Dovresti vedere una conferma che le righe sono state caricate in ogni tabella.

5. Crea il grafico delle proprietà

Una volta caricati i dati, puoi definire il grafico delle proprietà. In questo modo BigQuery sa quali tabelle rappresentano i nodi (entità come clienti, prodotti) e quali rappresentano gli archi (relazioni come "Visitato", "Effettuato", "Ha").

Esegui la seguente istruzione DDL:

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
);

In questo modo viene creato il grafico PetsOrderGraph, che ci consente di eseguire attraversamenti del grafico utilizzando l'operatore GRAPH_TABLE.

6. Visualizzare la cronologia acquisti di tutti i clienti

Apri Nuovo notebook in BigQuery Studio.

Per le parti di visualizzazione e suggerimenti di questo codelab, utilizzeremo un notebook Google Colab in BigQuery Studio. In questo modo possiamo visualizzare facilmente i risultati del grafico.

Incolla il seguente codice in una cella di codice:

!pip install bigquery-magics==0.12.1

BigQuery Graph Notebook è implementato come IPython Magics. Se aggiungi il comando magico %%bigquery con la funzione TO_JSON, puoi visualizzare i risultati come mostrato nelle sezioni seguenti.

Supponiamo che Cymbal Pets voglia ottenere una visualizzazione a 360 gradi di tutti i clienti e degli acquisti effettuati in un periodo di tempo specifico.

Esegui questo comando in una nuova cella:

%%bigquery --graph

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

BigQuery Graph ti consente di trovare corrispondenze tra più tabelle senza una sintassi JOIN complessa.

Dovresti vedere una rappresentazione visiva del risultato del grafico.

7. Visualizza la cronologia acquisti di Angelica

Supponiamo che Cymbal Pets voglia analizzare nel dettaglio un cliente di nome Angelica Russell. Vuole analizzare i prodotti acquistati da Angelica negli ultimi tre mesi e i negozi visitati dal cliente.

Sebbene questo scenario specifico possa essere ottenuto utilizzando un join relazionale tradizionale, qui utilizziamo BigQuery Graph per dimostrare i concetti e la sintassi fondamentali del grafico, che visualizza analisi di relazioni complesse.

%%bigquery --graph

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

8. Suggerimento di prodotti utilizzando la ricerca vettoriale

Cymbal Pets vuole consigliare ad Angelica dei prodotti in base a ciò che ha acquistato di recente. Possiamo utilizzare la ricerca vettoriale per trovare prodotti con incorporamenti simili ai suoi acquisti passati.

Esegui il seguente script SQL in una nuova cella Colab. Questo script:

1. Identifica i prodotti acquistati di recente da Angelica.
2. Utilizza VECTOR_SEARCH per trovare i quattro prodotti simili più importanti della tabella products.

Nota: questo passaggio presuppone che tu abbia già eseguito AI.GENERATE_EMBEDDINGS per creare una colonna di incorporamenti nella tabella dei prodotti.

%%bigquery
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;

Dovresti visualizzare un elenco di prodotti consigliati semanticamente simili a quelli acquistati da Angelica.

9. Consiglio che utilizza "Acquistati insieme" e la somiglianza di Jaccard

Un'altra potente tecnica di raccomandazione è il "filtraggio collaborativo", che consiste nel consigliare prodotti acquistati spesso insieme da altri utenti.

Possiamo trovare questi prodotti attraversando il grafico da un cliente ai prodotti acquistati, poi ad altri clienti che hanno acquistato questi prodotti e infine agli altri prodotti acquistati da questi clienti.

Superare il bias di popolarità con la somiglianza di Jaccard

Sebbene i conteggi grezzi degli acquisti congiunti siano utili, possono essere influenzati dai prodotti più popolari. Un prodotto molto popolare potrebbe essere acquistato con molti altri prodotti per caso.

Similarità di Jaccard fa un ulteriore passo avanti nei consigli normalizzando il conteggio degli acquisti congiunti. Misura la somiglianza tra due insiemi (in questo caso, gli insiemi di ordini contenenti ciascun prodotto).

La formula per la similarità di Jaccard è:

Dove:

• A intersect B è il numero di ordini contenenti sia il prodotto A che il prodotto B (conteggio di acquisto congiunto).
• A è il numero totale di ordini contenenti il prodotto A.
• B è il numero totale di ordini contenenti il prodotto B.

Generazione e riposizionamento dei candidati

Nei sistemi di raccomandazione reali che operano su set di dati di grandi dimensioni, spesso non è pratico calcolare punteggi di similarità complessi (come Jaccard) per tutte le possibili coppie di prodotti. Un pattern comune è invece quello di utilizzare un approccio in due fasi:

1. Generazione dei candidati: utilizza una metrica semplice e veloce (come il conteggio degli acquisti congiunti non elaborati) per filtrare lo spazio di ricerca e trovare un numero gestibile di candidati (ad es. i primi 10).
2. Riorganizzazione: applica una metrica più precisa, ma più pesante dal punto di vista computazionale (come la similarità di Jaccard), per classificare il piccolo insieme di candidati e selezionare i migliori consigli finali.

In questo codelab, seguiremo questo pattern:

• Fase 1: esegui una query per trovare i 10 prodotti acquistati insieme più frequentemente per ogni prodotto, in base al conteggio grezzo degli acquisti congiunti, e memorizzali in una tabella.
• Fase 2: utilizza una query del grafico per recuperare questi candidati, classificali in base alla similarità di Jaccard e restituisci i primi 3.

[!WARNING] Svantaggio: se filtriamo in base al conteggio non elaborato nella fase 1, potremmo perdere il "richiamo" degli acquisti congiunti altamente specifici ma a bassa frequenza. Se un prodotto è molto simile a un altro, ma entrambi vengono acquistati raramente, è possibile che non rientri tra i primi 10 candidati e non venga visualizzato.

Esegui la seguente query per calcolare sia il conteggio di acquisto congiunto non elaborato sia la similarità di Jaccard e memorizza i primi 10 candidati in base al conteggio non elaborato:

%%bigquery
CREATE OR REPLACE TABLE cymbal_pets_demo.co_related_products_for_angelica AS
WITH ProductOrderCounts AS (
    SELECT product_id, COUNT(DISTINCT order_id) as total_count
    FROM cymbal_pets_demo.order_items
    GROUP BY product_id
),
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,
        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;

Esegui questa query per consigliare i primi tre prodotti per ogni acquisto di Angelica, collegati direttamente tramite il nodo BoughtTogether, mostrando sia il conteggio degli acquisti congiunti sia la similarità di Jaccard:

%%bigquery
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
)
QUALIFY ROW_NUMBER() OVER (PARTITION BY OriginalProduct ORDER BY JaccardSimilarity DESC) <= 3
ORDER BY OriginalProduct;

Questa query attraversa Customer -> Order -> Product -> (BoughtTogether) -> Recommended Product, mostrando i consigli basati sul comportamento di acquisto collettivo e recuperando i relativi punteggi di similarità.

10. Esegui la pulizia

Per evitare addebiti continui al tuo account Google Cloud, elimina le risorse create durante questo codelab.

Elimina il set di dati e tutte le tabelle:

DROP SCHEMA IF EXISTS cymbal_pets_demo CASCADE;

Se hai creato un nuovo progetto per questo codelab, puoi anche eliminarlo:

gcloud projects delete $PROJECT_ID

11. Complimenti

Complimenti! Hai creato correttamente una visualizzazione a 360° del cliente e un motore per suggerimenti utilizzando BigQuery Graph.

Cosa hai imparato

• Come creare un grafico delle proprietà in BigQuery.
• Come caricare i dati nei nodi e negli archi del grafico.
• Come eseguire query sui pattern del grafico utilizzando GRAPH_TABLE e MATCH.
• Come combinare le query grafiche con la ricerca vettoriale per suggerimenti ibridi.

Passaggi successivi

• Esplora la documentazione di BigQuery Graph.
• Scopri di più sulla ricerca vettoriale in BigQuery.