---
id: educacao/infoprodutos-renovacao
industry: Education
usage: common
effort: medium
grain: Transaction (invoice or installment) and subscription (next charge)
---

# Digital products and renewals

How much each course sold in new sales, how much renewed, how much came back as refunds, how much came from affiliates and salespeople, and how much renewal revenue is expected to come in.

## Executive summary

**For:** Digital product creators and schools that sell courses, subscriptions and mentoring programs through platforms such as Hotmart and Eduzz.

**What it solves**

- New sales, renewals and follow-on installments kept apart, without counting the same purchase several times.
- Refund, affiliate and salesperson for each sale decided by a single rule that the whole team uses.
- A monthly renewal forecast that does not add canceled subscriptions or mistake a rescheduled late payment for a default.

**Why it matters:** For digital products, renewals are often one of the largest revenue lines of the month and the only predictable one. A forecast that adds canceled subscriptions or guesses the currency misleads the target. And without a channel rule, sales, affiliates and marketing all claim the same sale.

**Business questions it answers**

- How much did the course bill this month through yesterday, and how many sales were there?
- How much was new sales and how much was renewals last month?
- What was the average ticket of the last launch and how many refunds has it had so far?
- How much did the sales team sell, how much did affiliates sell and how much did marketing bring in?
- How much renewal revenue is expected next month, by course?
- How much renewal revenue did we lose this month to cancellations?

**What you need**

- Hotmart or Eduzz connected to Nekt, including commissions, participants and subscriptions for every account
- Reference table for products (course and purchase type) and for salespeople and affiliates (code, account and channel)
- A decision on which amount counts as revenue: gross including tax or the producer's net

**What you get**

- A purchases table with classification, phase, channel, salesperson and amounts in reais
- The renewal forecast by subscription, with an include flag and the subscription currency
- Revenue dashboard by course, with refunds on the sale date
- An AI agent that answers questions on sales and renewals with the rules applied

## Detailed

### The problem

The platform returns one row per installment or charge, with status in several spellings and no refund date. Whoever sums the rows counts the same purchase several times, mixes renewals with new sales and even books canceled sales as revenue. The renewal forecast, taken from a manual export, adds canceled subscriptions and treats the currency of customers who pay in euros as reais.

### Business rules

1. **A sale is the first installment, counted by transaction.** 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. (Avoid: Counting rows of the sales history as sales.)
2. **Valid status accepts approved and complete.** 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. (Avoid: Filtering only COMPLETE and losing sales still within the guarantee period.)
3. **New, renewal and continuation from the product reference.** 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. (Avoid: Calling every first purchase of a product a new customer.)
4. **Refunds stay on the sale date.** 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. (Avoid: Comparing last month's refunds with those from six months ago as if both were closed.)
5. **Channel: the affiliate beats the link code.** 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. (Avoid: Deciding the salesperson from the sck text, with a different rule in each report.)
6. **Channel is not offer.** 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. (Avoid: Attributing to the sales team every launch sale that went through a salesperson, and to the launch the channel.)
7. **Gross or net revenue, stated.** 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. (Avoid: Adding amounts in the original currency to amounts in reais.)
8. **Renewal forecast only with what can come in.** 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. (Avoid: Summing the entire subscriptions table as the month's forecast.)
9. **The forecast is a snapshot of the day.** 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. (Avoid: Combining snapshots from several days and multiplying the projected amount.)
10. **Cancellation by status, segmented by who canceled.** 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. (Avoid: Counting everything with a cancellation date as churn.)

### Subscription status and renewal forecast

Platform statuses and where each one goes.

- **ACTIVE** → Up to date: counts toward projected (`considerar = true`)
- **DELAYED** → Late with rescheduled charge (`considerar = true; outside the collection queue`)
- **OVERDUE** → Due and unpaid (`considerar = true; collection queue`)
- **CANCELLED_BY_CUSTOMER** → Canceled by the subscriber (`scheduled churn`)
- **CANCELLED_BY_SELLER** → Canceled by the producer (`scheduled churn`)
- **CANCELLED_BY_ADMIN** → Canceled by the platform (`separate: may be an operational event`)
- **STARTED, INACTIVE** → Out of scope (`considerar = false`)

### Definitions

| Metric | Definition |
|---|---|
| Sales | distinct transactions with the first installment in a valid status (APPROVED or COMPLETE), by sale date in America/Sao_Paulo |
| Billing | sum of the amount in reais of valid transactions in the period, on the stated measure (gross including tax or the producer's net) |
| Average ticket | billing / sales for the same slice, aggregating before dividing |
| Refunds | distinct transactions with status REFUNDED or CHARGEBACK, in the month of the sale; amount as a complement |
| Paying customers | distinct emails with a valid transaction in the period; they do not add up across months |
| New sale vs. renewal | classification of the first installment by the product's purchase type in the reference; later installments are continuations |
| Projected renewals | sum of price.value for subscriptions with considerar = true and next charge in the month, in the subscription currency |
| Scheduled churn | amount of subscriptions with considerar = false whose charge would fall in the month; different from cancellations recorded in the month |
| Purchase cohort | of the people who entered a funnel or requested contact in the month, how many purchased within 7, 30, 60 and 90 days, excluding prior buyers, with a complete-window flag |

### Pitfalls

- **Canceled sale with an absurd amount.** A dashboard billing measure did not filter by status and added a canceled transaction worth trillions, plus reversed sales. How to avoid: Every amount goes through the valid status filter, including in derived measures.
- **Currency guessed as reais.** The currency came from the subscription's last transaction. The join failed on more than half the rows and the BRL default turned euro and dollar subscriptions into reais. How to avoid: Use the subscription's own currency (price.currency_code), which is always filled in.
- **The same account read twice.** Two sources pointed to the same platform account and about 15% of the forecast rows were exact duplicates. How to avoid: Deduplicate by transaction and subscription code after combining the accounts.
- **Manual export above reality.** The manually exported forecast report came in 33% to 56% above what the API showed, and was treated as the truth. How to avoid: Automate through the API and, where they diverge, investigate the export before adjusting the number.
- **Late payments inflating collections.** Counting every late payment as the collection queue overstated the queue by 3.7 times: most already had the next charge rescheduled to the future. How to avoid: Queue = OVERDUE or next charge up to today; rescheduled DELAYED stays separate.
- **Broken amount at the source.** In one account, several subscriptions came in with an amount of R$ 1.00 and the forecast by amount for that course made no sense. How to avoid: List courses with subscription amounts outside the price range and use counts until it is fixed at the source.

### Acceptance test

- [ ] For a closed month, sales, billing, paying customers and refunds match to the cent the platform's official report or the validated dashboard.
- [ ] New purchase + renewal + continuation + initial fee = total valid transactions in the period.
- [ ] No canceled subscription enters the projected amount, and the forecast has one row per subscription after deduplication.
- [ ] Every sale has a channel and a channel reason; no sale whose affiliate is in the reference as a salesperson falls into marketing.
- [ ] Recalculated 30 days later, a month's refunds are equal to or higher than before, never lower.

### Context document

```text
Digital products and renewals
- Grain: transaction (installment). Sale = distinct transaction of the first installment (recurrency_number empty or 1) with status APPROVED or COMPLETE. Later installments = continuation.
- Refund = REFUNDED or CHARGEBACK, counted in the month of the sale. There is no refund date. Refunds for recent months will still grow: compare months at the same age.
- Classification from the product reference <table>: continuation, renewal, initial fee or new purchase. First purchase = first time on that product, not a new customer.
- Channel: 1) the sale's affiliate in the salespeople reference; 2) salesperson code in the sck; 3) marketing. Use the canal_venda column; do not recalculate from the sck. Channel is not the source offer.
- Amount: <official measure: gross including tax or the producer's net>, in reais. EUR at the day's PTAX (Central Bank reference exchange rate); other currencies at the platform's rate to USD x USD PTAX.
- Do not sum amounts without a status filter. Sales, customers and refunds do not add up across rows.
- Renewal forecast: only considerar = true (ACTIVE, DELAYED, OVERDUE and a marked product). Amount = price.value; currency = the subscription's price.currency_code; axis = date_next_charge. Scheduled churn = considerar = false.
- State the forecast denominator (active only or active + late). Collection queue = next charge up to today.
- The forecast is a daily snapshot: use one snapshot per query and state its date.
- Cancellation = status CANCELLED_*, date in end_accession_date, split by who canceled.
- Answer with totals. Buyer data stays inside the query.
Client-specific exceptions:
- <client>: the renewal forecast is released only to <owner>.
- <client>: the course <course> has a broken subscription amount at the source; use counts, not amounts.
- <client>: payments through <method outside the platform> are not in the data.
```

### Variations

- **Event tickets:** Ticket-type products; event taken from the name before the separator, with a mapping of spellings; complimentary vs. paid by payment type; kept out of recurring revenue.
- **Product ladder:** Sequence of programs bought by people who entered the funnels, from the lead magnet to the flagship program.
- **Calls and purchase:** How many buy after the strategy session, how quickly and at what ticket, matching calendar and buyer by email.