The full report is available in PDF format in the main branch.
- Project Summary
- Pareto Analysis Explanation
- Dataset Exploration
- Methodology
- Target Threshold Analysis
- Results & Recommendations
This is a Pareto analysis project using a dataset from a pet shop. I used SQL in Google BigQuery to develop the analysis. The objective is to determine what proportion of customers accounts for 20%, 50%, and 80% of total revenue, and to use those findings to evaluate how concentrated revenue is across the customer base.
Pareto analysis is a prioritization method used to identify the relatively small set of contributors that account for a disproportionately large share of an outcome. It is commonly associated with the 80/20 guideline, which suggests that 80% of effects often come from 20% of causes, although the exact distribution varies by dataset and business context.
In a business setting, Pareto analysis helps focus attention on the factors that matter most for performance, risk, or value creation. In this project, the analysis is applied to customer revenue to identify how much of the pet shop's revenue is generated by the highest-spending customers.
Pareto analysis identifies where to look first, but it does not determine the corrective or strategic action on its own. Interpretation should therefore be supported by business context, domain knowledge, and professional judgment.
The dataset was first reviewed to identify the available fields and confirm the structure of the source data. This step established the fields needed to calculate customer-level sales and cumulative revenue contribution.
Initial review showed that one invoice number can contain more than one stock code, while each invoice is associated with only one customer ID. This distinction is important because revenue must ultimately be aggregated at the customer level for Pareto analysis.
The analysis was developed incrementally through a sequence of SQL views in BigQuery. Each view added a new layer of transformation so that the final output could measure cumulative customer and revenue contribution accurately.
The first step was to calculate line-level sales by multiplying Quantity by UnitPrice and to identify the total number of customers in the dataset. These values were combined into an initial view named sales_v1.
At this stage, multiple rows still existed for the same customer because a customer could appear on more than one invoice. As a result, the data was not yet suitable for customer-level Pareto analysis.
The second view grouped the data by CustomerID and summed sales to produce one revenue total per customer. This created a customer-level measure called CustomerRevenue, which is the core input for the Pareto analysis.
This transformation in sales_v2 aggregated transaction-level records into one row per customer.
The third view ranked customers from highest to lowest revenue, preserving individual customer rows while calculating cumulative and dataset-wide totals using window functions. It calculated cumulative revenue, total customer count, and total revenue across the full dataset.
This step established the core Pareto logic by tracking how revenue accumulates as customers are added in descending order of spending.
The fourth view extended the previous results by adding two derived fields: Cuml_Sales_Share and Cuml_PCT_Customers. These fields represent the cumulative share of total revenue and the cumulative share of total customers, respectively.
This made it possible to identify what percentage of customers was required to reach a target revenue threshold. Both cumulative fields reached 100% (1.0) at the end of the ranked customer list, confirming that the calculations were complete.
A declared variable query was used to make the threshold selection dynamic. The variable Target_Sales_PCT was set to a value such as 0.80 (80%), and the query returned the first row where the cumulative sales share was greater than or equal to the selected target.
This approach allowed the same logic to be reused for different Pareto thresholds without rewriting the full query.
When Target_Sales_PCT was set to 0.80, 150 of 261 customers were required to reach approximately 80% of total revenue, roughly 57% of customers.
Revenue is concentrated, but not to the extent of a classic 80/20 distribution. A comparatively broad portion of the customer base is needed to generate most revenue.
When Target_Sales_PCT was set to 0.50, 79 of 261 customers accounted for about 50% of total revenue, roughly 30% of customers.
This shows a moderate concentration pattern: fewer than one-third of customers generate half of total sales. Higher-revenue customers are important, but not exclusively responsible for overall performance.
When Target_Sales_PCT was set to 0.20, 28 of 261 customers accounted for approximately 20% of total revenue, about 11% of customers.
This is the most concentrated portion of the revenue distribution, highlighting a small group of high-revenue customers. These customers may be a useful segment for retention analysis, loyalty initiatives, or more detailed behavioral review.
The analysis evaluated the minimum number of customers required to reach each revenue threshold, ranked from highest to lowest customer revenue.
| Revenue threshold | Customers required | Share of customers |
|---|---|---|
| 20% | 28 of 261 | 11% |
| 50% | 79 of 261 | 30% |
| 80% | 150 of 261 | 57% |
Customer revenue was unevenly distributed, but did not follow a classic 80/20 pattern. Approximately 57% of customers were needed to generate 80% of revenue, indicating that most revenue depended on a relatively broad customer group rather than a small minority.
- Investigate high-spending customers' purchasing frequency and repeat purchase behavior to identify potential retention opportunities.
- Consider both high-spending customers and the broader customer base when developing customer engagement strategies.
- Compare revenue concentration across time periods to assess whether customer dependency is stable or changing.
- Segment the analysis by country to explore differences across geographic markets.
- Extend the analysis to products or product categories to identify other sources of revenue concentration.
These findings describe revenue contribution within the analyzed dataset. They do not establish customer profitability or explain why some customers spend more than others.
Retention or loyalty initiatives would require further evidence about purchasing behavior, costs, and business context.
This project demonstrates how SQL views, window functions, cumulative share calculations, and a configurable threshold variable can support reusable customer revenue analysis.