revenue growth and product performances Dashboard
revenue growth and product performances Dashboard
SELECT
o.user_id,
o.id AS order_id,
TO_TIMESTAMP(
o.created_at,
'YYYY-MM-DD HH24:MI:SS'
) AS timestamp,
EXTRACT(
week FROM
TO_TIMESTAMP(
o.created_at,
'YYYY-MM-DD HH24:MI:SS'
)
) AS week,
p.name AS product,
COALESCE (
p.discounted_price,
p.price
) AS price,
CASE d.discount_type
WHEN 'amount'
THEN d.discount_value
WHEN 'percent'
THEN 0.01 * d.discount_value * COALESCE (p.discounted_price,p.price)
ELSE 0
END AS discount,
CASE
WHEN o.status = 'FAILED'
THEN 0
WHEN d.discount_type = 'amount'
THEN COALESCE (p.discounted_price,p.price) - d.discount_value
WHEN discount_type = 'percent'
THEN (100 - d.discount_value) * COALESCE(p.discounted_price,p.price)*0.01
ELSE COALESCE (p.discounted_price,p.price)
END AS revenue
FROM products AS p
INNER JOIN orders AS o
USING (sku)
LEFT OUTER JOIN discounts AS d
ON o.discount_code = d.code
Upon comparing total revenues and revenues by products, a lot of negative revenue is found due to the case when discount value exceed the product price. As there's no explanation whether the system allow negative revenue or not, a specific parameter is used to enable the option to transform negative revenue to zero.
More insights about the type of orders, active users and total discount value used is added to give more insights on the sales performance.