AI Automation for Paid UA Reporting: From Raw Data to Weekly Digest
Most growth teams drown in paid UA data. You've got Appsflyer or Adjust exporting CSVs, Meta Ads Manager in one tab, Apple Search Ads in another, Google App Campaigns in a third, and someone on the team manually stitching it all into a Google Sheet every Monday morning. The sheet is always slightly wrong. The Monday ritual eats two to three hours. And by the time the digest lands in Slack, the data is already a week stale.
AI business automation fixes this. Not by replacing the analyst—by eliminating the mechanical work so the analyst can spend time on what actually matters: interpretation, decisions, and experiments.
This post is a build walkthrough. We'll cover the full pipeline from MMP data ingestion through transformation to formatted digest delivery, with the specific tools, data shapes, and prompting patterns that make it work in production.
Why Paid UA Reporting Is a Good Automation Target
Before building anything, it's worth understanding why UA reporting specifically is well-suited to AI automation—and where it still needs a human.
Paid UA reporting has three properties that make it automatable:
- Structured inputs. MMP exports, ad platform APIs, and app store data all return predictable schemas. You're not parsing unstructured text—you're working with rows and columns.
- Repetitive transformation logic. The same calculations run every week: CPI by channel, ROAS by campaign, D7/D30 retention cohorts, blended CAC. These don't change unless your attribution model changes.
- Defined output format. A weekly digest has a fixed structure—summary metrics, winners, losers, anomalies, recommended actions. Once you define that structure, an LLM can populate it reliably.
Where automation doesn't replace human judgment: campaign creative decisions, budget reallocation calls above a certain dollar threshold, and reading context that isn't in the data (a competitor launched, a platform had an outage, your app had a bug that week).
Build the automation to handle the first three properties. Design the output explicitly to surface the data a human needs for the second three.
The Pipeline Architecture
Here's the full architecture at a high level:
| Layer | What Happens | Tools |
|---|---|---|
| Ingestion | Pull raw data from MMP, ad platforms, and app store | Appsflyer/Adjust API, Meta Marketing API, Apple Search Ads API, Google App Campaigns API |
| Normalization | Unify schema across sources, resolve currency, deduplicate installs | Python + Pandas or Node.js transform scripts |
| Storage | Land normalized rows into a queryable store | PostgreSQL or BigQuery |
| Aggregation | Run the weekly metric calculations | SQL views or dbt models |
| AI synthesis | Pass aggregated data to LLM, generate narrative and flags | OpenAI GPT-4o or Anthropic Claude |
| Delivery | Format and push digest to Slack channel or email | Slack Webhooks or SendGrid |
| Scheduling | Trigger the whole pipeline on a cron | GitHub Actions, Airflow, or n8n |
Each layer is independently replaceable. If you're already using Looker or Amplitude for the storage/aggregation layers, you don't rebuild those—you add the AI synthesis and delivery layers on top.
Step 1: Ingesting MMP and Platform Data
Your MMP (Appsflyer, Adjust, or Singular) is the source of truth for install attribution. Pull it first.
Appsflyer Pull API example (Python):
import requests
def fetch_appsflyer_report(app_id, api_token, date_from, date_to):
url = f"https://hq.appsflyer.com/export/{app_id}/partners_report/v5"
params = {
"api_token": api_token,
"from": date_from,
"to": date_to,
"timezone": "UTC",
"currency": "preferred"
}
response = requests.get(url, params=params)
return response.text # CSV
You'll get back installs, cost, and revenue attributed by media source. The challenge is that this report uses Appsflyer's channel naming, which won't exactly match what Meta or Google returns. Write a normalization map early—don't assume "Facebook Ads" from Appsflyer maps cleanly to "Meta" from your Meta API pull.
Platform APIs you'll need:
- Meta Marketing API (
/insightsendpoint,app_installsobjective) - Apple Search Ads API (
/reports/campaigns) - Google App Campaigns via Google Ads API (
GoogleAdsService.Searchwithcampaign_type = APP)
Pull spend from each platform API directly. Use the MMP for install attribution. Reconcile the two—discrepancies above approximately 10–15% on any channel typically indicate an attribution window mismatch or a tracking link issue worth flagging.
Step 2: Normalization and Storage
Once you've got raw data from four or five sources, you need a single normalized schema before anything else happens.
A minimal normalization schema looks like this:
CREATE TABLE ua_daily_metrics (
id SERIAL PRIMARY KEY,
report_date DATE NOT NULL,
channel VARCHAR(100) NOT NULL, -- "meta", "apple_search_ads", "google_uac"
campaign_id VARCHAR(255),
campaign_name VARCHAR(500),
impressions INTEGER,
clicks INTEGER,
installs INTEGER,
spend_usd NUMERIC(12,2),
revenue_usd NUMERIC(12,2),
d1_retained INTEGER,
d7_retained INTEGER,
created_at TIMESTAMPTZ DEFAULT NOW()
);
Keep it flat. Resist the urge to normalize into a star schema until you know your query patterns. For weekly digest automation, a flat table with indexed report_date and channel columns is fast enough and far easier to debug.
Step 3: Aggregation Queries
The AI synthesis step works best when you pass it pre-aggregated data, not raw rows. Pre-aggregation also keeps your prompt tokens manageable—a critical concern if you're working with campaigns across multiple geos and dozens of ad sets. (For more on managing token budgets in automation pipelines, see our post on AI agent context windows and token limits.)
Write a weekly aggregation query that produces:
SELECT
channel,
SUM(spend_usd) AS total_spend,
SUM(installs) AS total_installs,
ROUND(SUM(spend_usd) / NULLIF(SUM(installs), 0), 2) AS cpi,
ROUND(SUM(revenue_usd) / NULLIF(SUM(spend_usd), 0), 2) AS roas,
ROUND(100.0 * SUM(d7_retained) / NULLIF(SUM(installs), 0), 1) AS d7_retention_pct
FROM ua_daily_metrics
WHERE report_date BETWEEN :week_start AND :week_end
GROUP BY channel
ORDER BY total_spend DESC;
Run this weekly. Serialize the output to JSON. That JSON is what goes into your LLM prompt.
Step 4: AI Synthesis — The Prompt That Works
This is where most teams overcomplicate things. The prompt doesn't need to be clever. It needs to be specific about the output format.
Here's a prompt structure that works reliably in production:
You are a paid user acquisition analyst. You will receive a JSON object
containing aggregated weekly UA metrics across channels.
Your job is to produce a weekly digest with the following sections:
1. **Summary** — 2-3 sentences on overall spend, installs, and blended CPI.
2. **Channel Performance** — One bullet per channel: spend, installs, CPI, ROAS, D7 retention.
3. **Flags** — Anything anomalous: CPI spike >20% WoW, ROAS below 1.0, retention drop.
4. **Recommended Focus** — One or two specific things the team should investigate or act on.
Rules:
- Be specific. Use the numbers from the data.
- Do not invent trends that aren't in the data.
- If a metric is missing, say so rather than estimating.
- Output in Slack-formatted markdown (bold with *, bullets with •).
Data:
{aggregated_json}
Pass the serialized aggregation JSON in place of {aggregated_json}. With GPT-4o, this prompt reliably produces a clean, scannable digest with real numbers in approximately one to two seconds.
One important guardrail: instruct the model explicitly not to make budget recommendations above a threshold you define. In our engagements, we typically set that at $5,000—anything above that threshold, the digest flags for human review rather than recommending directly. The model isn't seeing your cash position, your pipeline, or your Q4 commitments. Keep it in its lane.
For more on where to draw that line in automated pipelines, the post on AI agent escalation logic covers the decision framework in detail.
Step 5: Delivery — Slack Webhook or Email
For Slack, use an Incoming Webhook. The digest posts to a #ua-weekly channel and tags the relevant stakeholders.
import requests, json
def post_to_slack(webhook_url, digest_text):
payload = {
"blocks": [
{
"type": "section",
"text": {
"type": "mrkdwn",
"text": digest_text
}
}
]
}
requests.post(webhook_url, data=json.dumps(payload))
For email delivery, SendGrid's transactional API works well. Render the digest as HTML—convert the markdown to HTML first using a library like mistune in Python—and send it as a styled HTML email.
Don't send both Slack and email by default. Pick one primary channel. Digest fatigue is real. If the stakeholder sees the same report in two places, they'll start ignoring it in both.
Step 6: Scheduling the Pipeline
The whole pipeline runs on a weekly cron. GitHub Actions is the lowest-overhead option if your code is already in GitHub:
name: UA Weekly Digest
on:
schedule:
- cron: '0 8 * * 1' # Every Monday at 8 AM UTC
jobs:
run-digest:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- name: Set up Python
uses: actions/setup-python@v4
with:
python-version: '3.11'
- name: Install dependencies
run: pip install -r requirements.txt
- name: Run digest pipeline
env:
APPSFLYER_TOKEN: ${{ secrets.APPSFLYER_TOKEN }}
OPENAI_API_KEY: ${{ secrets.OPENAI_API_KEY }}
SLACK_WEBHOOK_URL: ${{ secrets.SLACK_WEBHOOK_URL }}
DATABASE_URL: ${{ secrets.DATABASE_URL }}
run: python run_digest.py
If you need more observability or retry logic, n8n or Airflow are better fits. GitHub Actions works fine for teams that don't need sub-minute observability and just want Monday morning delivery without infrastructure overhead.
FAQ
How long does this take to build?
For a team with existing MMP and ad platform access, the ingestion and normalization layer typically takes one to two weeks of engineering time. The AI synthesis and delivery layer adds another two to three days. Expect roughly three weeks total for a production-ready version, not including QA of the attribution reconciliation logic.
Which MMP works best with this setup?
Appsflyer and Adjust both have well-documented Pull APIs and are the most common in our engagements. Singular is a solid option if you're already using it for cross-channel cost aggregation—it reduces the number of platform APIs you need to hit directly.
Can this replace our BI tool?
No, and it shouldn't try to. This pipeline is optimized for a weekly narrative digest—human-readable, action-oriented output. Your BI tool handles ad-hoc queries, historical trend analysis, and drill-down exploration. The two complement each other rather than compete.
What does it cost to run?
The main variable cost is LLM API calls. With GPT-4o at current pricing, a weekly digest prompt with pre-aggregated JSON typically costs well under $0.10 per run. The rest is compute for the ingestion scripts—negligible on a weekly schedule. Total monthly cost for most teams is under $5 in API fees.
What if our data has attribution discrepancies?
Build a reconciliation check into the pipeline. Compare MMP-attributed installs against platform-reported installs by channel. If the discrepancy exceeds a threshold you define (typically 10–15%), have the pipeline post a warning flag before the digest runs and optionally pause delivery until someone reviews it. Don't let the LLM synthesize a narrative on top of bad data.
Do we need a data engineer to maintain this?
Not necessarily. A growth engineer or technically proficient analyst can own this once it's built. The main maintenance surface is platform API changes—ad platforms update their APIs periodically, and your ingestion scripts will need to keep pace. Budget roughly two to four hours per quarter for that.
If your team is still spending Monday mornings manually pulling UA reports, that's a solvable problem—and it's exactly the kind of AI business automation that frees your growth team to focus on decisions rather than data formatting.
Our mobile app marketing team works with app teams on both the strategy and the tooling side of paid UA. If you want to scope what this pipeline would look like for your specific MMP and channel mix, book a call here.
