Example for creating a KNN model

First, you create the CUSTOMER_CHURN_VIEW sample data set that is based on the CUSTOMER_CHURN table as follows:

CREATE VIEW CUSTOMER_CHURN_VIEW AS (SELECT CUST_ID, DURATION, CASE WHEN CENSOR=1 THEN 'yes' ELSE 'no' END AS CHURN,
AVG_SPENT_RETAIN_PM, AVG_SQ_SPENT_RETAIN_PM IN_B2B_INDUSTRY,  ANNUAL_REVENUE_MIL TOTAL_EMPLOYEES, 
TOTAL_BUY TOTAL_BUY_FREQ, TOTAL_BUY_FREQ_SQ
FROM CUSTOMER_CHURN);

You can then split the CUSTOMER_CHURN_VIEW sample data set into a training data set and a validation data set as follows:

CALL IDAX.SPLIT_DATA('intable=customer_churn_view, traintable=customer_churn_train, 
testtable=customer_churn_test, id=cust_id, fraction=0.35');

The following call runs the algorithm on the customer_churn_train data set and builds the KNN model.

CALL IDAX.KNN('model=customer_churn_mdl, intable=customer_churn_train, id=cust_id, target=churn');

The PREDICT_KNN stored procedure predicts the value for the CHURN column.

The following call shows how to associate the values to new transactions.

CALL IDAX.PREDICT_KNN('model= customer_churn_mdl, intable= customer_churn_test, outtable=customer_churn_score');

You can validate the predictions from the previous step by comparing the churn values from the records of the CUSTOMER_CHURN_TEST data set that were not used for building the KNN model customer_churn_mdl with the prediction of the customer_churn_score:

SELECT s.id, s.class churn_predicted, churn from customer_churn_test i, customer_churn_score s where i.cust_id=s.id;