ANLY482 AY2017-18 T2 Group15 Data Analysis

From Analytics Practicum
Revision as of 13:09, 14 April 2018 by Amanda.lim.2014 (talk | contribs)
Jump to navigation Jump to search

HOME

PROJECT OVERVIEW

DATA ANALYSIS

DOCUMENTATION

REFLECTION

ABOUT US

BACK TO MAIN ANLY82


 

DATA PROVIDED


The client provided the following datasets in Excel format:
  1. Monthly Distribution Data of their brands across their distribution channels, from 2014 to 2017.
  2. Quarterly data on public perception of their brands and competitor brands, based on specific Key Performance Indicators (KPIs), from 2014 to 2017.
  3. Monthly Sales Data of their brands from an external Market Research Company, from 2014 to 2017.
  4. A file containing the current dashboard the client uses, and intermediate data which has been processed from raw data.


DATA CLEANING


To create a dashboard for Company X on visualization software, the team had to clean and check the data for consistency. However, the data format used by the client is not suitable for dashboard creation due to the following:
  1. Not every column contains a field name, making visualisation difficult. For example, if there is a column for each brand name, as shown in Figure 1, it is challenging to divide the data according to Brands, for analysis. Instead, the different brand names should be lists below a field, "Brand", as shown in Figure 2.
  2. Brand names were inconsistent across different files. For instance, "Overall Brand X" in one file could be named as "Brand X Total" in another. This causes inaccuracies when selecting brands in filters, as some brands may be unintentionally selected or unselected.
For the purposes of this study, the team changed the format of the data files by transposing the data in Excel to that seen in Figure 2 below. The format shown in Figure 2 is known as the Flat Table data structure, which ensures that every column starts with a cell containing the field name that describes the column values. This allows the data to be recognized by the visualization platforms. In addition, the problem of brand name inconsistency has been addressed by standardising the brand names where possible, which will minimise inaccuracies.


Figure 1: Format of Distribution data before cleaning
Figure 1: Format of sales data before cleaning


Figure 2: Format of Distribution data after cleaning
Figure 2: Format of sales data after cleaning
DATA CLEANING TOOLS USED


The team used Excel to transpose the data into Flat Table format and JMP to analyse the data for null values and inconsistencies.
Tools used in Data Cleaning


QUALITY CHECKING


Quality checking for deliverables is conducted by comparing the team's work to the existing dashboard of the client. Mistakes in the data were identified by looking out for differences in values and trends. During data cleaning, the team checked for null and negative values in the raw data and brought them to their client’s attention for clarification.