Digital products and renewals

The grain is the installment or charge. Sale = distinct transaction code with an approved first installment (`purchase.recurrency_number` empty or 1). Later installments are continuations: they count toward the month's receipts, never as a new sale. Sales, customers and refunds are distinct counts and do not add up across months, courses or channels: recalculate for the requested slice.

Valid sale = `purchase.status` APPROVED or COMPLETE: the platform moves from APPROVED to COMPLETE when the guarantee period ends. Refund = REFUNDED and CHARGEBACK. When statuses arrive in mixed spellings (Portuguese and English), use flags computed once and never filter by literal. The history query without a status filter returns only approved and complete sales: request the other statuses explicitly to see refunds and cancellations. Never sum amounts without a status filter.

The classification comes from the product reference table (purchase type for each product id) and the installment number: later installment = continuation; first installment of a renewal product = renewal; initial fee product = initial fee; everything else = new purchase. A first purchase is that person's first time on that product, not a new customer for the school. A course can have several product ids (upfront, subscription, 6 and 12 months): do not deduplicate by name.

The platform does not record the refund date: the reversal becomes a status on the original sale row. Refunds are counted in the month of the sale, and a month's figure keeps growing for weeks after it ends. Compare months of the same age and say so. To track maturation, use phases: approved, platform guarantee period over (`warranty_expire_date`), consolidated by the house rule (for example, 30 days after approval) and refunded.

Decision order: 1) the sale's affiliate (the cookie, in `hotmart_sales_participants` with role AFFILIATE) if the account is in the reference as sales team, former salesperson or external affiliate; 2) salesperson code found in `tracking.source_sck`; 3) otherwise, marketing, including sales with no sck. Record the reason (affiliate, sck, both, cookie vs. sck) and the salesperson who decided it. The list of codes and accounts lives in the reference table, never in the transformation code. An affiliate not on the list counts as marketing.

The sales team also sells launches and renewals. The source offer (evergreen, launch, webinar, live, renewal, organic) is a separate column, derived from the sck pattern. In the funnel, summarize as direct (the funnel's own offer), indirect (launches and events), renewal and unidentified. When indirect sales show up in a funnel, the launch cost is not in that funnel's numbers: say so.

Gross = transaction price converted to reais: BRL directly, EUR at the day's PTAX, other currencies at the platform's rate to dollars times the dollar PTAX. Net = producer commission (`hotmart_sales_commission`, source PRODUCER), what the school keeps. Pick one measure for billing and ROAS and state it. Contracted (price x installments on the first installment of the plan) is different from received in the month, and future installments are not in the data.

Grain: one row per subscription, across all accounts. Include = status ACTIVE, DELAYED or OVERDUE and a product marked for inclusion. Projected = sum of `price.value` for included subscriptions by month of `date_next_charge`, in the subscription currency (`price.currency_code`), never in the currency of the last transaction. Scheduled churn = not included. State the denominator: active only or active plus late. Late is not the collection queue: DELAYED already has the next charge rescheduled; the real queue is next charges up to today.

The forecast table is overwritten on every run: record the snapshot date in a column. For a historical series, write in append mode with the snapshot date, and never add different snapshots together. Month forecast = actual through yesterday + remaining projected x conversion rate (actual / projected through yesterday). Early in the month, a 3 or 4 day rate is unstable: use the previous month's closed rate until around day 10. Contracted pass-throughs that come in as subscriptions stay out of the rate.

Cancellation = status CANCELLED_BY_CUSTOMER, CANCELLED_BY_SELLER or CANCELLED_BY_ADMIN, with the date in `end_accession_date`. Filter by status, never by having a date: reactivated subscriptions also carry the date. Segment by who canceled: a batch canceled by the admin is an operational event, not churn. Accounts read by two sources produce the same subscription and the same transaction twice: deduplicate by code.