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.- 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 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 · ROAS = revenue ÷ spend (1.0× = breakeven).
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.
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