Is it possible to synchronize PMPro orders and memberships with Stripe?

Hi @respinffs

Thank you for reaching out to Paid Memberships Pro.

What I can recommend as a way forward is to import users into Paid Memberships Pro via CSV with their subscription ID -> https://www.paidmembershipspro.com/add-ons/pmpro-import-users-csv/

What that does is it creates the user in WordPress, assigns the membership level and then creates and order inside Paid Memberships Pro with the transaction ID. It sounds like you’ve done this via Python already but you may use the above link as a reference point.

When importing data, do not set an expiration date on the user’s level as this will cancel once the date occurs, regardless of the subscription status (this might be what’s causing the cancellations – I’m not sure without looking at membership data in detail).

Once the users are imported, you may setup your webhook for Stripe by following this guide – https://www.paidmembershipspro.com/gateway/stripe/setup/#webhook

Any new charges that are processed will be sent to PMPro and an order will be created inside Paid Memberships Pro (via webhooks). It may not backdate previous orders but should help move things over to Paid Memberships Pro moving forward.

Please let me know what level settings you are giving your members when adding them to the database. If they have an enddate or not?

Hello @andrewza,

Thank you very much for your reply.

What I can recommend as a way forward is to import users into Paid Memberships Pro via CSV with their subscription ID -> https://www.paidmembershipspro.com/add-ons/pmpro-import-users-csv/

To clarify my situation, users have already been created when building the new website, and before installing the PMPro plugin. Won’t using this method create duplicated users?

What that does is it creates the user in WordPress, assigns the membership level and then creates and order inside Paid Memberships Pro with the transaction ID. It sounds like you’ve done this via Python already but you may use the above link as a reference point.

I’ve wrote a POSIX shell script, as I find it much faster to write a piece of Shell script than a piece of Python.

For now, my shell script takes Stripe unified payments as CSV, as well as another CSV containing user emails and user IDs, to finally output the SQL commands that I’ll copy paste in phpMyAdmin.

However, I’ve started the process to convert my shell script into a simple C program, because there’s a lot of things to optimize. In the end, I hope to have a piece of software directly running on my server, that can be triggered with a custom WordPress plug-in, in order to directly communicate with the Stripe API, and my SQL database.

When importing data, do not set an expiration date on the user’s level as this will cancel once the date occurs, regardless of the subscription status (this might be what’s causing the cancellations – I’m not sure without looking at membership data in detail).

Unfortunately, our organization need to set an expiration date for the end year of the transaction. If a transaction has been made during the year 2020, then the expiration date must be 2020-12-31 23:59:59.

Here is the line of my script, to generate the SQL command for tables wp_pmpro_membership_orders and wp_pmpro_memberships_users:

Reminder of the fels of table wp_pmpro_membership_orders: id, code, session_id, user_id, membership_id, paypal_token, billing_name, billing_street, billing_city, billing_state, billing_zip, billing_country, billing_phone, subtotal, tax, couponamount, checkout_id, certificate_id, certificateamount, total, payment_type, cardtype, accountnumber, expirationmonth, expirationyear, status, gateway, gateway_environment, payment_transaction_id, subscription_transaction_id, timestamp, affiliate_id, affiliate_subid, notes.

Reminder of the fields of table wp_pmpro_memberships_users: id, user_id , membership_id, code_id, initial_payment, billing_amount, cycle_number, cycle_period, billing_limit, trial_amount, trial_limit, status, startdate, enddate, modified.

My SQL generating functions:

sql_orders_print()
{
  echo "($GLOBAL_COUNTER, '$PMPRO_CODE', '$PMPRO_SESSION_ID', $WP_USER_ID, $PMPRO_MEMBERSHIP_ID, '', '', '', '', '', '', '', '', '$STRIPE_MONEY', '0', '', $GLOBAL_COUNTER, 0, '', '$STRIPE_MONEY', '', '$STRIPE_CARD_TYPE', '$STRIPE_CARD_NUMBER', '$STRIPE_CARD_EXPIRATION_MONTH', '$STRIPE_CARD_EXPIRATION_YEAR', 'success', 'stripe', 'live', '$STRIPE_TRANSACTION_ID', '', '$STRIPE_TIMESTAMP', '', '', ''),"
}

sql_users_print()
{
  echo "(NULL, '$WP_USER_ID', '$PMPRO_MEMBERSHIP_ID', 0, $STRIPE_MONEY, 0, 0, NULL, 0, 0, 0, '$PMPRO_MEMBERSHIP_STATUS', '$STRIPE_TIMESTAMP', '$PMPRO_MEMBERSHIP_END', '$STRIPE_TIMESTAMP'),"
}

Please note that:

• PMPRO_CODE is generated if it doesn’t exist in the Stripe payment description;
• PMPRO_MEMBERSHIP_END is always the end of the year of the payment (ie: any payment made in 2021 will generate a PMPRO_MEMBERSHIP_END equals to 2021-12-31 23:59:59.

SIDE NOTE: The fact that the word membership is plural in wp_pmpro_memberships_users and singular in wp_pmpro_membership_orders is an oddity.

Once the users are imported, you may setup your webhook for Stripe by following this guide – https://www.paidmembershipspro.com/gateway/stripe/setup/#webhook

Any new charges that are processed will be sent to PMPro and an order will be created inside Paid Memberships Pro (via webhooks).

Thank you, I’ll see if it helps me in any way.

It may not backdate previous orders but should help move things over to Paid Memberships Pro moving forward.

Not being able to backdate previous orders will definitely be a problem. But I’ll try to see how I can get things moving forward, as you said.

Please let me know what level settings you are giving your members when adding them to the database. If they have an enddate or not?

There are three levels (12€, 30€, 50€) with the same settings.

Here are the settings for level 1 (12€)

Initial payment: 12

Recurring: Yes

Billing amount: 12

(per 1 year)
Cycle number: 1
Cycle period: Year

Billing limit: 0

Custom trial: No

Expiration: No

And in “Other parameters”

Expiration date : Y-12-31

Hope this very long reply will help you understand my situation better!

Best regards, and thank you again for your time and your on-going help.

• This reply was modified 2 years, 2 months ago by respinffs. Reason: wrong formatting

The Import Users From CSV updates existing users and if they don’t exist, it will create them.

Unfortunately, our organization need to set an expiration date for the end year of the transaction. If a transaction has been made during the year 2020, then the expiration date must be 2020-12-31 23:59:59.

If you need recurring payments to happen on a specific date, you may be able to use the Subscription Delay Add On instead and don’t run an expiration date. As mentioned, when the date 2022-12-31 23:59:59 is reached it will cancel the members level regardless if the recurring payment was successful or not. If you rather want people to checkout each year on January 1st

To backdate orders, you would need to run a custom SQL script with the previous data (or automate it similar to how you are trying now).

With regards to an overall view of your process, it seems fine except that the expiration date with recurring subscriptions isn’t always recommended – https://www.paidmembershipspro.com/important-notes-on-recurring-billing-and-expiration-dates-for-membership-levels/

Sorry for bumping this @respinffs. I forgot to ask, you mentioned that members are randomly being cancelled – do you have any commonality between the user’s that are randomly cancelled such as level ID or missing orders etc?

Is their subscription being processed, then they are being cancelled?

Hello @andrewza,

Thank you very much again for your reply.

The Import Users From CSV updates existing users and if they don’t exist, it will create them.

Understood.

If you need recurring payments to happen on a specific date, you may be able to use the Subscription Delay Add On instead and don’t run an expiration date. As mentioned, when the date 2022-12-31 23:59:59 is reached it will cancel the members level regardless if the recurring payment was successful or not. If you rather want people to checkout each year on January 1st

Alright, I’ll have a look into that plugin.

Just to make it extra clear: yes, members need to have their subscription payment automatically renewed on January 1st, 00:00:00.

To backdate orders, you would need to run a custom SQL script with the previous data (or automate it similar to how you are trying now).

Alright, I’m glad to see that my method wasn’t completely wrong. I also have to manage orders that haven’t been successful, refunds, etc. just to make sure that I have 1:1 parity with the Stripe history.

On a side note, coding this, from POSIX shell to C, is an interesting process, but not a difficult one.

With regards to an overall view of your process, it seems fine except that the expiration date with recurring subscriptions isn’t always recommended – https://www.paidmembershipspro.com/important-notes-on-recurring-billing-and-expiration-dates-for-membership-levels/

Understood, I’ll look into that.

Sorry for bumping this @respinffs. I forgot to ask, you mentioned that members are randomly being cancelled – do you have any commonality between the user’s that are randomly cancelled such as level ID or missing orders etc?

Is their subscription being processed, then they are being cancelled?

I’m afraid that I can’t answer that question. I haven’t been able to pinpoint what was causing the complete cancellation of the database.

Best regards.

Hello,

Unfortunately, as expected, subscription of all users has been cancelled.

Here is a little bit more insight on what is the current state of table wp_pmpro_memberships_users:

• All status has been changed to changed;
• All enddate and modified are equal. Which means that the modification only sets enddate to the current date and time;

If you have any indication on what I should investigate to have more insight onto what’s happening, and pin-point what is going wrong, I would be happy to hear it.

Otherwise, my current plan is only to have a daily cron job that completely DROP TABLES wp_pmpro_membership_orders and wp_pmpro_memberships_users, and rebuild them from scratch, using my custom tool.

Best regards.

Overlooking the code, our plugin tries to remove duplicate level data in the membership users table for the same level ID (as there shouldn’t be multiple ‘active’ levels of the same ID within the table – I’m not sure if your script is adding more than one active status per member or how many times you are running this script).

Here is one instance, when calling the pmpro_changeMembershipLevel function – https://github.com/strangerstudios/paid-memberships-pro/blob/7095907c33bdcc486feb606c8d4f93af820dfb0f/includes/functions.php#L1210

This was the only code reference I could find in our core plugin that may do this status.

I hope this help gives you some direction.

• This reply was modified 2 years, 2 months ago by Andrew Lima.

Hello,

Thank you very much for your fast answer.

I’m glad that you have pointed that out, as we do have indeed multiple people have multiple “active” memberships. Meaning that they have donated more than once to our association.

My script doesn’t, indeed, verify that multiple memberships aren’t active.

I’ll work my way in that direction. Thank you infinitely for that huge help.

Best regards, and thank you again for your time so far.

You may add multiple orders to a single user, but I recommend keeping their membership level to only one active per user.

Because there have not been any recent updates to this topic we will be changing the status to resolved. If you have any new questions or your issue persists please feel free to create a new topic.