Last updated: 26th October 2024
0. Preamble
Between the 25th and the 26th of October 2024, we received reports that the WooCommerce Analytics started showing much higher revenue figures than usual. Apparently, the reports started counting the same orders multiple times, and the reason seems to be the presence of duplicate data.
The Currency Switcher doesn't duplicate data, and the way it integrates with the Analytics hasn't changed recently. The root cause of the error seem to be some other element that suddenly started creating duplicates of the custom meta that the Currency Switcher stores against orders. This could be another plugin that has been updated just before the issue occurred,
As of the 26th of October, we haven't been able to pinpoint the exact element that causes this incorrect behaviour. We keep collecting information so that we can identify the triggering factor and prevent it in the future. Our suspicion is that some other plugin might have been updated, and it fiddled with the order meta, creating the duplicates.
While we gather more information about the issue, you can refer to this article to find and correct the duplicate data.
1. WooCommerce Analytics reports - Troubleshooting
100 USD + 100 EUR + 100 GBP = 300 USD
100 USD + (100 EUR x <exchange rate to USD>) + (100 GBP x <exchange rate to USD>) = 337.81 USD
Order ID | Total | Exchange rate |
1 | 100 | 0.4278356 |
2 | 120 | 0.4278356 |
3 | 130 | 0.4278356 |
Order ID | Total | Exchange rate |
1 | 100 | 0.4278356 |
1 | 100 | 0.4278356 |
1 | 100 | 0.4278356 |
2 | 120 | 0.4278356 |
3 | 130 | 0.4278356 |
1.1 How to find the duplicate data
1.1.1 With the HPOS feature enabled
# Change the "wp_" table prefix to the correct one SELECT WCOM.order_id ,COUNT(WCOM.meta_value) AS base_currency_exchange_rate_count FROM wp_wc_orders_meta WCOM WHERE (WCOM.meta_key = '_base_currency_exchange_rate') GROUP BY WCOM.order_id ORDER BY WCOM.order_id
1.1.2 With the HPOS feature disabled (legacy order tables)
# Change the "wp_" table prefix to the correct one SELECT WCOM.post_id ,COUNT(WCOM.meta_value) AS base_currency_exchange_rate_count FROM wp_postmeta WCOM WHERE (WCOM.meta_key = '_base_currency_exchange_rate') GROUP BY WCOM.order_id ORDER BY WCOM.order_id
order_id | base_currency_exchange_rate_count |
1 | 3 |
2 | 1 |
3 | 1 |
1.2 View the duplicate data
1.2.1 With the HPOS feature enabled
# Change the "wp_" table prefix to the correct one SELECT WCOM1.order_id ,WCOM1.meta_value AS base_currency_exchange_rate ,orders_with_duplicate_data.base_currency_exchange_rate_count FROM wp_wc_orders_meta WCOM1 JOIN ( # Change the "wp_" table prefix to the correct one SELECT WCOM.order_id, COUNT(WCOM.meta_value) AS base_currency_exchange_rate_count FROM wp_wc_orders_meta WCOM WHERE (WCOM.meta_key = '_base_currency_exchange_rate') GROUP BY WCOM.order_id HAVING (base_currency_exchange_rate_count > 1) ) as orders_with_duplicate_data ON (orders_with_duplicate_data.order_id = WCOM1.order_id) WHERE (WCOM1.meta_key = '_base_currency_exchange_rate')
1.2.2 With the HPOS feature disabled (legacy order tables)
# Change the "wp_" table prefix to the correct one SELECT WCOM1.post_id ,WCOM1.meta_value AS base_currency_exchange_rate ,orders_with_duplicate_data.base_currency_exchange_rate_count FROM wp_postmeta WCOM1 JOIN ( # Change the "wp_" table prefix to the correct one SELECT WCOM.post_id AS order_id, COUNT(WCOM.meta_value) AS base_currency_exchange_rate_count FROM wp_postmeta WCOM WHERE (WCOM.meta_key = '_base_currency_exchange_rate') GROUP BY WCOM.post_id HAVING (base_currency_exchange_rate_count > 1) ) as orders_with_duplicate_data ON (orders_with_duplicate_data.post_id = WCOM1.post_id) WHERE (WCOM1.meta_key = '_base_currency_exchange_rate')
2. Contact form
If you have any questions about the cleanup of the duplicate data, or if you need help with the troubleshooting, please feel free to contact us: https://aelia.co/contact. We will be happy to assist you.