Storing Amazon Marketing Stream Data in BigQuery: S3 → GCP Transfer Setup

Once Amazon Marketing Stream is running through Firehose, your S3 bucket fills up fast. Firehose writes newline-delimited JSON, partitioned into a folder tree:

year=2026/month=05/day=06/hour=13/<uuid>

That's excellent for cheap, durable storage — and useless for anyone who wants to ask a question. You can't hand a PPC manager an S3 bucket and a stack of extension-less JSON files. This guide moves nearly a year of that data into BigQuery, models it into a clean table, and connects it to Google Sheets so the ads team can slice it without ever touching AWS.

The whole pipeline is four hops, and only one of them needs any real configuration:

1. Amazon S3 — raw newline-delimited JSON from Firehose (already there)
2. BigQuery Data Transfer Service (DTS) — a managed connector that copies S3 objects into a BigQuery table on a schedule
3. BigQuery — a raw landing table, plus a cleaned reporting view/table
4. Connected Sheets — a live, refreshable Google Sheet on top of BigQuery for the PPC team

Why BigQuery: serverless, storage is ~$0.02/GB/month, you query with plain SQL, and it has a native Google Sheets connector. Why the Data Transfer Service: it's a no-code, managed S3 → BigQuery copy — you give it credentials, a path, and a schedule, and it handles the rest.

• A GCP project with billing enabled and the BigQuery API + BigQuery Data Transfer API turned on
• Your AMS S3 bucket with data in it (e.g. ams-sp-traffic-data)
• An AWS IAM user with programmatic access (access key + secret) scoped to read that one bucket

DTS authenticates to S3 with an access key. Don't reuse your root keys — create a dedicated, least-privilege user.

1. AWS Console → IAM → Users → Create user (e.g. ams-bq-reader)
2. Enable programmatic access and attach this inline policy (swap in your bucket name):

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": "arn:aws:s3:::ams-sp-traffic-data"
    },
    {
      "Effect": "Allow",
      "Action": ["s3:GetObject"],
      "Resource": "arn:aws:s3:::ams-sp-traffic-data/*"
    }
  ]
}

3. Create an access key for the user and copy the Access key ID and Secret access key — you'll paste them into the transfer config.

Create a dataset to hold the data. Pick a location and remember it — the transfer's region has to line up with it.

# with the bq CLI
bq mk --dataset --location=US your_project:ams

Now create a raw landing table whose columns match the JSON keys Firehose is writing. For an sp-traffic feed:

bq mk --table your_project:ams.sp_traffic_raw \
  advertiserId:STRING,marketplaceId:STRING,dataSetId:STRING,\
  date:DATE,hour:INT64,campaignId:STRING,adGroupId:STRING,\
  keywordText:STRING,matchType:STRING,placement:STRING,\
  impressions:INT64,clicks:INT64,cost:FLOAT64

This is the one screen that matters.

1. BigQuery console → Data transfers → Create transfer
2. Source: Amazon S3
3. Destination dataset: ams  |  Destination table: sp_traffic_raw
4. Amazon S3 URI — use wildcards to sweep the whole partition tree:

s3://ams-sp-traffic-data/year=*/month=*/day=*/hour=*/*

5. Access key ID / Secret access key — from the IAM user in Step 1
6. File format: JSON (newline delimited)
7. Write preference: APPEND (add new rows) or MIRROR (replace the table each run)
8. Schedule: daily is the minimum; set a time after your last hourly file lands

Save it, then use Run transfer now (Schedule backfill) once to pull the entire historical year in a single pass. After that, the daily schedule keeps it current.

The raw table is one row per keyword per hour — great for machines, noisy for humans. Build a view (or a scheduled query writing to a reporting table) that aggregates to daily and computes the metrics people actually ask for:

CREATE OR REPLACE VIEW ams.sp_daily AS
SELECT
  date,
  campaignId,
  SUM(impressions)              AS impressions,
  SUM(clicks)                   AS clicks,
  SUM(cost)                     AS spend,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions))  AS ctr,
  SAFE_DIVIDE(SUM(cost),   SUM(clicks))        AS cpc
FROM ams.sp_traffic_raw
GROUP BY date, campaignId;

Now a query that scans a date range hits a tidy, pre-aggregated shape instead of millions of hourly rows. If you also subscribed sp-conversion, join it here to get ACOS and ROAS in the same view.

This is the part the PPC team cares about. Connected Sheets puts a live, refreshable BigQuery table inside a normal Google Sheet — pivot tables, charts, and filters, no SQL required.

Two ways in:

• From Sheets: Data → Data connectors → Connect to BigQuery → pick your project → choose the ams.sp_daily table (or write a custom query).
• From BigQuery: run a query → Export → Explore with Sheets.

The sheet stays connected to BigQuery — the team refreshes on demand, and on Google Workspace you can set a scheduled auto-refresh so the numbers are current every morning.

• Transfer runs are free. You pay only for BigQuery storage (~$0.02/GB/month — hourly ad data for one account is tiny) and queries (per TB scanned).
• Keep queries cheap: partition the raw table by date and always filter on it. A dashboard that reads 30 days shouldn't scan the whole year.
• Need sub-daily freshness? DTS for S3 is 24-hour minimum. For hourly loads, run a small Cloud Function on a Cloud Scheduler cron that copies the latest S3 hour into a GCS bucket and bq loads it — or use Storage Transfer Service. Most reporting doesn't need this; the automation that does should read straight from S3 anyway.

Can I get truly hourly loads with the Data Transfer Service?

No — the Amazon S3 connector's recurring schedule has a 24-hour minimum. For sub-daily freshness, trigger bq load from a Cloud Function on a Cloud Scheduler cron, or use Storage Transfer Service. Daily is plenty for reporting; real-time automation should read from S3 directly.

Do I have to flatten the JSON first?

Not if the keys map one-to-one to your columns — the newline-delimited JSON loader handles that. If your records are nested, land the whole line into a single JSON/STRING column and parse it in a view with JSON_VALUE().

What about AWS egress cost?

AWS bills S3 GET requests and data transfer out. For one account's hourly ad data the volume is small — cents, not dollars. The big historical backfill is a one-time read.

Why not just query the S3 files directly?

You can (BigQuery Omni / external tables, or Athena on the AWS side), but importing into native BigQuery gives you faster queries, cheaper repeated scans, and the Connected Sheets integration that makes the data usable for a non-technical PPC team.