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.


What if the totals don't add up?

If the totals don't seem correct, even after taking into account the conversion, the reasons could be several:

  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.
  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.
  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.

    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

    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
    )

    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

    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
    )


You can purchase the Currency Switcher from our online shop.