The Ultimate E-Commerce Profitability Model
Customer File Segmentation
Send me an export of your customer file and I’ll find your best customers in a matter of hours.
If I could have any superpower, it wouldn’t be flying or invisibility; it would be omniscience. I want to know everything. I want to see the hidden mechanics behind why something works, the root cause of why it fails, the invisible chain that connects one decision to the next. I’ve always had this obsession with finding the ultimate truth in business, the place where the numbers line up and everything finally makes sense.
In e-commerce, understanding product-level profitability is about as close as we can get to that truth. You can analyze traffic sources, campaign performance, personas, audience segments, whatever you want, but until you break down the business all the way to the product level, you’re just looking at reflections of reality, not reality itself.
I’ve spent years trying to get there. At one company, I built a system that pulled daily data from UPS, CRMs, Google Ads, Meta, and our order management platform so that we could see, almost in real time, which products were actually profitable once you factored in everything: the cost of goods, marketing costs, shipping, and returns. It took months to automate. But we didn’t start that way.
We started with a spreadsheet.
And that spreadsheet, simple, ugly, manual, became our bible, or Torah, if you will. Every week we’d update it by hand. It was the one report that every team trusted. The category managers used it to figure out where their margins were slipping. The traffic team used it to see which products actually deserved paid support. The optimization team used it to prioritize their experiments. It was the closest thing we had to truth, and it changed how we worked. This report got the e-commerce team to be literally on the same page.
The document you can download alongside this post is a version of that same sheet. It’s simple, but it forces clarity. Here’s how it works, and what each section tells you.
Product Information, Revenue, COGS, and Marketing Costs for calculating profitablity
Product Information
Start by grounding everything in clear, reliable identifiers: product name, SKU, and product category. This sounds trivial, but most businesses fall apart right here. If your systems don’t speak the same product language, if your ad platform, your analytics, and your order management system all call the same item by different names, every analysis that follows is corrupted. Unifying this data is boring, unglamorous work, but it’s the foundation. Without it, you’ll never find the truth you’re looking for.
Revenue
Once the foundation is solid, you can look at revenue. You want to track the number of visitors to each product, the quantity ordered, and the product and shipping revenue associated with each SKU. From there, calculate a simple but incredibly revealing metric: Visitor to Quantity Ordered Rate.
This single number exposes more about your merchandising and product experience than almost anything else. If a product attracts traffic but barely sells, that’s not a media problem. That’s a value perception problem. Maybe the photography is weak. Maybe the price positioning is off. Maybe it’s just the wrong product. But until you can see it, you’ll never fix it. This metric became the favorite tool of our category managers and onsite teams because it forced everyone to confront uncomfortable truths about what was and wasn’t resonating with customers.
Costs: COGS and Marketing
Now comes the part that makes most people squirm: costs. We all love revenue, but understanding profitability means following the money out as carefully as we follow it in.
Start with cost of goods sold. Then layer on the direct marketing costs—Google Shopping, affiliates, maybe even Meta or TikTok if you can classify your spend by category or product. It doesn’t need to be perfect. We used rough percentages, allocating campaign spend based on each product’s share of total sales. It’s not accounting-grade precision, but it’s close enough to show where the opportunity to improve is.
When you finally see those numbers side by side; revenue next to COGS next to marketing; you start realizing how many products are secretly unprofitable once the real costs are accounted for. Those “hero” SKUs that drive all your volume? Sometimes they’re the ones quietly burning cash.
Shipping, Returns and Profitability for each Product
Shipping and Returns
This is where profitability quietly dies. Everyone loves to talk about CAC, CPM, ROAS, but few people want to look at the real costs of logistics and returns. Outbound shipping eats into your margins right away. Inbound returns double the pain. You lose the sale, you pay for return shipping, and sometimes you refund the entire order but sometimes you get back a restocking fee.
When we started tracking this by product, the results were humbling. Certain categories that looked fantastic on the surface, high volume, good conversion rates, turned out to be nightmares once return rates were factored in. It’s not enough to know how much you sell. You have to know how much of that revenue you keep.
Profitability Metrics
With all of those inputs in place, you can finally calculate what matters:
Gross Margin: Revenue minus COGS.
True Product Profitability: Revenue minus COGS, marketing, shipping, and return costs.
Gross Margin per Visitor: The amount of profit each product generates per site visit.
Gross Margin per Quantity Purchased: The profit per sale, fully loaded with real costs.
These metrics don’t just tell you how healthy your business is—they tell you where to focus. When we first ran this report, we found that several of our top-selling products were actually losing money. Seeing that in black and white changes you. It changes what you promote, what you bid on, what you restock. It changes how you think.
Where to begin and operationalize
Just as we did in the past, I recommend starting by manually filling out this sheet using a series of VLOOKUPS. This will allow you to understand where all this data needs to come from.
Start with revenue and COGS by product. That’s in your e-commerce system, and easy to report on. Next, add in product view data using Google Analytics or your analytics platform of choice. After that let’s integrate with your shipping provider, whether it’s UPS, FedEx or use a tool like Shipstation.
You’re starting to get most of the costs, so let’s now add all the return information. This will be the most revealing for most teams, and help them see that not all sales are equal. Lastly, integrate individually with all of your marketing platforms. These are the most complex, but have the most API access for automation.
To automate this process, I would start with an N8N or Zapier to get all of this information into a single spreadsheet. Once you’ve figured that part out, I’d upgrade to a database and reporting software that can produce this report for custom dates on the fly. This is obviously a more complex job, which may need some database design to complete. Once you have proven that the worksheet is so powerful, it will be easy to wrangle the technical resources to fully automate the process.
Want to use this, but not sure where to begin?
If you want to see how this model could work inside your business, I’m happy to walk you through the process. We’ll look at your systems, your data sources, and figure out what it would take to connect the dots.
Grab a spot on my calendar, and we’ll unpack it together.
Want more?
Make your job easier, let Mobile1st Grow RPV for you
At Mobile1st, we help e-commerce brands grow revenue per visitor.
We do it by combining customer-first research with testing and experimentation that cuts through the noise of dashboards and opinions. Our team uncovers what really drives purchase decisions, then runs experiments to prove impact — so you can stop guessing and start scaling
Want help? Reach out.
Want to be featured on Checkin to Checkout?
Send an email to justin@mobile1st.com, and we’ll set up a time to get to know you to see if you’re a great fit. We’re always looking for e-commerce leaders to feature on Checkin to Checkout
