Tracking Unregistered Abandon carts in Magento

Does anyone know where I can start trying to get at cart information from unregistered users. I want to be able to look at abandon carts from people who are not logged in. This doesn't seem to be a default function of magento, but 开发者_JS百科I have a feeling it's in the db somewhere.

all quotes are stored in sales_flat_quote table

I have checked this query and find one or two cases where some results are missing in the results but shows on the magento abandoned cart report. Here is what I tried.

SELECT entity_id, customer_firstname, customer_email, items_count, grand_total, created_at
FROM sales_flat_quote
WHERE entity_id NOT IN (SELECT quote_item_id AS quote_id FROM sales_flat_order_item) 
AND items_count >0
AND customer_email IS NOT NULL
ORDER BY `sales_flat_quote`.`created_at` DESC

In case anyone is interested, this is the sql query that I came up with for getting abandon cart items directly out of the database. You'll need to change the date

SELECT quote_id, sku, qty, price, custom_price, row_total, created_at, product_id, store_id 
FROM mage_sales_flat_quote_item  
WHERE created_at > "2011-04-01" 
AND quote_id NOT IN (SELECT quote_item_id AS quote_id FROM mage_sales_flat_order_item)
AND store_id IS NOT NULL

This is more like it.

SELECT `main_table`.*, GROUP_CONCAT(item.sku) AS `skus`, (main_table.base_subtotal_with_discount*main_table.base_to_global_rate) AS `subtotal`, `cust_email`.`email`, `cust_fname`.`value` AS `firstname`, `cust_lname`.`value` AS `lastname`, CONCAT_WS(' ', cust_fname.value, cust_lname.value) AS `customer_name` FROM `sales_flat_quote` AS `main_table`
 INNER JOIN `sales_flat_quote_item` AS `item` ON item.quote_id = main_table.entity_id
 INNER JOIN `customer_entity` AS `cust_email` ON cust_email.entity_id = main_table.customer_id
 INNER JOIN `customer_entity_varchar` AS `cust_fname` ON cust_fname.entity_id = main_table.customer_id AND cust_fname.attribute_id = 5
 INNER JOIN `customer_entity_varchar` AS `cust_lname` ON cust_lname.entity_id = main_table.customer_id AND cust_lname.attribute_id = 7 WHERE (items_count != '0') AND (main_table.is_active = '1') AND (main_table.created_at >= '2013-02-04 00:00:00' AND main_table.created_at <= '2013-02-04 24:00:00') AND (main_table.updated_at >= '2013-02-04 00:00:00' AND main_table.updated_at <= '2013-02-04 24:00:00') GROUP BY `item`.`quote_id` ORDER BY updated_at DESC