Build Your First E-Commerce Profitability Dashboard with AI
Sales tell you what customers bought. Profitability tells you whether you should sell more of it.
Your Shopify dashboard says sales are up.
Amazon shows a 5X ROAS.
Walmart orders increased.
Your best-selling product just had its strongest month of the year.
Everything looks great.
But there’s one question those numbers don’t automatically answer:
Did you actually make more money?
For an e-commerce business, revenue is only the beginning of the calculation.
Product costs, marketplace commissions, advertising, shipping, fulfilment, discounts, payment fees and returns can dramatically change the economics of a sale.
In this Practical Workshop, which continue on from yesterday’s article, we’re going to build a simple E-Commerce Profitability Dashboard using a spreadsheet and AI.
You don’t need a complicated business intelligence system.
Let’s start with the numbers you already have.
Step 1: Understand the Number We’re Trying to Find
Suppose you sell a product for:
$200
It costs you:
$90 to purchase.
It would be tempting to say:
$200 − $90 = $110 profit.
But then you discover:
Marketplace fee: $30
Advertising: $24
Shipping: $15
Fulfilment: $6
Payment/other fees: $4
Now:
$200 − $90 − $30 − $24 − $15 − $6 − $4 = $31
That’s a very different business.
For this workshop, we’ll call that remaining amount your Contribution Profit.
A simplified formula is:
Revenue − COGS − Marketplace Fees − Advertising − Shipping − Fulfilment − Payment Fees − Discounts/Returns = Contribution Profit
It isn’t necessarily your final accounting net profit because it doesn’t include every business overhead such as salaries, rent, software and professional fees.
But it gives you something extremely useful:
How much did this product or channel contribute after its major variable selling costs?
Step 2: Create Your Master Spreadsheet
Open Excel or Google Sheets.
Create columns for:
| Metric | What It Tells You |
|---|---|
| Month | Reporting period |
| Channel | Shopify, Amazon, Walmart, etc. |
| SKU | Product identifier |
| Product | Product name |
| Units Sold | Sales volume |
| Revenue | Sales generated |
| COGS | Product cost |
| Marketplace Fees | Channel commissions |
| Payment Fees | Transaction costs |
| Advertising | Ad spend |
| Shipping | Shipping expense |
| Fulfilment | Pick/pack/FBA-type costs |
| Discounts | Promotional cost |
| Returns | Refund/return impact |
| Contribution Profit | What remains |
| Contribution Margin | Profit as % of revenue |
| ROAS | Revenue ÷ advertising |
Don’t worry if you cannot populate every field yet.
Start with what you know.
The missing information itself is useful because it shows where your business currently has a data gap.
Step 3: Add Your First Profit Formula
Suppose:
Revenue is in column F.
COGS is G.
Marketplace fees are H.
Payment fees are I.
Advertising is J.
Shipping is K.
Fulfilment is L.
Discounts are M.
Returns are N.
Your simplified Contribution Profit formula becomes:
=F2-G2-H2-I2-J2-K2-L2-M2-N2
Then calculate Contribution Margin:
=O2/F2
Format it as a percentage.
Now something interesting starts happening.
A product with:
$50,000 revenue
may produce:
$4,000 contribution profit.
Another product with only:
$25,000 revenue
may generate:
$7,500 contribution profit.
Which one deserves more attention?
Revenue alone wouldn’t have told you.
Step 4: Add ROAS, But Don’t Let It Fool You
Calculate:
ROAS = Revenue ÷ Advertising Spend
Suppose:
Revenue = $10,000
Advertising = $2,000
ROAS = 5X
Excellent?
Maybe.
Now include:
COGS = $5,000
Marketplace fees = $1,500
Shipping and fulfilment = $900
Advertising = $2,000
Contribution = $600
That doesn’t necessarily make the campaign bad.
But it changes the conversation.
Instead of asking:
“Which campaign has the highest ROAS?”
ask:
“Which campaigns generate the strongest contribution profit after advertising and selling costs?”
That’s a much better business question.
Step 5: Compare the Same Product Across Channels
This is where the dashboard becomes extremely powerful.
Imagine the same coffee machine sells on three channels.
| Channel | Revenue | Contribution Margin |
|---|---|---|
| Shopify | $18,000 | 24% |
| Amazon | $31,000 | 11% |
| Walmart | $14,000 | 18% |
Amazon is clearly the biggest sales channel.
But Shopify generates the strongest margin.
Now you have a strategic question:
Should we try to grow Shopify while continuing to use Amazon for volume?
That is very different from saying:
“Amazon is our best channel because it has the most sales.”
Your dashboard should help expose these trade-offs.
Step 6: Build Your Channel Profitability Matrix
Create a second sheet called:
Channel Summary
Use your spreadsheet’s Pivot Table feature to summarize:
| Channel | Revenue | Ad Spend | Fees | Contribution | Margin |
|---|---|---|---|---|---|
| Shopify | $ | $ | $ | $ | % |
| Amazon | $ | $ | $ | $ | % |
| Walmart | $ | $ | $ | $ | % |
| Best Buy | $ | $ | $ | $ | % |
Now you can immediately compare channels.
But don’t automatically conclude that the highest-margin channel deserves all your resources.
Consider:
- Sales volume
- Customer acquisition
- Organic visibility
- Inventory
- Operational workload
- Return rate
- Growth potential
Profitability is a decision input, not the only decision.
Step 7: Create a Top-10 SKU Profitability Matrix
Now create another view for your ten most important products.
Include:
Revenue
Units
Contribution Profit
Contribution Margin
ROAS
Return Rate
Inventory
Now classify each SKU.
High Sales + High Profit
Scale
These are your strongest products.
High Sales + Low Profit
Fix
Investigate fees, discounts, advertising or costs.
Low Sales + High Margin
Grow
These products may deserve more traffic.
Low Sales + Low Profit
Question
Do they deserve inventory, advertising and management attention?
Suddenly, your dashboard isn’t merely describing the business.
It is helping you allocate resources.
Step 8: Add Inventory to the Dashboard
Profitability without inventory can also mislead you.
Imagine AI recommends:
“Increase advertising for Product A.”
Great.
Except Product A has only 12 units remaining.
Now the recommendation doesn’t make sense.
Add:
- Inventory available
- Average monthly unit sales
- Weeks of inventory
- Reorder lead time
Now AI can consider whether a growth opportunity is actually executable.
For example:
Product A has strong contribution margin and conversion, but current inventory represents only approximately two weeks of sales. Increasing advertising before replenishment could create a stockout.
That’s much better analysis.
Step 9: Give the Spreadsheet to AI
Now comes the interesting part.
Upload your completed workbook to an AI assistant that can analyze spreadsheet data.
Don’t simply ask:
“What do you think?”
Give AI a specific role.
Try:
“Act as an e-commerce profitability analyst. Analyze this workbook by SKU and sales channel. Identify where revenue and contribution profit tell different stories. Highlight unusually high costs, low-margin products, inefficient advertising, strong products with growth potential and possible inventory risks. Support every conclusion with numbers from the data. Do not invent explanations when the data does not prove causation.”
This is much more likely to produce useful analysis.
Step 10: Ask AI Five Questions
Once AI understands your data, ask these:
1. Which products generate the most contribution profit?
Not revenue.
Profit contribution.
2. Which high-revenue products have surprisingly weak margins?
These deserve investigation.
3. Which low-volume products have strong economics?
These may deserve more exposure.
4. Which channel is most profitable for each major SKU?
The answer may differ by product.
5. Where are we spending money without receiving enough contribution?
This can reveal advertising, promotion, shipping or marketplace-fee problems.
Now AI is helping management interrogate the business.
Step 11: Build Your Dashboard
Your visual dashboard doesn’t need 25 charts.
Keep it focused.
At the top, show:
Total Revenue
Contribution Profit
Contribution Margin
Orders
Advertising Spend
ROAS
Then add:
Revenue by Channel
Contribution Profit by Channel
Top 10 Products by Revenue
Top 10 Products by Contribution
Low-Margin Alerts
Inventory Risks
Month-over-Month Change
The most interesting comparison may be:
Top Products by Revenue
versus
Top Products by Profit
Put them beside each other.
If those lists look very different, you have something worth investigating.
Step 12: Add Simple Management Alerts
Your dashboard becomes even more useful when it tells you what deserves attention.
Create simple rules such as:
Contribution Margin < 10% → Review
ROAS declining significantly → Investigate
Returns above target → Review
High margin + low traffic → Growth Opportunity
Strong sales + low inventory → Reorder Risk
High revenue + low contribution → Margin Alert
Then ask AI to summarize only the exceptions.
This follows a useful operating principle:
AI handles repetition. Humans handle exceptions.
You shouldn’t spend Monday morning examining 300 healthy SKUs.
You should spend it investigating the 12 that need a decision.
Step 13: Ask AI to Explain Before It Recommends
This is one of the most important rules when using AI for business analysis.
Don’t accept:
Increase advertising on Product A.
Require:
Product A generated $12,400 in revenue and $3,100 contribution profit this month. Its 25% contribution margin is above the portfolio average, conversion remains strong, and approximately ten weeks of inventory are available. Testing increased advertising may therefore be justified.
Now you can evaluate the recommendation.
Use this sequence:
Number → Change → Interpretation → Recommendation
AI should show its reasoning through the business data.
Step 14: Turn the Dashboard Into Your Monthly Management System
Once the dashboard works, don’t rebuild it every month.
Create a standard workflow:
Export sales → Export ads → Update costs → Update inventory → Refresh dashboard → AI analysis → Human review → Action plan
Your monthly management meeting can then focus on:
What changed?
Why?
Which products need attention?
Where should we invest?
Where should we pull back?
What are the priorities this month?
Eventually, parts of the workflow can be automated.
But build it manually first.
Understand the numbers before automating the decisions.
Bonus: Build an AI Management Layer Above the Dashboard
Once your data is clean and consistent, you can go one step further.
Instead of opening ten reports every morning, imagine asking:
“What needs my attention today?”
Your AI system could examine:
Sales
Profitability
Advertising
Inventory
Returns
Conversion
and respond:
Product A sales increased 28%, but contribution margin fell from 19% to 12% because advertising cost increased.
Product B has strong margin and conversion but traffic is down 17%.
Product C has approximately 1.8 weeks of inventory remaining.
Walmart sales increased 14%, but contribution declined because marketplace fees and promotional discounts increased.
Now AI isn’t replacing the dashboard.
It is becoming the analysis layer sitting above it.
That’s where e-commerce reporting becomes much more interesting.
The Goal Isn’t More Data
Most e-commerce businesses don’t need another dashboard filled with charts.
They need better answers.
Which products actually make money?
Which channels deserve investment?
Which advertising campaigns are creating profitable growth?
Which products look successful but aren’t?
Where are we losing margin?
What deserves attention right now?
A good profitability dashboard helps answer those questions.
AI makes the dashboard even more valuable because it can examine hundreds or thousands of rows, identify unusual patterns and help management decide where to look.
But remember:
AI doesn’t make bad data good.
Build the financial logic first.
Keep your product and cost information accurate.
Then let AI help you understand it faster.
Because the goal isn’t to have the most impressive dashboard.
The goal is to make better e-commerce decisions.
Not Sure Where to Start?
If your sales are spread across Shopify and multiple marketplaces but you’re not sure which products and channels are actually generating the strongest returns, NorthPilot Digital Agency can help.
We can help structure your e-commerce data, build channel and SKU profitability reporting, identify the metrics that matter, and create AI-assisted workflows that turn your numbers into practical business decisions.
Contact NorthPilot and let’s find out where your e-commerce profit is really coming from.