ANLY482 AY2017-18T2 Group32: Project Overview/Methodology

Return to ANLY482 AY2017-18 Home Page

Home

About Us

Project Overview

Project Insights

Project Management

Documentation

Description

Data

Methodology

Methodology

Data Cleaning

Before performing any data analysis, it is critical to perform exploratory data analysis to understand the data better. Errors such as incomplete and invalid values could lead to inaccurate results. Hence, the data must be cleaned so that it is suitable for any further analysis. The following summarizes the data cleaning process we have taken for each dataset:

HCP

1. Removed dummy variables with suspicious looking values in the “postal code” column such as (000001,000002, etc)

HCO

1. Removed dummy variables with suspicious looking values in the “postal code” column such as (000001,000002, etc) or Test Accounts.

Invoice Detail

1. Filter transactions to obtain TCE products using TCE_Brands sheet
2. For negative values in the “sales qty” and “amount$” columns, we kept the values as it is. This is because after checking with our sponsor, these records are products that have been returned back to them, so they served as sales that have been void. Upon aggregation by quarters, no negative values will be present.
3. For the missing values in “price$” columns, we imputed their values to “0” as these rows contain records where free samples are given out to the customers.

4. Using Ctrl-F function to show “search data tables” interface, then enter the following fields:

5. For 5-digits values in “postal code” column, a new column is created to store the converted ‘postal code’ in 6-digits format, with data type changed from numerical to categorical and formula function used to add the missing ‘0’.

Data Transformation

With the use of JMP Pro, we explored and joined data sets using common joints to analyze the relationships between different variables across the various data sets.

For instance, we joined HCO data with the Invoice Details using “ZP Account” in HCO and “Customer_code” in Invoice, so that we can analyze the sales for each clinic and observe what products do they often purchase

Exploratory Data Analysis (EDA)

We conducted an Exploratory Data Analysis on our final datasets to seek greater insights between sales and healthcare organizations (HCO-Invoice) as well as to understand what products GSK sales representatives are pushing to healthcare practitioners ( HCP-Call_Details).

Sales and Healthcare organizations

GSK appears to have a strong performance in 2016 but sales amount decreased by 9% to a 3-year low in 2017. This signals a need to for GSK to keep their sales team in check using Key Performance Indicators as mentioned above.

Softwares Used

for example