Highest data quality, a Single Source of Truth and immediate access for the entire organisation through our data platform
Vivara
Lightspeed eCom Power BI dashboard
We bring orders, products and customers from all your Lightspeed eCom shops into a single Power BI model. For Vivara, we built a data platform using webshop data, among other sources.
Vivara brings its online shop, ERP and SharePoint together in one data platform with one data model
Vivara wanted its data in one central place, with the highest data quality. Our data platform pulls data daily from sources such as SAP, Magento and SharePoint. With a bespoke data model, teams from finance to marketing analyse their data.
Why do we have to log in to each shop to see how much we’re earning?
Each Lightspeed eCom shop has its own back office and figures. If you have multiple shops or operate in several countries, you have to compare those figures manually. A Power BI model reads data from all your shops and shows revenue and margin for each shop or across them all.
A Lightspeed eCom dashboard shows margin, not just revenue
Margin per product
An order in Lightspeed eCom includes the price excluding VAT and the cost of the products. We use these to calculate margin per product and category.
All shops in one model
Each shop has its own API key. The loading step retrieves data from them all and tags each order with its shop, so you can view the data per shop or together.
Customers who come back
Track first and repeat purchases by customer, shop and period to see which products bring in new customers and which customers keep coming back.
Webshop alongside other sources
Bring orders together with inventory, accounting and marketing in one model, as we did when we combined Magento, SAP and SharePoint for Vivara.
Revenue, margin and returns are defined for each shop before we load the data
-
Shops and definitions
Which shops and languages to include, what counts as revenue, and how to handle returns and shipping costs.
-
Keys and initial model
One API key per shop, then build a single overview and check it against the back office.
-
Expand and manage
Customers, products and other sources follow the same model. Refreshes and access rights each have a designated owner.
Does the Lightspeed back office give you enough visibility, or is your own model worth it?
A good fit if
You have multiple shops or operate in several countries, want to see margins by product or customer, or want to compare your online shop figures with your accounting, inventory system or marketing data.
Not a good fit if
You have one shop and the back-office reports already answer your questions. In that case, a second layer would require more maintenance than the value it delivers.
What determines the scope
How many shops and languages are included, how much historical data you want to load, whether Lightspeed Retail or an inventory system is involved, which other sources are added and who manages the model.
Will this put a strain on our online shop?
Lightspeed sets limits per API key for each shop. We use a dedicated key, retrieve only orders that have changed and spread the refreshes throughout the day.
Lightspeed data directly in Power BI, via a loading step or in a data warehouse?
Directly in Power BI
Choose this if you have one small shop and want a few reports. It’s quick to set up, but every refresh retrieves all the data again.
A loading step in between
Choose this if you have multiple shops or a large number of orders. We recommend a loading step that retrieves and stores only changed orders for each shop; the model reads from there.
A data warehouse
Choose this if Lightspeed is one of several sources, alongside inventory and accounting. See having a data warehouse built.
We often bring webshop data together with finance and marketing in one model
Data Science Lab has been building data and AI solutions since 2016 and is a Microsoft Partner for Fabric. For Vivara, we built a data platform using Magento data, among other sources; for Ammega, we built Power BI dashboards and a Power BI app.
Your shops, languages and the figures you’re missing are the focus of our first conversation
Stef Stoelwinder
Business DevelopmentStef is your first point of contact. In our first conversation, we’ll discuss your shops, your languages and the reports you put together by hand.
E-mail info@datasciencelab.nl
Phone +31 (0)20 3636 724
How many Lightspeed eCom shops do you want to view revenue and margin for together?
Tell us how many shops and languages you have, and which reports you’re missing. We’ll reply by email or suggest an initial conversation.
Message sent
Thanks for your message. We’ll be in touch as soon as possible.
Frequently asked questions
Through the Lightspeed eCom API. You need an API key and secret for each shop, and each request goes to the cluster the shop runs on: the EU cluster or the US cluster. We load the data and build the Power BI model on top of it.
For more on how a dashboard starts with the decision it needs to support, see Power BI dashboards.
Lightspeed calls the product eCom (C-Series) in its help centre; its developer documentation calls it Lightspeed eCom. This page is about that API. Lightspeed lists eCom (E-Series) as a separate product with its own support site.
If you’re not sure which one you have, we’ll work it out together in our first conversation.
Lightspeed Retail (R-Series) is the point-of-sale system. If eCom and Retail POS are connected, Lightspeed says Retail POS is the source for products and recommends the Retail API for product management.
For reporting, that means in-store sales and products come from Retail, while webshop orders come from eCom. In the model, we specify which source counts for each figure.
The API returns an item in the language specified in the request, provided that language is active in the shop. We therefore retrieve product names for each active language and link them to the same product.
Each shop has its own keys. The loading process retrieves data from the shops one after another and records the shop for each order.
Yes. The webshop knows what was sold and at what price; Picqer knows what was picked, shipped and returned. One model lets you see both side by side. See the Picqer Power BI dashboard.
We link orders by order number and agree which status counts as shipped for each source.
Usually revenue and margin by shop, category and product; paid versus unpaid orders; new versus returning customers; and the best- and worst-performing products by language or country.
For cash flow and budget versus actuals, see the financial dashboard in Power BI. If you want to go beyond repeat purchases to look at customer value, see customer analysis.
We store the API keys used to load the data outside the report, and retrieve only what’s needed. In Power BI, row-level security limits what each person can see: for example, a country manager sees only their own shop.
For how we set up workspaces, apps and refreshes, see Power BI consultant.
When you have one shop and the back-office reports are enough. Or when product costs are missing or incorrect in Lightspeed: the margin will then be wrong, and you need to start with data quality.
If a regular monthly export is enough, we’ll tell you that too.