Churn analysis on customer 360 data

h3. Information:

 

Churn analysis is the process of identifying which customers are likely to stop doing business with a company and predicting when they might churn. It is an essential analysis for businesses as understanding churn can aid in reducing customer attrition and enhancing customer retention strategies.

 

h3. Requirements:

 

Create a SQL query to add a is_churn flag column, calculate based on the last_interaction_date for the customer_360_raw table. If the last_interaction_date of a customer is greater than 60 days from current date, flag is_churn as 1; otherwise, 0.

 

 

 

Additionally, drop and load churn analysis views if it exits as follows:

 

1. vv_city_level_churn: This view should display the number of churned customers per city.
2. vv_state_level_churn: This view should display the number of churned customers per state.
3. vv_churn_product_count: This view should display the number of purchase_history

per churned customer, different products in the purchase_history column are separated by commas(“,“).

1. vv_manager_level_churn: This view should display the number of churned customers per account_manager based on the is_churn flag. 

 

Note: “ID” is the customer id column in customer_360_raw table.

 

Prerequisite:

 

1. Drop the table customer_360_raw_clone if exist.
2. Create replica of customer_360_raw table in customer_360_raw_clone table and perform the requirement in the replica table.

 

Unity Catalog Information: purgo_playground.customer_360_raw

 

Expected output:

 

* Databricks Delta SQL query

* above mentioned view based on purgo_playground database