Videnglobe Ads Integration

Overview

Videnglobe is Eli Health's marketing agency that manages Facebook and Google Ads campaigns. They store all advertising data in their own BigQuery instance (US region). This document describes how we synchronize that data to our eli_health_ads dataset in Montreal for analytics and reporting.

Why Custom Sync?

Unlike our other data sources, Videnglobe ads data requires a specialized synchronization approach because:

• Cross-Region Limitation: Videnglobe's BigQuery is in the US, while our data warehouse is in Montreal (northamerica-northeast1). BigQuery cannot directly write query results across regions.
• Third-Party Access: We have read-only access to Videnglobe's BigQuery via a service account they provided.
• Data Freshness: Ads data is updated daily by Airbyte pipelines in Videnglobe's infrastructure.

What Data is Synced?

Source	Tables	Description
Facebook Ads	2 raw + 2 processed	Ad insights for two ad accounts (1262147691460304, 1274589137744186)
Google Ads	5 tables	Campaign, ad group, and conversion data for account 8102572852

Total: 9 tables, ~66,000 rows, ~43MB

Architecture

How Cross-Region Sync Works

The key challenge is that BigQuery queries execute in the source region but cannot write results to a different region. Our solution uses in-memory transfer:

Incremental Sync Strategy

To minimize data transfer and costs, we use incremental sync:

1. Check Last Sync Date: Query MAX(date_column) from destination table
2. Fetch Only New Records: Query source with WHERE date_column > last_date
3. Insert New Records: Stream insert only the new rows (no TRUNCATE)

Benefits:

• ~10x faster sync when data is up-to-date (10s vs 2min)
• Lower BigQuery costs (less data scanned)
• No data loss risk from partial failures

First Sync: If destination table is empty, performs a full sync of all historical data.

BigQuery Configuration

Dataset Details

Setting	Value
Project	eli-health-prod
Dataset ID	eli_health_ads
Location	northamerica-northeast1 (Montreal)

Tables

Facebook Ads Tables

Table	Source	Date Column	Description
facebook_ads_1262147691460304_insights	videnglobe.airbyte_facebook_ads.eli_health_1262147691460304_custom_ads_insights	date_start	Raw ad insights
facebook_ads_1274589137744186_insights	videnglobe.airbyte_facebook_ads.eli_health_1274589137744186_custom_ads_insights	date_start	Raw ad insights
facebook_processed_1262147691460304_insights	videnglobe.airbyte_facebook_ads_processed._processed_eli_health_1262147691460304_ads_insights	date_start	Processed insights
facebook_processed_1274589137744186_insights	videnglobe.airbyte_facebook_ads_processed._processed_eli_health_1274589137744186_ads_insights	date_start	Processed insights

Google Ads Tables

Table	Source	Date Column	Description
google_ads_8102572852_ad_group_conv_action	videnglobe.airbyte_google_ads.eli_health_8102572852_ad_group_conv_action	segments_date	Ad group conversions
google_ads_8102572852_ad_group_general	videnglobe.airbyte_google_ads.eli_health_8102572852_ad_group_general	segments_date	Ad group metrics
google_ads_8102572852_campaign_adgroup_combined	videnglobe.airbyte_google_ads_processed.eli_health_8102572852_campaign_adgroup_combined	segments_date	Combined campaign data
google_ads_8102572852_campaign_conv_action	videnglobe.airbyte_google_ads.eli_health_8102572852_campaign_conv_action	segments_date	Campaign conversions
google_ads_8102572852_campaign_general	videnglobe.airbyte_google_ads.eli_health_8102572852_campaign_general	segments_date	Campaign metrics

Common Columns

All synced tables include:

• Original columns from source table
• _synced_at (TIMESTAMP): When the record was synced to Eli Health

Authentication

Service Account: ads-data-reader

Videnglobe provided a dedicated service account with read-only access to their BigQuery datasets:

Setting	Value
Service Account	ads-data-reader@videnglobe.iam.gserviceaccount.com
Access Level	BigQuery Data Viewer on specific datasets
Credential Storage	GCP Secret Manager (ads-data-reader-key)

Secret Manager Configuration

The service account key is stored in Secret Manager, not GitHub Secrets. This is because:

• JSON credentials with special characters break gcloud --set-env-vars
• Secret Manager allows secure mounting via --update-secrets

Cloud Run mounts the secret as an environment variable:

# In deploy.yml
--update-secrets=ADS_DATA_READER_KEY=ads-data-reader-key:latest

IAM Binding (managed by Terraform):

# eli-devops/tf/modules/regional/kpi-compute/main.tf
resource "google_secret_manager_secret_iam_member" "kpi_ads_data_reader_access" {
  secret_id = "ads-data-reader-key"
  role      = "roles/secretmanager.secretAccessor"
  member    = "serviceAccount:kpi-service-us@eli-health-prod.iam.gserviceaccount.com"
}

Sync Endpoint

API Details

Setting	Value
Endpoint	POST /sync/videnglobe-ads
Service	eli-kpi (Cloud Run)
Authentication	Public endpoint (for Cloud Scheduler)
Timeout	15 minutes

Response Format

{
  "success": true,
  "startTime": "2025-12-08T17:12:24.140Z",
  "endTime": "2025-12-08T17:12:34.286Z",
  "durationSeconds": 10.146,
  "totalTables": 9,
  "successfulTables": 9,
  "failedTables": 0,
  "results": [
    {
      "table": "facebook_ads_1262147691460304_insights",
      "success": true,
      "rowCount": 0,
      "isIncremental": true,
      "lastDate": "2025-11-18"
    }
  ],
  "errors": []
}

Status Endpoint

GET /sync/videnglobe-ads/status

Returns table metadata including row counts and last modified times. Requires authentication.

Cloud Scheduler

The sync job is triggered daily by Cloud Scheduler:

Terraform Resource (eli-devops/tf/modules/regional/kpi-compute/main.tf):

resource "google_cloud_scheduler_job" "videnglobe_ads_sync" {
  name             = "videnglobe-ads-daily-sync"
  description      = "Daily sync of Facebook/Google Ads data from Videnglobe"
  schedule         = "0 5 * * *"  # 5:00 AM Toronto time
  time_zone        = "America/Toronto"
  attempt_deadline = "900s"  # 15 minute timeout
  region           = "us-east1"

  retry_config {
    retry_count          = 3
    min_backoff_duration = "30s"
    max_backoff_duration = "300s"
  }

  http_target {
    http_method = "POST"
    uri         = "https://kpi.app.eli.health/sync/videnglobe-ads"

    oidc_token {
      service_account_email = "kpi-service-us@eli-health-prod.iam.gserviceaccount.com"
    }
  }
}

Data Volume & Performance

Metric	Value
Total Tables	9
Total Rows	~66,000
Data Size	~43 MB
Full Sync Duration	~2 minutes
Incremental Sync Duration	~10 seconds (when up-to-date)

Monitoring

Logs

Check Cloud Run logs for sync status:

gcloud logging read "resource.type=cloud_run_revision AND resource.labels.service_name=kpi AND textPayload:videnglobe" \
  --project=eli-health-prod \
  --limit=50

Key Metrics to Track

• Sync success rate (should be 100%)
• Sync duration (should be ~10s for incremental)
• Row counts per table
• Error messages

Common Issues

Permission Denied from Videnglobe

Symptoms: PERMISSION_DENIED when querying Videnglobe tables

Resolution:

1. Verify ads-data-reader-key secret is correctly mounted
2. Check if Videnglobe has revoked access
3. Contact Videnglobe to verify service account permissions

Streaming Buffer Conflicts

Symptoms: UPDATE or DELETE statement over table ... would affect rows in the streaming buffer

Resolution: Use TRUNCATE instead of DELETE (already implemented)

413 Request Entity Too Large

Symptoms: Insert fails with 413 error

Resolution: Reduce batch size (currently 500 rows per batch)

Querying Ads Data

Example: Facebook Ads Performance

SELECT
  date_start,
  campaign_name,
  SUM(spend) as total_spend,
  SUM(impressions) as total_impressions,
  SUM(clicks) as total_clicks,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100 as ctr
FROM `eli-health-prod.eli_health_ads.facebook_ads_1274589137744186_insights`
WHERE date_start >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2
ORDER BY date_start DESC, total_spend DESC;

Example: Google Ads Campaign Summary

SELECT
  segments_date,
  campaign_name,
  SUM(metrics_cost_micros) / 1000000 as cost,
  SUM(metrics_conversions) as conversions,
  SUM(metrics_clicks) as clicks
FROM `eli-health-prod.eli_health_ads.google_ads_8102572852_campaign_general`
WHERE segments_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2
ORDER BY segments_date DESC, cost DESC;

Example: Cross-Platform ROI Analysis

WITH facebook AS (
  SELECT
    date_start as date,
    'Facebook' as platform,
    SUM(spend) as spend,
    SUM(purchase_value) as revenue
  FROM `eli-health-prod.eli_health_ads.facebook_processed_1274589137744186_insights`
  WHERE date_start >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY 1
),
google AS (
  SELECT
    segments_date as date,
    'Google' as platform,
    SUM(metrics_cost_micros) / 1000000 as spend,
    SUM(metrics_conversions_value) as revenue
  FROM `eli-health-prod.eli_health_ads.google_ads_8102572852_campaign_general`
  WHERE segments_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY 1
)
SELECT
  platform,
  SUM(spend) as total_spend,
  SUM(revenue) as total_revenue,
  SAFE_DIVIDE(SUM(revenue), SUM(spend)) as roas
FROM (SELECT * FROM facebook UNION ALL SELECT * FROM google)
GROUP BY platform
ORDER BY total_spend DESC;

Related Documentation

• LoopWork Integration - Subscription data sync
• Airbyte Integration - Shopify data sync
• BigQuery Access - Data warehouse permissions
• DevOps Overview - Infrastructure overview

Support & Contact

For issues with Videnglobe ads integration:

1. Check Logs: Cloud Run logs in GCP Console
2. Videnglobe Contact: For access issues, contact Videnglobe team
3. Internal Contact: DevOps team at Eli Health

---

Last Updated: December 2025 Maintained By: Chip (chip@eli.health) Source Code: eli-kpi/src/ads-sync.ts