---
id: educacao/captacao-por-lancamento
industry: Education
usage: common
effort: medium
grain: Person x launch (sign-up) and person x launch x question (answer)
---

# Lead capture by launch

How many people signed up for each launch, who answered the survey and what their profile is, in a single table and without one copied query per launch.

## Executive summary

**For:** Digital product creators, open-enrollment course providers and schools that run launches, webinars and evergreen funnels with a capture page and a survey.

**What it solves**

- A new launch is a row in a table, no longer a copied query with dates and codes typed by hand.
- Sign-up and survey answer counted as different events, per unique person, on their first entry date.
- Lead profile (income, education, occupation, temperature) by launch, without mixing different questions in the same column.

**Why it matters:** During a launch, decisions are daily: raise budget, swap creatives, change the page. The team needs sign-ups and profile during capture, not after the cart closes. When each launch is a copied query, a forgotten date or a similar code pulls the wrong base, and the profile that shapes the offer comes from a different audience.

**Business questions it answers**

- How many people have signed up for the current launch so far, by day and by campaign?
- How many sign-ups answered the survey, and how many are hot, warm and cold?
- What is the income and education profile of this launch's hot leads?
- How does this launch's capture compare with the previous one on the same capture day?
- How many leads in this launch are MQLs under the product's criteria?
- Which page and which page version brought the most sign-ups?

**What you need**

- The source of sign-ups and answers connected to Nekt (page database, form, marketing automation)
- A launch table with code, product, opening, end of capture and cart
- Each product's qualification criteria, question by question

**What you get**

- A sign-ups table with one row per person and launch, with UTM, page and version
- A key-value answers table, one row per person, launch and question
- The launch spreadsheet updated automatically for the team
- Profile and MQLs by launch on the dashboard and in the AI agent

## Detailed

### The problem

The capture page writes one document per submission, with launch, page version, temperature and answers mixed in a JSON. The team asks for each launch's list, and the solution becomes one copied query per launch, with the code searched by a text fragment, the opening date typed by hand and the time zone fixed by subtracting three hours. Every new survey question becomes a new column, and the same question asked for different products lands in the same column.

### Business rules

1. **Launch by exact code, from a table.** Each submission's launch comes, in order, from the document's launch field, the same field inside the form data, the page path and the UTM, and is compared by equality with the launch table (code, product, opening, end of capture, cart). Searching by fragment (contains 'ora') catches similar codes and text that is not a launch. Opening and end of capture come from the table, never typed into the query. (Avoid: launch LIKE '%ora%' and the opening date typed into each query.)
2. **Sign-up and survey are different events.** A sign-up is a capture page submission. A survey answer is a thank-you page submission (for example version = 'typ'), with the profile. Classify by page or form type, not by missing answers: defining a sign-up as empty education leaves out those who answered and counts those who skipped the question. (Avoid: Sign-up = rows with nivel_escolaridade null.)
3. **Unique person, on first entry.** Count people per launch by normalized email (lowercase, no spaces), with phone only as support. The same person in the same launch counts once, on the first entry date; for attributes, use the most recent answer. When two sources record the same sign-up (form and page spreadsheet, old and new page versions), keep the one with the highest declared priority. (Avoid: Counting capture page rows as people.)
4. **Time zone and date format solved once.** createdAt arrives in ISO with Z (UTC) or as dd/mm/yyyy, sometimes without a time. Convert to America/Sao_Paulo in the capture layer, picking the parser by format, and compare already converted dates. Subtracting three hours by hand in one query and comparing text with dates in another shifts sign-ups between days. An unknown time is flagged, not invented. (Avoid: createdat - INTERVAL '3' HOUR in one query and a text comparison in another.)
5. **Key-value answers, by question reference.** Store one row per person, launch and question, with the question id or reference as the key. Pivoting by title merges different questions: the same investment forecast sentence existed once per product, and each launch creates its own expectations question. Any pivot happens at read time, by reference. (Avoid: One column per question title, with whichever answer shows up first.)
6. **Field id is per account.** In marketing automation with several accounts, the same qualification field has a different id in each account, and its title has already been renamed. Map account x field id in a reference table and pivot by id, always filtering by the pair. The contact field keeps only the latest answer, with its date: the answer from a specific sign-up comes from the form or the CRM. (Avoid: field IN (ids of the three accounts) or filtering by field title.)
7. **Temperature has an owner, and null is not cold.** Page temperature (hot, warm, cold, stored as q, m, f) comes from the page or campaign that brought the lead in. The advisor's perception is something else, in another column, and is usually sparsely filled. A missing value stays null: filling it with cold or adding a not-informed level inside the scale distorts the profile. (Avoid: Treating a lead without temperature as cold.)
8. **Normalized answers, no invented mappings.** Normalize case and spaces. Answers may arrive encoded (for example in hexadecimal) mixed with plain text: decode only when the result is readable text. Different scales across accounts or products (income brackets with different cut-offs) are only compared at the cut-offs they share. A value outside the option list stays visible, it is not discarded. (Avoid: Grouping income from two forms with different brackets on the same scale.)
9. **MQL is a per-product AND rule.** A lead is an MQL for a product when it meets every question marked as a criterion for that product. Questions marked as does not matter or not applicable are left out. A product with no defined criteria has no MQLs: do not treat all of its leads as qualified. Registration fields (name, email, company) never enter the criteria. (Avoid: A single MQL for every product, or every lead of a product with no rule becoming an MQL.)
10. **Fixed schema for the team spreadsheet.** Capture feeds spreadsheets and dashboards through reverse ETL. Fix the column types: a column that is empty on a given day cannot turn into a null type and break the load. A new question enters as a row in the key-value table, not as a new column in the spreadsheet. (Avoid: Letting the column type be inferred on every run.)

### How each submission becomes a sign-up, a survey or a temperature

Typical fields of the capture page document.

- **Capture page (version cap)** → Sign-up (`evento = cadastro`)
- **Thank-you page (version typ)** → Survey answer, with profile (`evento = pesquisa`)
- **launch, data.launch, path, utm** → Launch code, in this order (`equal to the table code`)
- **temperature = q** → Hot
- **temperature = m** → Warm
- **temperature = f** → Cold
- **empty temperature** → No temperature: null, not cold
- **Session form or hand-raise** → Entry into sales

### Definitions

| Metric | Definition |
|---|---|
| Launch sign-ups | distinct people (normalized email) with a capture page submission for the launch between opening and end of capture, on their first entry date |
| Survey answers | distinct people with a thank-you page submission for the launch |
| Survey response rate | survey answers / sign-ups for the same launch |
| Sign-ups by temperature | sign-ups by page temperature value, with no temperature on its own row |
| Capture day | days since the launch opened, to compare launches at the same point |
| Product MQL | lead that meets every criterion marked for the product, using the most recent answer to each question |
| Survey coverage | leads with at least one answer / leads in the period, stated next to any profile |

### Pitfalls

- **Query name says one launch, filter says another.** A query copied from launch to launch said in its name that it pulled four launches and filtered a fifth in the code. The team spreadsheet got the wrong base. How to avoid: One capture table with a launch column and filters coming from the launch table.
- **Opening date typed by hand.** The cutoff date typed into one query did not match the one in the comment right above it, and nobody knew the launch's real opening date. How to avoid: Opening and end of capture only in the launch table.
- **Questions merged in the pivot.** The BI pivoted answers by title, and the investment forecast question from several products landed in a single column, with one answer picked at random. How to avoid: Key by question reference.
- **Profile taken from a fraction.** The qualification survey only existed from a recent month onward and covered a small share of leads, but the whole year's profile was taken from that fraction. How to avoid: State coverage and the start of the series before talking about profile.
- **Hidden field that never arrived.** Only a minority of form submissions carried the hidden field with the funnel, and the rest had no launch. How to avoid: Derive the launch from the source page or form, when it belongs to a single launch.
- **Integration writing to the wrong field.** Consent and qualification fields in the automation received creative codes, phone numbers and the text trafegopago, and the profile ended up with meaningless values. How to avoid: Compare the stored value with the field's option list and flag the deviation.

### Acceptance test

- [ ] The sum of sign-ups per launch equals the number of distinct people in the source between opening and end of capture, with no difference.
- [ ] No person appears twice as a sign-up in the same launch.
- [ ] Adding a new launch takes only one row in the launch table, and the team spreadsheet picks it up on the next run.
- [ ] A sign-up made at 11 p.m. Brasília time appears on the correct local day.
- [ ] No profile column mixes answers to questions from different products: each question has a unique reference.

### Context document

```text
Lead capture by launch
- Launches: table <launch table> with code, product, opening, end of capture and cart. Date filters come from it.
- Launch code read from launch, then data.launch, then path, then the UTM <field>; normalized (lowercase, no spaces) and compared by equality. Never LIKE by fragment.
- Sign-up = capture page submission (version <cap>). Survey = thank-you page submission (version <typ>). Do not classify by missing answers.
- Person = normalized email. The same person in the same launch counts once, on the first entry date. Attributes from the most recent answer.
- Dates in America/Sao_Paulo, converted in the capture layer. createdAt arrives in ISO with Z or as dd/mm/yyyy.
- Key-value answers: person x launch x question reference. Do not pivot by title.
- Automation with several accounts: field id per account through the reference <account x field table>. The contact field keeps only the latest answer.
- Page temperature: q = hot, m = warm, f = cold; empty = null. Advisor perception is another column.
- MQL per product: every marked criterion (AND logic); does not matter is left out; a product with no criteria has no MQL.
- Always state survey coverage and the last load.
Exceptions by client:
- <client>: launch <code> had a second wave (<code>L2); add both only when asked.
- <client>: income brackets for <product> have different cut-offs; compare only at no income, up to <value> and above <value>.
- <client>: MQL for <product> = <question> in <values> AND <question> in <values>.
```

### Variations

- **Evergreen funnel:** Lead = one person's entry into a funnel (ebook, mini-course, session); the same person in the same funnel on the same day counts once, and a hand-raise becomes an entry into sales.
- **Qualification in the CRM:** When qualification lives in CRM fields, the customer type can come from the marketing survey answer, matched by email, with the number of fallbacks stated.
- **Buyer profile:** Cross buyer, class and job title to profile who buys high-ticket courses, segmenting only buyers, not the whole lead base.