Difference between revisions of "Analysis of User and Merchant Dropoff for Sugar App Data Source"
| Line 47: | Line 47: | ||
The users and merchants also belong to different regions, namely Singapore, Jakarta, Hong Kong, and we have to extract and focus on the Singapore region users and merchants. | The users and merchants also belong to different regions, namely Singapore, Jakarta, Hong Kong, and we have to extract and focus on the Singapore region users and merchants. | ||
| − | # | + | ==<div style="background: #95A5A6; line-height: 0.3em; font-family:helvetica; border-left: #6C7A89 solid 15px;"><div style="border-left: #FFFFFF solid 5px; padding:15px;font-size:15px;"><font color= "#F2F1EF"><strong>Data Preparation</strong></font></div></div>== |
| − | # | + | |
| − | + | As the data for Sugar is massive, cleaning the data took several stages. | |
| − | + | ||
| − | + | [[File:SugarBranch.png|600px]] | |
| − | + | ||
| − | + | First, the '''Users''' table was cleaned to only contain Singapore users with the following steps: | |
| − | + | # Clean out all Users that are not from Singapore by deleting Country_id != 1 | |
| − | # | + | # Clean out user_id:99280 (invalid mobile number(10000) and balance of 1200) |
| − | + | ||
| − | + | Secondly, '''Branch''' table was cleaned to only contain Singapore non-Sugar Businesses, and eliminate duplicates : | |
| − | + | ||
| − | + | # Clean out all Merchants that are not from Singapore by deleting [Country_id] != 1 | |
| − | + | # Clean out all Merchants that are Sugar’s administrative accounts | |
| − | + | # Clean out all Merchants that are accounts created for specific campaigns (e.g. Blackout Friday, SugarGSS etc) | |
| − | + | # Clean out Merchant duplicates | |
| − | + | # Corrected [Area] field for Merchants that have invalid/blank fields | |
| − | + | # Corrected [Lng,Lat] coordinates for following branches that have incorrect coordinates (i.e. [Country_id] == 1 but coordinates correspond to another country) | |
| − | + | # Cleaned out all Merchants that have invalid [Created_At] dates | |
| − | + | # Cleaned out all Merchants whose [Created_At] dates != 2014 OR 2015 | |
| − | + | ||
| − | + | Next, the '''Users''' table and '''Branch''' table was inner joined with the '''Order''' table to eliminate all non-Singaporean users and businesses. We further cleaned the data using latitude and longitude to eliminate all orders made outside Singapore. | |
| − | + | ||
| − | + | [[File:SugarMerchant.png|600px]] | |
| − | + | ||
| − | + | Lastly, the '''Branch''' table was inner joined with the '''Orders''' table to form '''Merchant''' table (for merchant analysis). It is further cleaned to include only paid orders and decomposed further for both the funnel plot analysis and time-series data mining analysis. | |
| − | + | ||
| − | + | # Clean out all unpaid orders (i.e. [Status] != 1,2 and 4) | |
| − | # | + | # Clean out all orders where [Price] == 0 |
| − | + | # Clean out all orders where it is gifted during a Sugar event (i.e. [Is_Reward] == 1) | |
| − | + | # Clean out all orders that are lucky draws | |
| − | + | # Decomposed [Redeemed_Date] into Hour, Day of Week and Month | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | # | ||
| − | # | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | # | ||
| − | # | ||
| − | # | ||
| − | # | ||
| − | # | ||
| − | # | ||
| − | # | ||
| − | # | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | # | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | # | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | # | ||
| − | # | ||
| − | # | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
Revision as of 12:09, 27 February 2016
| Background | Data Source | Methodology |
|---|
Data Source
We will be using Sequel Pro to connect to Sugar’s SQL database in order to extract any necessary data. We will also be logging on to their Flurry or Localytics dashboard to extract any further information that we may need.
JMP Pro will be used to perform the survival analysis.
Details of Data Source
We have 3 years of data from Sugar’s database and dashboards, from 2013 to 2015. From our dataset, we have identified certain variables that we will using in our analysis.
However, before embarking on our analysis, we have to do extensive data cleaning. For example, we have 45,000 users but a number of them are throwaway test accounts, or some are dead on arrival with no orders to their name.
The users and merchants also belong to different regions, namely Singapore, Jakarta, Hong Kong, and we have to extract and focus on the Singapore region users and merchants.
Data Preparation
As the data for Sugar is massive, cleaning the data took several stages.
First, the Users table was cleaned to only contain Singapore users with the following steps:
- Clean out all Users that are not from Singapore by deleting Country_id != 1
- Clean out user_id:99280 (invalid mobile number(10000) and balance of 1200)
Secondly, Branch table was cleaned to only contain Singapore non-Sugar Businesses, and eliminate duplicates :
- Clean out all Merchants that are not from Singapore by deleting [Country_id] != 1
- Clean out all Merchants that are Sugar’s administrative accounts
- Clean out all Merchants that are accounts created for specific campaigns (e.g. Blackout Friday, SugarGSS etc)
- Clean out Merchant duplicates
- Corrected [Area] field for Merchants that have invalid/blank fields
- Corrected [Lng,Lat] coordinates for following branches that have incorrect coordinates (i.e. [Country_id] == 1 but coordinates correspond to another country)
- Cleaned out all Merchants that have invalid [Created_At] dates
- Cleaned out all Merchants whose [Created_At] dates != 2014 OR 2015
Next, the Users table and Branch table was inner joined with the Order table to eliminate all non-Singaporean users and businesses. We further cleaned the data using latitude and longitude to eliminate all orders made outside Singapore.
Lastly, the Branch table was inner joined with the Orders table to form Merchant table (for merchant analysis). It is further cleaned to include only paid orders and decomposed further for both the funnel plot analysis and time-series data mining analysis.
- Clean out all unpaid orders (i.e. [Status] != 1,2 and 4)
- Clean out all orders where [Price] == 0
- Clean out all orders where it is gifted during a Sugar event (i.e. [Is_Reward] == 1)
- Clean out all orders that are lucky draws
- Decomposed [Redeemed_Date] into Hour, Day of Week and Month
