Used WooCommerce Import tool and now have double sales in stats section

Hi @sethbahookey,

I’m sorry to hear you’re experiencing an issue here. This could be possibly due to how your orders were imported.

As your reports table shows order rows that don’t match any actual order ID numbers in your database, you could try an SQL command to filter those duplicate rows. Then, you could delete these extra rows to resolve the issue:

SELECT * from wp_wc_order_stats where 'order_id' not in (select 'ID' from wp_posts where 'post_type' = 'shop_order');

Once done, verify the database table in WooCommerce > Status > Tools to be sure nothing is missing there.

We recommend taking a full backup of your site and database in case anything goes wrong. More info at: Backing up WordPress content.

Let us know how that goes.

Thanks Mirko! When running that query I seem to get a lot more orders than just the duplicate ones. For example, the orders I’m having these duplicate issues with are from Oct. 25 – Nov. 2. and there are about 28 of them I can see, but when I run this query and append ‘and date_created > ‘2021-10-25’ I get close to 100 orders.

Further when I had isolated this down to one order originally it did not remove the record from the analytics>order page. The real order and then subsequent erroneous order was still showing in the list.

Is the wp_wc_order_stats table the one responsible for displaying the orders in the list or is there some other table that is hooked up to this?

Thanks again!

Hi there,

Thanks for getting back!

It appears there was an issue with the apostrophes in the previous SQL command. Please try this one and see if it helps retrieve a different result:

https://pastebin.com/Db4F7nBz

Cheers.

Hi @mirko P.

Thanks for the updated query. It seems like I ended up with the same results. I know I did a big export/import awhile back when I had redesign the site so maybe that is why I’m getting all those extra orders.

I think I’ll just manually go through and pick out the orders to remove. Can you confirm if this is the only table required to update to remove the erroneous records from the Analytics>Orders page or is this page tied to other tables?

Thanks!
Seth

Hey @sethbahookey,

Have you tried clearing the “historical data” out of the Analytics section? Go to Analytics > Settings and scroll down to the bottom. You should see a button you can use to remove all of the analytics data. Once it’s finished removing the old, you can come back here and import it again.

See if that fixes the orders for you.

Let us know what you find out.

Okay! So it looks like I finally got it.

What I ended up doing was going through and manually finding the 32 orders that were dupes from the DB and created a query to delete from the wp_wc_order_stats, wp_woocommerce_order_items, wp_wc_order_product_lookup tables.

Then from there I went to the Setting page and set the date to 10-22-21 (a couple days before I started deleting dupes from the DB), and clicked start.

I’m now back on track with the correct sales data. Thank you!!

Can you help me understand why I need to ‘reimport’? I’m guessing WooCommerce just doesn’t look at the database each time to look at the data hence why it wouldn’t update when I deleted a record? Just curious why it’s setup like this for my own education in architecture/programming. ??

Thanks again,
Seth

Hi @sethbahookey,

I’m guessing WooCommerce just doesn’t look at the database each time to look at the data hence why it wouldn’t update when I deleted a record? Just curious why it’s set up like this for my own education in architecture/programming.

That is correct. WooCommerce has separate tables for the analytics data to speed up reporting. Since these tables don’t use the “original” data, they have tools to allow merchants to purge this data and start over just in case something goes wrong. Most of the time this works just fine but occasionally there are problems and often they can be fixed just by purging and importing again.

Let us know if you have any other questions.

Cheers!

Hi there,

We haven’t heard from you in a while, so I’m going to mark this as resolved. Feel free to start a new thread if you have any more questions.

Cheers.