Lead capture by launch

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.