One of the most common problems for WooCommerce stores that have multiple environments (such as a staging and live version of the site) is how to move data back and forth between these versions of the site. For example, what if you use an eCommerce-friendly host like SiteGround or Kinsta who has a one-click staging environment you can use? You could update a design or content on the staging site, but to push that information live, you have a couple choices:
- re-do the work on the live site
- push the staging site to live, but risk losing data that’s been added to the live site since cloning happened
Wait, why can’t I just “merge in” all of the new stuff? On the surface, it doesn’t seem like it’s too tough of a problem, right? I have orders on Site A, and I’m building new products and refreshing design on Site B. Let’s either move the orders to Site B, or the new design and products to Site A. Should be easy 😉
That’s the basic question we’ll look at today:
What is the best way to move WooCommerce orders between sites while keeping the order number the same between them?
In actuality, this is an extremely complex problem when data on one site diverges from data on the other. Before we dig deeper, know this sort of solution is best when you have sites that each have new data (ie one may have new products, options, or other stuff, while the other has new orders or customers). If you’re just moving data wholesale from one site to another, one of our favorite tools for moving data is WP MigrateDB Pro. However, WP MigrateDB Pro is best if you’re moving entire tables between sites.
So when is WP MigrateDB Pro not an ideal solution? What can get so hairy? Let’s look at why merging data between sites is so difficult.
This is where we show you something seemingly ordinary, right? Let’s take a look at this “innocuous” normal eCommerce data: orders, products, coupons. Each of these is represented differently on your WooCommerce site and in different lists or areas.
That’s because each of them is a unique “post type”, or kind of content.
Same goes for other content on your site that may be added via extensions: subscriptions, user memberships, custom product tabs, maybe even custom order statuses. They’re all different…or are they?
Now let’s make this data extraordinary (at least in the truest sense of the word, anyway). The issue is that, behind the scenes in WordPress, these are all the same thing: posts. So are your blog posts, pages, and many other kinds of content on the site: user memberships, subscription records, vouchers, custom product tabs — all of these are “posts” or “custom post types”.
The reason is that this gives you a pre-set data structure to work with and existing tables in the database for storing things: posts and post meta. This also gives developers ready-made tools to work with them:
WP_Query and other ways of working with posts, like
get_post_meta(). These “APIs” buy you a lot of pre-made integration when used. This is really valuable to make your code friendly to other WordPress developers out of the box.
However, the problem is that all of this data now uses a common identifier to distinguish them: the post ID. This is the counter that distinguishes all posts on the site — every post on a WordPress site has a unique, auto-incremented post ID. This is also why your order numbers are not sequential by default: the order number typically equals order ID; this ID is pulling from a counter that’s also incremented each time a product, blog post, page, or other content is added.
Now we start to see why this issue arises: we can’t just migrate a single database table that contains orders — they’re mixed up with every kind of other content on your site: products, pages, posts, and more. So unless you can move that entire “posts” table between sites (the blog posts, orders, products, and all other content is the same — which is where WP MigrateDB Pro comes in), along with every table that contains meta, such as the post meta table, order items meta, order items, and other tables, then we need a way to move orders alone.
The third act! This won’t quite be magic, but it is possible to move WooCommerce orders between sites and keep order number the same. However, we can’t keep order ID the same.
Wait, what? While on most sites, order ID is used as order number, these concepts are not identical. Order number provides us a different way to identify orders. While WooCommerce uses order ID (post ID) as the order number, the order number can be filtered and changed, so we could make this match between sites that have different post ID counters or differing content across the site. We just can’t force the post IDs to match, as another post on the destination site may already use that post ID, so WordPress won’t overwrite its data.
There are a few steps to syncing order numbers between sites, but before we get into it, know that migrating data is always hard. While there are plugins to help you, you need to be knowledgeable about what you’re doing, as porting data from one site to another requires a thorough understanding of what you’re actually changing or adding.
There a few cardinal rules for migrating data between sites to know up-front:
- Keep a source of truth at all times. Only one of the sites you’re working on can have the data you’re migrating changing at a time; the other site must be the record of truth (ie it has all of the orders as a given time), and you move data from that origin site to the destination site.
If you have “new” information for a given data type (such as new orders) on both sites, literally no one can help you, including this article. You must sort out the mess manually, and you’re on your own — no developer in the world wants to touch that for less than a bajillion dollars.
This is because there is absolutely no way to know which orders should be discarded and which should be kept when there are clashes. Or, even if all orders should be kept, there’s no programmatic way to know which orders would keep order numbers, but which would get re-assigned. You need a custom script that handles this on a case-by-case basis or individual human-made decisions.
So products could change on one site and orders on the other, but you can’t change orders on both sites.
If you have the ability to practice your data imports, PRACTICE. For example, SiteGround lets you have multiple staging sites — you could clone a live site right before you want to merge to it to test the process. While the import plugin we’ll use has a “dry run” capacity, this just dry runs to ensure there are no formatting errors in a file, not to ensure the merge will happen the way you think it should.
Tied to the point above, be aware that data merges are irreversible once done. If you haven’t practiced the exact process you’re doing, then you must have a current backup ready in case things go wrong.
Here are the tools I’m going to use in this guide to migrate orders from my live site to my staging site:
- Sequential Order Numbers (free) – this is a plugin our team has built to make order numbers on your site sequential. The reason we’ll use it here is to ensure we have order number as a way to match orders between sites, not order ID. The Sequential Orders Numbers Pro plugin ($49) can also be used.
- Customer / Order CSV Export ($79) – This will let me get order data out of my live site in the expected format.
- Customer / Coupon / Order CSV Import ($79) – This will bring data into my staging site, and it works with both Sequential Order Numbers (and the Pro version), and CSV Export.
Install Sequential Order Numbers / Pro on BOTH sites. This is important — you not only need to get the order number from the origin site, but use it on the destination site as well. This will not change existing order numbers; it matches them up so its order number equals the existing ID. It does change order numbers going forward to be sequential if you leave it activated on the origin site. Regardless, it must stay active on the destination site going forward to keep the order numbers synced.
Then CSV Export gets installed on the origin site, and the import plugin on the destination site to bring the data in.
Once you have the plugins activated, you’ll take these steps:
- Ensure the CSV Export format for your customers and orders is set to “CSV Import” (do this on both tabs). I’m going to leave moving customers over out of this scenario, but if you need to move new customers as well, do this first. Just as we will be cognizant not to use order ID here, be aware of using user ID between sites.
Export the orders from the origin site you’re looking to move to the destination site. You can start the export and move onto something else, as an admin notice will pop up once it’s done.
Take the new order CSV file over to the destination site. When you look to import this file, be aware of a few options for the import:
- If you don’t have products that match up between sites, either fix it so SKUs do match up, or allow unknown products in the import.
- If you don’t think you need to merge data, don’t do it. If you do need to merge data, it should be fine, but we want to force merging based on order number, not ID. So, be sure to skip order ID in the import file.
- Even if you’re not merging, still skip order ID (and to be safe, and line item IDs, too) in the file mapping. Order ID is used to merge data within the same site (e.g., updating tracking information for orders). Since you’re not merging or doing anything within a site, order ID and any other database identifiers are useless.
If you try to use order ID on a site whose orders won’t match the IDs in the file, bad things will likely happen. You’ll probably overwrite data you don’t want to change if you need to merge anything, so be conscious of what the data you’re importing actually represents.
Do a dry run to make sure your format is correct. Remember, this doesn’t ensure that things turn out the way you expect, as the plugin has no idea of your expectations. This ensures that the file can be read and processed. The CSV Import plugin is a hammer, it won’t frame a house for you — you have to choose how to swing it 🙂
If the dry run processes correctly, do a live import!
Now when the orders have been transferred, you’ll see that they have the same order numbers between both sites. 🎉