Klantencontrole voorspellen met op SQL gebaseerde logistieke regressie

Het voorspellen klantenkroon helpt ondernemingen klanten behouden, middelen optimaliseren, en rentabiliteit verhogen door tevredenheid en loyaliteit door actionable inzichten te verbeteren.

Ontdek hoe u op SQL gebaseerde logistieke regressie kunt gebruiken om het churn van de klant te voorspellen. Gebruik deze uitgebreide SQL-handleiding om onbewerkte e-commercegegevens om te zetten in betekenisvolle klantinzichten op basis van toetsgedragsmaatstaven (zoals aankoopfrequentie, gemiddelde orderwaarde en recenentie van laatste aankoop). Het document behandelt het volledige proces van gegevensvoorbereiding en eigenschapengineering tot modelverwezenlijking, evaluatie, en voorspellingen.

Gebruik deze handleiding om een krachtig model voor de voorspelling van schurnen te bouwen dat klanten met een verhoogd risico identificeert, retentiestrategieën verfijnt en betere bedrijfsbeslissingen aanstuurt. Het omvat geleidelijke instructies, SQL vragen, en gedetailleerde verklaringen om u te helpen machine het leren technieken binnen uw gegevensmilieu op een betrouwbare manier toepassen.

Aan de slag

Voordat u het churn-model maakt, is het belangrijk dat u de belangrijkste functies en gegevensvereisten van de klant verkent. In de volgende secties worden essentiële klantkenmerken en vereiste gegevensvelden voor een nauwkeurige modeltraining beschreven.

Klantfuncties definiëren define-customer-features

Het model analyseert aankoopgewoonten en trends om de keten nauwkeurig te classificeren. In de onderstaande tabel worden de belangrijkste gedragskenmerken van de klant beschreven die in het model worden gebruikt:

Aannames en vereiste velden assumptions-required-fields

Voor het genereren van prognoses voor het aantal kolommen bij de klant, hangt het model af van de belangrijkste velden in de webevents -tabel waarin de transactiegegevens van de klant zijn opgenomen. Uw dataset moet de volgende gebieden omvatten:

De dataset moet gestructureerde historische verslagen van de klantentransactie bevatten, met elke rij die een aankoopgebeurtenis vertegenwoordigt. Elke gebeurtenis moet timestamps in een aangewezen datum-tijd formaat verenigbaar met de SQL DATEDIFF functie (bijvoorbeeld, YYYY-MM-DD HH SS) omvatten. Bovendien moet elke record een geldige Experience Cloud-id (ECID) in het veld identityMap bevatten om klanten op unieke wijze te identificeren.

Een model maken create-a-model

Om klantenkring te voorspellen, moet u een op SQL-Gebaseerd logistiek regressiemodel tot stand brengen dat de geschiedenis van de klantenaankoop en gedragsmetriek analyseert. In het model worden klanten geclassificeerd als churned of not churned door te bepalen of ze een aankoop hebben gedaan in de afgelopen 90 dagen.

SQL gebruiken om het model van de kinnevoorspelling te creëren sql-create-model

Het op SQL-Gebaseerde model verwerkt webevents gegevens door zeer belangrijke metriek te groeperen en karnetiketten toe te wijzen die op een 90 dag onactiviteitregel worden gebaseerd. Deze benadering maakt onderscheid tussen actieve klanten en klanten die risico lopen. De SQL-query voert ook functietechniek uit om de nauwkeurigheid van het model te verbeteren en de indeling van de kolommen te verbeteren. Deze inzichten stellen uw bedrijf in staat om gerichte retentiestrategieën te implementeren, het vertrouwen te verminderen en de waarde van de levensduur van de klant te maximaliseren.

Gebruik de volgende SQL-instructie om het retention_model_logistic_reg -model te maken met de opgegeven functies en labels:

CREATE MODEL retention_model_logistic_reg
TRANSFORM (
  vector_assembler(array(total_purchases, total_revenue, avg_order_value, customer_lifetime, days_since_last_purchase, purchase_frequency)) features
  -- Combines selected customer metrics into a feature vector for model training
)
OPTIONS (
  MODEL_TYPE = 'logistic_reg',  -- Specifies logistic regression as the model type
  LABEL = 'churned'             -- Defines the target label for churn classification
)
AS
WITH customer_features AS (
    SELECT
       identityMap['ECID'][0].id AS customer_id,  -- Extract the unique customer ID from identityMap
       AVG(COALESCE(productListItems.priceTotal[0], 0)) AS avg_order_value,  -- Calculates the average order value, and handles null values with COALESCE
       SUM(COALESCE(productListItems.priceTotal[0], 0)) AS total_revenue, -- The sum of all purchase values per customer
       COUNT(COALESCE(productListItems.quantity[0], 0)) AS total_purchases,  -- The total number of items purchased by the customer
       DATEDIFF(MAX(timestamp), MIN(timestamp)) AS customer_lifetime,  -- The days between first and last recorded purchase
       DATEDIFF(CURRENT_DATE, MAX(timestamp)) AS days_since_last_purchase,  -- The days since the last purchase event
       COUNT(DISTINCT CONCAT(YEAR(timestamp), MONTH(timestamp))) AS purchase_frequency  -- The count of unique months with purchases
    FROM
        webevents
    WHERE EXISTS(productListItems, value -> value.priceTotal > 0)  -- Filters transactions with valid total price
      AND commerce.`order`.purchaseID <> ''  -- Ensures the order has a valid purchase ID
    GROUP BY customer_id
),
customer_labels AS (
    SELECT
      identityMap['ECID'][0].id AS customer_id,  -- Extract the unique customer ID for labeling
      CASE
          WHEN DATEDIFF(CURRENT_DATE, MAX(timestamp)) > 90 THEN 1  -- Marks the customer as churned if no purchase occurred in the last 90 days
          ELSE 0
      END AS churned
    FROM
        webevents
    WHERE EXISTS(productListItems, value -> value.priceTotal > 0)
      AND commerce.`order`.purchaseID <> ''
    GROUP BY customer_id
)
SELECT
    f.customer_id,
    f.total_purchases,
    f.total_revenue,
    f.avg_order_value,
    f.customer_lifetime,
    f.days_since_last_purchase,
    f.purchase_frequency,
    l.churned
FROM
    customer_features f
JOIN
    customer_labels l
ON f.customer_id = l.customer_id  -- Join features with churn labels
ORDER BY RANDOM()  -- Shuffles rows randomly for training
LIMIT 500000;  -- Limit the dataset to 500,000 rows for model training

Modeluitvoer model-output

De outputdataset bevat klant-gerelateerde metriek en hun kernostatus. Elke rij vertegenwoordigt een klant, hun eigenschapwaarden, en hun kernostatus. U kunt deze output gebruiken om klantengedrag te analyseren, voorspellende modellen op te leiden, en gerichte bewaarstrategieën te ontwikkelen om klanten op risico te behouden. Hieronder ziet u een voorbeeld van een uitvoertabel:

 customer_id  | total_purchases | total_revenue | avg_order_value  | customer_lifetime | days_since_last_purchase | purchase_frequency | churned |
|--------------+-----------------+---------------+------------------+-------------------+--------------------------+--------------------+----------
  100001      | 25              | 1250.00       | 50.00            | 540               | 20                       | 10                 | 0
  100002      | 3               | 90.00         | 30.00            | 120               | 95                       | 1                  | 1
  100003      | 60              | 7200.00       | 120.00           | 800               | 5                        | 24                 | 0
  100004      | 15              | 750.00        | 50.00            | 365               | 60                       | 8                  | 0
  100005      | 1               | 25.00         | 25.00            | 60                | 180                      | 1                  | 1

SQL gebruiken om het model te evalueren model-evaluation

Daarna, evalueer het model van de kinnevoorspelling om zijn doeltreffendheid te bepalen in het identificeren van op risico-klanten. Evalueer modelprestaties met zeer belangrijke metriek die nauwkeurigheid en betrouwbaarheid meten.

Gebruik de functie retention_model_logistic_reg om de nauwkeurigheid van het model_evaluate -model te meten in het voorspellen van de klantketen. In het volgende SQL-voorbeeld wordt het model geëvalueerd aan de hand van een gegevensset die is gestructureerd zoals de trainingsgegevens:

SELECT *
FROM model_evaluate(retention_model_logistic_reg, 1,
WITH customer_features AS (
    SELECT
       identityMap['ECID'][0].id AS customer_id,
       AVG(COALESCE(productListItems.priceTotal[0], 0)) AS avg_order_value,
       SUM(COALESCE(productListItems.priceTotal[0], 0)) AS total_revenue,
       COUNT(COALESCE(productListItems.quantity[0], 0)) AS total_purchases,
       DATEDIFF(MAX(timestamp), MIN(timestamp)) AS customer_lifetime,
       DATEDIFF(CURRENT_DATE, MAX(timestamp)) AS days_since_last_purchase,
       COUNT(DISTINCT CONCAT(YEAR(timestamp), MONTH(timestamp))) AS purchase_frequency
    FROM
        webevents
    WHERE EXISTS(productListItems, value -> value.priceTotal > 0)
      AND commerce.`order`.purchaseID <> ''
    GROUP BY customer_id
),
customer_labels AS (
    SELECT
      identityMap['ECID'][0].id AS customer_id,
      CASE
          WHEN DATEDIFF(CURRENT_DATE, MAX(timestamp)) > 90 THEN 1
          ELSE 0
      END AS churned
    FROM
        webevents
    WHERE EXISTS(productListItems, value -> value.priceTotal > 0)
      AND commerce.`order`.purchaseID <> ''
    GROUP BY customer_id
)
SELECT
    f.customer_id,
    f.total_purchases,
    f.total_revenue,
    f.avg_order_value,
    f.customer_lifetime,
    f.days_since_last_purchase,
    f.purchase_frequency,
    l.churned
FROM
    customer_features f
JOIN
    customer_labels l
ON f.customer_id = l.customer_id); -- Joins customer features with churn labels

Uitvoering modelevaluatie

De evaluatieoutput omvat zeer belangrijke prestatiesmetriek, zoals AUC-ROC, nauwkeurigheid, precisie, en herinnering. Deze metriek verstrekt inzicht in modeldoeltreffendheid die u kunt gebruiken om bewaarstrategieën te verfijnen en gegeven-gedreven besluiten te nemen.

 auc_roc | accuracy | precision | recall
|---------+----------+-----------+--------
1        | 0.99998  |  1        |  1

Modelvoorspelling model-prediction

Nadat het model is geëvalueerd, gebruikt u model_predict om het toe te passen op een nieuwe gegevensset en de klant een nieuwe keuze te laten maken. U kunt deze voorspellingen gebruiken om klanten op risico te identificeren en gerichte bewaarstrategieën uit te voeren.

SQL gebruiken om veelhoekvoorspelling te genereren sql-model-predict

In de SQL-query hieronder wordt het model retention_model_logistic_reg gebruikt om te voorspellen wat de klant doet met een gegevensset die is gestructureerd zoals de trainingsgegevens:

SELECT *
FROM model_predict(retention_model_logistic_reg, 1,  -- Applies the trained model for churn prediction
WITH customer_features AS (
    SELECT
       identityMap['ECID'][0].id AS customer_id,
       AVG(COALESCE(productListItems.priceTotal[0], 0)) AS avg_order_value,
       SUM(COALESCE(productListItems.priceTotal[0], 0)) AS total_revenue,
       COUNT(COALESCE(productListItems.quantity[0], 0)) AS total_purchases,
       DATEDIFF(MAX(timestamp), MIN(timestamp)) AS customer_lifetime,
       DATEDIFF(CURRENT_DATE, MAX(timestamp)) AS days_since_last_purchase,
       COUNT(DISTINCT CONCAT(YEAR(timestamp), MONTH(timestamp))) AS purchase_frequency
    FROM
        webevents
    WHERE EXISTS(productListItems, value -> value.priceTotal > 0)  -- Ensures only valid purchase data is considered
      AND commerce.`order`.purchaseID <> ''
    GROUP BY customer_id
),
customer_labels AS (
    SELECT
      identityMap['ECID'][0].id AS customer_id,
      CASE
          WHEN DATEDIFF(CURRENT_DATE, MAX(timestamp)) > 90 THEN 1  -- Identify customers who have not purchased in the last 90 days
          ELSE 0
      END AS churned
    FROM
        webevents
    WHERE EXISTS(productListItems, value -> value.priceTotal > 0)
      AND commerce.`order`.purchaseID <> ''
    GROUP BY customer_id
)
SELECT
    f.customer_id,
    f.total_purchases,
    f.total_revenue,
    f.avg_order_value,
    f.customer_lifetime,
    f.days_since_last_purchase,
    f.purchase_frequency,
    l.churned
FROM
    customer_features f
JOIN
    customer_labels l
ON f.customer_id = l.customer_id);  -- Matches features with their churn labels for prediction

Modelvoorspellingsuitvoer prediction-output

De outputdataset omvat zeer belangrijke klanteneigenschappen en hun voorspelde koordstatus, die erop wijst of een klant waarschijnlijk zal leiden. Gebruik deze inzichten om pro-actieve bewaarstrategieën uit te voeren en klantenkring te verminderen.

 total_purchases | total_revenue | avg_order_value | customer_lifetime | days_since_last_purchase | purchase_frequency | churned | prediction
|-----------------+---------------+-----------------+-------------------+--------------------------+--------------------+---------+------------
 2               | 299           | 149.5           | 0                 | 13                        | 1                  | 0       | 0
 1               | 710           | 710.00          | 0                 | 149                       | 1                  | 1       | 1
 1               | 19.99         | 19.99           | 0                 | 30                        | 1                  | 0       | 0
 1               | 4528          | 4528.00         | 0                 | 26                        | 1                  | 0       | 0
 1               | 21.84         | 21.84           | 0                 | 90                        | 1                  | 0       | 0
 1               | 16.64         | 16.64           | 0                 | 268                       | 1                  | 1       | 1

Volgende stappen

U hebt nu geleerd om een op SQL gebaseerd model te maken, te evalueren en te gebruiken om de klant te voorspellen. Met deze stichting, kunt u klantengedrag analyseren, klanten op risico identificeren, en pro-actieve behoudstrategieën uitvoeren om klantenbehoud te verbeteren. Neem de volgende stappen om uw model voor de voorspelling van fouten verder te verbeteren en toe te passen: