1.4 Example: Segmenting Customer Data
The examples in this section use an Expectation Maximization clustering model to segment the customer data based on common characteristics.
Example 1-6 Compute Customer Segments
This query computes natural groupings of customers and returns the number of
customers in each group. The em_sh_clus_sample
model is created by the oml4sql-clustering-expectation-maximization.sql
example.
SELECT CLUSTER_ID(em_sh_clus_sample USING *) AS clus, COUNT(*) AS cnt FROM mining_data_apply_v GROUP BY CLUSTER_ID(em_sh_clus_sample USING *) ORDER BY cnt DESC;
The output is as follows:
CLUS CNT
---------- ----------
9 311
3 294
7 215
12 201
17 123
16 114
14 86
19 64
15 56
18 36
Example 1-7 Find the Customers Who Are Most Likely To Be in the Largest Segment
The query in Example 1-6 shows that segment 9 has the most members. The following query lists the five customers who are most likely to be in segment 9.
SELECT cust_id FROM (SELECT cust_id, RANK() over (ORDER BY prob DESC, cust_id) rnk_clus2 FROM (SELECT cust_id, ROUND(CLUSTER_PROBABILITY(em_sh_clus_sample, 9 USING *),3) prob FROM mining_data_apply_v)) WHERE rnk_clus2 <= 5 ORDER BY rnk_clus2;
The output is as follows:
CUST_ID
----------
100002
100012
100016
100019
100021
Example 1-8 Find Key Characteristics of the Most Representative Customer in the Largest Cluster
The query in Example 1-7 lists customer 100002 first in the list of likely customers for segment 9. The following query returns the five characteristics that are most significant in determining the assignment of customer 100002 to segments with probability > 20% (only segment 9 for this customer).
SELECT S.cluster_id, probability prob, CLUSTER_DETAILS(em_sh_clus_sample, S.cluster_id, 5 using T.*) det FROM (SELECT v.*, CLUSTER_SET(em_sh_clus_sample, NULL, 0.2 USING *) pset FROM mining_data_apply_v v WHERE cust_id = 100002) T, TABLE(T.pset) S ORDER BY 2 desc;
The output is as follows:
CLUSTER_ID PROB DET
---------- ------- --------------------------------------------------------------------------------
9 1.0000 <Details algorithm="Expectation Maximization" cluster="9">
<Attribute name="YRS_RESIDENCE" actualValue="4" weight="1" rank="1"/>
<Attribute name="EDUCATION" actualValue="Bach." weight="0" rank="2"/>
<Attribute name="AFFINITY_CARD" actualValue="0" weight="0" rank="3"/>
<Attribute name="BOOKKEEPING_APPLICATION" actualValue="1" weight="0" rank="4"/>
<Attribute name="Y_BOX_GAMES" actualValue="0" weight="0" rank="5"/>
</Details>
Parent topic: Oracle Machine Learning With SQL