Van of Pamono Analytics
1/15/2020 - 3:26 PM
Sell through rate
Based on number of uploads and number of sales (NOTE here that it is not the quantity of products sales, rather as among the products have been uploaded, how many have been sold) with categorized as Category, Style..
WITH sales_table AS (
SELECT
a."month",
a."category",
a."subcategory",
a."price_range",
a."design_period",
a."vintage_contemporary",
COUNT(a."order_id_sku") AS "sales"
FROM
(select mp."sku",
"vintage_contemporary",
"design_period",
IFF("first_category"='Furniture',"second_category","first_category") AS "category",
IFF("first_category"='Furniture',"third_category","second_category") AS "subcategory",
CONCAT(YEAR(DATE_TRUNC('MONTH',"enabled_date")),CONCAT('_M_',CONCAT(IFF(LENGTH(MONTH(DATE_TRUNC('MONTH',"enabled_date")))='1','0',''), MONTH(DATE_TRUNC('MONTH',"enabled_date"))))) AS "month",
IFF("price_euro"<'500','0-499',
IFF("price_euro"<'1000','0500-999',
IFF("price_euro"<'1500','1000-1499',
IFF("price_euro"<'2000','1500-1999',
IFF("price_euro"<'3000','2000-2999',
IFF("price_euro"<'5000','3000-4999','>5000')))))) AS "price_range",
"order_id_sku",
mpo."order_id",
mo."order_date"
FROM magento_product AS mp
JOIN magento_product_order AS mpo
ON mp."sku" = mpo."sku"
JOIN magento_order AS mo
ON mpo."order_id" = mo."order_id"
LEFT JOIN magento_credit_memo_item As mcmi
ON mpo."order_id" = mcmi."order_id" AND mp."sku" = mcmi."sku"
WHERE (mcmi."credit_memo_reason" != 'Fraud' AND mcmi."credit_memo_reason" != 'Test Order') OR mcmi."order_id" IS NULL
AND mo."order_id" IS NOT NULL AND "vendor_name" != 'Pamono GmbH'
AND DATEDIFF(DAY, "enabled_date", mo."order_date") <= 30) AS a
GROUP BY a."month",
a."category",
a."subcategory",
a."price_range",
a."design_period",
a."vintage_contemporary"
), uploads AS (
SELECT "month",
"vintage_contemporary",
"design_period",
"category",
"subcategory",
"price_range",
COUNT("sku") As "uploads"
FROM (select "sku",
"vintage_contemporary",
"design_period",
IFF("first_category"='Furniture',"second_category","first_category") AS "category",
IFF("first_category"='Furniture',"third_category","second_category") AS "subcategory",
"material",
"color",
"category" AS "Category/Sub Category",
CONCAT(YEAR(DATE_TRUNC('MONTH',"enabled_date")),CONCAT('_M_',CONCAT(IFF(LENGTH(MONTH(DATE_TRUNC('MONTH',"enabled_date")))='1','0',''), MONTH(DATE_TRUNC('MONTH',"enabled_date"))))) AS "month",
IFF("price_euro"<'500','0-499',
IFF("price_euro"<'1000','0500-999',
IFF("price_euro"<'1500','1000-1499',
IFF("price_euro"<'2000','1500-1999',
IFF("price_euro"<'3000','2000-2999',
IFF("price_euro"<'5000','3000-4999','>5000')))))) AS "price_range"
FROM magento_product
WHERE "enabled_date" IS NOT NULL AND "enabled_date" >= '2018-01-01')
GROUP BY "month",
"vintage_contemporary",
"design_period",
"category",
"subcategory",
"price_range" )
SELECT uploads."month",
uploads."vintage_contemporary",
uploads."design_period",
uploads."category",
uploads."subcategory",
uploads."price_range",
uploads."uploads",
sales_table."sales",
sales_table."sales"/uploads."uploads" AS "sell_through_rate"
FROM uploads
LEFT JOIN sales_table
ON uploads."month" = sales_table."month"
AND uploads."vintage_contemporary" = sales_table."vintage_contemporary"
AND uploads."design_period" = sales_table."design_period"
AND uploads."category" = sales_table."category"
AND uploads."subcategory" = sales_table."subcategory"
AND uploads."price_range" = sales_table."price_range"
ORDER BY uploads."month",
uploads."vintage_contemporary",
uploads."design_period",
uploads."category",
uploads."subcategory",
uploads."price_range"