VBM Portfolio Projects: SQL Data Cleaning (e-commerce data)

Learn how to clean a legacy e-commerce orders dataset from Dataflow, fix duplicates, normalize dates and segments, and compute ten KPIs from a trusted fact table.

Explore the e-commerce data schema in the SQL workbench, inspect the single source table, and plan cleaning steps to standardize dates, normalize segments, and validate key fields for KPI accuracy.

Adopt a two-video, stepwise data cleaning approach that builds a baseline from the raw table by examining the core query, the results, and distributions for customer segment and payment method.

Launch the sql workbench, create a clean workspace, and run a simple core query to inspect the raw e-commerce data for mixed date formats, missing values, and extraction issues.

Run a one-pass SQL query to count total rows and nulls per key column, producing a damage report that shows how many orders are incomplete due to missing values.

Interpret the completeness snapshot from the query: 10,286 raw rows with 308 missing order amount old (0.3%), while other key columns show zero nulls; counts only genuine SQL nulls.

Inventory the raw segment values and counts, including standard, premium, platinum, and typo variants, to drive normalization, note case and whitespace sensitivity, null groups, and before-after KPI readiness.

Reuse the profiling pattern to build a frequency table of payment methods, confirming methods for analysis ready as a dimension and supporting indicators like return rate and max mix shift.

Identify three stable payment methods—credit card, PayPal, and debit card—normalized in snakecase; this clean data supports KPI analyses of return rates by method and monthly payment mix shifts.

The end of the data cleaning section makes row counts and missing values visible, analyzes customer segment and payment method, and sets up a cleaner, normalized table for future KPIs.

Build a robust silver layer by structuring messy data, creating a tight view over raw data with real timestamps and numeric columns, and normalizing customer segments for reliable KPIs.

Create a silver pass view that normalizes dates, standardizes customer segments, casts numeric fields for KPI calculations, and includes a health check for date pass failures.

Parse and validate data in the silver layer by casting numeric fields, normalizing strings to lowercase, and converting invalid values to null, guaranteeing no row loss and consistent downstream queries.

Run a simple select star on the silver layer to inspect ten rows, validate timestamp formatting, lowercased segments, and numeric fields for joining and aggregating.

Use this sample output as a visual check to confirm passing and typing logic, proper timestamp formatting, lowercase and trimmed customer segments, and sensible numeric fields in the silver layer.

Create a temp view named silver normalize to standardize the date representation for displays, clean segments with a regex, and harden the return flag to 0, 1, or null.

Run a profiling query on the cleaned layer to verify that customer segment mappings yield canonical tiers—standard, premium, and platinum—with minimal nulls, enabling reliable kpis for group by clauses.

Consolidate the raw table into a reliable silver view by casting dates, normalizing numeric fields, and standardizing categories, while preserving the raw data in the background for reuse.

Turn the structured silver temp view into a clean, trusted dataset by enforcing business rules, validating costs, flags, time ranges, and deduplicating orders.

Assess the silver normalize temp view against the brief rules to diagnostically count how many rows would fail each rule, yielding a one-row data quality summary.

Assess diagnostic results to see that 99.7% of rows pass, while 0.3% are removed (about 308 rows) due to the order amount rule; this demonstrates separating measurement from enforcement.

Enforce the brief and lock in a baseline count by creating a silver filter temp view that keeps only rows passing all core rules for reliable dashboard kpis.

Baseline count keeps 9978 rows after filtering, with 308 rows rejected by the amount rule and zero failures elsewhere, validating the filter and establishing a future monitoring anchor.

Count distinct orders by applying select distinct on the business key fields. Reveal inner query's unique combinations, and let the outer count show how many exist, highlighting the duplication gap.

Quantify and detect duplicates using the full business key to avoid inflated KPI metrics; identify 258 true duplicates among 9978 rule-compliant rows, then collapse to 9720 unique business events.

Define the clean table as the one-row-per-unique combination of eight business fields, deduplicating identical rows for final inspection and KPI-ready dashboards.

Interpret the clean dataset by deduplicating and inspecting results to ensure a defensible denominator, showing 9720 unique contract compliant orders as the final population for downstream KPIs.

Clean the messy legacy orders table by profiling raw data, normalizing the silver layer, enforcing explicit business rules, and removing duplicates to enable kpis and tell the business story.

Explore the KPI guide in the SQL workbench left panel and walk through ten KPIs to calculate, framing them as a business checklist for clear definitions and easier SQL writing.

Define KPI 1, the average order value (AOV), by dividing total order amount by orders to measure revenue per order and track changes in pricing, discounts, or basket size.

Calculate KPI two as the overall gross margin percentage to gauge profitability, using the formula (revenue minus cost) divided by revenue to produce the final ratio.

Introduce KPI 3, the return rate, defined as the share of orders that come back. Note that arising return rate points to issues with product quality, fit, or customer expectations.

Compute the median order amount to reveal a typical value not distorted by outliers. Sort all orders and take middle value, or mean of the two middle values if even.

Analyze KPI five, the return rate by payment method, using KPI three's approach to calculate returned orders divided by all orders for each method, to identify riskier channels.

Calculate KPI six as the premium and platinum share of GMV. Sum order amounts from premium or platinum orders and divide by total GMV across all segments.

Explore KPI seven, measuring share of orders failing margin rules by computing (order amount minus cost) divided by order amount and comparing to 40% standard, 30% premium, and >25% platinum.

Identify the peak month by grouping GMV by month, summing monthly sales, and selecting the highest total. Use this peak to inform seasonality analysis and planning.

Explore KPI nine, the month-on-month GMV growth percentage, calculated as (this month GMV minus last month GMV) divided by last month GMV; positive indicates growth, negative indicates contraction.

KPI ten measures the month-to-month payment method share shift by computing each method's share of total orders, comparing consecutive months, and taking the largest absolute difference across methods and months.

Submit KPI values as decimals for all percentages. Lock in the decimal submission definitions, then clean the data, build the pipeline, and express each KPI directly in SQL.

Turn the clean table into ten KPIs by translating questions into precise SQL, format results as KPI name, KPI value, and KPI key, and union all into KPI results.

Compute the first KPI, average order value (AOV), by building a temp KPI view, rounding to two decimals, and casting to varchar for a unified API schema.

Calculate the average order value (AOV) as total order amount divided by total orders on the clean table, rounded to two decimals, yielding about 8444 per order.

Compute KPI two’s gross margin. Sum gross profit order amount old minus cost and revenue order amount old; divide profit by revenue and cast to string, rounded to six decimals.

Assess KPI two, the gross margin, to gauge profitability after expenses and pricing power. Compute (revenue − cost) / revenue, weighted by GMV on a clean dataset, six decimals.

Compute KPI three return rate by summing is_return to count returns and dividing by total valid unique orders, yielding 0.079938 (about 7.99%).

interpret kpi three, the return rate, as returns divided by total orders on clean data, using is_return as 0/1 and count(*) as denominator; 0.079938 means just under 8%.

Compute the median order amount KPI by querying the 50th percentile of order values, rounding to two decimals, casting to varchar, yielding 6522 as the overall metric.

Determine the median order amount to reveal the typical customer spend, using the 50th percentile to minimize outliers, and compare with average order value to inform forecasting and leadership insights.

Compute KPI five by payment method: group by method, sum is_return, divide by orders; round to six decimals and cast to varchar. PayPal 8.08%, credit card 8.04%, debit card 7.76%.

Assess KPI five, the return rate by payment method, to see if certain methods attract more returns and what that reveals about customer experience and risk.

Compute KPI six, the high-value segment GMV share, by summing total GMV and GMV for premium and platinum customers, then the ratio yields 64.91%.

Evaluate KPI six by analyzing the high value segment GMV share to show how much revenue comes from premium and platinum customers, about 64.9% of total GMV, indicating concentration risk.

computes KPI seven, the below target margin rate, by comparing each order's gross margin to segment floors (40%, 30%, 25%), flagging failures, and reporting the share below floor.

Interpret KPI seven by calculating realized margin per order, attaching the correct margin by segment, and flagging failures to reveal a 0.29% below-margin rate.

Identify KPI eight, the top GMV month, by converting order dates to year-month, aggregating total GMV per month, and selecting December 2024 as the top result.

Identify KPI eight’s top GMV month by summing cleaned, de-duplicated orders per calendar month and selecting the highest GMV, breaking ties by most recent month; December 2024 provides the benchmark.

Compute kpi nine by aggregating monthly gmv, using lag to compare the latest month (december 24th) to the previous month (november 24th), then calculate and round growth to six decimals.

Compute KPI ten, the max payment mix shift, by calculating month-by-month shares per payment method, using lag differences to measure month-to-month changes, and reporting the maximum shift as KPI value.

Analyze KPI ten, the max payment mix shift, by calculating monthly shares per payment method, using lag to track shifts, and taking the maximum jump to show a 4.1 change.

Consolidate ten KPI views into a single KPI results table and verify automatic scoring on AOV, margin, returns, payment method, behavior growth, and mix shifts.

Consolidate ten KPI views into a single canonical KPI results table via a union all pattern on a clean deduplicated dataset, enabling automated grading and a concise business narrative.

Consolidate ten KPI views into a single submission ready table using a union all stack, then create or replace table KPI results and submit for grading.

Review the results page after submitting KPI results, where one submission shows one passed and zero failed, confirming all ten KPIs and the correct schema.

Audit the detailed results page to confirm every KPI value matches the reference, with a pass status, 100% score, and per channel metrics.

Turn a messy legacy dataset into a clean analytics asset with a layered sql pipeline and ten kpis. Package this portfolio-ready work for hiring managers to see reliable insights.