Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment