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 environments. For example, what if you use an eCommerce-friendly host like SiteGround or Kinsta with a one-click staging environment you can use? You could update a design on the staging site, but to push that information live, you have a couple choices:
- re-do the work on the live site by hand.
- push the staging site to live (overwriting the live site). But, you risk losing data that’s been added to the live site since cloning happened.
Wait, why can’t you 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 problem 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 reality, this is quite a complex problem when data on one site diverges from data on the other. Before we dig deeper, know this solution is best when you have sites that each have new data (e.g., one may have new products, options, or other stuff, while the other has new orders or customers). If you’re 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 (all data) from one site to another.
So when is WP MigrateDB Pro not the 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” eCommerce data: orders, products, coupons. Each of these is represented in different areas on your WooCommerce site.
That’s because each of them is a unique “post type”, or kind of content.
The same goes for other content on your site added via extensions: subscriptions, user memberships, maybe even custom order statuses. They’re all different data…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 — all of these are “posts” or “custom post types”.
They use the existing WordPress “post” data structure for storing things: posts and post meta. Doing so gives developers ready-made tools to work with with this data:
WP_Query and other ways of retrieving posts, or functions like
get_post_meta() to update or get meta data for posts. These “APIs” can save you a lot of time when building a new solution, and are 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: the post ID. The post ID is the “counter” that tells all posts on the site apart — every post on a WordPress site has a unique, auto-incremented post ID. (Sort of like a Social Security Number or driver’s license number.)
This is also why your order numbers are not sequential by default: the order number typically uses the post ID; this ID is generated by a counter that’s also incremented each time a product, blog post, page, or other content is added.
Now we start to see why order migrations are hard: 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 (if 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 (post ID) the same.
Wait, what? While on most sites, order ID is used as order number, these concepts are not identical. The order number can be filtered and changed, so we could make order numbers match between sites that have different post IDs or differing content. We just can’t force the post IDs to match, as a post on the destination site may already use the desired 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 changing or adding.
Rules of Engagement
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 be changed; the other site must be the record of truth (e.g., it has all of the orders or data you want to move); you move data from that origin site (source of truth) to the destination site.If you have “new” information (such as new orders) on both sites, 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 can automate this.
There is absolutely no automatic way to know which orders should be discarded and which should be kept when there are clashes. Or, even if all orders should be retained, there’s no programmatic way to know which orders would keep their order numbers, but which would get order numbers re-assigned. You need a custom script that handles this on a case-by-case basis (with criteria to use) 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, it does not 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.
Tools of the Trade
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 will equal 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.
CSV Export should be 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 sections). I’m going to omit moving customers in this scenario, but if you need to move new customers as well, do it 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 want to move. 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 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 happen. You’ll probably overwrite data you don’t want to change, so be conscious of what the data you’re importing 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. ????