Woocommerce-admin: Revenue columns for product lookup table doesn't account for coupons

[polevaultweb]

When viewing the Products or Categories report, the net revenue figure is incorrectly high.

This is because the gross and net revenue of the order record in the product lookup table does not have any coupon amount subtracted from it.

Eg. This order is for a product whose original price is $550, but a 20% coupon is used:

However, in the wc_order_product_lookup table the gross and net are based on the full price:

[rrennick]

@polevaultweb Can you post a screenshot of that order in the reports in WC Admin where you see an issue?

If you look at the WC order line items in the database you will find the same amounts

• Product - 550
• Tax - 22
• Coupon - 110

The order screen does the net amount calculation on load or recalculate for the convenience and UX of the shop manager.

[polevaultweb]

@rrennick ok so the order table displays the correct net amount.

I've narrowed the issue down to the Net Revenue stats for the categories and products reports - the product_net_revenue is summed and that is used without taking off the coupon summed amount https://github.com/woocommerce/woocommerce-admin/blob/master/src/API/Reports/Products/Stats/DataStore.php#L44

[rrennick]

@polevaultweb Thanks for the quick followup. I'll do some further investigation but for performance reasons it's often better to do calculations in PHP than in a MySQL query. I did compare one day with coupons between the core revenue report and the WC Admin report

Core:

$1,908.55 gross sales in this period
$1,908.55 average gross daily sales
$1,685.66 net sales in this period
$1,685.66 average net daily sales
8 orders placed
109 items purchased
$67.00 refunded 1 order (5 item)
$40.00 charged for shipping
$1.87 worth of coupons used

Adding a note to say that the Canadas is a translation string for Orders that I leave in place as an ongoing check that translations are working.

[LevinMedia]

@polevaultweb @rrennick Just confirming that I've encountered the same issue myself. In my experience it's displaying gross revenue (product sale price x quantity) rather than net.

Order report showing accurate net:

Product report showing net as gross:

[rrennick]

This should be addressed in #3095 which will be released in version 0.21.0.

@polevaultweb I'm going to close the issue. You would need to re-run your order import with 0.21.0 to update existing analytics data. Please let us know if that does not address these discrepancies.