Sources & Method — NEETprep Paid Acquisition

This dashboard measures Google Ads campaign efficiency for NEETprep, refreshed daily. Spend comes from the Ads API; leads, conversions and revenue are reconstructed from first-party data (the product database, the lead-form sheets, HubSpot and the GA4 export). The hard part is honest attribution — tying a rupee of spend to the lead it bought and the payment that lead made — so this page is deliberately explicit about every join and every assumption.

Data sources

live API Google Ads API v21 — spend, clicks, impressions

Account 146-940-3593 ("NEETprep 1", INR). Pulled per campaign × calendar day.
Why not BigQuery? The BQ mirror neetprep_ads.google_ads_daily is ~3.7× inflated — the same campaign-day is ingested ~3× with no dedup, and it's stale since 2026-05-12. Summing it once produced a wrong "app-install is losing money" conclusion. We always use the live API.

live Product database (Aurora, read-only) — leads, payments, contacts

Leads = rows in User, cohort date = createdAt (IST, +330 min).
Conversion / revenue = Payment where status = 'responseReceivedSuccess', first clean payment per user.
Contacts = email + phone, used to match leads to HubSpot and to the lead sheets.

live GA4 / Firebase export (BigQuery) — app-install attribution

analytics_181916006.events_* gives the first-touch campaign → Firebase user_id, which equals DB User.id. This is how app-install leads (Appdownload) are tied to their campaign.
App retarget uses the same export but the session/event campaign param (not sticky traffic_source), one touch per user×campaign×day, so existing users who reinstall or reopen from an Appdownload ad show up on the Retargeted view.
Reliable from ~2025-11-30 only; earlier cohorts have no GA4 coverage.

live Lead-form sheets + HubSpot — web lead-gen leads

Each web lead-gen campaign collects phone numbers on a Google lead form → a per-campaign Google Sheet (read via the Sheets API + service account). 3 sheet-less campaigns get their leads from HubSpot by utm_campaign.
Why a separate path? Web form-fills mostly never register: in a sample, 76% never became a DB User and GA4 saw only ~6%. So GA4 alone undercounted these campaigns up to 56× (Search-Practice_Test showed 88 leads vs a true 4,839). The sheets/HubSpot are the authoritative lead source for these campaigns.

live Max Topper — form leads and calling sheet

The New MaX Topper and MAX TOPPER TEACHOO sheets supply submission dates, mobile numbers and campaign tags. Test entries, implausible phones and invalid dates are excluded; each campaign/phone is counted once at its earliest submission.
The Ma-X Toppers Leads calling sheet supplies the current recorded status by mobile number. Repeated rows are merged; the last nonblank substantive status in sheet order is used, and a Duplicate label never erases a substantive outcome. These are current labels on submission cohorts, not dated call events or verified answered calls.
Calling-sheet phones absent from both current form sheets are included under Max Topper - Legacy / unassigned. Untagged current forms remain in their own unassigned rows. No campaign is inferred from spend timing or from another brand's Topper name.
The sheet tags cbse_10_classroom_test_series, cbse_10_classroom_test_series_parents and cbse_10_classroom_test_series_youtube map to the Ads IDs 24249822898, 24250194143 and 24267257881, verified from the live ads' landing URLs. The dashboard shows their actual Ads names and joins API spend to sheet leads for CPL. Known numeric campaign tags are also supported.
Max Topper payments, MQLs, connected-call counts and new/retarget classification have not been verified. Those metrics display as unavailable. Spend/CPL are unavailable for Teachoo and unassigned rows where no Ads campaign mapping is verified.
The Max Topper leads & calling panel follows brand, campaign, channel and submission-date filters and always shows all submissions in that selection, independent of the new/retarget view.

live HubSpot CRM — MQL, called, connected

Matched by phone (and email). Properties read: hs_v2_date_entered_marketingqualifiedlead, not_mql, notes_last_contacted.
We never use lifecyclestage for counts — it was contaminated by a May-2026 bulk upload. HubSpot is used only to tag matched leads.

Definitions (CEO-confirmed)

MQL = has a date-entered-MQL value and not_mql is blank. (Sales mark a reason in not_mql rather than moving the stage, so this is the honest "live MQL".)
Called = notes_last_contacted is set.
Connected = Called and not_mql is not one of the four "not connected" codes. Note: the API returns the stored value, not the clean label — so we match Not reachable phone, NC -2, NC- 3, NC - 4.
CPL = spend ÷ leads · CPA = spend ÷ paying students · Cost/MQL = spend ÷ MQLs · ROAS = revenue ÷ spend (1.0× = breakeven) · Net ROAS = net revenue ÷ spend.
Net revenue = what NEETprep keeps. Online products: net = gross ÷ 1.18 (18% GST). Classroom Test Series: net = gross ÷ 2.36 (GST, then 50% revenue share to the partner centre where the test is organised). Classroom payments are identified by the canonical Classroom course-id list (same as the product cohort dashboard) or a course name containing "classroom".
Retargeted leads (web + app-install): an existing DB user re-engaged by the ad — either a web form-fill whose phone matches a user created more than 7 days before the fill, or an Appdownload session touch (GA4 event campaign) by a user_id created more than 7 days before the touch. Conversion = the earliest clean payment on/after the touch (any payment, not just the user's first), credited at weight 0.5. Guards: a payment credited to a new-lead conversion anywhere is never also credited as retarget; a payment goes to at most one retarget campaign (the latest touch preceding it); each touch converts at most once. A touch within 7 days of the user's own registration is the same acquisition and stays on the new-lead path.
⚠ Caveat: the 0.5 weight is a flat judgment call, not measured incrementality — a retargeted buyer might have purchased anyway (weight → 0) or been genuinely reactivated by the ad (weight → 1). Treat retarget and blended revenue as directional.

How attribution works

App-install campaigns (Appdownload): GA4 first-touch → user_id → DB. Leads = registered users; the GA4 first-touch date is not used as a gate (it only reflects the earliest in-window event, not true acquisition), so cohorts are anchored on DB createdAt.
Web lead-gen campaigns (Search, PMax-leads, Demand Gen): leads = phone numbers from the sheets / HubSpot. Conversion/revenue = phone → DB User → first clean Payment. MQL/called/connected = phone → HubSpot.
Everything is grouped by lead-creation date; conversions are attributed back to the lead's cohort, and the conversion-window control (D7 / D30 / D90 / Lifetime) sets how long after the lead a payment may land.

Assumptions & things to distrust

Web-campaign revenue is conservative. It's phone-matched to DB payments; when a payer used a different phone on the form than on their account, we miss their revenue. So web lead-gen leads and CPL are trustworthy, but their ROAS reads low.

Recent date ranges understate ROAS. Spend is counted immediately but revenue accrues over months, so a recent or open-ended window looks worse than it will end up. Judge a campaign on a closed, matured window at Lifetime.

Divested courses excluded from revenue: course IDs {3125, 87, 2135, 3092, 5501} (Abhyas, NEET-PG, Reflex), via actualCourseId and CourseOffer.
Revenue = first clean payment per user (one conversion per payer). Repeat/upgrade purchases add ~6% and are not counted as extra conversions.
Spam lead numbers are dropped (all-same / sequential / <3 distinct digits), so raw lead counts are slightly lower than an unfiltered sheet total.
ROAS like-for-like: compare revenue and spend over the same cohort window. Pairing a campaign's full-life revenue against a partial-window spend overstates ROAS (this is why an earlier "app-install ≈ 3×" was really ~1.6× lifetime).
GA4 coverage begins ~2025-11-30; app-install attribution before that is not available.
Remarketing campaigns are under-counted by design. The dashboard attributes only new leads (first acquisition), so re-engagement of existing users via remarketing isn't credited here. Volume is low; to be handled later.

Refresh

A daily job re-pulls spend, GA4, DB leads/payments/contacts and the lead sheets/HubSpot, rematches the incremental HubSpot status, rebuilds the fact file and redeploys. The dashboard is static and computes all filters (date range, channel, window, grain) in the browser.

← back to the dashboard