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: Get Your Account IDs

Target Site ID

You'll need the ID of the site for which you'd like to retrieve data, so let's get that first from the login menu or the top navigation bar in your Google Ads account.

google-as-img3

That ID will be entered into the URL without dashes, e.g. https://googleads.googleapis.com/v12/customers/2668258503:generateKeywordIdeas

Manager Account ID

If your access to a customer account is through a manager account, you will also need to get the ID of that manager account (info).

This manager ID will be entered into a login-customer-id header, like this:

google-as-img14

In short, the request URL contains the ID of the account for which you'd like to fetch data, while the manager ID goes in the header as shown above.

Part 2: Pull Google Ads Keyword Ideas into Google Sheets

For this first request, we'll pull in a list of keyword ideas for your website. Here's an example request setup, just substitute your own account ID in the request URL where it says your_account_id, and your own website of interest.

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • Application: Custom
    • Method: POST
    • Request URL: https://googleads.googleapis.com/v12/customers/target_account_id:generateKeywordIdeas
    • OAuth: Google Ads
    • Headers
      • content-type: application/json
      • login-customer-id: manager_id (if you're connecting through a manager account)
    • Request body: {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS", "urlSeed": {"url":"https://mixedanalytics.com"}}
  3. When you select Google Ads from the OAuth menu, you'll see a blue Connect button if you haven't already authorized the connection. Click through to enable.
  4. Create a new tab and click Set current to use that tab as your data destination.
  5. 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-response
  6. (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. A list of geo target IDs (i.e. country codes) is 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"}

Part 3: 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. Now back in API Connector, set up your request like this:
    • Application: Custom
    • Method: POST
    • URL: https://googleads.googleapis.com/v12/customers/your_customer_id/keywordPlans/your_plan_id:generateHistoricalMetrics
    • OAuth: Google Ads
    • Request body: {}
  5. 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/v12/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 4: 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 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 Import Request File section contains a link to import all the requests you need, so you can get started quickly with only minor modifications required. The Request File Detail 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/v12/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/v12/customers/2668258503/keywordPlanCampaigns:mutate
    • Request body: {"operations":[{"create":{"name":"myNewKeywordCampaign","keyword_plan_network":"GOOGLE_SEARCH","geoTargets":[{"geoTargetConstant":"geoTargetConstants/2840"}],"cpc_bid_micros":10000000,"keywordPlan":"+++KeywordPlan!A2+++"}}],"partialFailure":false,"validateOnly":false}
    • Destination sheet: KeywordPlanCampaigns

      This sample request is targeted at geo ID 2840 (the US). A list of geo target IDs is provided here.

  3. Create KeywordPlanAdGroups. Creates the ad group for the keyword plan.
    • URL: https://googleads.googleapis.com/v12/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/v12/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/v12/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/v12/+++KeywordPlan!A2+++:generateHistoricalMetrics
    • Request body: {}
    • Destination sheet: GenerateHistoricalMetrics

30 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
  6. First of all - THANK YOU SO MUCH FOR THIS - this walkthrough is incredible, and got me up and running on a project that I previously thought was impossible.

    One small hitch though - when pulling Historical Keyword Metrics, I can't get it to target the locale that I want. No matter if I change the Google Ads plan to target the keyword, or try some json in the request body, the tool always pulls the historical metrics from the USA only.

    How do you target a historical keyword metrics request to a different geo target? I'm happy with a manual method, just need to know how to do it!

    Thank you!

    Reply
    • Hi Jersh, glad this was useful. Unfortunately Google doesn't have an option for locale targeting after you've already created your keyword plan. So to do this I think you'd need to create a keyword plan from scratch as described in the appendix. That allows you to edit the KeywordPlanCampaigns request like this: "geoTargets":[{"geoTargetConstant":"geoTargetConstants/2826"}]

      Reply
      • Hi Ana, I'm having the same issue as Jersh but what you've suggested I can't seem to do. Where can I edit the KeywordPlanCampaigns request to change the locale?

      • Yeah, it doesn't really make sense that the Google Ads API doesn't provide this as a simple parameter, it feels like an oversight or even a bug. But in any case you would edit the KeyWordPlanCampaigns request like this: {"operations":[{"create":{"name":"myNewKeywordCampaign","keyword_plan_network":"GOOGLE_SEARCH","geoTargets":[{"geoTargetConstant":"geoTargetConstants/2276"}],"cpc_bid_micros":10000000,"keywordPlan":"+++KeywordPlan!A2+++"}}],"partialFailure":false,"validateOnly":false}. You can get these geo IDs from here.

  7. Hello Ana,
    Thank you for your wonderful app.
    I've been trying to get some results but to no avail.
    Here's the request:
    {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS","keywordSeed":{"keywords":["credit", "cars"]}}

    And here's the error I get. Please may I request your assistance and pointing out what wrong.

    1) keyword gen: Completed with errors
    - We received an error from googleapis.com (400) show response
    { "error": { "code": 400, "message": "Invalid JSON payload received. Unknown name \"keywordSeed[keywords][0]\": Cannot bind query parameter. Field 'keywordSeed[keywords][0]' could not be found in request message.\nInvalid JSON payload received. Unknown name \"keywordSeed[keywords][1]\": Cannot bind query parameter. Field 'keywordSeed[keywords][1]' could not be found in request message.", "status": "INVALID_ARGUMENT", "details": [ { "@type": "type.googleapis.com/google.rpc.BadRequest", "fieldViolations": [ { "description": "Invalid JSON payload received. Unknown name \"keywordSeed[keywords][0]\": Cannot bind query parameter. Field 'keywordSeed[keywords][0]' could not be found in request message." }, { "description": "Invalid JSON payload received. Unknown name \"keywordSeed[keywords][1]\": Cannot bind query parameter. Field 'keywordSeed[keywords][1]' could not be found in request message." } ] } ] } }

    Thanks!

    Reply
  8. Thank you very much Anna for all this amazing guide!!

    When I try to generate historical metrics I get the following error:

    { "error": { "code": 400, "message": "Request contains an invalid argument.", "status": "INVALID_ARGUMENT", "details": [ { "@type": "type.googleapis.com/google.ads.googleads.v10.errors.GoogleAdsFailure", "errors": [ { "errorCode": { "keywordPlanError": "KEYWORD_PLAN_NOT_FOUND" }, "message": "No plan with planId[*******] exists" } ], "requestId": "******" } ] } }

    I took the planid from the string planId=*******

    Reply
    • Hey Manuel, I believe that error message only displays if the plan ID is incorrect, so I can only suggest double checking the account ID and plan ID. Feel free to message support with a screenshot of your API Connector config, along with the UI where you can see the plan ID, if you'd like me to take a look.

      Reply
      • Hey Ana,

        I have the same Problem. I'm using a MCC Account and I'm not sure how to add the login-customer-id to your request URL accoring to the linked article. Could you provide an updated URL on this?

        Kind regards
        Katharina

      • Hey Katharina, I added a section on manager accounts to this article to make it more clear, please check and just let me know if you run into any issues.

    • Hey Manuel, I had the same problem. It seems that if you have an MCC the query doesn´t find the plan id. I recomend to use an account without MCC

      Reply
      • That's interesting, thanks Alex! If you're connecting through an MCC account, you should be able to retrieve the data by adding a login-customer-id header info)

  9. Hello,

    That's a beautiful tutorial, But I am getting the following error after executing the call.

    { "error": { "code": 403, "message": "Request had insufficient authentication scopes.", "status": "PERMISSION_DENIED", "details": [ { "@type": "type.googleapis.com/google.rpc.ErrorInfo", "reason": "ACCESS_TOKEN_SCOPE_INSUFFICIENT", "domain": "googleapis.com", "metadata": { "service": "googleads.googleapis.com", "method": "google.ads.googleads.v10.services.KeywordPlanIdeaService.GenerateKeywordIdeas" } } ] } }

    Reply
  10. Hey!

    Thanks!

    Is it possible to add a date range to:
    {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS", "urlSeed": {"url":"https://mixedanalytics.com"}}

    L

    Reply
    • Sure, you can do it with the historicalMetricsOptions parameter like this: {"keywordPlanNetwork":"GOOGLE_SEARCH_AND_PARTNERS","urlSeed":{"url":"https://mixedanalytics.com"},"historicalMetricsOptions":{"includeAverageCpc":true,"yearMonthRange":{"start":{"year":"2022","month":"OCTOBER"},"end":{"year":"2022","month":"DECEMBER"}}}}

      Reply

Leave a Comment

Jump To...