Importing Aer.io Books into WooCommerce as Affiliate Products

We’re transferring our retail operations at Energion Publications (which includes Neufeld Computer Services) to the Aer.io platform. As part of this process, I wanted to import these products into a WooCommerce store as affiliate links.

An Aside

Why do I want to gather affiliate links to my own store in WooCommerce? Well, there are several reasons, one of which is that I want to provide a variety of external links to retailers, but I don’t want to do this on our catalog pages in our own store. We can now provide ebook files via the Aer.io platform, and we plan to do so. Yet many people will still want the books for Kindle, iBooks, Nook, Google Play, and so forth. We’d like to be able to include our own ebooks on the Aer.io platform along with these books.

The Problem

Aer.io has provided a nice CSV file to import into WooCommerce, but when I import this file directly using the built-in importer, they don’t come up as external products.

It took some work to find the issue and generate a fix. This will require that you have access to your database directly. If your site is on a cPanel system, you can go to the database using phpMyAdmin.

Warning

This is a procedure I worked out for a single site. I have tested it only on that one site. Any time that you modify a database, you should make a backup first. This is even more critical (if it’s possible for something to be more critical than “absolutely necessary”) when you’re modifying a WordPress table directly. You can make the whole system fall apart. Back everything up. Make sure you know how to restore it. I am not responsible for problems. The version of WooCommerce I used was 3.4.2.

The Procedure

1. Download your CSV file from Aer.io, the one that’s for Affiliate products, of course.
2. Open your database in phpMyAdmin and look for the highest ID in the wp_posts table. The reason you want this number is that you’re going to be modifying products using an SQL query, and you need to know which ones you just imported. If you have Simple products already in your store, you might change them to Affiliate products and break everything.
3. Import this into WooCommerce on your site. Select the file, do not select advance and do not check “Existing products that match by ID or SKU will be updated.” Click Continue, then accept all the mapping on the next screen.
4. When this completes, you will have the products, but unless my site is unique, your products will show as simple products. If you change them to External/Affiliate, they won’t have the affiliate link.
5. Now comes the really dangerous part. Do this at your own risk. I strongly recommend backing up the table that will be modified before you do this. (UPDATED – I have verified that the term taxonomy IDs may differ. You’ll need to look at the wp_term_taxonomy and verify the correct ID number. You can do this by comparing the two tables and looking for the ID that is one of the product types. Also, note that your table name prefixes may vary.)
   1. Select the wp_term_relationshps table, and then the SQL tab
   2. (Added) Access your wp_term_taxonomy table to determine the one for product type. These will be listed with a taxonomy of product_type.
   3. Enter the following: UPDATE wp_term_relationships SET term_taxonomy_id = 16 WHERE term_taxonomy_id = 13 WHERE object_id > first_post_id. Replace “first_post_id” with the number you found in step 2 + 1. Replace the table prefix with your table prefix and the taxonomy id numbers with those you discovered in step #2. To understand taxonomies and a bit of how they apply to WooCommerce, you can read the WordPress Codex page on taxonomies, and for a bit of how that applies to WooCommerce, read this explanation on StackOverflow.
   4. This changes your product type from Simple to External. I have not checked that these numbers are consistent for all WooCommerce installations, but I believe they are.
6. You’re not yet done. Import the file again, and this time check the box “Existing products that match by ID or SKU will be updated” before importing.
7. Your products should now be classes as external and should have the correct URLs.
8. Test your site thoroughly, including checking existing products to make sure they have not been modified. If you have problems, you will need to go back to your old wp_term_relationships table. You did back it up, right?

Let me know if you have any problems. Again, I emphasize this is at your own risk. I have tested it only once on my own site.