Analysis of User and Merchant Dropoff for Sugar App Data Source

Home

Project Overview

Findings

Project Documentation

Project Management

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 Beijing. Thus, we will have to segment them as their behaviour may differ from region to region.

1. User Data
   • ID
   • Latitude
   • Longitude
   • Timezone
   • Country
   • Username
   • Email
   • Creation Date
   • Updated At Data
2. Merchant Brand Data:
   • ID
   • Manager ID
   • Name
   • Description
   • Creation Date
   • Updated At Date
3. Merchant Branch Data:
   • ID
   • Brand ID
   • Latitude
   • Longitude
   • Country
   • Name
   • Address
   • Description
   • Redemption Type
   • Redemption Time
   • Rating
   • Enabled
   • Creation Date
   • Updated At Date
4. Item Data
   • ID
   • Brand ID
   • Name
   • Description
   • Creation Date
   • Updated At Date
   • Market Price
5. Campaign Data (Each campaign is an offer on the app)
   • ID
   • Brand ID
   • Item ID
   • Branch ID
   • Is all branch
   • Category Name
   • Start Time
   • End Time
   • Redemption Start Time
   • Redemption End Time
   • Redemption After Buy
   • Start Price
   • Floor Price
   • Unlock Price
   • Current Price
   • Bargain Range
   • Stock
   • Left
   • Click Count
   • Weight
   • Needs Booking
   • Allows Take Out
   • Tips(description)
   • Enabled
   • Creation Date
   • Updated At Date
6. Campaign Skim Data
   • ID
   • Campaign ID
   • Branch ID
   • User ID
   • Amount
   • Hour
   • Coordinates
7. Campaign Click Data
   • ID
   • Campaign ID
   • Branch ID
   • User ID
   • Amount
   • Hour
   • Coordinates
8. Campaign Buy Data
   • ID
   • Campaign ID
   • Branch ID
   • User ID
   • Amount
   • Hour
   • Coordinates
9. Campaign View Data
   • ID
   • Campaign ID
   • Branch ID
   • User ID
   • Amount
   • Hour
   • Coordinates
10. Campaign Spread Data
    • ID
    • Campaign ID
    • Branch ID
    • User ID
    • Amount
    • Hour
    • Coordinates
11. Order Data (when a user buys something from a campaign)
    • ID
    • Creator ID
    • Owner ID
    • Campaign ID
    • Branch ID
    • Price
    • Status
    • Trade Number
    • Redemption Date
    • Expiration Date
    • Is Commented
    • Is Deleted
    • Is Rewarded
    • Longitude
    • Latitude
    • Creation Date
    • Updated At Date
    • Refunded At Date