Hi Everyone,

I'm guessing with the fairly limited amount of reporting that LW.net produces that there is no such brand report?!

We have an extended property on each sku with the product brand on it. I would like to be able to see sales data for the brands, in order to get an idea of what brands do well etc etc. What would be even better would be a stock value next to it as well to get a really good breakdown of performance.

Anyone got any ideas, I know if I ask LW tech support I will get the standard "have Linnworks to write the script for you which is a chargeable service".

Thanks

Will

Will, 

Are your brands not your Linnworks categories? 

What we do is multi tier categories that includes the brand. 

Brand > Item Sub Type > second sub type if applicable. 

Then when we export sales reports (which they all have category I think) we can text to column in excel and group by brand / sub type etc

A few years ago we also took this one stage further and added an extended property for stock item parent so we could see the amount of sales on a product level as well as individual SKU level 

See this thread recently where I explain more - https://help.linnworks.com/support/discussions/topics/7000026451

We needed to get a custom query written to be able to pull out all the data we wanted but we have been using that query for about 6 years and it speeded up our reporting by about 30% by not having to combine multiple reports into one 

It was one of the best £120 we ever spent!! 

Daniel 

Hello Will,

This is Ethan from HyperCommerce. It is possible to write a custom query which will let you get sales in the last x period for a particular brand.

I can either provide you the query for a one-off charge or help you learn how you can build your own custom queries.

Here's a sample query to help you get started

SELECT 
SUM(nQty) AS 'Total Units Sold', 
COUNT(nQty) AS 'Total Orders', 
SUM (SubTotal + fTax) AS 'Sales in the last month', 
e.ProperyValue AS 'Brand'
FROM [Order] o
INNER JOIN [OrderItem] oi ON oi.fkOrderId = o.pkOrderId
INNER JOIN [StockItem_ExtendedProperties] e ON e.fkStockItemId = oi.fkStockItemId_processed
WHERE bProcessed = 1 
AND dProcessedOn > DATEADD(month, -1, GETDATE())
AND e.ProperyName = 'Brand'
GROUP BY e.ProperyValue

Results will look something like this

If you need custom solutions such as Advanced SQL Queries, Custom Reports, API Solutions or Integrations, please feel free to reach me at ethan@hypercommerce.co.uk

Thanks Ethan, that helped me create this query that does the job for now....

SET DATEFORMAT  YMD;

DECLARE
@FromDate AS DATETIME,
@ToDate AS DATETIME


--Change the number at the end to the number of days from, e.g ‘-365’ for year to date or '2017.11.27 00:00:00' for date rage.
SET @FromDate = GETDATE()-365
--

SET @ToDate = GETDATE()

SELECT
SUM(oi.nqty) AS 'Total Units Sold', 
COUNT(o.norderID) AS 'Total Orders', 
SUM(oi.CostIncTax)  AS 'Revenue', 
SUM(oi.fPricePerUnit)  AS 'Purchase Price', 
SUM(oi.CostIncTax - oi.fPricePerUnit)  AS 'Profit', 


-- This is the column title, change this to the Item Extended Property name want to query, e.g. ‘Brand’.
e.ProperyValue AS 'Brand' 
--

FROM [Order] o
INNER JOIN [OrderItem] oi ON oi.fkOrderId = o.pkOrderId
INNER JOIN [StockItem_ExtendedProperties] e ON e.fkStockItemId = oi.fkStockItemId_processed
WHERE o.dProcessedOn BETWEEN @FromDate AND @ToDate

-- Change this to the Item Extended Property name to want to query, e.g. ‘Brand’.
AND e.ProperyName = 'Brand'
--

GROUP BY e.ProperyValue