Difference between revisions of "ANLY482 AY2017-18 T1 Group03/ProjectSteadfast Project Data"

From Analytics Practicum
Jump to navigation Jump to search
 
(4 intermediate revisions by the same user not shown)
Line 14: Line 14:
 
| style="font-size:120%; text-align:center; background-color:#ff6600" width="0.5%" |
 
| style="font-size:120%; text-align:center; background-color:#ff6600" width="0.5%" |
 
| style="font-size:120%;  text-align:center;background-color:#ffffff; width=12%" | [[ANLY482_AY2017-18_T1_Group03/About Us|<font face = "Century Gothic" color="#000000">About Us</font>]]
 
| style="font-size:120%;  text-align:center;background-color:#ffffff; width=12%" | [[ANLY482_AY2017-18_T1_Group03/About Us|<font face = "Century Gothic" color="#000000">About Us</font>]]
−
 
−
| style="font-size:120%; text-align:center; background-color:#ff6600" width="1%" |
 
−
| style="font-size:120%;  text-align:center;background-color:#ffcc99; width=12%" | [[ANLY482_AY2017-18_T1_Group03/Project_Overview|<font face = "Century Gothic" color="#000000">Project Overview</font>]]
 
  
 
| style="font-size:120%; text-align:center; background-color:#ff6600" width="0.5%" |
 
| style="font-size:120%; text-align:center; background-color:#ff6600" width="0.5%" |
Line 23: Line 20:
 
| style="font-size:120%; text-align:center; background-color:#ff6600" width="0.5%" |
 
| style="font-size:120%; text-align:center; background-color:#ff6600" width="0.5%" |
 
| style="font-size:120%;  text-align:center;background-color:#ffffff; width=12%" | [[ANLY482_AY2017-18_T1_Group03/Final_Progress|<font face = "Century Gothic" color="#000000">Final Progress</font>]]
 
| style="font-size:120%;  text-align:center;background-color:#ffffff; width=12%" | [[ANLY482_AY2017-18_T1_Group03/Final_Progress|<font face = "Century Gothic" color="#000000">Final Progress</font>]]
 +
 +
| style="font-size:120%; text-align:center; background-color:#ff6600" width="1%" |
 +
| style="font-size:120%;  text-align:center;background-color:#ffcc99; width=12%" | [[ANLY482_AY2017-18_T1_Group03/Project_Overview|<font face = "Century Gothic" color="#000000">Project Overview</font>]]
  
 
| style="font-size:120%; text-align:center; background-color:#ff6600" width="0.5%" |
 
| style="font-size:120%; text-align:center; background-color:#ff6600" width="0.5%" |
Line 63: Line 63:
  
 
<b><u> HCP: </u></b>  
 
<b><u> HCP: </u></b>  
−
 
+
[[Image:HCP.PNG|800px|center]]
−
1. Removal of duplicates
 
−
Under the “Primary” column, there are 0 and 1, with 0 being those with old addresses and 1 being updated addresses. Therefore, with the help of JMP Pro software, we have removed all 0 as we will be following with the latest addresses in the invoice file. The new HCP file has been saved as HCP (Clean Copy).
 
  
 
<b><u>HCO: </u></b>
 
<b><u>HCO: </u></b>
−
 
+
[[Image:HCO.PNG|800px|center]]
−
1. Standardization of data
 
−
Once HCO file was imported into JMP Pro, we have decided to analyze if “ZP Account” is unique to “Name”.
 
−
 
 
−
By using the summary function for “Name” and “ZP Account”, we have found out that there are some ZP accounts representing more than one name. By analyzing further, some of the customer names are outdated when we cross-checked against the invoice file. Therefore, we have excluded those outdated names and renamed the file as HCO (Clean copy).  
 
  
 
<b><u>Invoice Detail Report (QlikView) – 2015 and 2016 </u></b>
 
<b><u>Invoice Detail Report (QlikView) – 2015 and 2016 </u></b>
 +
[[Image:Invoice1.PNG|800px|center]]
  
−
1. Combination of raw files
+
<b><u> Customer 360 SG (F2F) </u></b>
−
Client provided us with two invoice files which consist of the 2015 invoices and 2016/17 invoices. Using excel software, both invoice files are combined into one file.
+
[[Image:F2F1.PNG|800px|center]]
−
 
 
−
2. Filtering out non-essential data
 
−
After looking through the various columns, we have noticed that there are negative and insignificant sales amount. Those amounts were pertaining to voiding of sales, and free samples given out by the company respectively.
 
−
 
 
−
With the help of excel software, we have removed those amounts as they are not essential for our further analysis.
 
−
 
 
−
3. Including more details
 
−
After analyzing the invoice file, we have decided to add in additional column for Therapy Area. As per our client, we should analyze sales per Therapy Area. Therefore, with the data provided from the client and from prior year project, we have added the column “Therapy Area” to further categorize the various products.
 
−
 
 
−
4. Changing of formats
 
−
We have formatted the “Invoice date” and “Batch expiry” dates to short date with dd/mm/yyyy as standard date format. The purpose of doing so is that in the original invoice file, these 2 columns include dates and timings. By changing to the standard date format, the timings have been removed as we do not require them for further analysis.
 
−
 
 
−
We have also noticed that for “Postal Code”, there are some postal codes that have only five numeric digits when the file was imported into JMP Pro. The standard postal code should have six numeric digits in Singapore. Therefore, with the help of JMP Pro software, we have done the following to change those postal codes with five numeric digits to six:
 
−
 
 
−
i. Right click on Postal Code’s header -> Select “Column Info” -> Change data type to “Character”
 
−
ii. Right click on Postal Code’s header -> Select “Insert Columns”
 
−
iii. Right click new column -> Select “Formula” -> Enter the formula as shown below
 
−
 
 
−
[[Image:PostalCode.png|700px|center]]
 
−
 
 
−
iv. Click on “OK” to apply change
 
  
−
The new postal code column has been renamed to “Formatted Postal Code”.
+
<b><u>Customer 360 SG (Phone Web)</u></b>
−
After performing the abovementioned steps, we have saved the invoice file as Clean Invoice (2015-2017) for both Excel and JMP Pro copies.  
+
[[Image:PhoneWeb.PNG|800px|center]]
  
  
Line 107: Line 81:
  
  
−
</font></div><br>
+
</div><br>

Latest revision as of 13:57, 16 October 2017

Logo3.PNG

Home About Us Midterm Progress Final Progress Project Overview Project Management Documentation

 


Data

Our client will be supplying us with both current and prior year (2015-2017) data. The type of files and invoices are summarised in the table below.

FileMidterms1.PNG


Data Cleaning and Transformation

We have done cleaning up and transformation of various files provided by the client with the help of both Excel software and JMP Pro software.

HCP:

HCP.PNG

HCO:

HCO.PNG

Invoice Detail Report (QlikView) – 2015 and 2016

Invoice1.PNG

Customer 360 SG (F2F)

F2F1.PNG

Customer 360 SG (Phone Web)

PhoneWeb.PNG




Retrieved from ‘https://wiki.smu.edu.sg/ANLY482/index.php?title=ANLY482_AY2017-18_T1_Group03/ProjectSteadfast_Project_Data&oldid=16635’