Prognostizieren der Kundenabwanderung mit SQL-basierter logistischer Regression

Die Vorhersage von Kundenabwanderungen hilft Unternehmen, Kunden zu binden, Ressourcen zu optimieren und die Rentabilität zu steigern, indem die Zufriedenheit und Loyalität durch umsetzbare Einblicke verbessert werden.

Erfahren Sie, wie Sie mit SQL-basierter logistischer Regression Kundenabwanderungen vorhersagen können. Verwenden Sie dieses umfassende SQL-Handbuch, um E-Commerce-Rohdaten in aussagekräftige Kundeneinblicke umzuwandeln, die auf wichtigen Verhaltensmetriken basieren (z. B. Kaufhäufigkeit, durchschnittlicher Bestellwert und Neuigkeit des letzten Kaufs). Das Dokument umfasst den gesamten Prozess von der Datenvorbereitung und dem Feature Engineering bis hin zur Modellerstellung, -auswertung und -vorhersage.

Verwenden Sie dieses Handbuch, um ein leistungsstarkes Modell für Abwanderungsprognosen zu erstellen, mit dem gefährdete Kunden identifiziert, Aufbewahrungsstrategien verfeinert und bessere Geschäftsentscheidungen gefördert werden. Sie enthält schrittweise Anweisungen, SQL-Abfragen und detaillierte Erläuterungen, die Ihnen helfen, maschinelle Lerntechniken sicher in Ihrer Datenumgebung anzuwenden.

Erste Schritte

Bevor Sie das Abwanderungsmodell erstellen, müssen Sie sich mit den wichtigsten Kundenfunktionen und Datenanforderungen befassen. In den folgenden Abschnitten werden wesentliche Kundenattribute und erforderliche Datenfelder für ein genaues Modell-Training beschrieben.

Definieren von Kundenfunktionen define-customer-features

Um Abwanderung genau zu klassifizieren, analysiert das Modell Kaufgewohnheiten und Trends. In der folgenden Tabelle sind die wichtigsten im Modell verwendeten Funktionen für das Kundenverhalten aufgeführt:

Annahmen und erforderliche Felder assumptions-required-fields

Um Prognosen zur Kundenabwanderung zu generieren, hängt das Modell von Schlüsselfeldern in der webevents ab, die Details zu Kundentransaktionen erfassen. Ihr Datensatz muss die folgenden Felder enthalten:

Der Datensatz muss strukturierte historische Kundentransaktionsdatensätze enthalten, wobei jede Zeile ein Kaufereignis darstellt. Jedes Ereignis muss Zeitstempel in einem geeigneten Datums-/Uhrzeitformat enthalten, das mit der SQL-DATEDIFF kompatibel ist (z. B. JJJJ-MM-TT HHSS). Darüber hinaus muss jeder Datensatz eine gültige Experience Cloud-ID (ECID) im Feld identityMap enthalten, um Kunden eindeutig zu identifizieren.

Modell erstellen create-a-model

Um die Kundenabwanderung vorherzusagen, müssen Sie ein SQL-basiertes logistisches Regressionsmodell erstellen, das den Kaufverlauf und die Verhaltensmetriken der Kunden analysiert. Das Modell klassifiziert Kunden als churned oder not churned, indem es bestimmt, ob sie in den letzten 90 Tagen einen Kauf getätigt haben.

Verwenden von SQL zur Erstellung des Prognosemodells für Abwanderungen sql-create-model

Das SQL-basierte Modell verarbeitet webevents Daten, indem es Schlüsselmetriken aggregiert und Abwanderungskennzeichnungen auf Grundlage einer 90-tägigen Inaktivitätsregel zuweist. Dieser Ansatz unterscheidet aktive Kunden von Risikokunden. Die SQL-Abfrage führt auch Feature Engineering durch, um die Modellgenauigkeit zu verbessern und die Abwanderungsklassifizierung zu verbessern. Diese Einblicke ermöglichen Ihrem Unternehmen die Implementierung zielgerichteter Aufbewahrungsstrategien, reduzieren die Abwanderung und maximieren den Wert der Kundenlebenszeit.

Verwenden Sie die folgende SQL-Anweisung, um das retention_model_logistic_reg mit den angegebenen Funktionen und Beschriftungen zu erstellen:

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

Modellausgabe model-output

Der Ausgabedatensatz enthält kundenbezogene Metriken und deren Abwanderungsstatus. Jede Zeile stellt einen Kunden, seine Funktionswerte und seinen Abwanderungsstatus dar. Mithilfe dieser Ausgabe können Sie das Kundenverhalten analysieren, Prognosemodelle trainieren und zielgerichtete Aufbewahrungsstrategien entwickeln, um gefährdete Kunden zu binden. Nachfolgend finden Sie eine Beispiel-Ausgabetabelle:

 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 zur Auswertung des Modells verwenden model-evaluation

Bewerten Sie anschließend das Abwanderungsvorhersagungsmodell, um seine Effektivität bei der Identifizierung gefährdeter Kunden zu bestimmen. Bewerten Sie die Modellleistung mit Schlüsselmetriken, die Genauigkeit und Zuverlässigkeit messen.

Verwenden Sie die retention_model_logistic_reg-Funktion, um die Genauigkeit des model_evaluate-Modells bei der Prognose der Kundenabwanderung zu messen. Im folgenden SQL-Beispiel wird das Modell mithilfe eines Datensatzes ausgewertet, der wie die Trainingsdaten strukturiert ist:

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

Ausgabe der Modellevaluierung

Die Auswertungsausgabe enthält wichtige Leistungsmetriken wie AUC-ROC, Genauigkeit, Präzision und Rückruf. Diese Metriken liefern Einblicke in die Effektivität von Modellen, mit denen Sie Aufbewahrungsstrategien verfeinern und datengestützte Entscheidungen treffen können.

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

Prognose des Modells model-prediction

Nachdem das Modell evaluiert wurde, verwenden Sie model_predict , um es auf einen neuen Datensatz anzuwenden und die Kundenabwanderung zu prognostizieren. Sie können diese Prognosen verwenden, um gefährdete Kunden zu identifizieren und zielgerichtete Aufbewahrungsstrategien zu implementieren.

SQL zur Erzeugung von Abwanderungsprognosen verwenden sql-model-predict

Die folgende SQL-Abfrage verwendet das retention_model_logistic_reg, um Kundenabwanderung mit einem Datensatz vorherzusagen, der wie die Schulungsdaten strukturiert ist:

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

Modellvorhersageausgabe prediction-output

Der Ausgabedatensatz enthält wichtige Kundenfunktionen und den prognostizierten Abwanderungsstatus, der angibt, ob eine Abwanderung wahrscheinlich ist. Nutzen Sie diese Erkenntnisse, um proaktive Strategien zur Kundenbindung zu implementieren und die Kundenabwanderung zu reduzieren.

 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

Nächste Schritte

Sie haben jetzt gelernt, wie Sie ein SQL-basiertes Modell erstellen, auswerten und verwenden können, um die Kundenabwanderung vorherzusagen. Auf dieser Grundlage können Sie das Kundenverhalten analysieren, gefährdete Kunden identifizieren und proaktive Kundenbindungsstrategien implementieren, um die Kundenbindung zu verbessern. Zur weiteren Verbesserung und Anwendung Ihres Abwanderungs-Prognosemodells sollten Sie die folgenden Schritte in Betracht ziehen: