t1k:marketing:analytics:crawldata

Field	Value
Module	`analytics`
Version	`1.16.0`
Effort	`medium`
Tools	—

How to invoke

/t1k:marketing:analytics:crawldata

Ad Spend vs Revenue Crawl (Profit / ROAS)

Pull ad-spend from Mintegral, Google Ads, and Unity Ads, pull ad-revenue from Adjust (primary), AppLovin MAX, CloudX, and AdMob, and merge everything per app package name into one Profit/ROAS report: a Title (human-readable project name), a column per spend network, a spend Total, Ad Revenue (Adjust drives Profit/ROAS), a column per other revenue source, Profit (revenue − spend), and ROAS (revenue ÷ spend). Output is sorted by package; Title / Profit / ROAS are derived at output time, never stored, and new apps map automatically (Title from Adjust’s app dimension). For the operator’s daily deliverable — a Net-Revenue report that uses AppLovin (MAX) for chosen apps and Adjust for the rest — see Net-Revenue daily report.

Executed by

The pull-and-reconcile work delegates to cheap-tier agents; only the interpretation stays on a reasoning tier (routing SSOT: rules/orchestration-rules.md § “Task-Type → Agent Routing”):

Step	Agent
Pull one named vendor report for stated dates and report the figures	`t1k-fact-fetcher`
Reconcile spend/revenue across two or more sources against a stated tolerance	`t1k-data-verifier`
Aggregate a single source into the per-package table	`t1k-metrics-reporter`
Explain WHY a package’s spend or revenue looks wrong	`t1k-debugger` — reasoning tier, not cheap

Profit and ROAS are derived at output time from columns the connectors already returned, so the arithmetic is fully specified before the agent starts — that is what makes this cheap-tier work rather than analysis. The moment the question turns from do these sources agree? to why don’t they?, it has changed tier, and that is a different agent rather than a bigger model on the same one.

When to Use

Pulling a day’s or a date range’s ad spend, combined per package
Producing a merged spend-vs-revenue report with Profit / ROAS columns
Standing up the daily scheduled Excel export
Producing the Net-Revenue daily report (AppLovin chosen side)
Adding a new ad network / revenue source, or per-app revenue overrides
Computing CloudX First Look P90 CPM bid floors from MAX report data
Investigating why a package’s spend or revenue looks wrong

Connectors at a glance

Eight live connectors (plus one retired). China is excluded from every revenue source; spend sources are all-countries.

Source	Kind	One-liner
`mintegral`	spend	Advertiser Performance Reporting API; joins by `package_name`. Multi-account: extra advertiser accounts via `MINTEGRAL_<n>_ACCESS_KEY`/`_API_KEY` (n = 2–9), all summed into the one Mintegral column (same shape as Unity’s multi-org). A half-configured extra logs a skip warning instead of silently under-reporting.
`googleads`	spend	GAQL via the Google Ads API; joins each App campaign’s `app_id` → package.
`unity`	spend	Advertising Statistics API v2 acquisitions report (Service Account Basic auth); Management API maps Unity app id → store package; UTC day buckets, 429 backoff. Multi-org: extra accounts via `UNITY_ADS_<n>_*` (n = 2–9), all merged into the one `unity` column.
`applovin-ua`	spend (retired)	AppLovin advertiser (AppDiscovery UA) report. Neither crawled nor a column — buying stopped, so the connector is unregistered and its spend column was dropped from the CSV/HTML/XLSX reports. No historical number changed (`total` comes from crawled rows, not from the display columns). Buying window, figures, and how to resume: `references/applovin-ua-retirement.md`.
`adjust`	revenue (primary)	Datascape Report Service; drives Profit/ROAS; supplies the Title. Aggregates every network, CloudX included — nothing is added on top.
`adjust-s2s`	revenue (S2S source)	Same Report Service, scoped to the S2S ad-revenue source via the `ad_revenue_sources=S2S Ad Revenue` filter (a filter, not a dimension); emits an `adjust-s2s` column. Reconciliation against `cloudx` only — the Net-Revenue report no longer consumes it (the S2S swap is retired). Reuses `ADJUST_API_TOKEN`.
`applovin`	revenue (reference)	MAX Ad Revenue Report; estimated revenue re-bucketed to UTC+8; own column, does not change Profit/ROAS. Chosen side for `appLovinPreferred` apps in the Net-Revenue report.
`cloudx`	revenue (reference)	CloudX per-app report; joins by `app_id` (= package), one UTC+8-day window. Already inside Adjust — crawled for reconciliation against `adjust-s2s`, never added on top.
`admob`	revenue (reference)	AdMob Reporting API (OAuth2 `admob.readonly`); publisher `ESTIMATED_EARNINGS` by app, `apps.list` maps app id → store package; own column, does not change Profit/ROAS.

Per-app revenue overrides: --html uses Adjust as the primary revenue but applies per-app overrides automatically, driven by the committed config tool/report-overrides.json in the companion repo (data-driven — edit the JSON, no code): applovinOverride = packages whose Ad Revenue uses AppLovin instead of Adjust; cloudxAdd = packages whose Ad Revenue = Adjust + CloudX. The HTML shows a 4-KPI headline (Spend total / Ad revenue / Profit / ROAS), ROAS as %, and override row markers.

Running a crawl

Clone the companion repo (below); the crawler lives at .claude/skills/t1k-marketing-crawldata/tool/ (the repo-root tool/ holds only ad-hoc scripts, not the crawler). From that directory:

cd .claude/skills/t1k-marketing-crawldata/tool
npm install                                              # first run only
npm run pull -- --yesterday --xlsx ../data/spend.xlsx    # merged daily export
npm run pull -- --date 2026-06-10 --source cloudx        # a single source

A scheduler (launchd / cron) can run the merged daily export automatically. All flags, output formats, the daily-export setup, and per-app revenue overrides (--html + tool/report-overrides.json) are documented in the companion repo.

Net-Revenue daily report (AppLovin chosen side)

The operator’s daily deliverable, built by tool/gen-daily-report.mjs (in the companion repo) on top of a --pivot crawl CSV — interactive HTML + Excel. It sets Net Revenue per title: AppLovin (MAX) for appLovinPreferred apps (Adjust replaced, not added), Adjust for everyone else (CloudX is already inside Adjust).

The S2S swap (a former third Net-Revenue class using CloudX to replace Adjust’s server-to-server estimate) is retired — both sources are still crawled for reconciliation only; full migration history in references/net-revenue-history.md.

How the S2S figure is crawled — the adjust-s2s source. Adjust’s per-app S2S ad revenue is exposed by the reports-service API, but as a filter, not a dimension: metrics=ad_revenue + ad_revenue_sources=S2S Ad Revenue (the exact source id comes from filters_data?required_filters=ad_revenue_sources; use the bare ad_revenue_sources= key — the __in suffix form is silently ignored). The adjust-s2s connector wraps this, so npm run pull --source adjust-s2s (and the daily --pivot) emit an adjust-s2s column — kept for reconciliation, no longer consumed by the report.

Earlier docs claimed the source split was “not exposed” — that probe only tested dimensions (partner / network / monetization_partner carry UA attribution / “unknown”). The ad_revenue_sources filter is the correct mechanism.

cd .claude/skills/t1k-marketing-crawldata/tool
npm run pull -- --date 2026-06-30 --pivot --out ../data/spend-2026-06-30.csv
node gen-daily-report.mjs ../data 2026-06-30   # → data/daily-report-2026-06-30.{html,xlsx}

10 columns: Title · Total Spend · Mintegral · Google Ads · Unity · Rev Adjust · Rev AppLovin · Net Revenue · Profit · ROAS. HTML: 4-KPI headline; click-any-header sort; hide cost=0 / hide rev=0 toggles (visual only); ROAS band filter (all / high ≥100% / low <100%) that recomputes the headline; ROAS green >100% / red <100%. Full generator + column semantics live in the companion repo (tool/gen-daily-report.mjs).

14-day sparklines — REUSE saved data, never re-crawl

When the saved per-day files are present, the daily HTML adds three per-title 14-day sparkline columns — Spend, Net Rev, ROAS (dashed 100% line, hover for daily values). The trend is assembled by reusing the saved per-day spend-<date>.csv files in the report dir: each day is crawled once (its own daily run), saved, and reused thereafter — old days are never re-crawled. This also sidesteps the Mintegral 8-day range limit (see Gotchas), since every saved file is a single-day crawl. The daily job’s --out ../data/spend-<date>.csv IS the SSOT the sparklines consume.

Per-title trend report — `gen-trend-report.mjs`

node gen-trend-report.mjs <dir> <endDate> [days=7] builds a standalone is-each-game-trending-up-or-down HTML from the same saved per-day files (0 crawls): one row per title with Spend/Net/ROAS sparklines, a last-third-vs-first-third Δ, and a ▲ Better / ▼ Worse / ~ Flat verdict; sortable, red/green row edge. Writes trend-<days>d-<endDate>.html.

Discord delivery — `notify-discord-daily.mjs`

node notify-discord-daily.mjs <daily-report-<date>.xlsx> [.html] posts the daily headline (Spend / Net Rev / Profit / ROAS) + top titles to DISCORD_WEBHOOK_URL with both files attached. Missing webhook → exit 0 (best-effort).

Gotchas

AdMob ESTIMATED_EARNINGS is the MEDIATED total — only its standard half may be summed alongside MAX. The Network Report figure includes the slice AdMob fills on behalf of MAX, so summing it double-counts (~4x over-report). Split with mediationReport:generate + AD_SOURCE_INSTANCE: AdMob (default) is the MAX-adapter bucket (never sum), named AdMob Network instances are standard demand (safe to sum).
AD_SOURCE and AD_SOURCE_INSTANCE are different dimensions sharing the label AdMob Network — always name the dimension. Filtering the wrong one reproduces the very double-count above.
NEVER pass timeZone='America/Los_Angeles' because the error message named it. AdMob has no hourly dimension and accepts only an empty timeZone or LA; LA is a real alternate reporting window that re-buckets every day (~15h shift) and moves the two tiers in OPPOSITE directions, so it never looks like a uniform offset. Omit it.
AdMob’s reporting timezone is per-account — do NOT assume UTC+8. Read accounts.get on the explicit account id (accounts.list may 400) and compare numbers; the accepted-value list cannot tell you the window.
AdMob revises ESTIMATED_EARNINGS continuously — small drift on a settled date is EXPECTED, not a sync bug. Both tiers move together, so the MAX-vs-adapter relationship holds. Don’t chase it.

Full figures, cross-tabs, and the timezone proof chain: references/admob-revenue-splits.md.
A monetization partner can also sell traffic — that cost side is easy to miss entirely. AppLovin bought traffic while only its MAX revenue was crawled, so its spend appeared nowhere and every title it bought for read too well. Buying has since stopped and applovin-ua is retired, but if it restarts, re-register the connector before trusting a day’s ROAS. The buying window has hard edges — a $0 outside it is real, not a tracking gap, so do not normalize those edges out of a week-over-week comparison.
A dropped spend COLUMN does not drop the spend. total is accumulated from the crawled rows in pivot.ts, not summed from the displayed columns — so retiring a connector leaves every historical Total Spend, Profit, and ROAS untouched; only the column-vs-total arithmetic visibly changes. Check where a total actually comes from before predicting what removing a column will do to it. Figures and window: references/applovin-ua-retirement.md.
Mintegral silently drops out of pulls longer than 8 days. A --start/--end range >8 days returns rows with no Mintegral spend (its per-request cap), so the total is silently understated. Pull in ≤8-day chunks, or rely on the per-day reuse model (single-day files always include Mintegral).
MAX (AppLovin) ≈ 92–94% of total ad revenue; CloudX ≈ 4–5% and growing. MAX mediation is the dominant monetization; CloudX is a small S2S contributor.
A Unity Service Account missing Advertise API Viewer fails SILENTLY — the day total stays right while every per-title row for that org is wrong. The Management call does not 403: /advertise/v1/organizations/{org}/apps returns 200 {"total":0,"results":[]}, so the app→package map is empty and every app lands under unknown. The acquisitions report still returns 200 (it only needs Advertise Stats API Viewer), so spend looks present in the total. Verify a newly-added org with a direct apps call and assert total > 0 — an empty list is a missing role, not an org without apps.
Unresolved rows reconcile in three passes: exact title → normalized title → package slug. Sources disagree on titles (SG - Arrow Out: Charm Reveal in Adjust vs Arrow Out: Charm Reveal from Unity), so the exact-title pass misses and the spend strands on its own row: correct day total, but that title’s Profit/ROAS split across two rows. The normalized-title pass strips a studio prefix (TO - , SG - ) and a trailing platform marker ((AND), IOS) from both sides and catches most of these. The slug pass still backs it up when the bundle id spells the name out (com.arrow.out.charm.reveal). Every pass requires a UNIQUE match, so Android/iOS siblings normalize to the same key, stay ambiguous, and are correctly skipped rather than merged onto the wrong platform’s package. An app whose bundle id does not spell its name and whose title no source states the same way still strands — fix the role, not the title.
Unity’s daily quota dies after a few full crawls — and a failed source is non-fatal. A single-day --pivot run also pulls the prior 7 days for the comparison sheets, i.e. 8 Unity calls per crawl. Two or three full crawls in one day exhausts it and later days return unity: FAILED — 429 Quota has been exceeded. The run continues and writes a report with those days’ Unity column at 0, so the failure understates spend rather than stopping the job — read the per-source row counts in crawl.log before trusting a re-crawl’s comparison blocks. The primary day is pulled first, so it normally survives.
An unconfigured buying account is indistinguishable from an app with no spend. A second Mintegral advertiser account once went unwired: its apps showed revenue-only rows, and one app’s Unity spend could not be reconciled to a package at all (stranded under unknown) because no source supplied the package↔title link. Wiring it turned a day that looked like 105% ROAS into 100%, and exposed a title running at 26%. When a title’s ROAS looks implausibly good, check for a missing buying account before believing it.

Full setup, per-connector API reference & the runnable crawler

This skill is a discoverable playbook — the code, the per-connector setup docs, and the credential reference are owned by the companion repo (single source of truth):

The1Studio/marketing-crawl-ads-spend https://github.com/The1Studio/marketing-crawl-ads-spend

Clone that repo to run the crawl — it is self-sufficient (crawler code + setup docs + .env.example). Everything lives under .claude/skills/t1k-marketing-crawldata/:

SKILL.md — full operator guide (every flag, output format, scheduler).
*-setup.md — per-connector setup (adjust-setup.md, applovin-setup.md, cloudx-setup.md, google-ads-setup.md) and raw-data-crawl.md.
api-credentials.md — consolidated API + credential-NAME reference (all 5 sources).
tool/ — the Node/TypeScript crawler, report-overrides.json (per-app revenue override config), and .env.example (documents required env-var NAMES, no values).

Credentials live ONLY in a gitignored credentials/.env in that repo — never in this kit, never on the command line.

CloudX First Look bid-floor calculation (P90)

Distinct from the spend/revenue crawl: compute CloudX first-look price floors as the impression-weighted P90 eCPM per app × format × geo from AppLovin MAX report data. The MAX Reporting API is Mediation Partner Reporting, so the weighted empirical-CDF method is mandatory (a naive percentile(ecpm,90) inflates floors). Output is a results table + multi-tab .xlsx. Read-only — nothing is pushed to MAX; floors are recommendations.

Unlike the crawler (which lives in the companion repo), the First Look floor scripts are bundled with this skill — self-contained, zero-dependency (Node 18+ built-ins only) scripts under scripts/cloudx-first-look/ that read a local .env (MAX_REPORT_KEY=, same value as APPLOVIN_REPORT_KEY):

node scripts/cloudx-first-look/cloudx_firstlook_sheet.js 2026-06-16 2026-06-22      # .xlsx
node scripts/cloudx-first-look/cloudx_firstlook_p90_floors.js 2026-06-16 2026-06-22 # .csv + console

Full methodology, per-app geo buckets, uplift tiers, and gotchas: references/cloudx-first-look-floors.md.

Security

This skill is read-only (reporting). Any mutating ad-network call moves real money — see rules/ad-network-mcp-write-safety.md before any write.
Never print or commit API keys, tokens, or account ids. Document env-var names only; real values live solely in the companion repo’s gitignored credentials/.env (or, for the bundled First Look scripts, a local .env).
iOS apps come back as store ids (e.g. id6751266232) in some networks’ package fields — that is expected, not a bug.

Status

Operator playbook + connector overview. The crawler, per-connector setup docs, and credential reference are maintained in The1Studio/marketing-crawl-ads-spend (single source of truth); deepen this playbook iteratively via /t1k:sync-back.