Moving WooCommerce orders between sites

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

Launch your business in minutes with GoDaddy Airo™

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.

The Pledge

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?

The Turn

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 update_post_meta() or 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 Prestige

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

Process

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.

Importing URL coupons

When you move your orders from one site to another, you may find it useful to migrate your coupons as well. How you go about this will depend largely on how you manage the coupons for your store.

One of the best ways to increase conversions for your coupons is to use the URL coupons plugin. This extension lets you add unique URLs to each coupon so that the discount can automatically be applied when a shopper adds items to their cart. The plugin also comes with convenient options for migrating coupons in the event you need to move your site.

To import URL coupons, you’ll first need to set up your coupon CSV. The CSV Import documentation covers what data is accepted for coupons, and there’s also a sample CSV file you can download as a start point if desired.

To add URL Coupons data, you’ll need some additional columns in your CSV file. Let’s go through the 5 possible columns, and what data you can add for each.

Remember, since CSV Import uses a column mapper, the name of the column doesn’t much matter. However, if you use these names, it will automatically detect the right mapping to apply, but you can also set it up manually.

url_coupons_unique_url
This column should contain the slug for the coupon URL. This should not be a full URL, just the slug — for example: coupon/thanks/

Be sure to pay attention to whether or not you add a trailing slash, as coupon/thanks/ and coupon/thanks will behave differently. You can learn more about this in the plugin documentation.

url_coupons_redirect_page
This column should include one integer value: the ID of the content to which the coupon should redirect. This could be a page / post ID, such as the ID of your cart page. However, it could also be a taxonomy ID, such as the ID of a blog post category, product category, or product tag. Using a taxonomy ID will let you redirect to that category or tag’s archive page.

For example, if I want to redirect to the “Clothing” product category archive, I can use the term ID for that category.

If you want to redirect to anything that’s not a page, you must set the “redirect type” column as well.

url_coupons_redirect_page_type
This column should have a string to represent the post or taxonomy type for the coupon redirect. This defaults to page, but if your redirect ID is for a product, product_cat, product_tag or other content type, you must set this. The accepted values are:

pages
products
post
category
post_tag
product_cat
product_tag

url_coupons_product_ids
This column should contain a comma-separated list of product IDs (not SKUs) that should be added to the cart when this coupon is used. While the core coupon restrictions are based on SKUs, URL coupons requires you to use IDs for the products you want added to the cart. This can be blank, have one integer, or multiple integers (IDs) separated by a comma.

A quick note: this won’t apply to most of you, but if you manually build CSV files, just ensure comma-separted columns are automatically wrapped in quotes so the comma isn’t used as a delimiter automatically. Excel, Google Drive, and other programs do this automatically, but for the devs who may use a text file or CLI export, just don’t forget this

url_coupons_defer_apply
This is an easy column — enter “yes” or “no”, “yes” will ensure the coupon is saved in the session and defers applying until the cart meets its requirements. If this is no, “Defer apply” will be disabled, so the cart has to immediately meet requirements for the coupon to be added.

Once you have your CSV file built properly, you can import this coupon data.

Mapping Field Data

When both plugins are active, you’ll see new field options for mapping in the CSV Import column mapper. This will let you map your columns to the right URL Coupons fields in case they don’t map automatically.

New Import options

Set up your import file options, and when you’ve done so, you’ll map your coupon columns. Here’s a very simple example of a coupon merge / update I’m going to do to add URL Coupons fields to an existing code. I’ve mapped my URL Coupons data to my import file, ensuring that my product IDs and redirect ID / type is set up properly.

WooCommerce URL Coupons import field mapping

Map URL Coupons Fields

Notice that my redirect type is a product category. The ID is for the term ID — in this case my “Music” product category. When I’ve imported this data, the plugin will use both the type and the ID to properly set up your redirect.