Be Customer Wise or Otherwise - Findings
Contents
Data Collection and Preparation
Merging and Cleaning
GLC provided us with 3 datasets, namely Metadata, CRM, and Sales, collected over the 12 months of 2008. Metadata contains fields such as ‘Industry Description’ that help to interpret the industry codes used in the other files. The CRM dataset contains information about the accounts, including fields such as ‘New Ac Number’ and ‘Date Opened’. Finally, the Sales dataset contains 2.5 million rows of individual transactions that occurred within the 12 months of 2008. This file contains fields such as ‘Local Revenue’, ‘Destination’, ‘Origin’, ‘Billed Weight’ and ‘Sales Channel’.
Since we require variables from all three datasets, the first step we took was to merge them by joining the tables based on common fields such as ‘New Ac Number’ for the convenience of our subsequent analysis. Thereafter, we derived certain variables that, from our literature review, would be used in our later analysis, such as ‘Date of Last Transaction’ (i.e. Recency), ‘No. of Transactions’ per account (i.e. Frequency) and ‘Total 2008 Revenue’ per account (i.e. Monetary).
We then removed unnecessary and duplicate fields such as those that, for confidentiality purposes, were given an ‘XXX’ value for all rows.
Missing Data
Finally, we did a missing data analysis to examine how significant the missing data were and decided how to deal with them. A summary can be seen in the table below.
| Variable | Count (Missing) |
|---|---|
| description of activities | 2424057 (95.42%) |
| inbound_contract_code | 2099942 (82.66%) |
| outbound_contract_code | 328756 (12.94%) |
| industry_code_CRM | 265198 (10.44%) |
| site_grouping | 214763 (8.45%) |
| local revenue | 24970 (0.98%) |
| zip_code | 3466 (0.136%) |
