OudSeed Ads AI Pipeline

OudSeed is a Python ads data pipeline for syncing advertising performance data into BigQuery, then making it available to Looker Studio and future reporting workflows.

The current MVP priority is:

Meta Ads + Google Ads -> BigQuery -> unified_ads_daily -> Looker Studio -> AI reports

Future phases will add LINE Ads, Google Sheet export, and SaaS onboarding.

Project Structure

config/        Example client configuration
docs/          Product and engineering specifications
src/           Pipeline source code
sql/           BigQuery SQL scripts
tests/         Unit tests and fixtures
deploy/        Deployment files for later Cloud Run usage

Local Setup

python -m venv .venv
source .venv/bin/activate
make install
make check

Copy config/clients.example.yaml to config/clients.yaml for local development, and copy .env.example to .env when credentials are needed.

Do not commit real credentials, ad account IDs, service account JSON files, .env, or config/clients.yaml.

Local Run

Create local-only config files:

cp .env.example .env
cp config/clients.example.yaml config/clients.yaml
mkdir -p secrets

Fill .env with local credentials:

GCP_PROJECT_ID=oudseed
BIGQUERY_DATASET=ads_pipeline
GOOGLE_APPLICATION_CREDENTIALS=/Users/mark/Desktop/OudSeed/secrets/your-service-account.json
META_ACCESS_TOKEN=your-meta-marketing-api-token
META_API_TIMEOUT_SECONDS=60
GOOGLE_ADS_DEVELOPER_TOKEN=your-google-ads-developer-token
GOOGLE_ADS_CLIENT_ID=your-google-oauth-client-id
GOOGLE_ADS_CLIENT_SECRET=your-google-oauth-client-secret
GOOGLE_ADS_REFRESH_TOKEN=your-google-ads-refresh-token
GOOGLE_ADS_LOGIN_CUSTOMER_ID=your-manager-customer-id-without-dashes

Fill config/clients.yaml with the real Meta Ads account settings:

clients:
  - client_id: your_client_id
    client_name: Your Client Name
    enabled: true
    platforms:
      meta_ads:
        enabled: true
        accounts:
          - ad_account_id: "act_000000000000000"
            account_name: "Your Meta Ads Account"
            report_level: "ad"
            attribution_setting: "platform_default"
            timezone_setting: "platform_account_default"
            conversion_action_type: "purchase"

      google_ads:
        enabled: true
        accounts:
          - customer_id: "0000000000"
            account_name: "Your Google Ads Account"
            login_customer_id: null
            report_level: "ad"
            attribution_setting: "platform_default"
            timezone_setting: "platform_account_default"

For Google Ads, customer_id should be the target ad account ID without dashes. If the OAuth user accesses the account through a manager account, set GOOGLE_ADS_LOGIN_CUSTOMER_ID in .env to the manager account ID without dashes. The per-account login_customer_id is reserved for future multi-manager support and can stay null for now.

Generate a local Google Ads refresh token with the official OAuth flow:

.venv/bin/python scripts/generate_google_ads_refresh_token.py

The script opens a local OAuth callback at http://127.0.0.1:8080 and prints the GOOGLE_ADS_REFRESH_TOKEN value to paste into .env. This script is local-only and should not be deployed to Cloud Run.

If your OAuth client is a Web application, add http://127.0.0.1:8080 to its authorized redirect URIs in Google Cloud Console. If it is a Desktop app client, run:

.venv/bin/python scripts/generate_google_ads_refresh_token.py --client-type installed

Create or update the BigQuery tables and Looker Studio views:

.venv/bin/python -c 'from dotenv import load_dotenv; load_dotenv(); from google.cloud import bigquery; client=bigquery.Client(); client.query(open("sql/create_tables.sql", encoding="utf-8").read()).result(); client.query(open("sql/weekly_summary.sql", encoding="utf-8").read()).result(); client.query(open("sql/monthly_summary.sql", encoding="utf-8").read()).result(); client.query(open("sql/looker_studio_views.sql", encoding="utf-8").read()).result(); print("bigquery_ready=True")'

Run the default daily sync:

make run

The default sync range is yesterday and the previous 7 days in Asia/Taipei.

Backfill

Use SYNC_START_DATE and SYNC_END_DATE for a manual date range backfill:

SYNC_START_DATE=2025-03-01 SYNC_END_DATE=2025-03-31 make run

The sync flow will:

fetch Meta Ads Insights rows
fetch Google Ads ad-level rows and keyword-level raw rows when Google Ads is enabled
replace matching rows in raw_meta_ads_daily
replace matching rows in raw_google_ads_daily
normalize rows into unified_ads_daily
write a row to sync_logs
refresh weekly/monthly reporting marts and Looker Studio views

For the March 2025 test account validation, the expected BigQuery result is:

spend = 42218.0
link_clicks = 3611

Verify a backfill in BigQuery:

SELECT
  COUNT(*) AS row_count,
  ROUND(SUM(spend), 2) AS spend,
  SUM(link_clicks) AS link_clicks,
  SAFE_DIVIDE(SUM(spend), SUM(link_clicks)) AS cpc
FROM `oudseed.ads_pipeline.vw_looker_ads_campaign_daily`
WHERE date BETWEEN DATE("2025-03-01") AND DATE("2025-03-31")
  AND platform = "meta_ads";

Looker Studio Setup

In Looker Studio, create a BigQuery data source:

Project: oudseed
Dataset: ads_pipeline
Table/View: vw_looker_ads_campaign_daily

Recommended starter dashboard:

Scorecard: spend
Scorecard: link_clicks
Scorecard: cpc
Scorecard: add_to_cart
Scorecard: purchase
Scorecard: cost_per_purchase
Time series: date by spend
Time series: date by link_clicks
Table: campaign_name, spend, link_clicks, ctr, cpc

Available reporting views:

View	Purpose
`vw_looker_ads_campaign_daily`	Campaign-level daily dashboard source
`vw_looker_ads_campaign_weekly`	Campaign-level weekly WoW dashboard and AI summary source
`vw_looker_ads_campaign_monthly`	Campaign-level monthly MoM dashboard and AI summary source
`vw_looker_ads_ad_daily`	Ad-level detail table source
`vw_looker_ai_report_logs`	Generated AI report text and report status source
`vw_looker_sync_status`	Sync monitoring and error review

Use link_clicks as the primary click metric for Meta reporting. It maps to Meta inline_link_clicks, which matches the current validation baseline.

Meta action metrics are also exposed when the Meta API returns them:

add_to_cart
purchase
purchase_value
cost_per_add_to_cart
cost_per_purchase
outbound_clicks
page_engagement
post_engagement
post_reactions
post_comments
post_saves
post_shares

Reporting Summaries

Refresh weekly/monthly summary marts:

.venv/bin/python -c 'from dotenv import load_dotenv; load_dotenv(); from google.cloud import bigquery; client=bigquery.Client(project="oudseed"); client.query(open("sql/weekly_summary.sql", encoding="utf-8").read()).result(); client.query(open("sql/monthly_summary.sql", encoding="utf-8").read()).result(); client.query(open("sql/looker_studio_views.sql", encoding="utf-8").read()).result(); print("reporting_summaries_ready=True")'

The summary marts write to:

oudseed.ads_pipeline.weekly_performance_summary
oudseed.ads_pipeline.monthly_performance_summary

The Looker/AI-friendly views are:

oudseed.ads_pipeline.vw_looker_ads_campaign_weekly
oudseed.ads_pipeline.vw_looker_ads_campaign_monthly

This layer calculates spend, link clicks, conversions, CPC, CPA, ROAS, week-over-week deltas, and month-over-month deltas in BigQuery before any AI reporting is added.

make run and the Cloud Run Job refresh these marts automatically after a successful Meta Ads sync. For one-off troubleshooting runs, disable the refresh with:

REFRESH_REPORTING_MARTS=false make run

Current account-report productization status and operational commands are tracked in docs/web_deployment_runbook.md and docs/ai_report_operations_runbook.md.

AI Report Generation

The AI reporting layer prepares weekly/monthly context from BigQuery reporting marts, sends the prompt to OpenAI's Responses API, and stores the output in ai_report_logs.

Required environment values:

OPENAI_API_KEY=your-openai-api-key
OPENAI_MODEL=gpt-5.2
OPENAI_REASONING_EFFORT=medium
OPENAI_TIMEOUT_SECONDS=180
AI_REPORT_TYPE=monthly
AI_REPORT_PERIOD_START_DATE=2025-03-01
AI_REPORT_CLIENT_ID=your-client-id

For scheduled account-grouped sends, AI_REPORT_SCHEDULE_ID can read defaults from config/clients.yaml under clients[].report_schedules[], including report type, delivery day, recipient, depth, and optional account-group safety controls. AI_REPORT_PERIOD_START_DATE, recipient, depth, and account-group environment variables can still be used for one-off tests.

Generate one report locally:

.venv/bin/python -m src.ai.generate_report

Generate a one-off weekly report:

AI_REPORT_TYPE=weekly AI_REPORT_PERIOD_START_DATE=2025-03-24 .venv/bin/python -m src.ai.generate_report

Check a scheduled account-report send without generating AI text or sending email:

AI_REPORT_SCHEDULE_ID=monthly_email_default \
AI_REPORT_PREFLIGHT=true \
.venv/bin/python -m src.ai.send_account_reports

Generate a one-off Cloud Run report with a specific period:

gcloud run jobs execute oudseed-ai-report \
  --region asia-east1 \
  --update-env-vars=AI_REPORT_TYPE=monthly,AI_REPORT_PERIOD_START_DATE=2026-05-01,OPENAI_MODEL=gpt-5.2 \
  --wait

Generate a one-off Cloud Run weekly report:

gcloud run jobs execute oudseed-ai-report-weekly \
  --region asia-east1 \
  --update-env-vars=AI_REPORT_PERIOD_START_DATE=2026-05-04,OPENAI_MODEL=gpt-5.2 \
  --wait

For scheduled reports, AI_REPORT_PERIOD_START_DATE can be omitted. Monthly reports default to the first day of the previous month, and weekly reports default to the Monday of the previous complete week.

Email a generated report:

AI_REPORT_EMAIL_TO=recipient@example.com \
AI_REPORT_TYPE=weekly \
AI_REPORT_PERIOD_START_DATE=2026-05-04 \
.venv/bin/python -m src.ai.send_report_email

Generate and email account-name grouped HTML reports:

AI_REPORT_EMAIL_TO=recipient@example.com \
AI_REPORT_TYPE=monthly \
AI_REPORT_PERIOD_START_DATE=2026-04-01 \
AI_REPORT_DEPTH=deep \
.venv/bin/python -m src.ai.send_account_reports

Account-grouped HTML reports include the campaign table, AI-written insight, and deterministic diagnostics from the report context. The diagnostics section renders CPC/CPA/ROAS change causes, warning callouts for anomalies such as high spend with weak results, and contribution rows for campaign, ad group, ad, keyword, or search term data when available.

For the repeatable operator workflow, including list mode, one-account test sends, capped batches, full sends, and log verification, see docs/ai_report_operations_runbook.md.

List configured report schedules without calling BigQuery, OpenAI, or SMTP:

AI_REPORT_LIST_SCHEDULES=true \
.venv/bin/python -m src.ai.send_account_reports

List account groups for a report period without calling OpenAI or sending email:

AI_REPORT_LIST_ACCOUNT_GROUPS=true \
AI_REPORT_TYPE=monthly \
AI_REPORT_PERIOD_START_DATE=2026-04-01 \
.venv/bin/python -m src.ai.send_account_reports

For a one-account test send, cap the number of account groups:

AI_REPORT_ACCOUNT_GROUP_LIMIT=1 \
AI_REPORT_EMAIL_TO=recipient@example.com \
AI_REPORT_TYPE=monthly \
AI_REPORT_PERIOD_START_DATE=2026-04-01 \
AI_REPORT_DEPTH=deep \
.venv/bin/python -m src.ai.send_account_reports

To test a specific account group, match the account-group name discovered from the report period:

AI_REPORT_ACCOUNT_GROUP_NAME="Miniware TW" \
AI_REPORT_EMAIL_TO=recipient@example.com \
AI_REPORT_TYPE=monthly \
AI_REPORT_PERIOD_START_DATE=2026-04-01 \
AI_REPORT_DEPTH=deep \
.venv/bin/python -m src.ai.send_account_reports

Email delivery requires SMTP settings:

SMTP_HOST=smtp.gmail.com
SMTP_PORT=587
SMTP_USERNAME=your-sender-email@example.com
SMTP_PASSWORD=your-smtp-or-app-password
SMTP_FROM_EMAIL=your-sender-email@example.com
SMTP_USE_TLS=true

Preview the model-ready prompt without calling OpenAI:

from dotenv import load_dotenv

from src.ai.weekly_report import build_report_prompt
from src.destinations.bigquery import BigQueryDestination

load_dotenv()

destination = BigQueryDestination(project_id="oudseed", dataset_id="ads_pipeline")
result = build_report_prompt(
    destination=destination,
    report_type="monthly",
    workspace_id="mark_internal",
    client_id="your_client_id",
    period_start_date="2025-03-01",
)

print(result["prompt"])

Supported report types:

Report type	Source view	Comparison
`weekly`	`vw_looker_ads_campaign_weekly`	Week over week
`monthly`	`vw_looker_ads_campaign_monthly`	Month over month

Generated report logs are written to:

oudseed.ads_pipeline.ai_report_logs

Looker Studio can read generated report output from:

oudseed.ads_pipeline.vw_looker_ai_report_logs

The current AI report output is stored in BigQuery. Account-grouped HTML email delivery is available through src.ai.send_account_reports, while LINE delivery remains a future channel.

Preview recent AI reports:

SELECT
  report_id,
  account_group_name,
  report_type,
  status,
  is_email_delivery_failure,
  has_report_text,
  model_name,
  period_start_date,
  period_end_date,
  report_text,
  created_at
FROM `oudseed.ads_pipeline.vw_looker_ai_report_logs`
ORDER BY created_at DESC
LIMIT 5;

Useful fields for Looker Studio report monitoring:

Field	Purpose
`account_group_name`	Customer/account identity inferred from report context
`status`	Generation or delivery log status
`is_email_delivery_failure`	Flags failed SMTP delivery rows
`has_report_text`	Confirms whether the log row contains generated report text
`report_text_chars`	Quick check for unexpectedly short reports

Cloud Run Scheduler

Deploy the daily Meta Ads sync job with:

bash deploy/deploy_cloud_run_job.sh

Default deployment settings:

Cloud Run Job: oudseed-meta-ads-sync
Scheduler Job: oudseed-meta-ads-sync-daily
Schedule: 0 4 * * *
Timezone: Asia/Taipei
Region: asia-east1

Run the Cloud Run Job manually:

gcloud run jobs execute oudseed-meta-ads-sync --region asia-east1 --wait

See docs/web_deployment_runbook.md for the full deployment runbook.

AI Report Scheduler

Deploy the monthly AI report job with:

bash deploy/deploy_ai_report_job.sh

Default deployment settings:

Cloud Run Job: oudseed-ai-report
Scheduler Job: oudseed-ai-report-monthly
Schedule: 0 5 1 * * *
Timezone: Asia/Taipei
Region: asia-east1
Report type: monthly

Deploy the weekly AI report job with:

OPENAI_MODEL=gpt-5.2 bash deploy/deploy_weekly_ai_report_job.sh

Deploy scheduled account-grouped HTML email reports with:

AI_REPORT_SCHEDULE_ID=monthly_email_default \
bash deploy/deploy_account_ai_report_job.sh

This uses src.ai.send_account_reports as the Cloud Run Job module. SMTP settings are read from the environment or .env; SMTP_PASSWORD is stored in Secret Manager when provided. Account-report jobs default to OPENAI_MAX_OUTPUT_TOKENS=5000, OPENAI_TIMEOUT_SECONDS=180, and JOB_MAX_RETRIES=0 to reduce incomplete AI responses and avoid duplicate email sends after a partial failure.

Preview the effective deployment settings without touching GCP:

make ai-report-deploy-dry-run

Values passed directly to the deploy command take priority over .env, so the account-report wrapper keeps its production defaults even if an older .env contains generic AI report settings. The wrapper defaults AI_REPORT_SCHEDULE_ID to monthly_email_default; override it only when deploying a different scheduled report variant. Dry-run output is sanitized and can run without real secrets; it reports whether required secrets/config files are configured instead of printing their values.

For production verification without sending email, temporarily set AI_REPORT_PREFLIGHT=true on the Cloud Run Job and execute it once. Preflight prints the resolved report type, period, group count, depth, timeout, and account group names without exposing recipient emails or account IDs.

Run the safe preflight helper with:

make ai-report-preflight

The helper enables preflight, executes the Cloud Run Job, prints the sanitized preflight log lines, and removes AI_REPORT_PREFLIGHT before exiting.

Check the deployed job and scheduler status without exposing secrets:

make ai-report-status

This prints Cloud Run readiness, latest execution status, retry/timeout/model settings, preflight state, configured-secret flags, and the next Scheduler time.

Run the readiness gate before a scheduled send:

make ai-report-ready

This exits non-zero if production settings are unsafe, including enabled preflight, disabled Scheduler, missing schedule/recipient/secrets, wrong module, wrong model, low timeout/tokens, or non-zero Cloud Run retries.

Check whether the latest report period has matching AI report logs:

AI_REPORT_TYPE=monthly \
AI_REPORT_PERIOD_START_DATE=2026-04-01 \
make ai-report-logs

The log checker compares expected account groups against successful report ids, counts generation and delivery failures, and prints sanitized errors only. When a period has older test runs, set AI_REPORT_LOG_CREATED_AFTER to the start time of the batch you want to verify. Set AI_REPORT_LOG_REQUIRE_COMPLETE=true when the command should fail on any missing account group, generation failure, or delivery failure in the fetched window. Set AI_REPORT_LOG_SHOW_ROWS=false for summary-only output.

Run the full operations verification flow:

AI_REPORT_TYPE=monthly \
AI_REPORT_PERIOD_START_DATE=2026-04-01 \
AI_REPORT_LOG_CREATED_AFTER=2026-05-28T13:52:00Z \
make ai-report-verify

This runs the deploy dry-run, Cloud Run/Scheduler status check, and report-log completeness check in one pass. When AI_REPORT_LOG_CREATED_AFTER is provided, the log check fails on missing groups or failures by default. Without it, the log check is informational.

After the scheduled monthly run, verify only the new batch logs:

AI_REPORT_TYPE=monthly \
AI_REPORT_PERIOD_START_DATE=2026-05-01 \
AI_REPORT_LOG_CREATED_AFTER=2026-05-31T21:00:00Z \
make ai-report-post-run

Default weekly deployment settings:

Cloud Run Job: oudseed-ai-report-weekly
Scheduler Job: oudseed-ai-report-weekly
Schedule: 0 5 * * 1
Timezone: Asia/Taipei
Region: asia-east1
Report type: weekly

Run the AI report Cloud Run Job manually:

gcloud run jobs execute oudseed-ai-report --region asia-east1 --wait

Deploy a weekly AI report job instead:

AI_REPORT_TYPE=weekly \
JOB_NAME=oudseed-ai-report-weekly \
SCHEDULER_JOB_NAME=oudseed-ai-report-weekly \
SCHEDULE="0 5 * * 1" \
bash deploy/deploy_ai_report_job.sh

Current Scope

This repository currently includes the product-shaped MVP foundation:

Project skeleton
BigQuery schema
Config loader
Date utilities
BigQuery destination
Base connector
Meta Ads connector
Meta Ads normalize
Google Ads connector
Google Ads normalize
Main Meta + Google Ads sync flow
AI report generation and email delivery
Sync logs

LINE Ads, SaaS login, payment, Google Sheets export, and frontend dashboard work remain out of scope until explicitly requested.

Name		Name	Last commit message	Last commit date
Latest commit History 137 Commits
.github		.github
config		config
deploy		deploy
docs		docs
scripts		scripts
sql		sql
src		src
tests		tests
.env.example		.env.example
.gitignore		.gitignore
AGENTS.md		AGENTS.md
CLAUDE.md		CLAUDE.md
Makefile		Makefile
README.md		README.md
pyproject.toml		pyproject.toml
requirements-dev.txt		requirements-dev.txt
requirements.txt		requirements.txt

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

OudSeed Ads AI Pipeline

Project Structure

Local Setup

Local Run

Backfill

Looker Studio Setup

Reporting Summaries

AI Report Generation

Cloud Run Scheduler

AI Report Scheduler

Current Scope

About

Releases

Packages

Contributors

Languages

Folders and files

Latest commit

History

Repository files navigation

OudSeed Ads AI Pipeline

Project Structure

Local Setup

Local Run

Backfill

Looker Studio Setup

Reporting Summaries

AI Report Generation

Cloud Run Scheduler

AI Report Scheduler

Current Scope

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages