Question

Grouping product skus


Hi,


I have the following problem and if anyone has a clue how to solve this it would be amazing.


The Model:

A lingerie set called B has 10 sizes (SKUS).

I want to have a report that displays:


Style B: Total Aug Ordered 2999 | Total Ordered Sept 4000 | etc

Style C: Total Aug Ordered 1000 | Total Ordered Sept 2000 | etc


The table schema is below:

Table products - contains main products (products with names, description, pictures) and also skus (sizes)

id

name

sku


Table product_sizes (Links a main product to its children sizes)

product_id -> foreign to products

size_id -> foreign to products


Orders:

product_id -> foreign to products (size/sku only)

qty ordered


Thank you,

Andrei


11 replies

Do you have a SQL query that already solves the problem?

What’s Style B and C?

Which database are you on?

I do not see any “timestamps” in any tables, but believe it would be on the Orders table.

I’m not really sure I understand the problem thoroughly but here is what I suggest:q


On postgres:


SELECT date_trunc('month',orders.created_at)
, p.id
, size_id
, sum(qty_ordered)
from products p
join product_size ps on ps.product_id = p.id
join orders o on o.product_id = p.id
group by 1,2

and I’d pivot STYLE with p.id


You could use a derived table or easily create an explore for those tables.


I hope that helps.

Userlevel 6
Badge

Once you have generated your model, you should be able to just run this query without any LookML programming.


Here is a very similar example on https://learn.looker.com using our ecommerce model.


I started from order_items (items sold) and simply filtered orders by the past 2 complete months, I’ve chosen ‘category’ instead of ‘style’ but I can see how many have sold each month, how many different brands of each item (for you, this might be sizes).


<img src=

>


Here is a link to the Look


https://learn.looker.com/x/yb7pdV5


Please mail help.looker.com if you would like an account on learn.looker.com

Hi Fab,


I was focusing so much that I didn’t realise I didn’t explain it well. Sorry for that.


I am using mysql.


Table orders is actually order_items.

Style B and Style C is the product name. (Eg. Andra), Andra is sold in 3 sizes, S,M,L.


I want to know the total sold for Andra (sales for S + sales for M + sales for L).


The query would be:


SELECT p.name,

MONTH(order_item.created_at),

sum(order_item.ordered_qty)

FROM products p

JOIN product_size ps ON ps.product_id = p.id

JOIN order_item oi ON oi.product_id = ps.size_id – I get the sales for each size.

GROUP BY p.id, MONTH(purchase_order_product.created_at)


I do not think this helps me. I looked and I know that setup. Or maybe it does and I am not aware.


That setup doesn’t have the concept of sizes for a product.


We sell these simple sizes which are stored in the warehouse.

On the fronted we have the main product (name + image + description) and you are able to select the size you want.


When creating the report I want to know the total ordered for each main product.


Thank you for your help!

why not grouping together size and product name?


p.name || size_id ?


you would get something like


                MONTH 1 | MONTH 2 | ...
Andra S 1000 ...
Andra M ...
Andra L

Unfortunately this is not helpful, because some one would need to export the numbers to excel, cumulate the sizes to achieve the information needed.


Also there are a bunch of reports we will have to do using the same criteria, aggregate sales, purchases orders quantities by main product. To knowing best selling styles.

There are many KPIs around main products and not sizes.


Thanks,

Andrei

Continuing the discussion from Grouping product skus:


Coming back to what Lloyd suggested then,



could not you add a new dimension for sizes, and then add it to your table?


Following the example, you would get:


                  | Month   1 | Month 2 | ... 
product | size | count |count
Shorts | S | 80 | ...
Shorts | M | 18 | ...

Thank you, but this is useful if I want to make an analysis to size level.


Best,

Andrei

Userlevel 3

I think I might understand the disconnect here. The grouping is happening on the product.id, but a product.id implies a combination of a product name and a size. If you did the grouping by product name instead, you would get the result you were looking for.


This means that your dimension should be the product name, your pivot dimension would be the month, and your measure would be the sum of qty sold.

Grouping by name doesn’t help. There are products with the same name, but different colors, new versions etc.


The query below is the only way solving this and I can’t see how I can reproduce it in looker.


SELECT p.name,

MONTH(order_item.created_at),

sum(order_item.ordered_qty)

FROM products p

JOIN product_size ps ON ps.product_id = p.id

JOIN order_item oi ON oi.product_id = ps.size_id – I get the sales for each size.

GROUP BY p.id, MONTH(purchase_order_product.created_at)

I managed to achieve what I want by

adding a join to product_size table with order items


- join: order_items
relationship: one_to_many
sql_on: ${order_items.product_id} = ${product_sizes.size_id}

and adding a join on products table:


- join: product_sizes
relationship: one_to_many
sql_on: ${product_sizes.product_id} = ${products.id}

Then from explore I select Product Sizes, select product name, pivot by order_item created month,


Best,

Andrei

Reply