Converting WooCommerce Upsells to Cross Sells

Cross sells… Upsells… What are they? What’s the differece? Why does it matter?

What are they?

The short version: Recommendations of additional products to purchased based on what you are looking at or about to purchase. An upsell being something better than what you have (a newer or better model… an upgrade), while a cross sell is something in addition or an accesory that goes with the product. You can find more about this on Chris Lema’s great post about upsells and cross sells.

Why does the difference matter?

To start with recommendations and how you display them may not make much of a difference to a store. So say you start adding them just in either the cross sells or upsells depending on how you feel that day. Then your store gets bigger. Now you have hundreds of products. You realize that you want to start focusing your upsells and cross sells a bit better and see the mess in front of you. Now you will need to manually edit every product and put upsells and cross sells into their rightful place.

Converting Upsells to Cross Sells via MySQL

Having recently cleaned up this exact scenario using MySQL I thought I would share the queries I wrote to do this. My goal:

Convert all current upsells (that are not empty) to cross sells.

Before continuing please make a backup of your database.

To get a lay of the land, I decided to see what I was working with. This first select query will pull all the existing cross sells, and the matching upsell, where the upsell is not an empty set.

select cs.meta_value as 'cross sell', us.meta_value as 'up sell' from wp_postmeta cs join wp_postmeta us on cs.post_id = us.post_id and us.meta_key = '_upsell_ids' where cs.meta_key = '_crosssell_ids' and us.meta_value != 'a:0:{}';

For me these results showed I had an empty cross sell for every single upsell. This makes life a bit easier, because I do not need to worry about overwriting my cross sells with up sells. However if I had run into a case like this, my next query I would have limited to update only cross sells that were empty as well.

This update query is a bit more advanced than just a standard select but mostly straight forward. On products which have upsells, copy that upsell data into the cross sells.

update wp_postmeta cs join wp_postmeta us on cs.post_id = us.post_id and us.meta_key = '_upsell_ids' set cs.meta_value = us.meta_value where cs.meta_key = '_crosssell_ids' and us.meta_value != 'a:0:{}';

Success, we have copied our up sells to cross sells. Now, we have duplicate data. Our upsells and our cross sells are the same. Personally I do not like to make wide sweeping changes all at once, so I take baby steps. For how I work, I can take a step, check the results, double check the results, and then move to the next step. In my experience I’ve found if something ultimately does not work as expected it is easier to revert (outside of going and restoring a backup).

The process to empty all the upsells is even easier. Where the upsells are not empty, update it to an empty set!

update wp_postmeta us set us.meta_value = 'a:0:{}' where us.meta_key = '_upsell_ids' and us.meta_value != 'a:0:{}';

This worked well for me, and I hope is useful for you!