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

The Currency Switcher doesn't change how the analytics calculates the reports totals, but it interacts with to prevent the default calculations from returning the wrong values. One of the issues that the Analytics cause is that their sum doesn't take into account the currency in which orders were placed. For example, if your shop's base currency is USD, and you have orders for 100 USD, 100 EUR and 100 GBP, the Analytics would simply show you "300 USD". This has been an issue since the dawn of WooCommerce. Orders have a core "currency" attribute, but it has never been taken into account by any report.

The Currency Switcher fixes this by adding one simply operation to the sum, which is multiplying the order total by the exchange rate to convert it to the shop's base currency. WooCommerce does the following:
100 USD + 100 EUR + 100 GBP = 300 USD
The Currency Switcher changes it to the following:
100 USD + (100 EUR x <exchange rate to USD>)  + (100 GBP x <exchange rate to USD>) = 337.81 USD
When you disable the Currency Switcher, obviously, the operation is removed, so you get the result of the basic sum performed by WooCommerce.

One important thing to keep in mind is that this applies only when you chose the "All data, totals in base currency" option in the Analytics (see below):

If you filter the reports by currency, then WooCommerce will do its own basic sum, without any changes. In this case, the Currency Switcher would just tell WooCommerce to include only orders in the chosen currency.

Why some orders could be processed twice
The most likely cause of orders being processed twice, or more, is the presence of duplicate data. If the exchange rate stored by the Currency Switcher got somehow duplicated, that would cause the query used by WooCommerce to get extra rows for the related order. That is, in normal cases you would have the following (using sample values, for illustration purposes):
Order IDTotalExchange rate
11000.4278356
21200.4278356
31300.4278356

If the order meta gets duplicated, every matching entry, including the duplicates, is included in the query:
Order IDTotalExchange rate
11000.4278356
11000.4278356
11000.4278356
21200.4278356
31300.4278356
In this example, there are three entries with the exchange rate saved with the order, and that causes the same order to be picked three times.

Important
This kind of issue can only occur when you choose the "All data, totals in base currency" option. That is, if you have 10 orders in USD, 10 in EUR and 10 in GBP, if you filter the Analytics by currency you should see exactly that number of orders, as the calculations in that case are the basic ones. 

Why could there be duplicate data
The Currency Switcher doesn't duplicate data, as it always uses an "update" operation for the existing information. However, if the data is copied, migrated or restored, altered by other plugins, or manually, then the process could lead to the duplication. Unfortunately, we can't say for sure what caused the error, as we don't have sufficient information to determine in what circumstances the issue occur. We will have to keep gathering data from the affected sites until we can narrow down the trigger to a specific event.

1.1 How to find the duplicate data

To find the duplicate data, you can use a query like the following:


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


This will return a result like the following:
order_idbase_currency_exchange_rate_count
13
21
31

This column base_currency_exchange_rate_count must always contain "1". If the number is higher, it means that the order has duplicate data. In that case, the redundant data must be deleted. The best way to do so depends on what the duplicate data is.

1.2 View the duplicate data

The second step would be to view the duplicate data, with the following query:


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')
This will show the exchange rates (i.e. all the duplicates) stored against the orders. That information can be used to determine which duplicates to delete.
After deleting the duplicate data, the last step will be to purge the Analytics cache, by going to WooCommerce > Status > Tools. This will allow WooCommerce to rebuild the analytics data, which should show the correct values.


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.