Hi,
I used the Goals Module API to get the information of the ecommerce abandoned carts metrics. While I have read the code but I have not able to completely understand how it gets this particular data, I therefore queried the database directly. The query takes the data from the log_conversion_item table and maps the idaction of product sku (type 5) and product name (type 6) with idorder value 0 groups by idvisitor.
Hi Thomas,
I am generating the data with this query :-
SELECT idvisitor, user_id, GROUP_CONCAT(sku), SUM(cartValue) FROM (SELECT piwik_log_visit.user_id, piwik_log_visit.idvisitor, tbl.sku, tbl.productName, tbl.price, tbl.quantity, tbl.cartValue FROM piwik_log_visit, (SELECT idvisitor, idorder, asku.name sku, aname.name productName, price, quantity, (price * quantity) cartValue, deleted FROM piwik_log_conversion_item tbl,piwik_log_actionasku,piwik_log_actionanameWHERE idvisit IN (SELECT idvisit FROM piwik_log_conversion WHERE server_time BETWEEN '2016-03-31 18:30:00' AND '2016-04-30 18:30:00' AND idgoal = - 1 AND idsite = <your site id>) AND deleted = 0 ANDasku.idaction=tbl.idaction_skuANDaname.idaction=tbl.idaction_nameAND idsite = <your site id> AND idorder = 0) tbl WHERE idsite = <your site id> AND tbl.idvisitor = piwik_log_visit.idvisitor AND visit_last_action_time BETWEEN '2016-03-31 18:30:00' AND '2016-04-30 20:30:00' GROUP BY piwik_log_visit.idvisitor , sku) tbl3 GROUP BY idvisitor , user_id
When I sum the total cartValue it differs from what i get through the api.
Essentially I require the visitors and their abandoned carts info.
I couldn’t try the query as there seems to be some whitespace missing when being rendered by discourse forum and it gives me some errors when I’m trying to execute it.
I’m not sure if the and idorder=0 is needed or whether it is good. Maybe it should be idorder is null?
In general by careful between idvisit and idvisitor. I’m not sure which numbers you need but you might want to do eg a distinct idvisitor and distinct userid