Search API Connector Documentation

Print

Import Google Ads Keyword Planner Data to Google Sheets

premium

In this guide, we’ll walk through how to pull Keyword Planner data from the Google Ads / AdWords API data directly into Google Sheets, using the API Connector add-on for Sheets.

Google’s Keyword Planner tool provides two main functions: discover new keywords and get keyword search volume. We’ll show how to access both of these functions through the API.

google-a_ds-keyword-planner-url-features

One of the best things about getting this data from the API vs. the interface is that the data is more precise. For example, where Keyword Planner says Avg. Monthly Searches = 1K – 10K, the API gives a number like 2400.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Connect to the Google Ads API

If you haven’t connected API Connector to Google Ads before, you’ll first need to initiate the connection as follows:

  1. Open up Google Sheets and click Extensions > API Connector > Manage Connections.
  2. In the list of available connections, find “Google Ads” and click Connect.
    google-as-img1
  3. You will be directed to google.com and asked to allow API Connector to manage your AdWords campaigns:
    google-as-img2
  4. You’ll then be returned to your Google Sheet, and can verify that your Google Ads connection is active in the Connections screen.

Part 2: Get Keyword Ideas from Google Ads

For our first request, we’ll pull in a list of keyword ideas for your website.

  1. First, get your account ID as we’ll need this for subsequent requests. You can get your account ID from the login menu or the top navigation bar in your Google Ads account.
    google-as-img3
  2. Next, create your request URL. The keyword idea generator is accessed by sending POST requests to the following API Request URL, substituting in your own account ID where it says 2668258503 (don’t include the dashes from your account ID).
    https://googleads.googleapis.com/v10/customers/2668258503:generateKeywordIdeas
  3. Finally, create your POST body. Google provides various parameters we can use to customize the keyword idea generator, and these get entered through the POST body.
    {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS", "urlSeed": {"url":"https://mixedanalytics.com"}} 

Part 3: Pull Keyword Ideas API Data into Sheets

We’ll now enter those values into API Connector.

  1. In API Connector, click Create, choose POST from the dropdown menu, and paste in the Request URL we created above.
    google-a_ds-keyword-planner-url
  2. Under OAuth, choose Google Ads from the dropdown menu. You should see a “connected” badge.
    google-a_ds-keyword-planner-connected

  3. Under headers enter Key = content-type, Value = application/json
    google-as-img5
  4. In the POST Body section, paste in the POST body we created above.
    google-a_ds-keyword-planner-request-body
  5. Create a new tab and click Set current to use that tab as your data destination.
  6. Name your request and click Run. A moment later you’ll see a list of keyword ideas and associated metrics populate your sheet.
    google-a_ds-keyword-planner-results1
  7. (Optional) Click Edit Fields and use the visual field editor to filter out all the fields but these: avgMonthlySearches, competitionIndex, lowTopOfPageBidMicros, highTopOfPageBidMicros, and text

Variation 1: get ideas with a keyword seed

Set this up exactly the same as above, but instead of adding a seed URL, enter a seed keyword, like this:

{"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS","keywordSeed":{"keywords":["my keyword"]},"pageSize":"1000"}

Variation 2: target a locale

Target locales through the geo_target_constants parameter. Geo target constant IDs are provided here.

{"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS",
"urlSeed":
{"url":"https://mixedanalytics.com"},
"geo_target_constants":["geoTargetConstants/2158","geoTargetConstants/2392"]}

Variation 3: target a language

Target a language through the language parameter. Language constant IDs are provided here.

{"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS",
"keywordSeed":{"keywords":["水果"]},
"language":"languageConstants/1018"}

Any of these parameters can be combined into a single request body.

Part 4: Get Search Volume for a List of Keywords

We’ll now show how to access Keyword Planner’s other main functionality: retrieving historical data for a list of keywords. While it is technically possible to do all of this completely through the API, for simplicity we’ll first create the keyword plan through the interface.

  1. In Google Ads, open the Keyword Planner  (Google Ads > Tools and settings > Keyword Planner)
  2. Click Get search volume and forecasts and enter your list of keywords, then click Get Started
    google-a_ds-keyword-planner-create-plan
  3.  You have now created your keyword plan. Check the plan ID in the URL before you go.
    google-a_ds-keyword-planner-plan-id
  4. Also, get your account ID if you haven’t already. You can get your account ID from the login menu or the top navigation bar in your Google Ads account.
    google-as-img3
  5. Now back in API Connector, set up your request like this:
    • Method: POST
    • URL: https://googleads.googleapis.com/v10/customers/your_customer_id/keywordPlans/your_plan_id:generateHistoricalMetrics
    • OAuth: Google Ads
    • Request body: {}
  6. Substitute in your own account ID (no dashes) and plan ID and hit Run. You’ll see monthly searches and other historical metrics for all the keywords in your plan:
    google-a_ds-keyword-planner-results2

Variation 1: get list of plans and plan IDs via the API instead of through the interface:

  • Method: POST
  • URL: https://googleads.googleapis.com/v10/customers/your_customer_ID/googleAds:searchStream
  • Request body: {"query": "SELECT keyword_plan.name, keyword_plan.id, keyword_plan.forecast_period, keyword_plan.resource_name FROM keyword_plan"}

Variation 2: choose the historical metrics period

By default, historical metrics will be returned for the past 12 months. If you’d prefer to get records for a different period, set this up exactly the same as above, but instead of adding a request body of {}, enter a request body containing your start/end dates like this: {"historicalMetricsOptions":{"yearMonthRange":{"start":{"year":"2022","month":"FEBRUARY"},"end":{"year":"2022","month":"APRIL"}}}}

Part 5: API Documentation

Appendix: Create a Keyword Plan via the API

For simplicity, the instructions above show how to create a keyword plan in the interface, since creating keyword plans via the API is a rather hefty multi-step process:
google-a_ds-keyword-planner-5steps

However, a few people have asked how to do this via the API, so I’ll provide instructions for that here. The first section contains a link to import all the requests you need, so you can get started quickly with only minor modifications required. The lower section provides detail about these requests, to help you better understand, modify, and/or create these requests manually.

Import Request File

  1. Download this request file to your computer
  2. Import it into API Connector by clicking Extensions > API Connector > Import/Export > Import API Requests.
  3. Open each request and substitute in your own customer ID where it says 2668258503
    google-a_ds-keyword-planner-customerid
  4. In request #5, substitute in your own keywords of interest.
    google-a_ds-keyword-planner-keywords
  5. Click Run on each request, in order, from top to bottom.
    google-a_ds-keyword-planner-run

Request File Detail

Nearly every request requires that we plug in a value from a previous response, so these instructions reference response cells, and expect you to use the destination sheet names provided. This makes it easier to convey which response needs to be plugged in where, and lets you easily re-use these requests in the future.

All requests use Method = POST, OAuth = Google Ads, and Headers of Key = Content-Type, Value = application/json.
  1. Create a KeywordPlan. Creates a keyword plan. Substitute in your own customer ID where it says 2668258503, in this and every request.
    • URL: https://googleads.googleapis.com/v10/customers/2668258503/keywordPlans:mutate
    • Request body: {"operations":[{"create":{"name":"myNewPlan","forecast_period":{"date_interval":"NEXT_MONTH"}}}],"partialFailure":false,"validateOnly":false}
    • Destination sheet: KeywordPlan
  2. Create KeywordPlanCampaigns. Creates the campaign for the keyword plan.
    • URL: https://googleads.googleapis.com/v10/customers/2668258503/keywordPlanCampaigns:mutate
    • Request body: {"operations":[{"create":{"name":"myNewKeywordCampaign","keyword_plan_network":"GOOGLE_SEARCH","geoTargets":[{"geoTargetConstant":"geoTargetConstants/2276"}],"cpc_bid_micros":10000000,"keywordPlan":"+++KeywordPlan!A2+++"}}],"partialFailure":false,"validateOnly":false}
    • Destination sheet: KeywordPlanCampaigns

      Geo targeting is optional, so just leave the bolded text out if you don’t need it. Otherwise, geo target IDs are located here.

  3. Create KeywordPlanAdGroups. Creates the ad group for the keyword plan.
    • URL: https://googleads.googleapis.com/v10/customers/2668258503/keywordPlanAdGroups:mutate
    • Request body: {"operations":[{"create":{"name":"myNewAdGroup","keywordPlanCampaign":"+++KeywordPlanCampaigns!A2+++"}}],"partialFailure":false,"validateOnly":false}
    • Destination sheet: KeywordPlanAdGroups
  4. Create KeywordPlanCampaignKeywords. Creates negative campaign keywords for the keyword plan.
    • URL: https://googleads.googleapis.com/v10/customers/2668258503/keywordPlanCampaignKeywords:mutate
    • Request body: {"operations":[{"create":{"match_type":"exact","text":"a negative keyword","negative":true,"keywordPlanCampaign":"+++KeywordPlanCampaigns!A2+++",}}],"partialFailure":false,"validateOnly":false}
    • Destination sheet: KeywordPlanCampaignKeywords
  5. Create KeywordPlanAdGroupKeywords. Adds keywords to the keyword plan. Substitute in your own keywords where it says “cars” and “motorcycles”. You can follow the JSON structure to add as many keywords as you need.
    • URL: https://googleads.googleapis.com/v10/customers/2668258503/keywordPlanAdGroupKeywords:mutate
    • Request body: {"operations":[{"create":{"match_type":"broad","text":"cars","negative":false,"keywordPlanAdGroup":"+++KeywordPlanAdGroups!A2+++"}},{"create":{"match_type":"phrase","text":"motorcycles","negative":false,"keywordPlanAdGroup":"+++KeywordPlanAdGroups!A2+++"}}],"partialFailure":false,"validateOnly":false}
    • Destination sheet: KeywordPlanAdGroupKeywords
  6. Call KeywordPlanService.GenerateHistoricalMetrics. Retrieves the keyword plan’s search volume metrics into your sheet.
    • URL: https://googleads.googleapis.com/v10/+++KeywordPlan!A2+++:generateHistoricalMetrics
    • Request body: {}
    • Destination sheet: GenerateHistoricalMetrics
Previous Import Google Ads Data to Google Sheets
Next Import Google Analytics (GA4) Data to Google Sheets

12 thoughts on “Import Google Ads Keyword Planner Data to Google Sheets”

  1. Can you give an example of adding location to this –

    {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS",
    "keywordSeed":
    {"keywords": ["hello"]}
    }

    Reply
  2. Hi Ana,

    Thanks for your amazing work! Would it be possible to start the keyword planner API from a cell input? I would like to start the keyword planner output based on a keyword you give in a certain cell. In your example you used the fixed example with ‘hello world’ but probably this could change to cell value

    Reply
  3. Hi Ana
    This is exactly what I was looking for, thank you very much for your work.
    However, I’d like to do what you are not showing: «While it is technically possible to do all of this completely through the API»
    Could you give instructions on how to achieve this throught the API only?
    Thanks for reading this commen 🙂

    Reply
    • Hey Thomas, sorry, I’m not totally sure myself as Google doesn’t provide much documentation for this. They also write here that it’s a 5-step process of creating a KeywordPlan, then adding KeywordPlanCampaign, KeywordPlanAdGroups, KeywordPlanAdGroupKeywords, and KeywordPlanCampaignKeyword. So I think it’s easiest to just make the keyword plan in the interface, but you can check their keyword plan API documentation and see what you find.

      Reply
  4. Hi, super useful! I’m using for the historical metrics, can you show how we can add a geo filter for this one, I can’t get it working!
    Thanks

    Reply
    • Hey John, I don’t believe it’s possible to set a geo filter for historical metrics via the API. You can see the available options for historical metrics here, which shows that Google only enables setting the time range.

      Reply
  5. Hello,

    Is it possible to use this tool to get search volumes for a list of keywords already present in a gsheet? i.e.

    Phrase Avg monthly search volume
    phrase 1? 1000
    phrase 2? 2000

    Thank you

    Chris

    Reply
    • Yes, it should be possible, but not from the Google Ads Keyword Planner API since it requires that you first create a keyword plan that contains all your keywords. So you’d need to use a different (and generally paid) API for this, as some of them have much simpler methods of retrieving data. For example, the Keyword Tool or Serpmaster APIs let you simply input a list of search terms and get back search volumes. That list of keywords could come straight from your sheet.

      Reply

Leave a Reply to Chris Cancel reply

Table of Contents