Find every expired discount still running in Chargebee

Build an internal app for our finance team that finds every discount we are still giving away in Chargebee and lets us stop the ones that were supposed to expire. Temporary discounts have a habit of becoming permanent: sales extends a discount for three months to close a deal, the terms live in a PDF rather than as structured data, and nobody ever removes it from the billing system because nobody remembers it exists. This app is the recurring audit ritual that catches those. Our Chargebee site is on Product Catalog 2.0, so work with items and item_prices and never the legacy plans or addons model.

The main screen is a single table: every active subscription that currently carries an applied coupon, one row per subscription and coupon pair, ranked by how much monthly revenue the discount costs us with the biggest give-away first. Columns: customer name, coupon name, the discount itself (either a fixed amount or a percentage), the estimated monthly cost of that discount to us, how many months it has been running, the coupon's duration type, and a status flag for whether the coupon was set up as a limited-duration offer that has now outlived its intended term. Above the table, show summary tiles for total monthly revenue being discounted, the annualized equivalent, and the count of discounts that should already have ended.

Build the table from List Subscriptions filtered to active subscriptions, plus List Coupons and List Coupon Codes for the catalog side. A subscription carries its applied coupons inline, and each entry gives the coupon id and when it was applied; join that to the coupon record to get the name, discount type, discount amount or percentage, and duration type. Handle Chargebee's pagination when listing (pass a limit and follow next_offset until it stops coming back) so a site with hundreds of subscriptions loads completely rather than showing only the first page.

The field that tells us whether a discount was ever meant to be permanent is the coupon's duration_type, which is one of one_time, limited_period, or forever. A limited_period coupon still attached to an active subscription long past its period is the single highest-priority row in the app: flag it prominently as expired but still applied. Compute months running from the date the coupon was applied to the subscription up to today, and compare that against the coupon's intended period to decide whether it has overstayed. Coupons set to forever are intentional, so mark them neutrally as permanent by design rather than treating them as errors. Sort the table so expired limited_period rows come first, then everything else by monthly cost descending.

Chargebee money amounts are integers in the currency's smallest unit, cents for USD, so divide by 100 before displaying anything and format it as proper currency. For a fixed amount coupon the monthly give-away is the discount amount; for a percentage coupon it is that percentage applied to the subscription's recurring total. Normalize everything to a monthly figure so annual subscriptions are spread across twelve months and every row ranks on the same basis. Timestamps are Unix epoch seconds, so convert them before doing any date math or showing a date.

Selecting a row opens a detail pane, so nobody strips a discount blind. It shows the customer from Retrieve Customer with name, email, and company; their invoice history from List Invoices filtered to that customer and sorted most recent first, with dates, totals, and paid status; and a full-price preview from Update Subscription Estimate For Items showing what the subscription would bill at list price without the coupon. Put that next to what it bills today and call out the difference as the amount we would recover. This preview is the authoritative number, so prefer it over the table's estimate once the pane is open.

From the detail pane the user has two actions. Remove Coupons drops the applied coupons off that one subscription, which is the safe per-customer fix. Delete Coupon retires the coupon across the whole catalog so sales cannot hand it out again. Both are destructive, so each needs a confirmation step that restates the customer, the coupon, and the recovered monthly amount before it runs, and the Delete Coupon confirmation needs a stronger warning because it affects every future use rather than just this one subscription. Remember that every Chargebee write is a POST, including the deletes. After an action succeeds, refresh the affected row and the summary tiles so the table always reflects current reality.

Every removal appends a row to a Google Sheets audit log using Append Values, capturing the timestamp, customer name and id, coupon name and id, the subscription id, the recovered monthly amount, which action was taken (removed from subscription, or deleted from catalog), and who made the change, taken from the signed-in user. The spreadsheet id and tab name are settings the user configures once inside the app. If the append fails, surface that clearly rather than silently losing the audit trail, because the log is the reason finance trusts the tool.

The app tracks a running total of what has been recovered during the current working session. A button labelled something like Post to finance channel sends that summary to Slack using Slack Bot's Send a Message: the number of discounts removed, the total monthly amount recovered, the annualized figure, and a short list of the biggest individual wins. The destination channel is configurable in settings.

Give the table a search box for customer or coupon name and filters for duration type and for an expired-only view, since working through the expired limited_period rows is what most audit sessions are actually for. Show a clear empty state when no active subscription carries a coupon, and make sure a failed Chargebee call surfaces a readable message rather than an empty table, since finance needs to know the difference between no leakage and no data.