E-Commerce Performance Analysis for a U.S. Pet Supply Company
Using SQL & Power BI to uncover revenue drivers, product profitability, shipping-cost pressures, geographic growth, and cross-selling opportunities.
I. Business Context
Selling more products does not always mean making more profit.
For an e-commerce business, revenue can be influenced by many factors beyond sales volume — including product costs, shipping expenses, returns, customer demand, product characteristics, and geographic distribution.
This project analyzes an online pet supply retailer operating across the United States to understand where revenue and profit come from, which products and markets perform best, and how operational costs affect profitability. It covers transactions from December 1, 2020 to December 9, 2021, representing 374 days of business activity. Their product portfolio consists of 20 products across 6 categories and 3 major divisions.
II. Business Problem
The company had limited visibility into its revenue drivers, product performance, and operational costs due to unstandardized transactional and customer data.
In particular, the business needed to better understand:
- How much sales were generated throughout the dataset period?
- How do Cost of Goods Sold (COGS) and shipping costs affect profitability?
- When do sales peak?
- Which product category generate the most sales and profit?
- Which products are the top and bottom performers?
- Does product weight influence revenue or costs?
- What opportunities could be considered to reduce operational costs?
- Which states generate the highest profitability and customer value?
- Where are potential expansion opportunities?
- Which products could be bundled or cross-sold?
These questions formed the foundation of the analysis.
III. Goal
The business goal was straightforward: Increase revenue and reduce operational costs.
IV. Data Preparation & Processing
The dataset contains four core tables: Sales, Products, Customers, State Mapping. These are sourced from three CSV files and a PostgreSQL database. The CSV files were imported directly into Power BI for initial inspection and preparation.
Several data-quality issues found in the Customers table, which required initial processing in PostgreSQL before loading it into Power BI:
The blank geospatial values in longitude and latitude columns, causing import failures, which was resolved by disabling standard quote and escape characters and defining two single quotes as null identifiers then converted the data type to numeric. The order_state column contained inconsistent state values such as different capitalization and formatting variations. PostgreSQL was used to standardize these values before importing the cleaned data into Power BI.
The rest of the cleaning and transformations were executed in Power BI, these included:
- Correcting data types
- Cleaning product names and categories
- Removing blanks, nulls, and duplicate records
- Renaming and removing columns
- Separating transaction date and time information
- Reclassifying transactions such as returns, free items, adjustments, and etc.
- Creating an invoice-level table for additional analysis
- Creating Date and Time dimension tables
- Developing DAX measures and calculated columns
V. Data Modeling
A snowflake-schema model connects tables, utilizing relationships, DAX, and metadata to optimize report navigation and analysis efficiency.
VI. Data Analysis
The analysis was conducted using DAX, it revealed the following key performance metrics during the transaction period:
These metrics serve as the baseline for analyzing revenue and profitability variances across products, time periods, and geographic markets.
VII. Data Visualization
The data visualization package presents the analytical results through an interactive and user-friendly dashboards, allowing users to navigate the data seamlessly, uncover trends, and drill into specific business questions as needed.
The dashboards were organized into four report pages:
An Overview page highlighting KPIs, high-level financial viability metrics, and trends over time.
A Products page focused on division-category-item-level product performance.
A Customer Geography page showing the spatial distribution of customer activity across U.S. states and regions.
An Invoice Summary page summarizing transaction patterns and invoice characteristics.
Collectively, these visuals transform raw data into interpretable insights, offering both breadth and depth across operational and commercial dimensions of the business.
VIII. Findings & Insights
Based on the analysis, the following findings and insights were extracted from the dashboards:
1. Overall Financial Status & Temporal Performance Trends
The business demonstrated strong financial health, generating $1.531 million in net sales with a 51.12% gross profit margin ($782,800 gross profit vs. $748,516 COGS) through 75,298 units sold. After accounting for $178,178 in shipping costs, the business retained $604,621 in FGP, yielding a 39.48% fulfillment margin. Although shipping costs reduce overall profitability, the business maintained decent margins relative to typical e-commerce benchmarks.
Monthly and Seasonal Performance Trends
One of the clearest patterns was the presence of seasonal fluctuations in sales, with June and November showing significant spikes in both gross sales and FGP.
November 2021 was the strongest month, it recorded:
- $243,103.32 gross sales
- $108,829.36 FGP
- and the highest FGP MoM% change, +114.86%
Meanwhile:
- February recorded the lowest gross sales at $69,912.30
- April recorded the lowest FGP at $21,300.88
Time-Series & Behavioral Analytics
On a quarterly basis, Q4 2021 generated $460,693.38 in gross sales, more than 25% above the second-highest quarter.
The analysis also uncovered recurring daily and hourly patterns.
Sales spikes appeared around the 7th, 10th, and 14th of the month, with the 10th producing almost 20% more sales than the 7th.
At the weekly level, Monday and Wednesday each generated more than $300,000 in sales, while Saturday was the slowest day at approximately $111,971.
The strongest sales hour occurred between 3:00 PM and 4:00 PM, while the fastest order activity occurred between 10:00 AM and 1:00 PM.
This means the business has identifiable periods when revenue is considerably stronger. This creates opportunities to align operational resources and marketing efforts with these historically peak periods.
2. Product and Category Performance
Both product and category performance varied considerably across the portfolio.
Top Category Performance
The Food category under Consumables was the largest contributor, generating:
- $607,894.74 gross sales
- 28,199 units sold
- 1,973 customers
- $286,064.36 gross profit
- $209,475.86 FGP
The Disposables category followed with:
- $433,030.74 gross sales
- 23,555 units sold
- 1,913 customers
- $229,014.16 gross profit
- $178,434.16 FGP
Top-Performing Products
One product stood out as the primary revenue contributor:
Taste of the Wild High Prairie Grain-Free Dry Dog Food 40 lb
Get Ralph Matthew Ong’s stories in your inbox
Join Medium for free to get updates from this writer.
It generated:
- $270,364.38 gross sales
- $264,233.58 net sales
- $141,033.58 gross profit
- $101,033.58 FGP
- 3,428 units sold
- 881 customer purchases
Other strong performers included Dog and Puppy Pads, Earth Rated Dog Poop Bags, Memory Foam Pet Beds, and Sheba Perfect Portions Pate Wet Cat Food.
Sheba Perfect Portions Pate Wet Cat Food recorded the highest unit sales at 9,911 units, demonstrating particularly strong demand.
Slow-Moving and Lower-Margin Products
Not every product contributed equally.
Several grooming-related products generated less than $50,000 in gross sales.
The bottom-performing products included:
- ChomChom Pet Hair Remover — lowest gross sales at $42,300.72
- Indoor Pet Camera — lowest unit sales at 602
- NexGard Chews — lowest FGP at $9,748.46
- Canned Cat Food — lowest customer purchases at 140
Several products also recorded relatively low gross or fulfillment margins, including ProBiotic Supplements, NexGard Chews, Milk-Bone MaroSnacks, UltraSound Dog Barking Deterrent, and Purina ONE SmartBlend.
This suggests that simply maintaining a large product assortment may not be enough. A more useful approach is to evaluate products using both demand, reach, and profitability, rather than revenue alone.
Correlation Analysis
One of the most interesting findings came from analyzing product weight against sales, volume, COGS, shipping, and profitability.
Product weight had no significant correlation with unit sales:
Weight vs. Unit Sales: r = -0.1434
In other words, heavier products did not necessarily sell fewer units.
However, weight had a much strong positive correlation with shipping costs:
Weight vs. Shipping Cost: r = +0.8788
Weight was also highly positively correlated with:
- Gross Sales: r = +0.8205
- COGS: r = +0.8180
- FGP: r = +0.6690
This reveals an important distinction:
Weight may not strongly determine demand, but it can significantly influence the cost structure of a product.
A good example:
The bulky 40-lb Taste of the Wild generated the most sales but yielded a lower 38.24% fulfillment margin due to heavy COGS and shipping costs, whereas the lightweight 0.5-lb ChomChom Pet Hair Remover generated about one-sixth of that revenue but secured a significantly higher 68.49% fulfillment margin through minimal production and transit costs.
This demonstrates why SKU characteristics such as weight, size, and packaging requirements should be considered alongside revenue when evaluating product profitability.
3. Customer Geographic Performance
Customer demand extended across all 50 U.S. states plus District of Columbia, covering more than 2,200 cities.
Regional Performance
The East region was the strongest region across the major KPIs, generating:
- $548,004.18 gross sales
- $533,429.28 net sales
- $252,190.78 gross profit
- $188,598.28 FGP
- 27,333 units sold
- 1,389 customer purchases
State Performance
At the state level, several markets stood out.
Massachusetts led in profitability, generating:
- $57,517.28 FGP
- 71.64% gross profit margin
- 65.47% fulfillment margin
California recorded the highest revenue, sales volume, and customer reach with $172,697.76 gross sales, $171,149.94 net sales, 11,639 units sold, and 419 customers.
Florida and Texas were also significant revenue and profit contributors.
At the other end of the spectrum, District of Columbia showed operational loss due to unusual high returns and COGS corresponding to its gross sales. While Vermont and Wyoming generated significantly low revenue, sales volume, profitability, and customer reach.
Regional performance ranks from best to lowest in a clear East-Central-West-Other Region tiering, anchored by California, Florida, Texas, and Massachusetts as the core revenue and profit centers. Focus on these high-yielding states while addressing underserved markets could maximize total earnings.
Expansion Opportunities
Massachusetts’s high revenue and profitability, with its comparatively low purchasing and shipping costs, combined with a dense concentration of high-value customers making it a prime candidate for business expansion.
Interestingly, Delaware, Nevada, Louisiana, and North Dakota showed relatively high customer LTV but very few customer reach, suggesting potential growth opportunities for further investigation rather than automatically assuming expansion would be successful.
4. Invoice and Market Basket Analysis
The analysis also looked beyond standard sales metrics.
Promotional Items
Of the 965 free items distributed, 671 were Dog and Puppy Pads, representing approximately 70% of all free items.
This indicates that promotional activity was highly concentrated on a single product.
Returned Goods
Dog and Puppy Pads and Earth Rated Dog Poop Bags were among the strongest-selling products, but they also accounted for nearly half of returned items, 591 of 1,214.
This does not establish the reason for the returns, but it highlights products that warrant further investigation into factors such as product expectations, quality, fit, pricing, or descriptions.
Frequently Purchased Products
A recurring product combination was uncovered among the transactional data:
- Sheba Perfect Portions Pate Wet Cat Food + Templation Soft Cat Treats + Pet Hair Remover + Pet Odor Eliminator
This creates a potential cross-selling and bundling opportunity.
IX. Recommendations
Based on the findings and insights, several strategies were proposed:
1. Maximize high sales periods
Use Q4, particularly November as key promotional periods while aligning campaigns with historically strong days and hours.
2. Reduce COGS and shipping costs
Focus cost-reduction efforts on cost-heavy products. Potential actions include supplier pricing negotiations, reducing dimensional weight, carrier-rate comparisons, reviewing shipping strategies for bulky products.
3. Optimize the product portfolio
Continue supporting strong performers while reviewing low-demand and low-margin products. Products should be evaluated using a combination of Demand + Revenue + Profit + Margin, rather than sales alone.
4. Strengthen high-value geographic markets
Expand presence in highly profitable states to maximize growth, while launching targeted regional awareness and customer acquisition campaigns to penetrate states with low customer density but high LTV.
5. Develop cross-selling opportunities
Promote frequently bought together product pairs through discounted bundle offers, on-site cross-sell widgets, and automated email triggers to drive higher order value.
6. Set financial targets
Establishing annual financial targets for revenue, gross profit margin, fulfillment margin, and FGP.
X. Final Takeaway
The analysis demonstrates that revenue alone does not tell the full story of e-commerce performance.
Some of the company’s highest revenue products also carry significant cost of goods sold and shipping costs. Meanwhile, lighter products can maintain considerably stronger fulfillment margins despite generating less revenue.
The analysis also revealed clear seasonal demand patterns, geographic differences in customer value, strong and weak product performers, and opportunities for cross-selling and targeted market development.
Ultimately, the findings point toward a more balanced approach to e-commerce decision-making: Grow revenue, but understand the costs required to generate it.
By combining sales performance, product economics, shipping behavior, and geographic customer data, the business can make more informed decisions about which products to prioritize, where to focus marketing, when to promote, and where operational costs can be reduced.
Implementing these strategic and operational improvements can help reduce avoidable costs, improve fulfillment efficiency, and strengthen revenue performance — creating potential for sustained quarterly improvement in fulfilled gross profit.
Relevant Links:
Access all files and information related to this project in my Google Drive Link and Notion Portfolio
Connect with me on Linkedin.
