Charlie Harvey

Magento tip: How many and which products are in abandoned carts

Written by Charlie on

↙

I wanted to get an abandoned carts report in Magento the other day. What I wanted to see was what SKUs were in carts and how many of each SKU. A long search turned up nothing of use, so I went straight to the database and started typing SQL. In the end this is what I got and it seems reasonably corrrect, if my assumptions are also correct. SELECT item.name, SUM (item.qty) AS total_qty, item.sku FROM sales_flat_quote AS quote, sales_flat_quote_item AS item WHERE quote.entity_id = item.quote_id AND quote.is_active = 1 AND quote.updated_at > date_add(now(), interval - 30 day ) GROUP BY item.sku ORDER BY total_qty DESC INTO OUTFILE '/tmp/cart_products.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

This will create a CSV of the results at /tmp/cart_products.csv. Hope this is useful for others.


Comments

  • Be respectful. You may want to read the comment guidelines before posting.
  • You can use Markdown syntax to format your comments. You can only use level 5 and 6 headings.
  • You can add class="your language" to code blocks to help highlight.js highlight them correctly.

Privacy note: This form will forward your IP address, user agent and referrer to the Akismet, StopForumSpam and Botscout spam filtering services. I don’t log these details. Those services will. I do log everything you type into the form. Full privacy statement.