"""Club Pulse — a wine-club retention dashboard in one read-only script. WHAT IT DOES. Four GETs through `sipspos.py`, one pure function over the payloads, one self-contained HTML file: fetch() /v1/club/members, /v1/customers?club_status=none, /v1/club/tiers, /v1/orders?created_since=… — nothing derived analyse() those four lists in, findings out. No clock, no network. render_html() findings in, one page out (`report.py`) main() environment, argument parsing, and the only clock read WHAT IT IS FOR. `sipspos.py` beside it is the part a partner copies — it knows nothing about clubs. THIS file is the worked example of using it: which endpoint answers which question, and which field on which payload produces which conclusion. Nothing here is clever; the whole point is that a reader can follow a number on the rendered page back to the field it came from without guessing. Two things in it are genuinely non-obvious, and they are the reason this example is worth reading rather than skimming. FIRST: CHURN IS ONLY VISIBLE THROUGH `/v1/customers?club_status=none`. `/v1/club/members` is `Customer` narrowed to `club_status != "none"`, so the moment a member cancels they are GONE from it — a retention dashboard built on the members endpoint alone can show you everything except a loss. Cancelling at SipsPOS clears `club_status`, `club_tier_id` and `is_club_member` but leaves `club_since` alone, and that surviving date is the whole signal: a customer with `club_status == "none"` AND a `club_since` was in the club once and is not now. A customer who was never a member has no `club_since` at all. That is the entire definition of `lost` below, and there is no other way to ask this API the question. SECOND: `analyse` TAKES `today` AS A PARAMETER, AND THAT IS NOT PEDANTRY. "Quiet since" and "which cohort" are both answers about a date, so a function that reads the clock itself has an invisible input: its result changes overnight, its tests either freeze time with a patch or go flaky in the window either side of midnight, and nobody can ask it "what would this report have said on the first of the month?". Passing the date in costs one argument and buys a function that is a plain mapping from data to findings — testable with dictionaries typed by hand, no HTTP, no monkeypatching, no fixtures. The clock is read in exactly one place in this file (`main`), which is where the rest of the outside world already lives. Every reference application should be able to point at one line and say "this is where the impurity is". READ-ONLY, AND STRUCTURALLY SO. Every request this makes is a GET, the key it asks for carries `customers:read` and `orders:read`, and there is no code path anywhere in `examples/` that can change a row at a winery — `scripts/ club_pulse_test.py` walks the AST to keep it that way. A dashboard that reads somebody's customer list should not be able to damage it. """ import argparse import datetime import os import sys import urllib.parse # Flat imports, because the three modules sit in one directory and this is how # a partner who copied that directory runs it: `python pulse.py`. `sipspos` is # the copyable client; `report` is only the HTML rendering, kept out of here so # that the analysis below can be read (and tested) without a page of markup in # the way. import sipspos from report import render_html #: The endpoints, named once. Paths carry the `/api` prefix that the winery's #: host serves the v1 surface under; `base_url` is the winery's own origin #: (SipsPOS is addressed per winery, not through a shared api.* domain). MEMBERS_PATH = "/api/v1/club/members" CUSTOMERS_PATH = "/api/v1/customers" TIERS_PATH = "/api/v1/club/tiers" SALES_PATH = "/api/v1/sales" ORDERS_PATH = "/api/v1/orders" #: Endpoints this example can do without. `orders:read` requires the #: `online_store` capability, which is an Estate feature — so a Growth winery #: with a thriving wine club CANNOT issue a key carrying it, even for its own #: sandbox. Refusing to run at all for those wineries would be the wrong #: trade: the shop is where a fraction of a club's purchases happen and the #: register is where most of them do. #: #: `services/api_examples.py` reads this to split the scope table on #: /developers into required and optional, so the page cannot tell a Growth #: winery to ask for a scope it is not allowed to have. OPTIONAL_PATHS = (ORDERS_PATH,) #: How far back to look for an order before calling a member quiet. 120 days #: is roughly one quarterly shipment cycle plus a month of slack, which is the #: interval at which "they haven't bought anything" starts meaning something #: at a winery rather than just meaning "it's February". DEFAULT_QUIET_DAYS = 120 #: Where the report lands when `--out` says nothing else. DEFAULT_OUT = "club-pulse.html" #: THE PRECEDENCE, AND IT IS FIXED. A member in trouble is usually in trouble #: in several ways at once — a failing card is very often also a paused #: membership — and a dashboard that lists them once per symptom is a dashboard #: nobody finishes reading. So each member appears EXACTLY ONCE, under the #: first reason in this tuple that fits, and the tuple is ordered by what the #: winery should do first: money that is already failing, then money that is #: about to, then two forms of drifting away, then silence. REASONS = ("past_due", "no_card", "paused", "skipping", "quiet") #: `ClubTier.billing_period` -> months per charge. The tier's `price` is one #: CHARGE, not one month, so a quarterly tier's price divided by three is what #: belongs in a column headed "monthly". Publishing a quarterly price under a #: monthly heading would be a number nobody could stand behind — see the note #: on `_tier_rows` for the rounding, and the README for the caveat. MONTHS_PER_PERIOD = {"monthly": 1, "quarterly": 3, "annual": 12} #: What a tier row is called when a member's `club_tier_id` matches nothing in #: `/v1/club/tiers`. See `_tier_rows`: the member is COUNTED, never dropped. UNKNOWN_TIER = "Unknown tier" # --------------------------------------------------------------------------- # Fetch — the only part that touches the network # --------------------------------------------------------------------------- def fetch(client, *, today, quiet_days=DEFAULT_QUIET_DAYS): """The four reads, verbatim. Nothing is derived here. Returns `{"members", "lapsed", "tiers", "orders"}` — exactly what the API sent, in the order a reader would ask for it. Keeping this function free of judgement is what lets `analyse` be pure: everything that needs a network is above this line, everything that needs a decision is below it, and a partner adapting the example can replace either half alone. `today` is passed in rather than read, for the same reason `analyse` takes it: the window this asks the API for and the window the analysis reasons about must be the SAME window, and the only way to guarantee that is for one date to flow through both. Two calls to `date.today()` a few milliseconds apart will almost always agree — and the report that runs at 23:59:59.9 on the last day of the month is the one you will be asked to explain. Each `paginate` call is a cursor walk that may be many HTTP requests; the client re-sends the filters on every page (see its docstring for why that is not optional). `client.request_count` afterwards is how many requests that actually took. """ since = (today - datetime.timedelta(days=quiet_days)).isoformat() # The optional read. Only these two types are tolerated: both mean "this # winery/key does not have the shop", which is a plan fact. Anything else # — a bad token, a malformed cursor, the API being down — is a real # failure and must not be swallowed into a quietly wrong report. try: orders = list(client.paginate(ORDERS_PATH, created_since=since)) orders_included = True except sipspos.ApiError as exc: if exc.type not in ("permission_denied", "feature_not_enabled"): raise orders, orders_included = [], False return { # Everyone in the club funnel: `club_status` of pending, active or # past_due. Cancelled members are NOT here — that is the next call. "members": list(client.paginate(MEMBERS_PATH)), # The churn read. `club_status=none` is every customer who is not in # the club, which includes everyone who never was; `analyse` filters # it down to the ones with a `club_since`. See the module docstring. "lapsed": list(client.paginate(CUSTOMERS_PATH, club_status="none")), # One page, deliberately: a winery has a handful of tiers, not # hundreds, and `paginate` here would spend a cursor walk to prove it. # If a winery ever did have more tiers than one page holds, the # members on the tiers past the first page land in the UNKNOWN_TIER # row rather than vanishing — which is the same safety net that # catches a deactivated tier, and the reason that row exists at all. "tiers": client.get(TIERS_PATH)["data"], # THE PRIMARY PURCHASE SIGNAL, and the one an online-store-shaped # dashboard gets wrong. A wine club member buys at the cellar door; # those are SALES, not orders. Reading only `/v1/orders` marks a # member who bought a case in the tasting room last week as "quiet" # — telling the winery to chase the customers it should be thanking. # # `paid_since` filters a NULLABLE column: an open tab has a NULL # `paid_at` and is excluded, which is right here. An unpaid sale is # not evidence that somebody bought something. "sales": list(client.paginate(SALES_PATH, paid_since=since)), # `created_since` is an ordinary range filter on an immutable column, # so this window is stable: an order's `created_at` never moves. Only # the window matters — a member with one order in it is not quiet, # and how much they spent is not this dashboard's question. # # OPTIONAL. See OPTIONAL_PATHS: `orders:read` needs the `online_store` # capability, so a Growth winery cannot issue a key with it. A refusal # here is a fact about the winery's plan, not an error in the # integration, and the report says which sources fed the quiet list # rather than quietly narrowing it. "orders": orders, "orders_included": orders_included, } # --------------------------------------------------------------------------- # Analyse — pure. No clock, no client, no I/O. `today` is an argument. # --------------------------------------------------------------------------- def analyse(members, lapsed, tiers, orders, sales=(), *, today, quiet_days=DEFAULT_QUIET_DAYS, orders_included=True): """The payloads in, the findings dict out. No I/O of any kind. {"at_risk": [{"name", "email", "reason", "detail"}, …], "cohorts": [{"month", "joined", "still_active"}, …], # ascending "tiers": [{"name", "members", "monthly_value_cents", "currency"}, …], "lost": [{"name", "email", "club_since"}, …], "totals": {"members", "at_risk", "lost", "monthly_value_cents", "orders_included"}} Everything this returns is derived from a field named in the code below, and every lookup is `.get()` rather than `[...]`: a field the API adds later must not break this, and a field it omits for a particular row should produce a missing finding, never a traceback in a nightly job. `today` and `quiet_days` are the only two things this needs that are not in the payloads. Hand it a date and it will tell you what the report would have said on that date — which is exactly what makes it testable. """ window_start = today - datetime.timedelta(days=quiet_days) # One membership test for the quiet check, built once. `customer_id` on an # order is the customer's opaque public id (`cus_…`), the same string as # `id` on a member — the API never publishes integer primary keys, so # joining on these ids is the intended way to connect the two payloads. # An order placed by somebody who is not a customer (a walk-in, a guest # checkout) has `customer_id: null`; those simply never match anyone. # BOTH SOURCES. A sale is a cellar-door purchase and an order is a shop # purchase; a member who did either is not quiet. Reading only orders is # how a retention dashboard ends up listing a winery's most loyal # in-person buyers as the ones to chase. bought_recently = {row.get("customer_id") for row in list(sales) + list(orders) if row.get("customer_id")} at_risk = [] for member in members: found = _reason_for(member, bought_recently=bought_recently, window_start=window_start, quiet_days=quiet_days) if found is None: continue # A healthy member. The dashboard is a list of exceptions. reason, detail = found at_risk.append({ "name": _display_name(member), "email": member.get("email"), "reason": reason, "detail": detail, }) # Sorted by urgency, then by name, so the top of the page is the call list # somebody works down and two runs over unchanged data produce the same # file. The API's own ordering is not meaningful here. at_risk.sort(key=lambda row: (REASONS.index(row["reason"]), row["name"].lower())) lost = _lost(lapsed) tier_rows = _tier_rows(members, tiers) return { "at_risk": at_risk, # Cohorts read BOTH lists: a month's joiners include the ones who have # since left, and leaving them out would make every cohort look like # it retained everybody. "cohorts": _cohorts(list(members) + list(lapsed)), "tiers": tier_rows, "lost": lost, "totals": { "members": len(members), "at_risk": len(at_risk), "lost": len(lost), "monthly_value_cents": sum(row["monthly_value_cents"] for row in tier_rows), # Provenance for the quiet list, carried to the report. False # means the key could not read the shop (a Growth winery has no # shop to read), so "quiet" was judged on register sales alone. # A reader deciding whether to phone somebody deserves to know # which question the page actually asked. "orders_included": bool(orders_included), }, } def _reason_for(member, *, bought_recently, window_start, quiet_days): """The one reason this member is at risk, or None if they are fine. Read this top to bottom: the order of the branches IS the precedence in `REASONS`, and the first one that matches returns. That is why a member whose card is failing AND missing appears once, as `past_due` — the winery cannot fix the second problem without fixing the first. """ status = member.get("club_status") last4 = member.get("club_card_last4") next_charge = member.get("club_next_charge_date") # 1. past_due — `club_status`. The API sets this when a dues charge has # already failed, so this is money that has ALREADY been lost, not # money at risk. Nothing else on the page outranks it. if status == "past_due": detail = "The last dues charge did not go through" detail += (f", on the card ending {last4}" if last4 else ", and there is no card on file to try again") detail += (f". The next attempt is due {next_charge}." if next_charge else ".") return "past_due", detail # 2. no_card — `club_status == "active"` and `club_card_last4` empty. # THE STATE THIS API MAKES UNIQUELY VISIBLE. A member migrated from # another system arrives active, with a real `club_since` and a real # tier, and no card: the old processor's card cannot come across, so # the member sits here until they re-authorize payment at this winery. # Nothing is failing yet, which is exactly why nobody notices — the # failure is scheduled for the next charge date. `pending` members are # deliberately NOT caught here: a signup that has not been completed is # not the same problem and does not want the same email. if status == "active" and not last4: detail = ("Active in the club with no card on file " "(club_card_last4 is empty). This is where a member " "imported from another system waits until they " "re-authorize payment here") detail += (f", so the charge due {next_charge} will fail unless " f"somebody asks them for a card." if next_charge else ", so the next charge will fail.") return "no_card", detail # 3. paused — `club_paused`, which is a separate column from # `club_status`: a paused member is still `active`, and reading only # the status would show them as perfectly healthy. if member.get("club_paused"): resume = member.get("club_resume_on") detail = ("The membership is paused (club_paused)" + (f", due to resume on {resume}." if resume else " with no club_resume_on date set, so nothing will " "restart it on its own — an open-ended pause is the " "shape a quiet cancellation usually takes.")) return "paused", detail # 4. skipping — `club_skip_next`. One skip is ordinary; it is on this list # because two in a row rarely are, and this is the only place the # winery would ever see the first one. if member.get("club_skip_next"): detail = "They have chosen to skip the next shipment (club_skip_next)" detail += (f"; the next charge is due {next_charge}." if next_charge else ".") return "skipping", detail # 5. quiet — no order in the window. Last, because it is the weakest # signal on the page: it says only that nothing was bought THROUGH # SipsPOS in `quiet_days`, and the club shipment itself may not be an # order. BOTH counters are read — a cellar-door sale and a shop order — # because for a wine club the cellar door is where most of the buying # happens, and a check that saw only the shop would accuse a winery's # best in-person customers of going quiet. Restricted to `active` so a `pending` signup, who has no # reason to have ordered yet, is not accused of going quiet. if status == "active" and member.get("id") not in bought_recently: detail = (f"Nothing bought since {window_start.isoformat()} " f"(the last {quiet_days} days) — no cellar-door sale and " f"no online order. Paying dues, buying nothing else.") club_since = member.get("club_since") if club_since and club_since >= window_start.isoformat(): # Dates from this API are `YYYY-MM-DD`, which sorts correctly as # text — no parsing needed, and none risked. detail += (f" They joined on {club_since}, so this may simply be " f"a new member whose first shipment has not gone out " f"yet.") return "quiet", detail return None def _cohorts(customers): """Joiners per month and how many of them are still in the club. Grouped on `club_since[:7]` — the first seven characters of a `YYYY-MM-DD` date are its month, so this needs no date parsing and cannot throw on a value the API formats slightly differently than expected. `joined` counts everyone who joined that month whether or not they are still here; `still_active` counts the ones whose `is_club_member` is still true. The gap between the two columns is the retention curve, and it only exists because the lapsed customers were passed in alongside the members. A customer with no `club_since` has never been in the club and belongs to no cohort, so they are skipped rather than bucketed under a blank month. """ joined = {} still_active = {} for customer in customers: club_since = customer.get("club_since") if not club_since or len(str(club_since)) < 7: continue month = str(club_since)[:7] joined[month] = joined.get(month, 0) + 1 if customer.get("is_club_member"): still_active[month] = still_active.get(month, 0) + 1 return [{"month": month, "joined": joined[month], "still_active": still_active.get(month, 0)} for month in sorted(joined)] def _tier_rows(members, tiers): """Members and monthly value per tier, joined on `club_tier_id`. NOBODY IS DROPPED. A member whose `club_tier_id` matches no tier in the payload — a deactivated tier, a tier past the first page, a member with no tier at all — is counted under UNKNOWN_TIER. The alternative, silently skipping them, is the worst available outcome: the tier table would still look plausible while the member count quietly disagreed with the club's, and nothing on the page would say so. MONTHLY VALUE, FROM A PRICE THAT IS NOT MONTHLY. `tier["price"]` is one charge, and `tier["billing_period"]` says how often that charge happens (monthly, quarterly, annual). A quarterly tier's price is three months of value, so it is divided by three here rather than published as if it arrived every month. Rounding is done on the tier's TOTAL rather than per member, so a hundred members on a $59.99/quarter tier do not accumulate a hundred half-cent errors. A tier whose price is null — money is `{"amount_cents", "currency"}` or null at this API, never a bare number, and null means unknown — contributes zero and says nothing it cannot support. """ by_id = {tier.get("id"): tier for tier in tiers if tier.get("id")} counts = {} for member in members: tier = by_id.get(member.get("club_tier_id")) key = tier.get("id") if tier else None # None -> the unknown row counts[key] = counts.get(key, 0) + 1 rows = [] for key, count in counts.items(): tier = by_id.get(key) if tier is None: rows.append({"name": UNKNOWN_TIER, "members": count, "monthly_value_cents": 0, "currency": None}) continue price = tier.get("price") or {} amount = price.get("amount_cents") months = MONTHS_PER_PERIOD.get(tier.get("billing_period"), 1) if amount is None: monthly = 0 else: # Integer arithmetic with explicit half-up rounding: cents are # counted, not measured, and float division would put a # 0.30000000000000004 in a money column. total = int(amount) * count monthly = (total + months // 2) // months rows.append({ "name": tier.get("name") or UNKNOWN_TIER, "members": count, "monthly_value_cents": monthly, "currency": price.get("currency"), }) # Biggest contribution first — the tier mix is read to answer "where does # the club's money come from", and ties broken by name keep it stable. rows.sort(key=lambda row: (-row["monthly_value_cents"], row["name"])) return rows def _lost(lapsed): """Customers who WERE in the club and are not now. `lapsed` is `/v1/customers?club_status=none`, which is every customer outside the club — overwhelmingly people who were never in it. The filter is `club_since`: cancelling clears `club_status`, `club_tier_id` and `is_club_member` but leaves the join date alone, so a `none` customer WITH a `club_since` is precisely somebody who left. Somebody who never joined has no join date to keep. This is the only way to see churn through this API — `/v1/club/members` excludes `club_status == "none"`, so every member it can show you is by definition one you have not lost. """ lost = [{"name": _display_name(customer), "email": customer.get("email"), "club_since": customer.get("club_since")} for customer in lapsed if customer.get("club_since")] # Newest join date first: it is a rough proxy for who left most recently # (the API publishes no cancellation date), and recent losses are the ones # still worth a phone call. lost.sort(key=lambda row: (str(row["club_since"] or ""), row["name"].lower()), reverse=True) return lost def _display_name(customer): """A name for a row on a page a human reads. `name` is required at this API, but `""` and whitespace both survive older imports, and a blank cell in a call list is a row nobody can act on. The email is the next best handle a winery actually has. """ name = (customer.get("name") or "").strip() if name: return name email = (customer.get("email") or "").strip() return email or "(customer with no name on file)" # --------------------------------------------------------------------------- # The command line # --------------------------------------------------------------------------- def main(argv=None): """`python pulse.py [--out FILE] [--quiet-days N]`; 0 on success. NO TRACEBACK EVER REACHES THE TERMINAL, and that is a deliberate feature of a file people copy. A traceback is a bug report about this script; what somebody running a nightly report needs is a sentence about THEIR problem — a missing scope, a bad host, an expired key — with the request id attached so support can find it. `ApiError.__str__` is already written to be that sentence, so this only has to print it and choose an exit code. """ parser = argparse.ArgumentParser( prog="pulse.py", description="Build a wine-club retention report from the SipsPOS " "public API. Read-only: it makes GET requests and " "nothing else.") parser.add_argument( "--out", default=DEFAULT_OUT, help=f"where to write the HTML report (default: {DEFAULT_OUT})") parser.add_argument( "--quiet-days", type=int, default=DEFAULT_QUIET_DAYS, help=f"how many days without an order makes a member 'quiet', and " f"how far back to read orders (default: {DEFAULT_QUIET_DAYS})") args = parser.parse_args(argv) base_url = os.environ.get("SIPSPOS_BASE_URL") token = os.environ.get("SIPSPOS_API_KEY") # Named individually, because "configure your credentials" sends somebody # to a README and "SIPSPOS_API_KEY is not set" sends them to a shell. for name, value in (("SIPSPOS_BASE_URL", base_url), ("SIPSPOS_API_KEY", token)): if not (value or "").strip(): print(f"club-pulse: {name} is not set. Set it and try again:\n" f" export SIPSPOS_BASE_URL=https://your-winery.sipspos.com\n" f" export SIPSPOS_API_KEY=sk_test_…\n" f"A test key (Admin -> API keys) reaches your sandbox twin, " f"so nothing you do while learning touches real customers.", file=sys.stderr) return 2 if args.quiet_days < 1: print(f"club-pulse: --quiet-days must be at least 1, got " f"{args.quiet_days}.", file=sys.stderr) return 2 # THE CLOCK, READ ONCE, HERE. Everything below this line is handed the # date rather than asking for it — see the module docstring. One read also # means the window `fetch` asks the API for and the window `analyse` # reasons about cannot disagree. today = datetime.date.today() # A naive `datetime`, not a formatted string: `report.render_html` does # the wording, and handing it the moment rather than one rendering of the # moment keeps the choice of format in the one module that draws pages. generated_at = datetime.datetime.now() client = sipspos.Client(base_url, token) try: payloads = fetch(client, today=today, quiet_days=args.quiet_days) except sipspos.ApiError as e: print(f"club-pulse: {e}", file=sys.stderr) if e.type == "permission_denied": # The single most common first-run failure, and the fix is two # checkboxes rather than anything to do with this code. print("club-pulse: this key needs the customers:read and " "sales:read scopes (Admin -> API keys). orders:read is " "optional — the shop is skipped without it.", file=sys.stderr) return 1 findings = analyse(payloads["members"], payloads["lapsed"], payloads["tiers"], payloads["orders"], payloads["sales"], today=today, quiet_days=args.quiet_days, orders_included=payloads["orders_included"]) # The report is handed the winery's HOST and the environment, and never # the client or the key: this file gets emailed around a winery, and the # surest way for a token never to appear in it is for the renderer to have # no way to reach one. html_text = render_html( findings, winery=urllib.parse.urlsplit(base_url).netloc or base_url, environment=client.environment, generated_at=generated_at) try: with open(args.out, "w", encoding="utf-8") as fh: fh.write(html_text) except OSError as e: print(f"club-pulse: couldn't write {args.out}: {e}", file=sys.stderr) return 1 totals = findings["totals"] print(f"club-pulse: {args.out} — {totals['members']} members, " f"{totals['at_risk']} at risk, {totals['lost']} lost " f"({client.request_count} API requests)") if client.environment == "test": # Said on the terminal as well as on the page: somebody who ran this # against the sandbox and then quoted the numbers in a meeting is the # failure this line exists to prevent. print("club-pulse: this is SANDBOX data — the winery in it is a twin, " "and the members are invented.") return 0 if __name__ == "__main__": sys.exit(main())