Data Analytics

Tokopaedi Business Analysis

Open Repository on Github
Open Documentaion

Overview

This project analyzes Tokopaedi e-commerce business performance using SQL based on transaction, customer, product, payment, and marketing funnel datasets.

The analysis focuses on identifying sales trends, customer behavior, product category performance, and channel effectiveness to support data-driven business decisions.

This project was completed as part of the Final Project – Data Analyst Bootcamp (MySkill Batch 27).

Objectives

  • Analyze monthly revenue trends
  • Evaluate product category performance
  • Compare sales channel growth
  • Measure funnel conversion effectiveness
  • Analyze customer onboarding and activation
  • Generate business recommendations using SQL insights

Tech Stack

Dataset

The project uses six datasets:

  • order_detail : Order-level transaction details
  • transaction_detail : Transaction payment summary
  • product_detail : Product information
  • customer_detail : Customer profile and registration
  • payment_detail : Payment method list
  • funnel_detail : Marketing funnel activity

Workflow

Data Preparation
Data Preparation

  • Converted raw files into CSV format for easier processing and analysis
  • Checked duplicate rows and validated missing values across all tables
  • Reviewed column types including date and numeric formatting
  • Verified data quality before starting SQL analysis

h
Database Relationship Mapping
Database Relationship Mapping

  • Identified primary and foreign keys between all datasets
  • Mapped relationships using customer_id, transaction_id, order_id, and sku_id
  • Determined which tables should be joined for each business question
  • Built a clear relational structure for accurate analysis

h
SQL Query Development
SQL Query Development

  • Built analytical queries using JOIN, aggregation, filtering, and date functions
  • Used CTE to simplify complex calculations and improve readability
  • Applied business filters such as valid transactions and analysis period
  • Structured queries based on stakeholder requirements

h
Business Case Analysis
Business Case Analysis

  • Analyzed monthly revenue and seasonal performance trends
  • Evaluated product category sales and inventory movement
  • Compared growth across multiple sales channels
  • Measured funnel conversion and customer activation performance

h
Insights & Recommendations
Insights & Recommendations

  • Identified key business patterns from revenue, product categories, sales channels, and customer activity
  • Evaluated strong and weak performance areas to identify growth opportunities and business risks
  • Translated SQL outputs into actionable recommendations for revenue, inventory, marketing, and customer activation
  • Connected analytical findings with business decisions to support stakeholder strategy

h

Key Insights

  • Revenue showed a clear seasonal pattern, with the strongest performance occurring in Q4 and a noticeable decline during mid-year periods.
  • Customer purchasing behavior shifted toward lifestyle-focused categories, especially Fashion & Footwear and Beauty & Personal Care.
  • Website generated the highest traffic volume, but conversion performance remained lower than App Store and Play Store.
  • Offline Store delivered the strongest year-over-year growth and became the top-performing sales channel.
  • Customer activation speed varied by channel, with Website converting faster while Mobile App attracted more users but required longer time to complete first purchase.

Recommendations

  • Prioritize inventory planning and promotional strategy for high-growth categories such as Fashion & Footwear and Beauty & Personal Care.
  • Improve website conversion through better UX/UI, checkout optimization, and funnel performance review.
  • Strengthen operational and campaign planning ahead of Q4 to maximize revenue during peak season.
  • Continue supporting high-performing channels like Offline Store while optimizing budget allocation for lower-performing channels.
  • Improve customer onboarding and follow-up strategy to reduce time to first purchase and increase activation rate.

Future Improvements

  • Expand the analysis with interactive dashboards using Looker Studio or Tableau for easier business monitoring.
  • Build deeper customer segmentation analysis based on purchasing behavior and registration channel.
  • Analyze payment method trends to understand customer payment preferences and transaction patterns.
  • Add profitability metrics such as gross profit and margin analysis using product cost data.
  • Develop forecasting models for revenue and category demand to support future business planning.