Assignment Detail

Part One: Small Project for the Telecom company

Customer Distribution and Deactivation Analyses
Objective:
The attached data is the CRM data of a wireless company for 2 years. The wireless company would like to investigate the customer distribution and business behaviors, and then gain insightful understanding about the customers, and to forecast the deactivation trends for the next 6 months.
Data:
Acctno: account number.
Actdt: account activation date
Deactdt: account deactivation date
DeactReason: reason for deactivation.
GoodCredit: customer’s credit is good or not.
RatePlan: rate plan for the customer.
DealerType: dealer type.
Age: customer age.
Province: province.
Sales: the amount of sales to a customer.

Analysis requests:
1.1 Explore and describe the dataset briefly. For example, is the acctno unique? What is the number of accounts activated and deactivated? When is the earliest and
latest activation/deactivation dates available? And so on….

1.2 What is the age and province distributions of active and deactivated customers?

1.3 Segment the customers based on age, province and sales amount:

Sales segment: < $100, $100—500, $500-$800, $800 and above.

Age segments: < 20, 21-40, 41-60, 60 and above.

Create analysis report by using the attached Excel template.

1.4.Statistical Analysis:

1) Calculate the tenure in days for each account and give its simple statistics.

2) Calculate the number of accounts deactivated for each month.

3) Segment the account, first by account status “Active” and “Deactivated”, then by Tenure: < 30 days, 31—60 days, 61 days— one year, over one year. Report the number of accounts of percent of all for each segment.

4) Test the general association between the tenure segments and “Good Credit” “RatePlan ” and “DealerType.”

5) Is there any association between the account status and the tenure segments? Could you find out a better tenure segmentation strategy that is more associated with the account status?

6) Does Sales amount differ among different account status, GoodCredit, and customer age segments?