better-visitor-log-abandonment.sql

SELECT 
  l.email as 'Email'
  , MAX(l.created_at) as 'Last Viewed'
  , group_concat(product_name.value) as 'Products'
  , IFNULL(customer_firstname.value, 'Guest') as 'Customer Name'
FROM kj_bettervisitorlog_log AS l
LEFT JOIN sales_flat_order AS o ON o.customer_email = l.email AND o.created_at > l.created_at
LEFT JOIN customer_entity AS customer ON customer.email = l.email
LEFT JOIN customer_entity_varchar AS customer_firstname ON customer_firstname.attribute_id = 5 
  AND customer_firstname.entity_id = customer.entity_id
LEFT JOIN catalog_product_entity AS product ON product.entity_id = l.product_id
LEFT JOIN catalog_product_entity_varchar AS product_name ON product_name.entity_id = product.entity_id 
  AND product_name.attribute_id = 71
WHERE l.product_id is not null
GROUP BY l.email