How to Combine Google Analytics and Google Ads Data for Comprehensive Campaign Analysis

kind: source
spec:
  name: googleanalytics
  path: cloudquery/googleanalytics
  registry: cloudquery
  version: "v4.3.3"
  tables: ["*"]
  destinations: ["postgresql"]
  backend_options:
    table_name: "cq_state_googleanalytics"
    connection: "@@plugins.postgresql.connection"

  spec:
    property_id: "<YOUR_PROPERTY_ID_HERE>"
    oauth:
      access_token: "<YOUR_OAUTH_ACCESS_TOKEN>"
    reports:
    - name: example
      dimensions:
      - campaignId
      - campaignName
      - sourceMedium
      - date
      - language
      - country
      - city
      - googleAdsCampaignId
      - deviceCategory
      metrics:
      - name: Sessions
      - name: totalUsers
      - name: screenPageViews
      - name: averageSessionDuration
      - name: bounceRate
      - name: transactions
      - name: purchaseRevenue
      - name: activeUsers
      - name: newUsers
      keep_empty_rows: true

SELECT
    ga.date,
    ga.dimensions->>'campaignName' AS campaign_name,
    ga.dimensions->>'sourceMedium' AS source_medium,
    ga.dimensions->>'deviceCategory' AS device_category,
    ga.dimensions->>'country' AS country,
    ga.dimensions->>'city' AS city,
    ga.metrics->>'Sessions' AS sessions,
    ga.metrics->>'totalUsers' AS total_users,
    ga.metrics->>'screenPageViews' AS pageviews,
    ga.metrics->>'averageSessionDuration' AS avg_session_duration,
    ga.metrics->>'bounceRate' AS bounce_rate,
    ga.metrics->>'transactions' AS transactions,
    ga.metrics->>'purchaseRevenue' AS revenue,
    gad.name AS google_ads_campaign_name,
    gad.customer_id,
    gad.advertising_channel_type,
    gad.start_date AS google_ads_start_date,
    gad.end_date AS google_ads_end_date,
    gad.status AS google_ads_status,
    gad.campaign_budget AS google_ads_campaign_budget
FROM
    public.ga_example ga
JOIN
    googleads_campaigns gad
ON
    ga.dimensions->>'googleAdsCampaignId' = gad.id::text;