E-commerce Product Analytics

Executive summary

A BigQuery SQL review of TheLook e-commerce data, focused on revenue, product categories, market quality, fulfilment, and customer value. The output is a set of commercial findings that a product or growth team can act on.

Business context

The analysis asks where revenue comes from, which markets matter, and where operational leakage is hiding. It also tests whether reported customer and country patterns can be trusted before they become targets or budget decisions.

Data and preparation

The work joins BigQuery public tables for order items, orders, users, and products. Exploration exposed country-label inconsistencies such as Spain and España, plus Brasil and Brazil. Those are material data-quality issues, not cosmetic cleanup, because they split market totals.

Method

Fourteen SQL questions move from table inspection to revenue aggregation, order status checks, lifetime value, market ranking, and month-on-month growth. The queries use joins, COUNTIF, date functions, deduplication, RANK(), and LAG() rather than treating the dataset as a single flat report.

Results, limits, and next step

Outerwear and Coats lead category revenue. China and the United States have the largest user bases. Fifteen percent of orders are cancelled and ten percent returned, while only a quarter reach Complete status. The next step is to standardise country values, then separate cancellation causes from fulfilment performance before setting market targets.