4

How can one get a quick list of sales/revenue per brand/manufacturer in Magento?

Something like

Brand    Sales (minus returned/cancel)
brand 1  100 euro
brand 2  200 euro

Difficulty I see is: grouping by a product attribute. Correcting for returned and cancelled. And one could add a filter over time (from date - to date)

Any help appreciated

4

We've spent some time and that is the query we got:

SELECT 
    data_table.value AS Brand, SUM(data_table.amount) AS Sales
FROM
    (SELECT 
        EAOV.value,
            (SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded) AS amount
    FROM
        eav_attribute_option AS EAO
    INNER JOIN eav_attribute_option_value AS EAOV ON EAO.option_id = EAOV.option_id
        AND EAO.attribute_id = (SELECT 
            attribute_id
        FROM
            eav_attribute
        WHERE
            attribute_code = 'manufacturer')
    INNER JOIN catalog_product_entity_int AS CPEI ON CPEI.value = EAOV.option_id
    INNER JOIN sales_flat_order_item AS SFOI ON CPEI.entity_id = SFOI.product_id
        AND SFOI.price > 0
        AND SFOI.created_at >= '2015-01-01' AND SFOI.created_at < '2015-12-31'
        ) AS data_table
GROUP BY data_table.value
ORDER BY data_table.value ASC;

You may need to adjust the math:

SFOI.row_total + SFOI.tax_amount + SFOI.hidden_tax_amount - SFOI.discount_amount - SFOI.amount_refunded

We don't pretend this query is 100% accurate.

2
  • You rock! I almost dare not asked. How would one add a DATE FROM and TO as a filter? (period of time) – snh_nl Sep 19 '16 at 11:54
  • We've updated the query. Please check. – Neklo.com Sep 19 '16 at 12:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.