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

• August 7, 2024

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

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 Postgres 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 #

date	campaign_name	source_medium	device_category	country	city	sessions	total_users	pageviews	avg_session_duration	bounce_rate	transactions	revenue	google_ads_campaign_name	customer_id	advertising_channel_type	google_ads_start_date	google_ads_end_date	google_ads_status	google_ads_campaign_budget
2024-07-31 00:00:00	Summer Sale	google / cpc	mobile	USA	New York	1000	900	1500	300	0.5	50	5000	Summer Sale	123456789	SEARCH	2024-06-01	2024-12-31	ENABLED	1200000
2024-08-01 00:00:00	Winter Sale	google / cpc	desktop	USA	San Francisco	800	700	1200	250	0.6	30	3000	Winter Sale	123456789	DISPLAY	2024-07-01	2024-11-30	PAUSED	800000
2024-08-02 00:00:00	Spring Sale	google / cpc	tablet	USA	Los Angeles	1200	1100	1800	320	0.4	60	6000	Spring Sale	123456789	SEARCH	2024-08-01	2024-10-31	ENABLED	1500000

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.

Written by Ron Shemesh

Ron is a Senior Software Engineer at CloudQuery and has a background in data science and now works as a senior software engineer at CloudQuery. He has experience working in Python, SQL, React, Java and R. At CloudQuery, Ron has worked on our range of integrations and several projects foundational to platform performance. He loves taking on a challenge and using it to improve his skills.

On this page