Customer Churn Dataset

• subscriptions.customer_id →references customers.customer_idmany to one: each Subscriptions row points to one Customers row
• usage_data.subscription_id →references subscriptions.subscription_idmany to one: each Usage data row points to one Subscriptions row

1. What are the key features that predict customer churn using logistic regression?

   Consider features from both customer demographics and subscription details. Look for patterns in plan type, monthly charges, and customer segments.

   tables: customers, subscriptions

2. Is there a correlation between data usage patterns (data_usage_gb, call_duration_minutes) and churn rate?

   Join usage data with subscription data and analyze average usage metrics for churned vs. active customers.

   tables: customers, subscriptions, usage_data

3. How does subscription tenure (calculated from start_date and end_date) impact the likelihood of churn?

   Calculate the duration between start_date and end_date (or current date if active) for each subscription. Then, group by tenure ranges and observe churn rates.

   tables: customers, subscriptions

4. Analyze churn rate by customer segment and payment method.

   Join customers and subscriptions tables. Group by customer_segment and payment_method and calculate the proportion of churned customers.

   tables: customers, subscriptions

Table names match the SQLite file and the SQL script.

SELECT customer_segment, COUNT(customer_id) AS customer_count FROM customers GROUP BY customer_segment LIMIT 20;

SELECT plan_type, AVG(monthly_charge) AS average_monthly_charge FROM subscriptions GROUP BY plan_type LIMIT 20;

SELECT payment_method, SUM(CASE WHEN churn_label = 'yes' THEN 1 ELSE 0 END) * 100.0 / COUNT(subscription_id) AS churn_rate FROM subscriptions GROUP BY payment_method LIMIT 20;

SELECT s.subscription_id, SUM(ud.data_usage_gb) AS total_data_usage FROM subscriptions s JOIN usage_data ud ON s.subscription_id = ud.subscription_id GROUP BY s.subscription_id LIMIT 20;

SELECT c.customer_id, s.churn_label, JULIANDAY(s.end_date) - JULIANDAY(s.start_date) AS tenure_days FROM customers c JOIN subscriptions s ON c.customer_id = s.customer_id WHERE s.churn_label = 'yes' LIMIT 20;

Excel

Load each table into a separate sheet. Use Power Query to join tables based on customer_id and subscription_id. Create a PivotTable on the 'subscriptions' sheet to analyze churn by plan type or payment method.

Power BI

Create a star schema with 'customers' and 'subscriptions' as dimension tables and 'usage_data' as a fact table. Establish relationships: customers[customer_id] to subscriptions[customer_id], and subscriptions[subscription_id] to usage_data[subscription_id]. Create DAX measures for Churn Rate (e.g., CALCULATE(COUNTROWS(subscriptions), subscriptions[churn_label] = "yes") / COUNTROWS(subscriptions)) and Average Monthly Charge.

SQL

Load the SQLite or SQL relational download. Use customer_id to join 'customers' and 'subscriptions' tables. Use subscription_id to join 'subscriptions' and 'usage_data' tables. For example: SELECT * FROM customers c JOIN subscriptions s ON c.customer_id = s.customer_id JOIN usage_data ud ON s.subscription_id = ud.subscription_id;

Python

Use pandas to load CSV or other formats. Join tables using `pd.merge`. Perform analysis using libraries like scikit-learn for modeling and matplotlib/seaborn for visualization.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file