Automate Client Reporting Across Meta, Google and TikTok Ads

To automate client reporting across Meta, Google and TikTok Ads, pull the numbers straight from each platform's API on a schedule. Map all three into one daily table with the same columns, and build every report from that table. Two details make or break it. First, re-pull the last few days on every run, because platforms keep updating conversions after the fact. Second, store spend in one unit and one currency, so the totals add up.

I build reporting pipelines for agencies, and most of the ones I've replaced had the same setup: someone exports CSVs on Monday, pastes them into a sheet, takes screenshots and drops them in a deck. It works until the person who does it is on holiday. This post is the approach I use instead, with the code.

What "automated" should mean

A reporting system is automated when:

• Nobody exports anything by hand.
• Every client's numbers are calculated the same way.
• The numbers match the platform UIs, within known, explainable differences.
• Adding a new client is connecting an account, not building a new sheet.
• When a token breaks, you hear about it before the client does.

Screenshots fail all five.

The architecture

Keep it boring. Five parts:

1. Connections. One OAuth connection per client per platform, with tokens stored encrypted.
2. Extractors. One small function per platform that pulls daily campaign-level data for a date range.
3. One table. Every extractor writes rows with the same columns into one table.
4. A scheduler. A cron job or a queue worker that runs the extractors every night.
5. A reporting layer. Looker Studio, a BI tool or your own dashboard, reading only from that table.

The rule I hold myself to: the reporting layer never talks to an ad platform. It only reads your table. That's what makes reports fast, consistent and cheap on API quota.

What you need before you start

API access for each platform. Each one has its own setup and review, and those are the slow part:

• Google Ads API: a Cloud project, OAuth client, refresh token and an access level that can read production accounts. Since September 2026 the access level belongs to the Cloud project. See the Google Ads API access guide.
• Meta Marketing API: an app with ads_read, and a token that doesn't expire. See the long-lived token guide. To read client-owned accounts at scale, you need advanced access through App Review.
• TikTok Marketing API: an approved developer app with the reporting scope. See TikTok Marketing API access.

Plus Python 3.10+, a database (Postgres is fine, BigQuery if you're already on Google Cloud), and somewhere to run a nightly job.

Step 1: Define one schema

Decide on the columns before writing any API code. This is the one I start with:

CREATE TABLE ad_daily (
    client_id       text        NOT NULL,
    platform        text        NOT NULL,   -- 'google' | 'meta' | 'tiktok'
    account_id      text        NOT NULL,
    campaign_id     text        NOT NULL,
    campaign_name   text,
    date            date        NOT NULL,   -- in the ad account's time zone
    currency        char(3)     NOT NULL,
    spend           numeric(14,4) NOT NULL, -- major units (dollars, not cents or micros)
    impressions     bigint,
    clicks          bigint,
    conversions     numeric(14,4),
    conversion_value numeric(14,4),
    pulled_at       timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (platform, account_id, campaign_id, date)
);

The primary key matters. When you re-pull a day, you overwrite it instead of duplicating it.

Step 2: Write one extractor per platform

Each extractor takes an account and a date range and returns rows in the schema above. Nothing else.

Google Ads

from google.ads.googleads.client import GoogleAdsClient

def extract_google(client: GoogleAdsClient, customer_id: str, start: str, end: str, currency: str):
    ga = client.get_service("GoogleAdsService")
    query = f"""
        SELECT campaign.id, campaign.name, segments.date,
               metrics.cost_micros, metrics.impressions, metrics.clicks,
               metrics.conversions, metrics.conversions_value
        FROM campaign
        WHERE segments.date BETWEEN '{start}' AND '{end}'
    """
    for batch in ga.search_stream(customer_id=customer_id, query=query):
        for r in batch.results:
            yield {
                "platform": "google",
                "account_id": customer_id,
                "campaign_id": str(r.campaign.id),
                "campaign_name": r.campaign.name,
                "date": r.segments.date,
                "currency": currency,
                "spend": r.metrics.cost_micros / 1_000_000,   # micros -> units
                "impressions": r.metrics.impressions,
                "clicks": r.metrics.clicks,
                "conversions": r.metrics.conversions,
                "conversion_value": r.metrics.conversions_value,
            }

Get the account's currency and time zone once from customer.currency_code and customer.time_zone.

Meta

Meta returns conversions inside an actions list, so you choose which action type counts as a conversion. Make that a per-client setting, not a constant.

import requests

GRAPH = "https://graph.facebook.com/v26.0"

def extract_meta(token: str, account_id: str, start: str, end: str, currency: str,
                 conversion_action: str = "omni_purchase"):
    url = f"{GRAPH}/act_{account_id}/insights"
    params = {
        "level": "campaign",
        "time_increment": 1,
        "time_range": f'{{"since":"{start}","until":"{end}"}}',
        "fields": "campaign_id,campaign_name,spend,impressions,clicks,actions,action_values",
        "limit": 500,
        "access_token": token,
    }
    while url:
        body = requests.get(url, params=params, timeout=60).json()
        if "error" in body:
            raise RuntimeError(body["error"].get("message"))
        for r in body.get("data", []):
            acts = {a["action_type"]: float(a["value"]) for a in r.get("actions", [])}
            vals = {a["action_type"]: float(a["value"]) for a in r.get("action_values", [])}
            yield {
                "platform": "meta",
                "account_id": account_id,
                "campaign_id": r["campaign_id"],
                "campaign_name": r.get("campaign_name"),
                "date": r["date_start"],
                "currency": currency,
                "spend": float(r.get("spend", 0)),   # already in units
                "impressions": int(r.get("impressions", 0)),
                "clicks": int(r.get("clicks", 0)),
                "conversions": acts.get(conversion_action, 0.0),
                "conversion_value": vals.get(conversion_action, 0.0),
            }
        url = body.get("paging", {}).get("next")
        params = None  # the next URL already has the params

For long date ranges or many accounts, use Meta's async insights jobs (POST /act_<id>/insights returns a report run ID you poll). They're much gentler on rate limits.

TikTok

import json, requests

TT = "https://business-api.tiktok.com/open_api/v1.3"

def extract_tiktok(token: str, advertiser_id: str, start: str, end: str, currency: str):
    page = 1
    while True:
        body = requests.get(f"{TT}/report/integrated/get/", headers={"Access-Token": token}, params={
            "advertiser_id": advertiser_id,
            "report_type": "BASIC",
            "data_level": "AUCTION_CAMPAIGN",
            "dimensions": json.dumps(["campaign_id", "stat_time_day"]),
            "metrics": json.dumps(["campaign_name", "spend", "impressions", "clicks", "conversion"]),
            "start_date": start, "end_date": end,
            "page": page, "page_size": 1000,
        }, timeout=60).json()
        if body.get("code") != 0:
            raise RuntimeError(body.get("message"))
        data = body["data"]
        for r in data["list"]:
            m, d = r["metrics"], r["dimensions"]
            yield {
                "platform": "tiktok",
                "account_id": advertiser_id,
                "campaign_id": d["campaign_id"],
                "campaign_name": m.get("campaign_name"),
                "date": d["stat_time_day"][:10],
                "currency": currency,
                "spend": float(m.get("spend", 0)),
                "impressions": int(m.get("impressions", 0)),
                "clicks": int(m.get("clicks", 0)),
                "conversions": float(m.get("conversion", 0)),
                "conversion_value": None,  # pick the value metric that matches the client's optimisation event
            }
        if page >= data["page_info"]["total_page"]:
            break
        page += 1

TikTok's conversion and value metrics depend on what the campaign optimises for. Agree with each client which metric is "the" conversion, write it down, and map it per client, the same way as with Meta.

Step 3: Upsert with a lookback window

Platforms keep updating recent days. Conversions get attributed late, and invalid clicks get removed. If you only pull "yesterday", your table will always be a little wrong.

So every night, pull yesterday plus the previous several days and overwrite them:

from datetime import date, timedelta

LOOKBACK_DAYS = 7  # longer if your clients use long attribution windows

def run_nightly(accounts, upsert):
    end = date.today() - timedelta(days=1)
    start = end - timedelta(days=LOOKBACK_DAYS)
    for acc in accounts:
        try:
            rows = EXTRACTORS[acc.platform](acc, start.isoformat(), end.isoformat())
            upsert(rows, client_id=acc.client_id)  # INSERT ... ON CONFLICT DO UPDATE
        except Exception as e:
            alert(f"{acc.client_id}/{acc.platform}/{acc.account_id}: {e}")

The upsert is a plain INSERT ... ON CONFLICT (platform, account_id, campaign_id, date) DO UPDATE. Re-running is always safe.

Step 4: Build reports from one query

Now the cross-platform view is one query:

SELECT platform,
       sum(spend)            AS spend,
       sum(conversions)      AS conversions,
       sum(conversion_value) AS revenue,
       sum(conversion_value) / nullif(sum(spend), 0) AS roas,
       sum(spend) / nullif(sum(conversions), 0)      AS cpa
FROM ad_daily
WHERE client_id = 'acme'
  AND date BETWEEN '2026-10-01' AND '2026-10-25'
GROUP BY ROLLUP (platform);

Point Looker Studio, Metabase or your own dashboard at this table. Every client report uses the same definitions, because there's only one place they're defined.

The mismatches you'll get, and why

Someone will compare your numbers to Ads Manager. These are the usual reasons they differ, in the order I check them:

1. Attribution windows. Meta's default reporting window may not be what the client looks at in Ads Manager. Pass the same action_attribution_windows the client uses, and write it in the report footer.
2. Time zones. Each ad account reports in its own time zone. Don't convert dates to UTC. A "day" is the account's day.
3. Currency. A client with a CAD Google account and a USD Meta account can't just sum spend. Store the original currency, and convert with a daily rate in the reporting layer if you must combine them.
4. Units. Google uses micros. Meta and TikTok return currency units as strings. Convert once, in the extractor, and nowhere else.
5. Conversion definitions. "Conversions" means three different things on three platforms. That's why the conversion action is a per-client setting.
6. Freshness. You pulled before the platform finished processing. The lookback window fixes this on the next run.

Put a short "how these numbers are calculated" note on every report. It ends most of these conversations before they start.

Running it in production

The extract code is the easy part. These are what keep it running:

• Token health checks. Run a cheap call per connection every night (list accounts, for example). Alert when one fails. Google refresh tokens die after 7 days if your OAuth app is still in Testing. Meta and TikTok tokens die when a client removes access.
• Rate limits. Google counts operations per day per Cloud project. Meta throttles per ad account. TikTok throttles per app. Pull sequentially per platform with backoff, and spread clients across the night.
• Alerts on silence. Alert when a client has zero rows for yesterday but had spend the day before. Usually it's a disconnected account, not a paused client.
• Backfills. Keep a command that re-pulls any client, platform and date range. You'll need it the first week.
• Secrets. Tokens encrypted at rest, never logged, never in the reporting layer.

Build it, buy a connector, or both?

Connector tools that push ad data into sheets or a warehouse are fine when you have a few clients and standard metrics. They get awkward when you need custom conversion mapping per client, your own branded client portal, or when per-account pricing grows with every client you sign. I wrote more about that decision in build vs buy for agency tools.

A common middle path: use an API gateway that handles OAuth and token refresh for all three platforms, and own the schema, the sync and the reports yourself. That's what we built Adlyfy for, and it's what our own dashboards run on.

A checklist to start with

• API access for every platform you report on
• One table, one schema, primary key on platform + account + campaign + date
• One extractor per platform, converting units and currency in one place
• Per-client conversion mapping, written down
• Nightly job with a lookback window and upserts
• Token health checks and "no data" alerts
• Reports that read only from your table
• A calculation note on every report

How Scalvo can help

We build this for agencies: the API approvals, the pipeline and a branded client dashboard on your own server. If you're paying for a reporting SaaS you'd rather own, see custom tool replicas. If you want the pipeline and alerts set up in a focused sprint, see the AI automation sprint. If your blocker is getting API access approved, start with app approval and launch. Or just book a call.