ANLY482 AY2016-17 T2 Group10 Project Overview: Methodology

HOME

ABOUT US

PROJECT OVERVIEW

ANALYSIS & FINDINGS

PROJECT MANAGEMENT

DOCUMENTATION

Overview

Data

Methodology

<< ANLY482 AY2016-17 T2 Projects

Data Preparation

Data preparation involves cleaning, transformation, and integration, which are standard procedures to standardize data across different datasets for their many formats, errors in data entries and granularity. We will first look at each of the data files, determine best ways to standardize formats and then perform aggregations on more granular data for integration purposes.

Invoice Details

Data Cleaning

A brief scan of the entire Invoice Details data table led to 3 main areas to be cleaned.

1. Missing values in Price$ column
2. Negative values in Sales Qty and Amount$ columns
3. Some Postal Code with only 5 digits (because they start with 0)

Handling of missing values in Price$ column

The Price$ column determines the unit price of a specific dosage (SKU) of a drug and it can vary across different customers, time for different reasons (marketing, incentive for new purchase, etc). It becomes important for us to know why some of them have missing values because the unit price of any drug is usually defined before any purchase.
Upon close inspection on the missing values using data filter, we are made known the following:

• 2379 rows with missing
• 1677 rows belong to product E/F
• Most records have sales amount which are $0
• Either Bonus Qty or Sample Qty are positive

This tell us that these rows represented transactions that took place when drugs are given as samples or bonuses to serve as goodwill.
Actions taken:

• We will be assigning a fair value of 0 to the missing values as JMP will ignore rows which have missing values if we were to take into consideration of price in our predictive analysis.

Handling of negative values in Sales Qty and Amount$ columns

The Sales Qty and Amount$ columns indicate the quantity of drug and total amount involved in the transaction for a dosage (SKU) of a drug. Out of pure curiosity of the presence of negative values, we asked Elaine and she explained that negative values are credit amount which are needed to offset the initial sales.
We are made known of the following:

• 1233 rows with both Sales Qty and Amount$ negative
  • Have corresponding transactions
• 229 rows with only Amount$ negative, all Sales Qty = 0, all Price$ have missing values
  • No corresponding transactions

Actions taken:

• For the 229 rows, there is no indication of what the credit sales could be for and hence, we will filter them out.
• For the 1233 rows, we can simply ignore them as the corresponding transactions will cancel them out.

Handling of Postal Code with only 5 digits

Initial import of the data table assumes that the Postal Code is a numerical variable instead of a categorical one. This causes some of the values which starts with 0 to be omitted. This is an easy fix whereby we only need to perform the recode function available in JMP.

Data Transformation

To facilitate integrating the Invoice Details data table with others, we will add new formulated column like Year-Quarter.

Addition of Year-Quarter columns

The Year-Quarter column categorizes the invoice date into Year-Quarter, as businesses usually look at quarterly sales performance.

Integrated MCCP & Invoice

Data Transformation

Data transformation at integration stage involves the following:

1. Determining linking variables between the two tables
2. Comparing differences in formats
3. Aggregating based on granularity of variables
4. Joining of tables

Determining linking variables between the two tables

Comparing differences in formats

Sales Rep Name Differences
All Sales Rep Name in MCCP can be found in Invoice but there are 8 additional names not captured in MCCP. A data filter on these 8 names shows records from all quarters and hence, does not suggest reasons related to departure.

Product Name Differences
All MCCP product names have a corresponding alias in Invoice albeit differences in format and granularity. For 1-to-1 mapping, 1-to-Many or Many-to-1 mapping, a single, standardized name will be assigned. This will ascribe variants of a product (RandomBrandName, RandomBrandName – Variant) to its main brand (RandomBrandName). Recoding is performed for both data tables in JMP.

Aggregating based on granularity of variables

Aggregating Invoice
Aggregating MCCP

Joining of tables