Importing subscriptions

When importing subscriptions there are two ways to do this:

• manual (time consuming) via WordPress admin, but non-technical, no coding experience required
• automatic import via script, very technical, PHP experience required

Manual import via WordPress admin

This option is only possible if you already have your customers as customers in the Mollie API with a valid mandate, for example because you where already using another system for recurring payments via Mollie and want to migrate to WooCommerce/WooCommerce Subscriptions.

You can create customers and mandates via the Mollie API, but then this would become a technical solution to importing, for which we have the script.

• Create all users/customers via the WordPress admin > Users

• Create all subscriptions via WooCommerce > Subscriptions > Add subscription

  • Select one of the users
  • Leave "Parent order" empty
  • Add the subscription product manually
  • Make sure status is "Active" and "Save" the subscription

• Remember the Subscription ID, in this example we will use 701

• Install the plugin ARI Adminer, this allows you to edit the database via a UI

• Open ARI Adminer (you will see the settings)

You'll need to repeat these steps for all subscriptions:

Setting _requires_manual_renewal to false

• Open ARI Adminer again
• Now click on "Run ARI Adminer in modal window"
• Go to table that #__postmeta where #_ is the database prefix for your site.
• Click on "Search", change the dropdowns to post_id = your subscription ID (in this example 701), example http://take.ms/W3pCA
• Set the next dropdown to meta_key = _requires_manual_renewal, example: http://take.ms/NuoUh
• Click "Select"
• Click "Edit", see: http://take.ms/NuoUh
• In this screen, change true to false
• Click "Save"

Checking payment method title

• Go to table that #__postmeta where #_ is the database prefix for your site.
• Click on "Search", change the dropdowns to post_id = your subscription ID (in this example 701), example http://take.ms/W3pCA
• Set the next dropdown to meta_key = _payment_method_title
• Click "Select"
• IF this is already "iDEAL" or something like that and not empty, go to the "Checking payment method"
• Otherwise click "Edit"
• In this screen, type "iDEAL"
• Click "Save"

Checking payment method

• Click on "Search", change the dropdowns to post_id = your subscription ID (in this example 701), example http://take.ms/W3pCA
• Set the next dropdown to meta_key = _payment_method
• Click "Select"
• IF this is already "mollie_wc_gateway_ideal" or something like that and not empty, go to "Adding Mollie Customer ID"
• Otherwise click "Edit"
• In this screen, type "mollie_wc_gateway_ideal"
• Click "Save"

Adding Mollie Customer ID

• Now click "New Item" in the top right
• In this screen set post_id to the subscription ID (in this example 701)
• At meta_key enter _mollie_customer_id
• At meta_value enter the Mollie Customer ID. If your customers already have valid mandates, they will have a customer ID. The customer ID can be found in Mollie Dashboard > Customers or via the API. The Mollie Customer ID is not the same as the WooCommerce customer ID!
• Click "Save"

To test, go to WooCommerce > Subscriptions, open one of the new subscriptions. On the right find a dropdown, select "Process renewal" and save. A new renewal order should be created with a Mollie payment connected (and stored in the order notes) to it for SEPA Direct Debit or credit card.

Automatic import via script

**This script was developed for Mollie Payments for WooCommerce 3.x. If you want to use this script, also use that version of the plugin. **

It's possible to import existing SEPA direct debit (recurring) payments into a WooCommerce shop with WooCommerce Subscriptions and Mollie Payments for WooCommerce if all required information is available. The import can be done if all required data is available in a CSV file in the correct format. Mollie provides a PHP script which can then process the CSV file and import the data into WooCommerce in the correct way.

This script is offered "as is, where is", and without dedicated support (off course, you can always ask some questions). If merchants want to use this script to import subscriptions, we expect that their technical people will use this script as a starting point to build there own script that works in their specific situation.

Get the script from the gist: 'Mollie Payments & WooCommerce Subscriptions import'.

Introduction

• Importing existing credit card (recurring) payments is not possible.

• Importing with other import plugins or tools for WooCommerce or WooCommerce Subscriptions is not possible, as these do not handle the payments part for Mollie.

• PHP and WordPress coding experience is required. It is likely that custom modifications have to be made to this PHP script to allow it to better fit your situation and data. If you have a non-default WordPress installation you might need to update the WordPress file includes at the beginning of the script.

• The data that is required, is a combination of customer (general, billing, shipping) data for WooCommerce and payment information for Mollie (IBAN, BIC etc).

• The amount of records that you can put into the CSV depends on the server on which the script is used. If your server is not performant enough, split your CSV file into smaller parts. For example, a CSV file with 10.000 records can be split into two CSV files with 5.000 records each.

• When orders are imported, WooCommerce wil automatically send an email to the shop-owner and shop-customer. You can disable these e-mails during the import under WooCommerce > Settings > Emails.

• The CSV file should use quotes like "data" to wrap information in a CSV column.

• The CSV file should be encoded as UTF-8 encoded text file.

• During the import, keep a close eye on the logs as success and failure is communicated there. The logs can be found at WooCommerce > System status > Logs > mollie-payments-for-woocommerce.

• The script will automatically try to create a mandate for the recurring payment, so subscription renewals payments are processed automatically. You do not have to create the mandates yourself! If this fails for a subscription import, this will be communicated in the logs at WooCommerce > System status > Logs > mollie-payments-for-woocommerce.

• The import script will create a mandatory "first" order and a subscription. On the next payment date (as added in the CSV) a renewal order will be automatically created by WooCommerce Subscriptions for that order.

• For subscriptions in WooCommerce, a WordPress user account is required. The script will try to create one for every subscription.

• All subscription products that you will import will need to be created in WooCommerce before the import. The ID of those products will need to be added to the CSV. If you want to use another product ID during testing, create a database option in WordPress with key 'davdebcom_import_test_product' and as value set the product ID.

• WooCommerce, WooCommerce Subscriptions and Mollie Payments for WooCommerce needs to be completely set up before the import. Test imports should be done with Mollie Payments for WooCommerce set to test mode, so "Enable test mode" should be checked and the final production import should be done with "Enable test mode" unchecked (on the correct live API key). You can not change your API key after the subscriptions are imported, as the mandates are connected to that single API key.

Import workflow

• The import script can be called via a cron job (to automate things). If you don't have a lot of experience with cron jobs, consider a service like easycron.com.

• The script will process one subscription record per load. This is because a lot of shared hosting accounts have difficulty processing more then one import per run (server time-outs). Of course the script can be adjusted to process more then one subscription per load.

• If an import is successful, this will be stored in the database. On the next load, the next subscription row in the CSV will be processed.

• During import, details about the subscription will be stored at WooCommerce > Status > Logs > mollie-payments-for-woocommerce-[current-date]. Keep checking the log and keep an eye out for log messages with the message "ERROR" or "IMPORT FAILED".

• Only when you see "IMPORT FAILED" the import of a subscription is completely failed. With message "ERROR" the script will try to fix the import by switching the subscription to "manual renewal" instead of "Automatic renewal with SEPA". This will all be described in the logs.

• If a one subscription import permanently fails, the script will continue with the next subscription import on the next load.

• When the script is set to test, it uses a dummy email as customer e-mail, to make sure that actual customers don't get any emails during import (even if you disabled emails as described above). Change this email address to one you have access too, so emails with customer details aren't sent to '@example.com'. E-mails set here need to be unique, as they are used for the WordPress users too. Therefor +microtime()is added. Gmail support these kind of emails. If your system does not, those emails will probably bounce. The email can be changed at the top of function davdebcom_import_create_user().

So after completing the import, it is a good idea to check the logs for the message "IMPORT FAILED". Check all logs for all days that you imported. Use CTRL + F or CMD + F shortcuts to quickly search long log files for that message. Read why the subscription import failed in the log, adjust the subscription data in a new CSV (with all failed subscriptions) and try importing those failed subscriptions again.

• When starting the production import after testing, a few changes need to be made to the script.
  • At the beginning of the script find $mollie_test and change this to false. The API status is hardcoded into the script to make sure that imports aren't accidentally done on the live API when Mollie Payments for WooCommerce is set to live. Changing this value will mean that imports are added to the Mollie live API on the next import.
  • In the WordPress options database table create a new option davdebcom_import_status and set the value to live.
  • The counter needs a reset when you restart the import on production. In the options database remove the following key davdebcom_import_counter.

Tips

• Install a plugin like Mail Logs to see what emails are created and sent by WordPress during import
• Subscriptions or renewal payments can be tested at Tools > Scheduled Actions > Run
• Download the script and place it in te root of the website.
• In the same location add a file with name subscriptions.csv (if needed, the name can be changed in the script).

Required data/columns in CSV

• customer_email - required for creating the mandatory WordPress account, if unknown, add fake but unique emails to the CSV.

• customer_iban - example: NL00INGB0000000000

• customer_bic - example: INGBNL2A

• shipping_company

• shipping_first_name

• shipping_last_name

• shipping_address_1

• shipping_address_2

• shipping_postcode

• shipping_city

• shipping_country

• billing_company

• billing_first_name

• billing_last_name

• billing_address_1

• billing_address_2

• billing_postcode

• billing_city

• billing_country

• billing_phone

• billing_email

• start_date - example format: 2018-09-21 0:00:00. The next_payment date must occur after the start date (not on the same date!) and the script will automatically adjust the next payment date to start_date+7days if the start date is in the future. But the advice is to adjust this in the CSV before importing!

• last_payment_date - example format: 2018-09-21 0:00:00.

• next_payment_date - example format: 2018-09-21 0:00:00. WooCommerce Subscriptions can't handle subscriptions with a date in the past, so next_payment_date should be at least 48 hours in the future from the current date (today+48 hours). The script will automatically adjust older dates to today+7 days. The next_payment date must occur after the start date (not on the same date!) and the script will automatically adjust the next payment date to today+7days if needed. But the advice is to adjust this in the CSV before importing!

• billing_interval - unit of time, for "1 year(s)", this would be 1.

• billing_period - time period, for "1 year(s)", this would be year. Options: year, month, day.

• product_id (integer) - WooCommerce subscription product ID

• product_quantity (integer) - Quantity of subscription product (defaults to 1)

• order_shipping (integer) - an amount for the shipping costs. Now uses default description "Verzendkosten" on the subscription and default tax "21%". For other descriptions or tax rates, contact [email protected].

Importing other data

It's possible to also import other customer or subscription data into WordPress. This data will be added to the user or subscription that will be created for the import, as custom user meta or custom post meta (for subscriptions).

Please note that adding other customer data will increase the size of the CSV file and can result in server timeouts on less performant servers. Therefor, only add the customer data that is essential to your website or business.

Adding custom data to the WordPress database on import does not immediately make this data visible in the WordPress UI. To show this custom meta in the user edit view or the subscription edit view, you will need to do additional programming. This page about Custom Fields is a good starting point if you haven't done this before. Showing the data in the WordPress UI can be done at a later moment, after the import the data is already safely stored in the database.

To add other information to the WooCommerce subscription, add that information (string of text) to the CSV row for that subscription with a prefix of custom_post_meta_ and then your preferred post meta key for that information. So a complete column header could be custom_post_meta__my_custom_field.

To add other information to the WooCommerce customer/WordPress user, add that information (string of text) to the CSV row for that subscription with a prefix of custom_user_meta_ and then your preferred user meta key for that information. So a complete column header could be custom_user_meta__my_custom_field.