Overview
You've decided that it's time to increase (or perhaps decrease) the price of subscriptions for your current MemberPress members.
But you want to do this without asking them to resubscribe at the new price point, which would run the risk of you losing members.
Depending on your new pricing scheme, you may or may NOT want to also:
A) Offer proration
B) Create an immediate charge
In this article we'll share how we seamlessly updated MemberPress subscriptions in bulk for one of our customers in anticipation of a scheduled price increase for their membership tiers.
Your particular needs may be different so the aim of this article is to provide you with a general framework that you can use in a variety of situations.
Note: this is a technical tutorial which will require some coding skills.
If you need any help from our team, or want us to do this for you, just reach out. 🙂
Requirements and Resources
We used the following documentation to guide our efforts:
1 – https://docs.stripe.com/api/subscriptions/update
2 – https://docs.stripe.com/billing/subscriptions/upgrade-downgrade
3 – Node.js – In order to implement our price increases on both the Stripe and MemberPress sides programmatically we used node.js
Updating MemberPress and Stripe Subscriptions (Tutorial)
1 – Map data
For updating MemberPress subscriptions the best and fastest way to do this is directly via the database.
We've tried doing this using Airtable and Zapier, Google Sheets, etc.
But all of those methods run into rate limits, errors, and just aren't as reliable and flexible.
For this example, let's say we have the following Membership prices:
We have the old prices in the 3rd column and the new prices we want to charge as of the next billing date in the 4th column.
In this particular case, the customer was in the UK, so we had to take into consideration a coupon and taxes for each individual subscription.
2 – Write SQL queries and run in WordPress Database
Now we need to write SQL queries.
The number of queries depends on the situation.
For example, queries to update the Annual membership and Full individual membership upgrade.
Note: CHARITY50 coupons id in this example is 15601
1st Case:
UPDATE wp_mepr_subscriptions
SET price = 240.00, total = 240.00
WHERE product_id IN (12948, 13428)
AND coupon_id != 15601
AND tax_rate = 0;2nd Case – No CHARITY50 coupon with tax rate:
UPDATE wp_mepr_subscriptions
SET price = 240.00, total = 288.00, tax_amount = 48.00
WHERE product_id IN (12948, 13428)
AND coupon_id != 15601
AND tax_rate != 0;3rd Case – CHARITY50 coupon applied with no tax rate:
UPDATE wp_mepr_subscriptions
SET price = 120.00, total = 120.00
WHERE product_id IN (12948, 13428)
AND coupon_id = 15601
AND tax_rate = 0;4th Case – CHARITY50 coupon applied with tax rate:
UPDATE wp_mepr_subscriptions
SET price = 120.00, total = 144.00, tax_amount = 24.00
WHERE product_id IN (12948, 13428)
AND coupon_id = 15601
AND tax_rate != 0;Note: This is a very specific use case.
If we had more coupons, we could make a more complex query in which we get the coupon, its meta, check how it's discounted, etc.
For this example we'll keep it simple.
3 – Check changes reflected on live site
The reason we do this instead of just updating stripe, is to make sure the change is reflected on the live site and for members to be able to see the price change in their subscription.
Cases:
Reflected on frontend for the member:
4 – Create new prices in Stripe
Now let's implement the subscription updates in Stripe.
Go to Stripe ➝ Product Catalog.
Now we need to add prices for each relevant product (aka memberships):
Fill out all the relevant information.
In this case with Annual Membership, we are going to set the new amount, set Billing period to Yearly:
Now we have created a new price with API ID that starts with price_.
Note: we'll need this value later.
5 – Export subscriptions and map data
We export subscriptions by going to Stripe➝Subscriptions➝Export
Select Date range to All and Columns leave Default.
Click Export.
I used Airtable for this, but Microsoft Excel can work fine as well.
We import as CSV and hide all fields except id (subscription id), Customer ID, Plan, memberpress_product (metadata), group by Plan.
Now we need to create new column with the new Plan ID that we created in step 4.
For each product, we need to make sure that existing Plan ID and new Plan ID match the same product.
Now we have something like this:
5.5 – Adding trial days (optional step)
Note: this step is relevant for Active subscriptions only.
In my first attempt to update subscriptions, I did not add trial days.
This immediately charged the member.
In this example this is obviously something we want to avoid as it will confuse the member and create a headache for our customer.
To mitigate this I added trial days until the next billing date.
This has to do with what Stripe calls “immediate payment”.
See https://docs.stripe.com/billing/subscriptions/upgrade-downgrade#immediate-payment
When you make the API request use the parameter billing_cycle_anchor: "unchanged" and it should mitigate this issue.
For this, we will need to show the Current Period End (UTC) field from exported subscriptions.
Now we need to create 1 Formula field.
It will be Current Period End (UTC) in Unix (Note: This can be done through code in later steps).
Formula is:
DATETIME_FORMAT({Current Period End (UTC)}, 'X')6 – Exporting CSV: coding and running the script
For the actual subscription update, we are going to use Node.js and API from stripe.
This means that the code will be run on our local machine.
First we need fs, csv-parser and Stripe.
fs is a built it Node.js module for reading files.
csv-parser and stripe need to be installed via npm:
npm install csv-parser stripe
This is the entire code.
The step by step explanations are found below.
const fs = require("fs");
const csv = require("csv-parser");
const Stripe = require("stripe");
const stripe = Stripe("your_stripe_live_key");
async function processCsvRow(row) {
const customerId = row["Customer ID"];
const subscriptionId = row["\ufeffid"] || row.id;
const newPlanId = row["New Plan ID"];
try {
const subscription = await stripe.subscriptions.retrieve(subscriptionId);
const currentPaymentId = subscription.items.data[0].id;
// Update subscription with new plan ID
const updateSubscription = await stripe.subscriptions.update(
subscriptionId,
{
items: [{ id: currentPaymentId , price: newPlanId }],
billing_cycle_anchor: 'unchanged'
proration_behavior: "none",
}
);
console.log(
`Subscription updated successfully for subscription ${subscriptionId}`
);
} catch (error) {
console.error(
`Error updating subscription for subscription ${subscriptionId}:`,
error.message
);
}
}
// Function to process CSV file with delays between API calls
async function processCsvFileWithDelays(csvFilePath) {
const rows = [];
// Read CSV file and collect rows
fs.createReadStream(csvFilePath)
.pipe(csv())
.on("data", (row) => {
rows.push(row);
})
.on("end", async () => {
console.log(`Total rows to process: ${rows.length}`);
// Process each row with a delay
for (let i = 0; i < rows.length; i++) {
const row = rows[i];
await processCsvRow(row);
// Add a 50-milisecond delay between API calls
if (i < rows.length - 1) {
console.log(`Waiting for 50 miliseconds before processing next row...`);
await new Promise((resolve) => setTimeout(resolve, 50));
}
}
console.log("CSV file processed successfully");
});
}
const csvFilePath = "subs.csv";
processCsvFileWithDelays(csvFilePath);We first initialize the fs, parser and stripe objects:
const fs = require("fs");
const csv = require("csv-parser");
const Stripe = require("stripe");We connect stripe Secret key by using
const stripe = Stripe("stripe_secret_key");We can find Secret key in Stripe ➝ Developers ➝ API keys (live or test key).
Next we have the processCsvRow function, which will go through each row in our subscriptions.
async function processCsvRow(row) {
const customerId = row["Customer ID"];
const subscriptionId = row["\ufeffid"] || row.id;
const newPlanId = row["New Plan ID"];Then in try, catch block, we have to retrieve the subscription and declare paymentId.
Payment ID is not exported, hence why we need to retrieve it.
It is located here in subscriptions:
const subscription = await stripe.subscriptions.retrieve(subscriptionId);
const paymentId = subscription.items.data[0].id;We need this to update the existing price.
Without it, we would add the new plan, and the member would be billed for BOTH prices (no bueno).
In this case, we only have one price, so we grab the only one.
const updateSubscription = await stripe.subscriptions.update(
subscriptionId,
{
items: [{ id: paymentId, price: newPlanId }],
billing_cycle_anchor: "unchanged",
proration_behavior: "none",
}
);subscriptionId is the ID we pull from csv, which is the ID of subscription we are updating.
In items, we have to put the ID of which subscription we are updating.
We pulled that in previous step.
In price, we set the new plan ID, which we created in step 4 and added it to the csv.
proration_behavior set to none, so we don't prorate the customer.
billing_cycle_anchor to unchanged, so we don't immediately charge them.
For any additional parameters you may need to include refer to official Stripe API Documentation here:
1 – https://docs.stripe.com/api/subscriptions/update
2 – https://docs.stripe.com/billing/subscriptions/upgrade-downgrade
NOTE: Refer to the optional step.
If you've completed the points from the optional step we will then add the trial_end: trialEnd as parameter.
We need to do this ONLY for ACTIVE users, since non-active, unpaid, past due, will get the trial, and have a transaction created in MemberPress.
This means they will have a membership, even if they should not:
const updateSubscription = await stripe.subscriptions.update(
subscriptionId,
{
items: [{ id: paymentId, price: newPlanId }],
trial_end: trialEnd,
proration_behavior: "none",
}
);Refer to: https://docs.stripe.com/billing/subscriptions/upgrade-downgrade
We log the success or Error of updating subscription console.
console.log(
`Subscription updated successfully for subscription ${subscriptionId}`
);
} catch (error) {
console.error(
`Error updating subscription for subscription ${subscriptionId}:`,
error.message
);
}Next we have a function to process the .csv file with a delay.
Why a delay?
Because Stripe places limits on API calls.
The basic rate limiter restricts the number of API requests per second as follows:
- Live mode: 100 read operations and 100 write operations
- Test mode: 25 read operations and 25 write operations
This means we can make a call every 10 milliseconds.
In this case, I have set it to every 50 milliseconds, just in case, to avoid errors.
We can modify this depending on the number of subscriptions.
async function processCsvFileWithDelays(csvFilePath) {
const rows = [];
// Read CSV file and collect rows
fs.createReadStream(csvFilePath)
.pipe(csv())
.on("data", (row) => {
rows.push(row);
})
.on("end", async () => {
console.log(`Total rows to process: ${rows.length}`);We initialize rows as an empty array.
Use fs to read the csv file.
for (let i = 0; i < rows.length; i++) {
const row = rows[i];
await processCsvRow(row);
// Add a 50-milisecond delay between API calls
if (i < rows.length - 1) {
console.log(`Waiting for 0.05 seconds before processing next row...`);
await new Promise((resolve) => setTimeout(resolve, 50));
}
}
console.log("CSV file processed successfully");
});
}We process each row, and call the processCsvRow function.
Set Timeout to 50.
We now monitor the console to see if there are any errors.
We can also write to a .csv on each iteration, whether successful or not, for further debugging.
After each iteration is done, we have now successfully updated the subscription prices.
Whew!
DISCLAIMER:
This is a very specific use case and depends on various parameters but it should give you a good conceptual idea of how it can be done for YOUR use case. 🙂
As always, feel free to reach out if you'd like our assistance!
