Nothing by searching for address, name, and similar. But. Since I know there were no orders before certain date on new site, I found this by searching wc_order_stats for same order ID as on old site:
Removing this entry from DB that order has dissapeared from the Analytics. So data is there in that table but I cannot go through 3000 orders to get their IDs and remove it manually.
So I wrote the SQL code to go through wc_order_stats and for each order_id try to find the same ID in the table woocommerce_order_items – if there is no same data, that means this is an “orphan order” so I removed it.
MAKE SURE TO CREATE A BACKUP OF THE DB! I AM NOT RESPONSIBLE IF SOMETHING GETS MESSED UP!
DELETE a
FROM cwd_wc_order_stats a
WHERE NOT EXISTS (
SELECT 1
FROM cwd_woocommerce_order_items b
WHERE b.order_id = a.order_id
);
After that just clear Analytics Cache from Woocommerce – Status – Tools.
Only woocommerce active from plugins, and twenty twenty three theme – still same issue… Repeated all the previous solutions you all mentioned, with only woocommerce and default theme, nothing has happened.
@wpnomad I am the host and server is at my place – nothing to do with permissions. We are spinning around as I said I already cleared transient_wc_report from the DB in the first post and that was suggested 1 month later…
They come back once I visit analytics page – so transients do not exist until I visit analytics page.
I tried many different outcomes like first to remove it, then clear cache and all other Status>Tools options, and visit analytics lastly, but it still shows wrong numbers.
Yes, I do make backups always. Unfortunately, this did not fix it – still Analytics show wrong values, while Reports are correct as explained multiple times in this thread.
So to summarize removing order from the backend removes analytics while quick SQL actions do not remove it(mentioned earlier in the ticket), can you check why is that happening and tell me where analytics are stored in the database and what I should do to remove them from SQL so I can totally wipe out that info?
Anyway, replies to your questions. Screenshots attached Reports – CORRECT:
Analytics – WRONG:
Cron Events – no errors:
This reply was modified 2 years, 9 months ago by Danilo Krdzic.
So even if I remove all the data by using SQL codes I sent you, analytics data is still there and I have no idea how?
When you delete order from backend panel, what tables it affect, because obviously something is missing here:
DELETE FROM wp_woocommerce_order_itemmeta;
DELETE FROM wp_woocommerce_order_items;
DELETE FROM wp_comments WHERE comment_type = 'order_note';
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'shop_order' );
DELETE FROM wp_posts WHERE post_type = 'shop_order';
This reply was modified 2 years, 9 months ago by Danilo Krdzic.
It happens when you delete orders – so deleted orders are still visible in the analytics even if they do not exist anywhere on the website.
To test that quickly I cloned my DB and then used this SQL to remove all order data:
DELETE FROM wp_woocommerce_order_itemmeta;
DELETE FROM wp_woocommerce_order_items;
DELETE FROM wp_comments WHERE comment_type = 'order_note';
DELETE FROM wp_postmeta WHERE post_id IN ( SELECT ID FROM wp_posts WHERE post_type = 'shop_order' );
DELETE FROM wp_posts WHERE post_type = 'shop_order';
And guess what? Analytics still there. Followed all the steps that were mentioned, but Analytics data is still there from the deleted orders.
