# Ingest Google Analytics 4 into MotherDuck with dlt as a Flight
> A reusable Flight that runs a dlt pipeline pulling Google Analytics 4 (GA4) report data into MotherDuck on a schedule, with Parquet loader files, schema evolution, and a run ledger. Use when you want scheduled GA4 reporting data (sessions, users, pageviews by dimension) in MotherDuck without hand-writing the API calls or INSERTs.
[View source on GitHub](https://github.com/motherduckdb/motherduck-cookbook/tree/main/flight-plans/flight-dlt-ga4-ingest)
Category: ingestion
Features: flights
Tags: dlt, ingest

AI assistant prompt:

```text
I want scheduled GA4 reporting data in MotherDuck without hand-writing API calls or INSERTs. Help me adapt the "Ingest Google Analytics 4 into MotherDuck with dlt as a Flight" recipe to my own data and use case, using it as a guide: https://motherduck.com/docs/cookbook/flight-dlt-ga4-ingest
```

A single-file Flight that runs a [dlt](https://dlthub.com/docs/dlt-ecosystem/destinations/motherduck)
pipeline pulling **Google Analytics 4 (GA4)** report data into MotherDuck. It is
the GA4 adaptation of [flight-dlt-ingest](/cookbook/flight-dlt-ingest/): the demo
`repo_rows()` source is replaced with a `ga4_rows()` source that calls the GA4
Data API, and the rest of the pattern — dlt-managed schema, Parquet loader files,
a run ledger, and Flight scheduling — is unchanged.

Everything is driven by Flight config, so you adapt it by setting config values,
not by editing `flight.py`. The defaults pull the last 7 days of
sessions/users/pageviews by default channel group from the GA4 property you set
in `GA4_PROPERTY_ID`, and load it into `ga4_ingest.ga4.ga4_report` in your own
account.

## Aggregated reports, not raw events

The GA4 Data API (`runReport`) returns **pre-aggregated report rows** — metrics
like `sessions` and `totalUsers` broken down by dimensions like `date` and
`sessionDefaultChannelGroup`. It **cannot** return raw, event-level data
(individual `event_name` rows, event params, user properties).

- Want **dashboards / KPI reporting**? This template is the right fit.
- Want **raw GA4 events**? Use the native **GA4 → BigQuery export** and ingest
  with [the GA4 BigQuery Flight](/cookbook/flight-ga4-bigquery-ingest/) instead.

## How it works

`flight.py` runs a fixed sequence; the config values only change its inputs:

1. Set `HOME=/tmp` (dlt writes working files under `HOME`, and a Flight has a
   writable `/tmp`) and point the dlt MotherDuck destination at
   `DESTINATION_DATABASE` through an environment variable, so no token is written
   anywhere.
2. Connect to MotherDuck (`md:`) and `CREATE DATABASE IF NOT EXISTS` the
   destination, because dlt creates the dataset and tables but not the database.
3. Build a dlt pipeline and `run()` the `ga4_rows()` source with
   `loader_file_format="parquet"`. In merge mode, the date is the merge key and
   the configured dimension columns are the primary key.
4. Append one row to the run ledger capturing the dlt load package summary.

`ga4_rows()` authenticates with a Google service account, calls the GA4 Data API
`runReport` for the configured property, dimensions, metrics, and date range, and
yields one dict per report row. It requests the GA4 maximum of 250,000 rows per
page, then continues until it reads the full `row_count`.

## Why this dlt setup

The important default is the load format. For MotherDuck, prefer Parquet loader
files over row-wise `insert_values`, so larger sources stay on a bulk-loading
path. The Flight makes that choice explicit with `loader_file_format="parquet"`.

The second important default is **a date-partition merge over a moving lookback
window**. GA4 keeps revising recent days as attribution and conversion windows
settle. Re-pulling `7daysAgo`→`yesterday` replaces each returned date partition.
For each returned date, that corrects changed rows and removes rows that
disappear from a revised report.
`append` would double-count, while `replace` would discard history.

## Adapt the pattern

- Set `GA4_PROPERTY_ID` to your numeric GA4 property id.
- Choose your grain with `GA4_DIMENSIONS` and `GA4_METRICS`. Keep `date` in
  `GA4_DIMENSIONS` when using the default merge mode. The dimension list is the
  primary key, so adding a dimension later changes the table grain.
- Tune the lookback with `GA4_START_DATE` / `GA4_END_DATE`. GA4 accepts relative
  strings (`7daysAgo`, `yesterday`, `today`) as well as `YYYY-MM-DD`.
- Use `WRITE_DISPOSITION=merge` with `date` plus the other dimension columns as
  `PRIMARY_KEY` for date-partition replacement. Use `replace` if you re-pull the
  full range each run, or `append` only for an immutable pull log.
- Keep `loader_file_format="parquet"` unless you have measured a reason to change
  it. See the [dlt MotherDuck destination docs](https://dlthub.com/docs/dlt-ecosystem/destinations/motherduck).

## Questions to answer

- Which GA4 **property** (numeric id), and which **dimensions/metrics** (the grain)?
- What **lookback window** matches how late your GA4 data settles?
- Target MotherDuck database and dataset (`DESTINATION_DATABASE`, `DATASET_NAME`);
  is letting the Flight create the database acceptable?
- Load behavior: date-partition `merge` with dimensions as the row key
  (default), `replace`, or `append`?
- Which service account token, and is the GA4 service-account key stored as a
  Flights secret (not config)?
- What schedule (cron) should it run on?

## Prerequisites (google side)

1. **Enable the Google Analytics Data API** in a GCP project.
2. **Create a service account** and download its JSON key.
3. In **GA4 Admin → Property Access Management**, add the service account's email
   as a **Viewer** on the property. (API enabled but service account not granted
   on the property is the most common cause of a `403`.)
4. Note the **numeric GA4 Property ID** (Admin → Property Settings), e.g.
   `123456789` — not the `G-XXXXXXX` measurement id.

## Caveats

- **Aggregated, not raw.** See [Aggregated reports, not raw events](#aggregated-reports-not-raw-events).
- **dlt does not create the database.** It creates the dataset (schema) and
  tables, so the Flight pre-creates `DESTINATION_DATABASE` with
  `CREATE DATABASE IF NOT EXISTS`.
- **`merge` needs `date` and a primary key.** With `WRITE_DISPOSITION=merge`,
  `GA4_DIMENSIONS` must include `date` so the Flight can replace each returned
  date partition. `PRIMARY_KEY` defaults to the dimension columns. Keep it
  aligned with your grain or switch to `append`/`replace`.
- **Keep source credentials out of config.** The GA4 service-account key is a
  secret. Add a `GA4_SERVICE_ACCOUNT_JSON` parameter to a MotherDuck **Flights
  secret**, then attach that secret to the Flight through `flight_secret_names`.
  The Flight reads the attached, prefixed environment variable.
- **Keep the token out of config.** The runtime attaches a MotherDuck token and
  injects it as `MOTHERDUCK_TOKEN`; never place a token in `config`.

## What you'll adjust

Every knob is a config/env value read at the top of `flight.py`. Set them as
Flight config, not by editing code. The source itself lives in the `ga4_rows()`
function.

| Config key | Default | Purpose |
|---|---|---|
| `GA4_PROPERTY_ID` | (required) | Numeric GA4 property id, e.g. `123456789`. Validated as digits. |
| `GA4_DIMENSIONS` | `date,sessionDefaultChannelGroup` | Comma-separated GA4 dimension API names. Defines the grain. Must include `date` in merge mode. |
| `GA4_METRICS` | `sessions,totalUsers,screenPageViews` | Comma-separated GA4 metric API names. The Flight preserves integer and currency values, and loads other metric types as doubles. |
| `GA4_START_DATE` | `7daysAgo` | Report start date. Relative (`NdaysAgo`, `yesterday`, `today`) or `YYYY-MM-DD`. |
| `GA4_END_DATE` | `yesterday` | Report end date. Same formats as `GA4_START_DATE`. |
| `DESTINATION_DATABASE` | `ga4_ingest` | MotherDuck database dlt loads into. Created if missing. Validated as a SQL identifier. |
| `DATASET_NAME` | `ga4` | dlt dataset (schema) that holds the loaded tables. |
| `TABLE_NAME` | `ga4_report` | dlt table name for the loaded rows. |
| `WRITE_DISPOSITION` | `merge` | `merge` (default; replaces returned date partitions), `append`, or `replace`. |
| `PRIMARY_KEY` | `GA4_DIMENSIONS` | Row key in merge mode. Defaults to the dimension columns. |
| `PIPELINE_NAME` | `ga4_dlt_ingest` | dlt pipeline name (also used for dlt state). |
| `RUN_LEDGER_TABLE` | `dlt_ingest_runs` | Audit table in the database's `main` schema. Validated as a SQL identifier. |
| `GA4_SERVICE_ACCOUNT_JSON` | (Flights secret) | Service-account key JSON. Store as a Flights **secret**, never in config. |
| `MOTHERDUCK_TOKEN` | (Flight-injected) | Auth. Select a token on the Flight; never put it in config. |

## Run it

You need a MotherDuck account and access token, a GA4 property, and a service
account with Viewer access on that property (see
[Prerequisites](#prerequisites-google-side)).

To smoke-test the pipeline locally before deploying, run the file directly
against your account, supplying the GA4 key inline:

```bash
export MOTHERDUCK_TOKEN=your_token_here
export GA4_SERVICE_ACCOUNT_JSON="$(cat service-account.json)"
GA4_PROPERTY_ID=123456789 uv run --with-requirements requirements.txt flight.py
```

That single run creates the `ga4_ingest` database, loads the last 7 days of the
default report into `ga4.ga4_report`, and writes one ledger row. Override any
default inline, for example:

```bash
GA4_PROPERTY_ID=123456789 \
GA4_DIMENSIONS=date,country,deviceCategory \
GA4_METRICS=sessions,engagedSessions,conversions \
uv run --with-requirements requirements.txt flight.py
```

### Deploy as a Flight

Create the Flight with the `MD_CREATE_FLIGHT` SQL function (no deploy SQL is
checked in; adapt the arguments to your situation), passing:

- `name`: a Flight name, for example `ga4_dlt_ingest`
- `source_code`: the contents of [`flight.py`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dlt-ga4-ingest/flight.py)
- `requirements_txt`: the contents of [`requirements.txt`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dlt-ga4-ingest/requirements.txt)
- `flight_secret_names`: `["ga4_creds"]` so the `GA4_SERVICE_ACCOUNT_JSON`
  parameter is injected as `ga4_creds_GA4_SERVICE_ACCOUNT_JSON`
- `config`: the keys from [What you'll adjust](#what-youll-adjust) you want to
  override (at minimum `GA4_PROPERTY_ID`; omit any you are keeping at default)

Before the first run, create the `ga4_creds` Flights secret with a
`GA4_SERVICE_ACCOUNT_JSON` parameter. You can create it in
[Settings > Secrets](https://app.motherduck.com/settings/secrets) or with
`CREATE SECRET ... (TYPE flights, ...)`. Then attach it through
`flight_secret_names` and select a MotherDuck token. The runtime injects the
token as `MOTHERDUCK_TOKEN`.

Create the Flight without a schedule first, trigger one manual run with
`MD_RUN_FLIGHT(flight_id := ...)` (the id is returned by `MD_CREATE_FLIGHT` and
listed by `MD_FLIGHTS()`), and confirm it succeeds and the GA4 tables and ledger
row appear. Once the manual run is green, add a daily schedule (`15 7 * * *`,
07:15 UTC, is a reasonable default) by updating the Flight's `schedule_cron` with
`MD_UPDATE_FLIGHT`. Schedule updates are metadata-only and do not create a new
Flight version.

## Security

- **Identifier validation.** `DESTINATION_DATABASE` and `RUN_LEDGER_TABLE` flow
  into `CREATE`/`INSERT` statements that cannot be parameterized, so each is
  checked against `^[A-Za-z_][A-Za-z0-9_]*$` before any SQL runs.
  `GA4_PROPERTY_ID` is validated as digits.
- **Parameterized data.** The ledger row (pipeline name, dataset, table, and load
  summary) is written with bound parameters, never string-formatted into SQL.
- **Secret handling.** The GA4 service-account key is read from a prefixed
  environment variable supplied by an attached Flights secret, or the unprefixed
  local value during development. It is never written to disk or placed in config.

## Learn more

- Flight mechanics (creating, running, scheduling): use the MotherDuck MCP
  `get_flight_guide` tool.
- dlt sources, write dispositions, and the MotherDuck destination:
  [dlt MotherDuck destination docs](https://dlthub.com/docs/dlt-ecosystem/destinations/motherduck).
- GA4 Data API dimensions and metrics:
  [GA4 Dimensions & Metrics reference](https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema).
- The base template this adapts: [flight-dlt-ingest](/cookbook/flight-dlt-ingest/).
- For raw event-level GA4 data:
  [the GA4 BigQuery Flight](/cookbook/flight-ga4-bigquery-ingest/) using the GA4 →
  BigQuery export.
- Files in this template: [`flight.py`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dlt-ga4-ingest/flight.py) (the single-file Flight source)
  and [`requirements.txt`](https://github.com/motherduckdb/motherduck-cookbook/blob/main/flight-plans/flight-dlt-ga4-ingest/requirements.txt) (`duckdb`, `dlt[motherduck]`,
  `google-analytics-data`).


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fcookbook%2Fflight-dlt-ga4-ingest%2F&page_title=Ingest%20Google%20Analytics%204%20into%20MotherDuck%20with%20dlt%20as%20a%20Flight&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.