The reports generated by a "plain" WooCommerce installation will most likely show different figures from the ones generated when the Currency Switcher is active. This is because WooCommerce reports are not aware of orders currency, and simply calculate a grand total without any conversion. 


Example

Supposing that your shop's base currency is USD, and you had sales for 100 USD, 100 EUR and 100 GBP.

  • With the Currency Switcher disabled, WooCommerce Sales Reports will show absolute totals, calculated as 100 + 100 + 100 = 300 USD. This figure is, of course, incorrect, as it's a mixup of amounts in different currencies.
  • With the Currency Switcher enabled, the same report you activate the Currency Switcher, the same reports will show a total of 354.97 USD, i.e. 100 USD + 100 EUR converted to USD + 100 GBP converted to USD. This is the correct total.


1. What to do if the sales report show incorrect totals

If the totals don't seem correct, even after taking into account the conversion, the reason could vary depending on the specific issue.


1.1 The totals don't seem to be based on actual exchange rates
The Currency Switcher uses the exchange rates set in the Currency Switcher Options page to save order data that will be used for reporting. If the FX rates were entered manually, or were not updated, then the reports will be skewed.

1.2 The totals with the Currency Switcher active are lower than expected
If the figures are lower with the Currency Switcher active, then the processing of past orders might not have been completed successfully. You can trigger the processing again by following the instructions in the following article: Sales report show lower amounts when the Currency Switcher is active. This will re-process past orders all the way back to the beginning of last year, ensuring that the report data is in place.


1.3 The totals with the Currency Switcher active are higher than expected
If the figures are higher with the Currency Switcher active, then some data has been duplicated. This can happen if other plugins process past orders and create duplicates of their meta. For example, the upgrade process of the WooCommerce Subscriptions 2.0 caused this issue in the past.
To fix this issue, the duplicated data must be removed. The following queries can help performing such operation.

IMPORTANT
The queries below delete data from the database, and the operation is irreversible. If you make any mistake when running them, you might delete data that you actually need. Make sure that you take a full backup of your database before running the queries, and that you test your site thoroughly after running the commands.

The use of the queries is at your own risk. We can't take responsibility for any damage, deletion or loss caused by the use of the commands listed below, or any other instructions in the article.

Step 1 - Find duplicate order meta
-- Find all the order with duplicate meta data
-- IMPORTANT
-- Replace the table prefix (in the example, "wp_", with the appropriate one.
select
  post_id
  ,meta_key
  ,count(meta_key) as value_count
from 
  wp_postmeta
where
  (meta_key like '%_base_currency%')
group by
  post_id,
  meta_key
having
  value_count > 1

Step 2 -  Remove duplicate order meta

-- Delete the duplicate meta rows
-- IMPORTANT
-- Replace the table prefix (in the example, "wp_", with the appropriate one.
DELETE FROM wp_postmeta
WHERE meta_id IN
(
  SELECT meta_id
  FROM
  (
    -- This query will return a list of all the meta rows that are duplicates and
    -- that should be removed
    SELECT
      PM.meta_id
      ,PM.meta_key
      ,PM.post_id
      ,meta_duplicates.meta_id_to_keep
      ,meta_duplicates.duplicates_count
    FROM
      wp_postmeta PM
      JOIN
      (
        -- This query returns the meta keys with duplicates, as well as the ID of
        -- the first, or last meta that was added
        -- Use MIN() to keep the first meta that was added (i.e. when the
        -- duplicates, which were added later, are incorrect,
        -- Use MAX() to keep the last meta (i.e. when the duplicate was added
        -- deliberately, and the original one is now obsolete.
        SELECT
          post_id
          ,meta_key
          ,min(meta_id) as meta_id_to_keep
          ,count(meta_key) as duplicates_count
        FROM 
          wp_postmeta
        WHERE
          (meta_key like '%_base_currency%')
        GROUP BY
          post_id,
          meta_key
        HAVING
          duplicates_count > 1
      ) AS meta_duplicates ON
      (meta_duplicates.post_id = PM.post_id) AND
      (meta_duplicates.meta_key = PM.meta_key)
    -- This clause filters out the meta rows that we want to keep
    WHERE
      -- If you used MIN() above, you should check for "(PM.meta_id > meta_duplicates.meta_id_to_keep)"
      -- If you used MAX() above, you should check for "(PM.meta_id < meta_duplicates.meta_id_to_keep)"
      (PM.meta_id > meta_duplicates.meta_id_to_keep)
  ) AS duplicates_to_remove
)


Step 3 - Find duplicate order item meta

-- Find all the order with duplicate order item meta data
-- IMPORTANT
-- Replace the table prefix (in the example, "wp_", with the appropriate one.
SELECT
  order_item_id
  ,meta_key ,count(meta_key) as value_count
FROM
  wp_woocommerce_order_itemmeta 
WHERE
  (meta_key like '%_base_currency%') 
GROUP BY
  order_item_id
  ,meta_key
HAVING value_count > 1


Step 4 - Remove duplicate order item meta

-- Delete the duplicate meta rows
-- IMPORTANT
-- Replace the table prefix (in the example, "wp_", with the appropriate one.
DELETE FROM wp_woocommerce_order_itemmeta
WHERE meta_id IN
(
  SELECT meta_id
  FROM
  (
    -- This query will return a list of all the meta rows that are duplicates and
    -- that should be removed
    SELECT
      OIM.meta_id
      ,OIM.meta_key
      ,OIM.order_item_id
      ,meta_duplicates.meta_id_to_keep
      ,meta_duplicates.duplicates_count
    FROM
      wp_woocommerce_order_itemmeta OIM
      JOIN
      (
        -- This query returns the meta keys with duplicates, as well as the ID of
        -- the first, or last meta that was added
        -- Use MIN() to keep the first meta that was added (i.e. when the
        -- duplicates, which were added later, are incorrect,
        -- Use MAX() to keep the last meta (i.e. when the duplicate was added
        -- deliberately, and the original one is now obsolete.
        SELECT
          order_item_id
          ,meta_key
          ,min(meta_id) as meta_id_to_keep
          ,count(meta_key) as duplicates_count
        FROM 
          wp_woocommerce_order_itemmeta
        WHERE
          (meta_key like '%_base_currency%')
        GROUP BY
          order_item_id,
          meta_key
        HAVING
          duplicates_count > 1
      ) AS meta_duplicates ON
      (meta_duplicates.order_item_id = OIM.order_item_id) AND
      (meta_duplicates.meta_key = OIM.meta_key)
    -- This clause filters out the meta rows that we want to keep
    WHERE
      -- If you used MIN() above, you should check for "(OIM.meta_id > meta_duplicates.meta_id_to_keep)"
      -- If you used MAX() above, you should check for "(OIM.meta_id < meta_duplicates.meta_id_to_keep)"
      (OIM.meta_id > meta_duplicates.meta_id_to_keep)
  ) AS duplicates_to_remove
)

After removing the duplicate data, the report totals should show more accurate figures.


2. Contact us if you have any questions

As indicated at the top of the article, all the code examples are provided on an "as is" basis, and they fall outside the scope of our support service. Although we won't be able to implement them for you, nor provide a guarantee that they will work, we will be happy to answer any questions you might have. You can simply contact us, and we will get back to you as soon as possible.


You can purchase the Currency Switcher from our online shop.