How to pull and reconcile AppsFlyer data in Google Sheets

"Get AppsFlyer into Google Sheets" sounds like one task. It's actually four decisions wearing a trench coat: which API (the aggregate and event-level worlds answer different questions), which filters (the difference between a number and this client's number), how it reconciles with what the ad platforms claim, and how it lands in a Sheet without wrecking the structure. This is the complete guide to all four — written for the practitioner who owns the report, with the exact mechanics.

Part 1 — The two APIs, and the mistake everyone makes once

The Master API serves aggregated performance: cost, impressions, clicks, installs and in-app events by media source, campaign, adset, geo and date. It's the workhorse for spend views, CPI and rollups — anything where a pre-aggregated row per dimension-combination is the right shape.

The Raw Data Pull API serves event-level rows: event_name, event_time, media_source, campaign, country_code, appsflyer_id, one row per event. It exists for event-counter KPIs — counting the users who completed purchase or purchase, per campaign, per day.

The mistake: using aggregate exports for event-counter KPIs, or counting raw rows instead of unique users. A user who fires the event three times in a re-engagement-heavy week inflates "purchases" by 2 if you count rows. The rule: aggregate API for money and volume; raw API, deduplicated per appsflyer_id, for people. (SKAN postbacks are a third lane — delayed, coarse, capped — and live in their own labeled columns, never summed into attributed series.)

Part 2 — Filters: where "the number" becomes "this client's number"

Every pull carries the report's stored scope, or the numbers are accidents:

• Date range — last full ISO week, resolved in the app's configured timezone (per app, per OS — iOS and Android are separate apps with their own settings)
• Geo — country_code for market and market reports (geo = US for the US market view)
• Campaign prefix — US_ campaigns and nothing else; one unprefixed retargeting campaign pollutes a UA report silently
• Event name — the exact string from the taxonomy, character-for-character
• Re-engagement — excluded for new-customer KPIs, or your retargeting wins get counted as acquisition
• Media source — include/exclude lists where the report is channel-scoped

These belong in the workbook's definitions block, applied identically every pull. (The manual workflow this replaces, itemized.)

Part 3 — Reconciliation: AppsFlyer vs the platforms

The pull isn't done when AppsFlyer lands — it's done when it's reconciled against what Meta, Google, TikTok and Snap claim for the same window. The gap is mechanical (attribution windows, credit dates, multi-touch double-claims, SKAN, timezones) and the report's job is to keep both series visible with a variance column per channel. Stable variance is physics; jumping variance is a broken postback or a changed window, caught the week it happens. (The full mechanics.)

Part 4 — Landing it in the Sheet without breaking the Sheet

A structured report imposes write rules no raw export respects:

1. Schema first — tabs, header rows, the current monthly section, the append anchor, formula regions — read and matched against the stored mapping before anything writes; drift halts the run with a diff
2. Append-only into the anchor; history immutable; the duplicate guard refusing a week that already exists
3. Formulas extended, never pasted over — CAC/CPI/ROAS columns compute on the new row by the client's definitions
4. Preview, then write, then log — the diff before, the audit entry after

(Write mechanics in depth.) What lands, weekly:

— where purchases is deduplicated, prefix-scoped, geo-scoped and computed exactly as it was in March.

Part 5 — The build options, priced honestly

Manual exports — fine at one app, one geo, fortnightly. The loop runs 45–90 minutes weekly past that, with the error taxonomy you'd expect.

Apps Script + the APIs — workable if an engineer owns it: token management, the dedup logic, timezone resolution, anchor detection, drift handling, failure alerts. It's internal software with a maintenance tax, and it fails silently at 7am Monday unless someone built the alerting too.

A connector — refreshes a raw tab on schedule; the structured-report last mile (mapping, anchoring, KPIs, reconciliation, summary) stays yours. Right tool when the raw tab is the deliverable.

An operations platform — the four parts of this guide as native behavior. The honest decision criterion: how many reports, how many markets, how client-visible is a mistake.

Part 6 — QA before you trust any of it

• ✓ One event-counter KPI recounted from raw rows with dedup, matched to the report
• ✓ Spend tied out against the AppsFlyer dashboard for the exact range and app
• ✓ Week boundaries verified in the app's timezone
• ✓ SKAN confirmed absent from attributed columns
• ✓ Drift and duplicate guards tested deliberately on a copy

How Opera runs it

This entire pipeline is the AppsFlyer → Google Sheets integration on Opera: both APIs at the right grain, your filters stored once, reconciliation built in, schema-validated append-only writes, and scheduling with loud failure alerts. The fintech-specific version — purchases, markets, funnel maturity — is its own guide: AppsFlyer reporting for fintech.

"Update this week's US report with AppsFlyer spend and purchases from US_ campaigns."