Find lapsed BigCommerce customers and win them back in Klaviyo

On the first Monday of every month at 8am, find the customers who used to buy from my BigCommerce store but have gone quiet, push them into my Klaviyo win-back list with the context my flow needs, and post a summary to my marketing channel in Slack.

Start by pulling my customer list from BigCommerce with Get All Customers (V2), then pull order history with Get All Orders. Both paginate with page and limit, with a maximum limit of 250 per page. The V2 order endpoint returns 204 No Content with an empty body when a page has no results, so keep requesting pages until you get a 204 rather than waiting for an empty array. Pull the full order history rather than only the last year: the lapsed decision only needs recent orders, but lifetime spend needs everything. Orders placed at guest checkout are not tied to a customer record, so skip them when matching orders to customers. Money values come back as decimal strings in my store currency, not integer cents.

A customer qualifies if their most recent valid order was between 90 and 365 days ago. Skip anyone who has ordered in the last 90 days, they are still active and I do not want them getting a win-back email. Treat orders that were refunded, partially refunded, cancelled, declined or never completed as invalid, and ignore them when working out both the last order date and the spend totals. Drop any customer whose only orders fall into those buckets. Resolve the status names for my store with Get All Order Statuses rather than hardcoding status IDs. Also require a minimum lifetime spend of $100 so I am not chasing people who bought one cheap item once, and skip customers with no email address on file.

For the customers that pass those filters, use List Order Products to get the line items on their valid orders. From that work out, for each customer: the date of their last valid order, lifetime spend across all valid orders, how many valid orders they placed, their average order value, days since the last order, and the product they bought most often across those orders. Break ties on the most frequent product by whichever they bought most recently. That product is the repeat-purchase recommendation.

Cap each run at 300 customers. If more than that qualify, rank by lifetime spend descending and take the top 300, so the most valuable customers always make it in and the rest get picked up on a later run. Note in the Slack summary when the cap trimmed the list.

For each qualifying customer, call Klaviyo Create or Update Profile (Upsert) matched on email address so I do not create duplicates of profiles I already have. Write custom properties for last order date, lifetime spend, order count, average order value, days since last order, and the recommended repeat-purchase product, and set first and last name where BigCommerce has them. Keep those property names identical run to run so my Klaviyo flow conditions and email templates can rely on them. Then take the profile IDs that come back and call Add Profiles to List against my win-back list, batching the profiles into grouped calls rather than one call per profile. Klaviyo needs a revision header on every request and rate limits profile writes, so pace the calls and back off on a 429 using the Retry-After header.

Finish with a Slack Send a Message to my marketing channel. Tell me how many customers went into the list, the total lifetime revenue that group represents, the average lifetime spend per customer, and a numbered list of the ten highest-value lapsed customers showing name, lifetime spend, days since their last order, and their recommended product. Use Slack mrkdwn formatting, so single asterisks for bold rather than double. If nobody qualified this month, post a short note saying so rather than staying silent.

Treat the 90 to 365 day lapsed window, the $100 minimum lifetime spend, the 300 per run cap, and the destination Klaviyo list as settings I will want to adjust, so keep them clearly defined at the top of the workflow rather than scattered through the logic.