BigCommerce customer segment and lifetime value workbench

Build me a customer segment workbench for my BigCommerce store. My marketing lead needs to build and work audiences by hand instead of guessing from the store's new versus returning split, which is the only customer breakdown BigCommerce gives us natively. This is an interactive tool she opens and browses, not something that runs on a schedule.

The main view is a filterable customer table. One row per customer, with the behavioural numbers BigCommerce never shows me, computed per person: total orders, lifetime spend, average order value, first order date, last order date, days since last purchase, and top category bought. Every one of those columns is sortable and has a filter control on it, sliders or min/max ranges for the numeric ones and date ranges for the dates, so I can carve out cohorts like "spent over 500 and has not ordered in 120 days" or "three or more orders and rising average order value". Rising average order value means comparing the average of the customer's earlier orders against their more recent ones, so include a computed trend field for that rather than making me do it by eye. Filters combine with AND. Always show a summary bar above the table with the current cohort size and the total lifetime value of everyone in it, and keep it updating live as I move the filters.

Clicking any customer row opens a detail panel next to the table with their profile and their full order history, each order expandable to show its line items with product name, quantity, and price. This is the sanity check before I market to someone, so show the raw orders rather than only the rollups.

For the data, handlers read people with Get All Customers (V2) paging through the whole customer base, and Get a Customer for the detail panel. Then compute every metric from Get All Orders joined to the customer records by customer id, and use List Order Products per order to get line items, which is what gives us the top category bought and the per order detail. None of these metrics exist natively in BigCommerce, they all have to be derived from that orders plus order products join. Because that is a lot of API calls, load the orders once into a computed metrics cache the table reads from, with an explicit refresh control, rather than recomputing on every filter change. Watch out for two things: the V2 customer endpoints return 204 No Content when there are no results instead of an empty array, so treat that as the end of pagination rather than an error, and BigCommerce money amounts come back as decimal strings, so parse them before doing arithmetic.

Let me name and save a segment definition, meaning the whole filter set, so I can reopen the same one next month. When I reopen a saved segment, recompute it against current data and compare against the membership snapshot stored the last time I opened it, then show me who has entered the segment and who has left it since. Store the definitions and the membership snapshots in the app's own storage, per user, and let me rename and delete them.

Once a cohort is selected I want to act on it two ways. First, assign them to a BigCommerce customer group: list the existing groups with Get All Customer Groups so I can pick one, offer Create a Customer Group if the group I want does not exist yet, then set each customer's group id with Update a Customer. Second, push the cohort to a Klaviyo list: let me pick the destination list using Get Lists, then for each person in the cohort upsert them with Create or Update Profile (Upsert) keyed on their email, collect the profile ids that comes back, and pass those ids to Add Profiles to List. That order matters because Add Profiles to List takes profile ids, not emails, so the upsert has to happen first to obtain the id. Offer Remove Profiles from List as an option for people who have dropped out of a saved segment since the last push, and use Get Profiles where you need to look someone up.

Before either action runs, show me a confirmation with the cohort size and the total lifetime value of the people in it, and report back per person results afterwards so I can see any that failed. Nothing should ever write to BigCommerce or Klaviyo until I explicitly confirm.