@rosnoun opened this Issue on September 12th 2017

When looking to the ecommerce products report, the average product price display incorrect value. The value seems to be multiplied by number of days for which there has been visit on the product page.

I've check that all my visits log product information report correct values directly into the database...

Piwik 3.1.0
Custom e-commerce report plugin (woocommerce)

@tsteur commented on September 12th 2017 Owner

Just had a look and the queries itself in the standard ecommerce plugin looks good.

Just to be sure: Are you using the standard ecommerce reporting plugin in Piwik or did you write your own custom reporting plugin? If so, it is open source and we can have a look at it?

@rosnoun commented on September 12th 2017

I'm using the standard ecommerce build in reporting functions of piwik yes...
I've developped my own woocommerce javascript plugin to track ecommerce data, sorry for the confusion. This plugins modifies the javascript tracking code from WP-piwik to include ecommerce data, that's all...

@tsteur commented on September 12th 2017 Owner

Just to avoid any confusion. Do you mind letting me know which report you are looking at and experience this issue?

@rosnoun commented on September 12th 2017

I'm looking at ecommerce -> product then both "product code" and "product name" reports show incorrect average price data
"Product category" does seems to be affected

@tsteur commented on September 12th 2017 Owner

When you see an Average Price... does it show next to it a number for "Unique purchases"?

image

Do you mind commenting this line https://github.com/piwik/piwik/blob/3.1.0/plugins/Goals/API.php#L324 meaning putting // (two slashes) in front of that line like // $rowView->renameColumn(Metrics::INDEX_ECOMMERCE_ITEM_PRICE_VIEWED, self::AVG_PRICE_VIEWED);

By looking at the code I can think of two problems which makes me think there really is a bug. The referenced link which renames "privce_viewed" to "avg_price" and then it does not calculate a proper value anymore...

and there are also filters like Average Price https://github.com/piwik/piwik/blob/3.1.0/plugins/Goals/Columns/Metrics/AveragePrice.php#L42-L46 where we fall back and divide by abandoned cards instead of orders (but I don't think this is the problem)

@rosnoun commented on September 12th 2017

As an example, here after the report gives me from september 8 to september 12:
HE-orange-douce-10 | 0 € | - | - | 4 € | - | 3 | 0 %
HE-orange-douce-5 | 0 € | - | - | 3.40 € | - | 2 | 0 %

And here after the report gives me from september 12 to september 12:
HE-orange-douce-10 | 0 € | - | 2 € | - | 1 | -
HE-orange-douce-5 | 0 € | - | 1.70 € | - | 1 | -

In the database, I extract with this query:
SELECT server_time, custom_var_k2, custom_var_v2, custom_var_k3, custom_var_v3, custom_var_k4, custom_var_v4, custom_var_k5, custom_var_v5 FROM piwik_log_link_visit_action WHERE idsite = 3 AND custom_var_v3 LIKE 'HE-orange%';

And I obtain (CSV):
2017-09-08 22:24:12,"_pkp","2","_pks","HE-orange-douce-10","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"
2017-09-08 22:24:17,"_pkp","2","_pks","HE-orange-douce-10","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"
2017-09-08 22:24:17,"_pkp","1.7","_pks","HE-orange-douce-5","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"
2017-09-08 22:24:21,"_pkp","2","_pks","HE-orange-douce-10","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"
2017-09-08 22:24:21,"_pkp","1.7","_pks","HE-orange-douce-5","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"
2017-09-09 21:31:47,"_pkp","2","_pks","HE-orange-douce-10","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"
2017-09-12 21:11:11,"_pkp","2","_pks","HE-orange-douce-10","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"
2017-09-12 21:11:31,"_pkp","2","_pks","HE-orange-douce-10","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"
2017-09-12 21:11:31,"_pkp","1.7","_pks","HE-orange-douce-5","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"
2017-09-12 21:23:07,"_pkp","2","_pks","HE-orange-douce-10","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"
2017-09-12 21:23:07,"_pkp","1.7","_pks","HE-orange-douce-5","_pkn","Orange douce - Huile essentielle bio","_pkc","[""Huiles essentielles""]"

@rosnoun commented on September 12th 2017

In the example I provided, the unique purchase is empty. But I've found other example were the unique purchase is 4 (for also 4 unit purchased) and the problem still occurs

I've commented the line as indicated.
After that, the average price shows 0

@rosnoun commented on September 13th 2017

any update on the issue?

@tsteur commented on September 14th 2017 Owner

The price "should" be correct when there are unique purchases. When there are no purchases, the price should be zero as well I'd presume but maybe not @mattab ? I'd find it very confusing to not see any purchases but then still see a price. Are you sure your custom woocommerce tracker plugin is tracking the orders etc correctly? You should probably compare the shown pricing information with data from log_conversion and log_conversion_item.

Commenting the other line I mentioned should still leave the price when there are purchases but set all others to zero.

@mattab commented on September 14th 2017 Owner

The price "should" be correct when there are unique purchases. When there are no purchases, the price should be zero as well I'd presume but maybe not @mattab ? I'd find it very confusing to not see any purchases but then still see a price.

FYI: the reason you can still see a price when there is no purchase, is when the following feature is used: "Tracking Product Page Views & Category Page Views (optional)" see the guide: https://piwik.org/docs/ecommerce-analytics/#tracking-product-page-views-category-page-views-optional
The tracking of product/category pageviews also include tracking of the price.

@rosnoun commented on September 15th 2017

Indeed, as matab pointed out, this is what I have implemented in my code.
That's why I have product information in the piwik_log_link_visit_action table, which wouldn't be the case if I only had cart and order tracking (in such case, the data are indeed in log_conversion and log_conversion_item)
For the case in my example, I don't necessarily have data related HE-orange-douce-5 or HE-orange-douce-10 in log_conversion and log_conversion_item tables.

@tsteur commented on September 17th 2017 Owner

@mattab be good to make this more clear in the UI. I did not expect this behaviour... Eg beside "Ecommerce orders and Ecommerce abandoned carts" I would have expected another item "Ecommerce views" or "Product views" etc.

@rosnoun do you have this problem only for week / month / year periods or also when only viewing a day? Looks like the average is calculated directly in MySQL which looks to me like this might not be showing correct values when viewing for week/month/year/range in https://github.com/piwik/piwik/blob/3.1.1-b1/plugins/CustomVariables/Archiver.php#L125

Next problem is that it looks like it generates an average of all custom variable values here even if the custom variable slot is used for something else: https://github.com/piwik/piwik/blob/3.1.1-b1/plugins/CustomVariables/Archiver.php#L112-L114

After seeing this and quickly debugging it I stopped any further investigation as it is clear this feature was not properly implemented and needs to be re-built in a less "hacky" way. The data it generates may be quite buggy as you pointed out @rosnoun .

As the feature might require schema changes we cannot work on it before Piwik 4.0

@mattab commented on September 18th 2017 Owner

@tsteur as per your suggestion, moving this issue to Piwik 4 where we'll try to improve the implementation of Ecommerce view tracking.

Powered by GitHub Issue Mirror