6

we have multiple config products SHIRTS (option is size, S, M, L etc). This results in simple products with SKU's like SOCKS.WHITE.S, SOCKS.WHITE.M, etc

And this question has always boggled me: does Magento not provide a way to report SKU's sold per timeframe?

And ofcourse I am wondering how we can add this (maybe through a free extension or some code). We want to know what selss best per size per period ....

When I look at reports it is showing me the numbers per config.product and not per SKU

Many thanks, Sean

6

I logged in php MYadmin and executed this query in SQL window.

SELECT name, sku, sum(qty_ordered), sum(row_total) 
FROM sales_flat_order_item 
WHERE price > 0 
AND created_at > "2013-01-01 00:00:00" 
GROUP BY sku 
ORDER BY sku 
LIMIT 1000

After this I saved as new VIEW CSALES_PER_SKU

Improvements to query are welcome. Would be interested in adding store view/name

  • 2
    This query doesn't take into account time zone offset, which may incorrectly report some orders as having happened on the wrong dates – philwinkle Mar 28 '13 at 15:14
  • Note that the default table name is sales_flat_order_item - there appears to be a prefix in use here. – benmarks Jun 4 '13 at 0:28
3

Not sure if this answers your question, but I worked on this issue during the recent Magento Bugathon in Zürich. My pull request has been merged in master, so it should be there when Magento 1.8 is released - possibly during or right after Imagine 2013.

FYI, here is the entry in the issue tracker and the commits that fixes it. Maybe you can use some of the code to write your own module.

https://github.com/magento/bugathon_march_2013/issues/147

*Update Dec 04, 2014: Unfortunately this commit wasn't included in 1.8 or 1.9.0.1.

  • Woohoo. Cant wait for 1.8. And I really do think it is cool that an 1.8 vesion is made (in between moving to 2.0) – snh_nl Mar 28 '13 at 13:37
  • 1
    It could signal that 2.0 is much farther off than we hope... – philwinkle Mar 28 '13 at 15:13
  • @fmrng I was looking at index.php/admin/report_product/sold and did not see the sku column – snh_nl Mar 16 '14 at 10:31
1

Its not a free extension but you can check http://mamoc.com/product-sales-information. With this extension you can get more detailed sales information (sales last week/2 Weeks/Month/Quarter/Half Year/Year and total).

1

** We (now) use aheadworks advanced reports - has nice graphs

** and custom made scripts that report in EXCEL and from there we use pivot table to do all the magic (be aware we use table prefix)

  1. for stock

https://gist.github.com/seansan/aab2707e596b7ce5748b

  1. for sales (and copies the file several times for always, YTD, last 3 months, last month)

https://gist.github.com/seansan/ebe61b82f6ce5863fa87

1

Here's a free extension that exports detailed order info including sku: http://www.magentocommerce.com/magento-connect/blue-jalappeno-order-export.html

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.