Segment Based on Customer Behavior - Not Characteristics - with Louvain

CREATE OR REPLACE TABLE ORDER_ITEM_COUNTS AS
SELECT
  order_id,
  COUNT(*) AS items_in_order
FROM BASKETS
GROUP BY 1;

CREATE OR REPLACE TABLE USER_KMEANS_FEATURES AS
WITH ORDER_STATS AS (
  SELECT
    order_id,
    COUNT(*) AS items_in_order,
    AVG(reordered::FLOAT) AS reorder_rate_in_order
  FROM BASKETS
  GROUP BY 1
)
SELECT
  o.user_id,
  AVG(s.items_in_order::FLOAT)       AS avg_items_per_order,
  AVG(s.reorder_rate_in_order)       AS reorder_rate
FROM ORDERS o
JOIN ORDER_STATS s
  ON o.order_id = s.order_id
GROUP BY 1;

select * from user_kmeans_features

from snowflake.snowpark.context import get_active_session

session = get_active_session()

df = session.table("USER_KMEANS_FEATURES").select(
    "USER_ID", "AVG_ITEMS_PER_ORDER", "REORDER_RATE"
).dropna()

pdf = df.to_pandas()
pdf.head()

import os
os.environ["LOKY_MAX_CPU_COUNT"] = "1"
os.environ["JOBLIB_MULTIPROCESSING"] = "0"

from sklearn.preprocessing import MinMaxScaler
from sklearn.cluster import KMeans

X = pdf[["AVG_ITEMS_PER_ORDER", "REORDER_RATE"]].to_numpy()

X_scaled = MinMaxScaler().fit_transform(X)

km = KMeans(n_clusters=3, n_init=10, random_state=42, algorithm="lloyd")
pdf["CLUSTER"] = km.fit_predict(X_scaled)

import matplotlib.pyplot as plt

plt.figure()
plt.scatter(
    pdf["AVG_ITEMS_PER_ORDER"],
    pdf["REORDER_RATE"],
    c=pdf["CLUSTER"]
)
plt.xlabel("avg_items_per_order")
plt.ylabel("reorder_rate")
plt.title("K-means customer clusters")
plt.show()

CREATE OR REPLACE VIEW CUSTOMERS AS
SELECT DISTINCT
  user_id AS nodeId
FROM ORDERS;

CREATE OR REPLACE VIEW PRODUCTS_NODES AS
SELECT
  product_id AS nodeId
FROM PRODUCTS;

CREATE OR REPLACE VIEW PURCHASES AS
SELECT
  o.user_id    AS sourceNodeId,
  b.product_id AS targetNodeId
FROM ORDERS o
JOIN BASKETS b
  ON o.order_id = b.order_id;

-- Use a role with the required privileges
USE ROLE ACCOUNTADMIN;

-- Create a consumer role for users of the Graph Analytics application
CREATE ROLE IF NOT EXISTS MY_CONSUMER_ROLE;
GRANT APPLICATION ROLE Neo4j_Graph_Analytics.app_user TO ROLE MY_CONSUMER_ROLE;
SET MY_USER = (SELECT CURRENT_USER());
GRANT ROLE MY_CONSUMER_ROLE TO USER IDENTIFIER($MY_USER);

USE SCHEMA retail_recs.PUBLIC;
CREATE TABLE NODES (nodeId Number);
INSERT INTO NODES VALUES (1), (2), (3), (4), (5), (6);
CREATE TABLE RELATIONSHIPS (sourceNodeId Number, targetNodeId Number);
INSERT INTO RELATIONSHIPS VALUES (1, 2), (2, 3), (4, 5), (5, 6);

-- Grants needed for the app to read consumer data stored in tables and views, using a database role
USE DATABASE retail_recs;
CREATE DATABASE ROLE IF NOT EXISTS MY_DB_ROLE;
GRANT USAGE ON DATABASE retail_recs TO DATABASE ROLE MY_DB_ROLE;
GRANT USAGE ON SCHEMA retail_recs.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA retail_recs.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA retail_recs.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
-- Future tables also include tables that are created by the application itself.
-- This is useful as many use-cases require running algorithms in a sequence and using the output of a prior algorithm as input.
GRANT SELECT ON FUTURE TABLES IN SCHEMA retail_recs.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA retail_recs.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
GRANT CREATE TABLE ON SCHEMA retail_recs.PUBLIC TO DATABASE ROLE MY_DB_ROLE;
GRANT DATABASE ROLE MY_DB_ROLE TO APPLICATION Neo4j_Graph_Analytics;

-- Ensure the consumer role has access to tables created by the application
GRANT USAGE ON DATABASE retail_recs TO ROLE MY_CONSUMER_ROLE;
GRANT USAGE ON SCHEMA retail_recs.PUBLIC TO ROLE MY_CONSUMER_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA retail_recs.PUBLIC TO ROLE MY_CONSUMER_ROLE;

-- Use the consumer role to run the algorithm and inspect the output
USE ROLE MY_CONSUMER_ROLE;

CALL Neo4j_Graph_Analytics.graph.louvain('CPU_X64_XS', {
    'defaultTablePrefix': 'retail_recs.public',
    'project': {
        'nodeTables': [ 'CUSTOMERS', 'PRODUCTS_NODES'],
        'relationshipTables': {
            'PURCHASES': {
                'sourceTable': 'CUSTOMERS',
                'targetTable': 'PRODUCTS_NODES',
                'orientation': 'UNDIRECTED'
            }
        }
    },
    'compute': {
        'mutateProperty': 'community_id'
    },
    'write': [{
        'nodeLabel': 'CUSTOMERS',
        'outputTable': 'CUSTOMERS_GROUP',
        'nodeProperty': 'community_id'
    }]
});

select  community_id, count(*) as node_count from customers_group group by community_id