Last updated: 31st October 2024


TABLE OF CONTENTS


1. 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 root cause of the issue seems to be a glitch that occurs in the WooCommerce core when the Currency Switcher saves against an order the exchange rate used to calculate the analytics in the shop's base currency. In version 5.2.0.241007 of our solution, we made a minor change to the logic that occurs when an order is saved, or created. In simple terms, the logic can be described as

  1. Update custom order meta A (e.g. the order total in base currency).
  2. Update custom  order meta B  (e.g. the order tax total in base currency) .
  3. Update custom  order meta C (e.g. other meta in base currency).
  4. Etc.
  5. Update base currency exchange rate.


The exchange rate to be saved at step #5 was calculated at every step, but it was always the same. Due to that, we optimised the logic as follows:

  1. Update base currency exchange rate.
  2. Update custom order meta A (e.g. the order total in base currency).
  3. Update custom  order meta B  (e.g. the order tax total in base currency) .
  4. Update custom  order meta C (e.g. other meta in base currency).
  5. Etc.


All these calls are made via the libraries provided by WooCommerce, which are responsible for adding the meta when it's missing, and updating it when it's present. This works fine, but, for some reason, our minor change causes WooCommerce to "miss" the exchange rate saved against the order after it has been set. When that happens, WooCommerce ends up adding the custom meta twice, instead of updating it.


This looks like an edge condition, which doesn't seem to occur on every site, which is why we didn't post it during our regular testing. As of the 31st October 2024, we haven't been able to isolate the exact element that causes . It seems to be connected to the presence of specific plugins that perform their operations during the order creations, but we don't have a conclusive answer. While we keep looking for the root cause, we've implemented a safeguard in version 5.2.3.241101 of the Currency Switcher, which prevents the issue from occurring.


While we gather more information about the glitch, you can perform the following operations:

  1. Update the Currency Switcher to version 5.2.3.241101. This will prevent the duplication of further duplicates.
  2. Follow the instructions below to find and remove the duplicate data.


2. 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 taken into account twice in the analytics
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.post_id
ORDER BY
  WCOM.post_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

# IMPORTANT! Change the "wp_" table prefix to the correct one before running the query
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.order_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.


3. Cleanup the duplicate data

If your database contains duplicate data, you can use the following queries to delete the duplicates, keeping only one entry per order.


3.1. HPOS feature enabled
# Change the "wp_" table prefix to the correct one before running the query
DELETE wp_wc_orders_meta
FROM wp_wc_orders_meta 
JOIN
(   
    -- Return the duplicate with the lowest ID. Since there are always two, we can
    -- delete either one
    SELECT
      WCOM1.order_id
      ,MIN(WCOM1.id) AS base_currency_exchange_rate_id
    FROM 
        wp_wc_orders_meta WCOM1
        JOIN
        (
            -- Find all the duplicates of the the _base_currency_exchange_rate meta
            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')
    GROUP BY
        WCOM1.order_id
) AS entries_to_delete ON
    (entries_to_delete.base_currency_exchange_rate_id = wp_wc_orders_meta.id);

2. HPOS feature disabled
# IMPORTANT! Change the "wp_" table prefix to the correct one before running the query
DELETE wp_postmeta
FROM wp_postmeta 
JOIN
(   
    -- Return the duplicate with the lowest ID. Since there are always two, we can
    -- delete either one
    SELECT
      WCOM1.post_id
      ,MIN(WCOM1.meta_id) AS base_currency_exchange_rate_id
    FROM 
        wp_postmeta WCOM1
        JOIN
        (
            -- Find all the duplicates of the the _base_currency_exchange_rate meta
            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.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')
    GROUP BY
        WCOM1.post_id
) AS entries_to_delete ON
    (entries_to_delete.base_currency_exchange_rate_id = wp_postmeta.meta_id);
If you enabled the synchronisation between the HPOS and the legacy table, you could run both queries to clean the data on both sides.

 Important
The queries delete data from the database. The operation is irreversible. Please take a full backup of your site before running the query. It would also be a good idea to put it the site in maintenance mode, so you won't risk getting orders while you're cleaning up the data, or if you have to restore the database.

After the cleanup, you can purge the Analytics cache, by going to WooCommerce > Status > Tools (see screenshot below).
WooCommerce will re-generate the analytics data, and you should see the correct results. Thanks to the safeguard we put in place, no further data duplication should occur.


4. Contact us if you have any questions

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.