SyncWith

Google Ads to Google Sheets

Chris Reid

Chris Reid

Sync Google Ads Data

If you don’t want to call the API directly and write glue code and just want the data sync’d to your data platform of choice SyncWith does this for 100,000s of users and businesses:
  • Google Ads API to Databases (S3, BigQuery, MySQL, Postgres) - contact support to join our beta

Try SyncWith’s Google Ads connector

Before you go digging into setting up OAuth and digesting the complexity of the Google Ads API, it may be useful to see what kind of data is returned by it. Here are 3 preset calls to the API to better understand what is possible and what data you can expect to get back from the API:

Pulling Keywords

This is a pre canned request designed to get the dimensions:
  • campaign name
  • ad group name
  • keyword
  • keyword match type
and the following metrics:
  • clicks
  • impressions
  • CTR
  • average CPC
  • cost
  • conversions
  • cost per conversion

Data

Data should look like this (sample data below is null)
notion image
Date is set to the last 90 days and sort by clicks descending.

Give it a Go!

Campaign NameAd Group NameAd Group Criterion Keyword TextAd Group Criterion Keyword Match Type
ClicksImpressionsCTRAverage CPCCostConversionsCost Per Conversion

Preview

Expand nested data, rename and delete columns

Campaign Name
Ad Group Name
Ad Group Criterion Keyword Text
Ad Group Criterion Keyword Match Type
Clicks
Impressions
CTR
Average CPC
Cost
Conversions
Cost Per Conversion
           
           
           

Get a live Google Sheet

Export your data to a google sheet, with the SyncWith Addon, or download to CSV

Automatically updated

Add data sources

Build reports & dashboards

Resources to Call the API Directly

Google Ads Video Overview

Video preview

Get a Developer Token

  • You an only associate one manager account ( you must have an account to apply) and it will be permanently paired
  • You’ll need to get your app reviewed by the token team

Client Libraries

Google provides client libraries that make accessing the Google ads API easier, including automating Oauth tokens, pagination, etc.. The have these available for Java, .NET, PHP, Python, Ruby and Perl - although each has different API version support.
Google has a GIT project called Ads Doctor that can help verify your credentials and OAuth2 issues. You can check out the readme file.
Google has a tonne of code samples to check out to help you through various use cases and a great place to start

Authentication

The client libraries they’ll handle the access tokens otherwise you’ll need to generate your own token. The libraries will also use the refresh token to automatically regenerate a OAuth2 access token (the tokens expire. You can see a video on Google Auth if you’re not using the client libraries:
Video preview

API Tips

Object Hierarchy

Its instructive to understand the object hierarchy when calling the API
notion image
And then understanding the entities in greater detail:
notion image

Versioning

  • Major versions have their own endpoint
  • Versions are formatted like MAJOR.MINOR
  • https://googleads.googleapis.com/v{MAJOR}.{MINOR}
  • Major versions have breaking non backwards compatible changes
  • Minor versions are backwards compatible
  • Client libraries will have differences in support for older versions

Changing Objects

  • Objects will have mutate methods that accept a mutate request
  • Provide the customerId, operations and content type to be returned
  • For example CampaignService has a method MutateCampaigns that takes a MutateCampaignsRequest with a
    • customerId
    • Collection of CampaignOperation
    • response_content_type
  • Each operation can only create, update or remove a resource but a mutate requests can have many operations so be sure to bulk your operations into a single mutate request.
Example - Adding an Ad Group
<?php namespace Google\Ads\GoogleAds\Examples\BasicOperations; require __DIR__ . '/../../vendor/autoload.php'; use GetOpt\GetOpt; use Google\Ads\GoogleAds\Examples\Utils\ArgumentNames; use Google\Ads\GoogleAds\Examples\Utils\ArgumentParser; use Google\Ads\GoogleAds\Examples\Utils\Helper; use Google\Ads\GoogleAds\Lib\V11\GoogleAdsClient; use Google\Ads\GoogleAds\Lib\V11\GoogleAdsClientBuilder; use Google\Ads\GoogleAds\Lib\V11\GoogleAdsException; use Google\Ads\GoogleAds\Lib\OAuth2TokenBuilder; use Google\Ads\GoogleAds\Util\V11\ResourceNames; use Google\Ads\GoogleAds\V11\Enums\AdGroupStatusEnum\AdGroupStatus; use Google\Ads\GoogleAds\V11\Enums\AdGroupTypeEnum\AdGroupType; use Google\Ads\GoogleAds\V11\Errors\GoogleAdsError; use Google\Ads\GoogleAds\V11\Resources\AdGroup; use Google\Ads\GoogleAds\V11\Services\AdGroupOperation; use Google\ApiCore\ApiException; /** This example adds ad groups to a campaign. */ class AddAdGroups { private const CUSTOMER_ID = 'INSERT_CUSTOMER_ID_HERE'; private const CAMPAIGN_ID = 'INSERT_CAMPAIGN_ID_HERE'; public static function main() { // Either pass the required parameters for this example on the command line, or insert them // into the constants above. $options = (new ArgumentParser())->parseCommandArguments([ ArgumentNames::CUSTOMER_ID => GetOpt::REQUIRED_ARGUMENT, ArgumentNames::CAMPAIGN_ID => GetOpt::REQUIRED_ARGUMENT ]); // Generate a refreshable OAuth2 credential for authentication. $oAuth2Credential = (new OAuth2TokenBuilder())->fromFile()->build(); // Construct a Google Ads client configured from a properties file and the // OAuth2 credentials above. $googleAdsClient = (new GoogleAdsClientBuilder())->fromFile() ->withOAuth2Credential($oAuth2Credential) ->build(); try { self::runExample( $googleAdsClient, $options[ArgumentNames::CUSTOMER_ID] ?: self::CUSTOMER_ID, $options[ArgumentNames::CAMPAIGN_ID] ?: self::CAMPAIGN_ID ); } catch (GoogleAdsException $googleAdsException) { printf( "Request with ID '%s' has failed.%sGoogle Ads failure details:%s", $googleAdsException->getRequestId(), PHP_EOL, PHP_EOL ); foreach ($googleAdsException->getGoogleAdsFailure()->getErrors() as $error) { /** @var GoogleAdsError $error */ printf( "\t%s: %s%s", $error->getErrorCode()->getErrorCode(), $error->getMessage(), PHP_EOL ); } exit(1); } catch (ApiException $apiException) { printf( "ApiException was thrown with message '%s'.%s", $apiException->getMessage(), PHP_EOL ); exit(1); } } /** * Runs the example. * * @param GoogleAdsClient $googleAdsClient the Google Ads API client * @param int $customerId the customer ID * @param int $campaignId the campaign ID to add ad groups to */ public static function runExample( GoogleAdsClient $googleAdsClient, int $customerId, int $campaignId ) { $campaignResourceName = ResourceNames::forCampaign($customerId, $campaignId); $operations = []; // Constructs an ad group and sets an optional CPC value. $adGroup1 = new AdGroup([ 'name' => 'Earth to Mars Cruises #' . Helper::getPrintableDatetime(), 'campaign' => $campaignResourceName, 'status' => AdGroupStatus::ENABLED, 'type' => AdGroupType::SEARCH_STANDARD, 'cpc_bid_micros' => 10000000 ]); $adGroupOperation1 = new AdGroupOperation(); $adGroupOperation1->setCreate($adGroup1); $operations[] = $adGroupOperation1; // Constructs another ad group. $adGroup2 = new AdGroup([ 'name' => 'Earth to Venus Cruises #' . Helper::getPrintableDatetime(), 'campaign' => $campaignResourceName, 'status' => AdGroupStatus::ENABLED, 'type' => AdGroupType::SEARCH_STANDARD, 'cpc_bid_micros' => 20000000 ]); $adGroupOperation2 = new AdGroupOperation(); $adGroupOperation2->setCreate($adGroup2); $operations[] = $adGroupOperation2; // Issues a mutate request to add the ad groups. $adGroupServiceClient = $googleAdsClient->getAdGroupServiceClient(); $response = $adGroupServiceClient->mutateAdGroups( $customerId, $operations ); printf("Added %d ad groups:%s", $response->getResults()->count(), PHP_EOL); foreach ($response->getResults() as $addedAdGroup) { /** @var AdGroup $addedAdGroup */ print $addedAdGroup->getResourceName() . PHP_EOL; } } } AddAdGroups::main();

Retrieving Objects

  • Google Ads has a unified object for retrieval and reporting.
 
Paging vs Streaming Data with Google Ads
Video preview
 
Code Sample: Get Your Campaigns
#!/usr/bin/env python """This example illustrates how to get all campaigns. To add campaigns, run add_campaigns.py. """ import argparse import sys from google.ads.googleads.client import GoogleAdsClient from google.ads.googleads.errors import GoogleAdsException def main(client, customer_id): ga_service = client.get_service("GoogleAdsService") query = """ SELECT campaign.id, campaign.name FROM campaign ORDER BY campaign.id""" # Issues a search request using streaming. stream = ga_service.search_stream(customer_id=customer_id, query=query) for batch in stream: for row in batch.results: print( f"Campaign with ID {row.campaign.id} and name " f'"{row.campaign.name}" was found.' ) if __name__ == "__main__": # GoogleAdsClient will read the google-ads.yaml configuration file in the # home directory if none is specified. googleads_client = GoogleAdsClient.load_from_storage(version="v10") parser = argparse.ArgumentParser( description="Lists all campaigns for specified customer." ) # The following argument(s) should be provided to run the example. parser.add_argument( "-c", "--customer_id", type=str, required=True, help="The Google Ads customer ID.", ) args = parser.parse_args() try: main(googleads_client, args.customer_id) except GoogleAdsException as ex: print( f'Request with ID "{ex.request_id}" failed with status ' f'"{ex.error.code().name}" and includes the following errors:' ) for error in ex.failure.errors: print(f'\tError with message "{error.message}".') if error.location: for field_path_element in error.location.field_path_elements: print(f"\t\tOn field: {field_path_element.field_name}") sys.exit(1)