Power BI for Ecommerce Businesses: Margin by Product, Channel and Order

An online seller's numbers are spread across systems that were never meant to agree: the storefront for orders and refunds, Amazon Seller Central for marketplace sales and fees, the ad platforms for spend, a 3PL or shipping tool for fulfilment cost, and the accounting system for what actually reached the bank. Power BI for ecommerce businesses is the work of joining those into one model, so that margin by product, by channel and by order comes from the same numbers every morning instead of a spreadsheet rebuilt every month.

This page is for brands that sell mainly online, through their own store, marketplaces or both. If you run physical stores and your data starts in a point-of-sale system, our retail analytics consulting page covers that work. The general engagement with no industry attached is our Power BI consulting services page.

What should an ecommerce business measure in Power BI first?

Start with contribution margin by product and channel: revenue after discounts and refunds, less product cost, shipping, payment and marketplace fees, and the ad spend that drove the sale. It is the number most sellers cannot see today, and it changes decisions about which products to push, which to reprice and which channels to cut.

Around margin sit the growth and stock measures. Blended return on ad spend tells you whether marketing pays for itself when every platform's double counting is set aside. Repeat purchase rate and cohort revenue tell you whether this month's new customers are worth what they cost. Days of cover by SKU tells you whether the best seller will be out of stock before the next container lands.

• Contribution margin by product, variant, channel and order, before and after ad spend
• Blended ROAS and marketing efficiency ratio alongside each platform's own reported figure
• New versus returning customer revenue, repeat purchase rate and cohort value by first-order month
• Refund and return rate by SKU and reason, with the cost of each return counted
• Days of cover, stockouts and slow-moving stock across your own warehouse, 3PL and FBA
• Marketplace fees and payouts reconciled to the orders behind them

Why platform dashboards disagree with each other

Shopify analytics, Seller Central reports and each ad platform are accurate about their own slice and silent about everything else. The storefront knows the order and the discount but not what the ad cost. Meta and Google each attribute the sale to their own campaign, so adding their reported revenue together counts some orders twice. Amazon's business reports show sales, while the fees that decide whether those sales were worth having sit in settlement reports on a different schedule.

There is also a definitional problem that no single tool solves. Is revenue gross or net of refunds? Is a sale counted on the order date or the ship date? Does margin include the packaging and the payment fee? A brand that has not settled those rules gets a different answer from every report. Writing them down once, inside a shared semantic model, is most of the work of making ecommerce reporting trustworthy.

• Ad platforms attribute the same order to more than one campaign
• Marketplace fees and payouts arrive in settlement reports, not alongside the sale
• Refunds land days or weeks after the order and quietly restate last month
• Product cost changes with every purchase order, and most storefronts hold only today's cost

Getting Shopify, Amazon and ad data into Power BI

Plainly: Power BI does not ship a built-in connector for Shopify or Amazon Seller Central, and several ad platforms are in the same position. The data reaches the model through the platform's API, a third-party connector service, or scheduled exports landed in a database or a Microsoft Fabric lakehouse first. Each route has an ongoing cost, in subscription fees or in maintenance, and that cost belongs in the decision rather than in a surprise invoice later.

We confirm the route for each source before any report is designed, because it decides how much history you can load, how often the data refreshes and what the setup costs to run. A brand already paying for a connector tool usually keeps it, and the model is built on what it lands. A brand starting from CSV downloads gets a landing process that is repeatable without someone exporting files every Monday.

One model for every channel and every order

The shape that holds up is a star schema. Order lines, refunds, ad spend, fees and inventory snapshots are fact tables; products, channels, customers, campaigns and a calendar are shared dimensions. The work that makes it useful is the mapping: the same product must carry the same key whether it sold on your store, on Amazon or through a wholesale order, and every campaign must map to a channel the finance team recognises.

With that in place, one filter moves every visual on the page, and a measure such as contribution margin after ad spend is defined once for every report. Product cost is held by date, so a price rise on the last purchase order does not rewrite last year's margin. Ad spend is allocated to orders by a rule your team agrees, labelled as an allocation, and shown next to the platforms' own claims rather than replacing them.

• Map storefront, marketplace and wholesale SKUs to one product key
• Hold product cost by effective date, not just the current value
• Bring marketplace fees in from settlement data and tie them to the order
• Snapshot inventory daily so days of cover and stockouts have real history

How much does Power BI for an ecommerce brand cost?

Ecommerce dashboard projects are quoted per business, because the number of channels, the connector route and the state of the product cost data move the price too much for a list figure to be honest. Our Power BI implementation cost guide explains what each part of a first project buys.

If you have someone in-house who can maintain reports and you want senior help in blocks rather than a fixed project, Power BI Flex Support starts at $2,500 for 20 hours, at $100–$125 per hour, in prepaid blocks whose hours do not expire. On Power BI work we offer one guarantee: if you don't like working with us within the first 4 hours, you get your money back.

Every engagement is run remotely by Edward Pinela, MBA, Principal Consultant at Lightray Solutions. The person who scopes the work is the person who builds it. The first conversation is about access and definitions rather than visuals: which platforms you sell on, where product cost lives, and how your business wants to count revenue and margin. From there the pattern is a working prototype on your own data, reviewed with the people who will use it, then extended one source at a time.

Further reading: Microsoft Learn — star schema guidance for Power BI.