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

Overview

Have you ever wished that you could run analytics on your Google Analytics and Google Ads data all in the same place? In this post, you will learn how to combine your data from Google Analytics and Google Ads. This could be useful if you wanted to explore how your campaigns are performing and see the effect of each campaign on your website or other tracked events in Google Analytics. Combining data from these two sources can enrich your analysis and gain a deeper understanding of each campaign’s performance from multiple perspectives. This approach allows for more granular insights, helping you determine which campaigns work best and how they impact user behavior.

Syncing Your Google Data and Your Google Ads Data

If you are new to CloudQuery, follow the instructions on the Downloads page to install the CloudQuery CLI and get started with syncing your data.

First, we will sync data using the Google Analytics plugin. Note that for this specific plugin, Google Analytics tables are auto-generated from the database schema. Therefore, you need to specify the dimensions and metrics manually. Below is a sample configuration:

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: ""
    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

For this task, we also use CloudQuery with the Google Ads plugin. Ensure that the googleads_campaigns table is in your database.

You can export Google Ads data to PostgreSQL (or other listed data destinations) for analysis. We will use PostgreSQL for our example.

Query to Combine Data

The following SQL query can be used to join your Google Analytics and Google Ads data based on your campaign ID:

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;

This query joins the two tables you created using CloudQuery's Google Analytics and Google Ads plugins. The join is based on the campaign ID common to both tables, allowing us to extract relevant columns from both. The metrics and dimensions in the ga_example table are stored as JSON data, so we must extract the values properly.

Example Results

Summary

By combining Google Analytics and Google Ads data, you can gain a better understanding of your campaigns, getting more information for each campaign that is not available by using just one table. Feel free to adjust the dimensions and metrics to suit your specific needs, and use the provided SQL query as a starting point for your analysis. If you want to enhance your data analysis capabilities, try CloudQuery for free or connect with our engineering team for further assistance.

Want help getting started? Join the CloudQuery community to connect with other users and experts, or message our team directly here if you have any questions.