9 {{ upvoteCount | shortNum }}
9 {{ upvoteCount | shortNum }}

Automate Data Retrieval From Google Adwords Keyword Tool

by cheapskatemate
7 replies
I am trying to develop a script to download data from the adwords keyword tool.

Specifically, I am trying to:

Filter to show global monthly searches above 1000
Filter to show exact matches
Filter to show a single category
Save the results as csv.

So far I can use excel to open up an internet explorer window. But I don't know how to handle the form filling.

Any tips?
#adwords #automate #data #google #keyword #retrieval #tool
  • Profile picture of the author Eager2SEO
    Originally Posted by cheapskatemate View Post

    I am trying to develop a script to download data from the adwords keyword tool.

    Specifically, I am trying to:

    Filter to show global monthly searches above 1000
    Filter to show exact matches
    Filter to show a single category
    Save the results as csv.

    So far I can use excel to open up an internet explorer window. But I don't know how to handle the form filling.

    Any tips?
    I've done it, but if that is the extent of your programming skill you are far from accomplishing the job. You are going to need .net or php to do this task. It is not easy, and I'm not 100% sure my script will work on a consistent basis without making changes.
    Signature

    Available for article writing or <?php | .net ?> programming work! Article samples available on request.

    {{ DiscussionBoard.errors[2983459].message }}
    • Profile picture of the author cheapskatemate
      Originally Posted by Eager2SEO View Post

      I've done it, but if that is the extent of your programming skill you are far from accomplishing the job. You are going to need .net or php to do this task. It is not easy, and I'm not 100% sure my script will work on a consistent basis without making changes.
      Do you have any tips on how to do it? Or would you be able to share your script?
      Signature

      CheapskateMate.com
      {{ DiscussionBoard.errors[2984636].message }}
      • Profile picture of the author Eager2SEO
        Originally Posted by cheapskatemate View Post

        Do you have any tips on how to do it? Or would you be able to share your script?
        First of all, in my system, you have to log in to your adwords account thru my program. This is not a bad thing, because you can also pull google insights for search data too. I actually do that too.

        I was not able to figure out how the no-login captcha system gets a certain value, so you need to log in. You need the ability to run Javascript to do that.

        I'll think about sharing the script. I did it jointly with a client(they paid I programmed- it wasn't cheap, but there were other features too), I have to see what they say. I know he is very happy with it though, he has a library of 250K keywords and data now.


        The issue was that market samurai gets Australian results for certain searches. So we rewrote it. MS is also painfully slow sometimes. I honestly wish now I did it on my own, But I can use the software for myself.

        Maybe I will build it into a class library so you just supply your keyword and parameters and it returns your data. Again, there is one value that seems to stay constant but Google did change it once. I'm still trying to find the source of that. It is a PITA to get it manually when it changes. I still prefer that occasional glitch over using the KWD tool, since I can integrate mine with my competition module.

        Here is where you can start your journey. Download firebug for firefox, enable it, and open it up by clicking on the icon in the status bar. Click on the net tab. Observe the net tab as you log into adwords and request data. Pay attention to what is GET and POSTed. These are some of the messages you are going to have to send.

        You are going to be sending a httprequest to Google, download the page, cherry pick pieces of info off the page, and send back cookies and certain information to Google for the login. After you are authenticated, you will send a weird string and it will send you back a CSV to have fun with.

        If I can release it I'll PM you. I know he said if I put it in a product of my own I have to notify him and give him a cut.

        The other option is looking into iMarcos. I'm pretty sure that would do the job.
        Signature

        Available for article writing or <?php | .net ?> programming work! Article samples available on request.

        {{ DiscussionBoard.errors[2984992].message }}
        • Profile picture of the author Brandon Tanner
          Originally Posted by Eager2SEO View Post

          The other option is looking into iMarcos. I'm pretty sure that would do the job.
          Yeah, I was just about to suggest iMacros. For that type of job, it would be a lot easier to setup than a php script. Outwit Hub is another option.
          Signature

          {{ DiscussionBoard.errors[2985745].message }}
  • Profile picture of the author cheapskatemate
    Thanks for the tip.

    I have found that the download URL is:

    https://adwords.google.com/o/Targeting/file/DownloadAll

    But the post data is :

    6|1|69|https://adwords.google.com/o/Targeti...14q|g|TiAction (Search.MatchType.KEYWORD_IDEAS.KeywordCategoryIdS earchParameter)|JRW0sH_uPdUxxqNoPEZ6eC5zy_Y:129185 0568724|197|15b|14x|14z|c|h|i|16n|195|ADVANCED_OPT IONS|CountryTargetSearchParameter|TiImpression|Par ameterInQuery|LanguageTargetSearchParameter|Device TypeSearchParameter|GlobalMonthlySearchesSearchPar ameter|z6|yw|17u|sm|en_GB|tn|105|zf|17l|zg|NEGATIV E_KEYWORDS|IDEA_IN_ADGROUP|KEYWORD|COMPETITION|GLO BAL_MONTHLY_SEARCHES|AVERAGE_TARGETED_MONTHLY_SEAR CHES|TARGETED_MONTHLY_SEARCHES|IDEA_TYPE|AD_SHARE| EXTRACTED_FROM_WEBPAGE|SEARCH_SHARE|KEYWORD_CATEGO RY|NGRAM_GROUP|10i|19e|11s|United Kingdom|GB|10z|11u|English|en|10l|10m|10t|11i|190| 10x|10y|17r|b9|1|2|3|4|1|5|5|6|YtOaQyf6g|8|7|1|8|0 |9|b_FGOS|10|2|GNDki6|D1Ffvy|11|12|13|5|14|15|16|-4|0|0|0|17|5|A|0|0|0|0|0|0|0|18|0|19|1|0|0|0|0|0|0 |0|0|0|0|0|7|0|0|0|20|21|2|22|23|1|24|21|1|25|14|1 5|16|-4|0|0|0|-11|A|0|0|0|0|0|0|0|-12|-13|0|0|0|0|0|0|0|0|0|0|0|7|0|0|0|20|21|2|22|26|1|2 4|-17|14|15|16|-4|0|0|0|-11|A|0|0|0|0|0|0|0|-12|-13|0|0|0|0|0|0|0|0|0|0|0|7|0|0|0|20|21|2|22|27|1|2 4|-17|14|15|16|-4|0|0|0|-11|A|0|0|0|0|0|0|0|-12|-13|0|0|0|0|0|0|0|0|0|0|0|7|0|0|0|20|21|2|22|28|1|2 4|-17|29|15|16|-4|0|0|0|-11|A|0|0|0|0|0|0|0|-12|-13|0|0|0|0|0|0|0|0|0|0|0|7|0|0|0|30|31|86|32|0|33| 34|50|0|35|0|0|36|0|37|11|38|39|38|40|38|41|38|42| 38|43|38|44|38|45|38|46|38|47|38|48|38|49|37|3|-49|38|50|38|51|37|6|52|53|1|54|55|0|1|56|0|57|53|1 |58|59|60|61|62|4|63|64|53|0|0|65|Es|66|194610|67| 68|69|0|0|

    So the post data is quite a hand full. All I'm currently trying to do is download data from each category, with filters set to Exact Match, and global month searches over 300.

    So I need to somehow use a script to identify the apparel button first, then download it and save. Then click the clothing button. then save.

    I have basic Excel VBA skills. But am finding it hard to parse the DOM object, as it appears that the category menu on the left of the keyword tool is populated using javascript.
    Signature

    CheapskateMate.com
    {{ DiscussionBoard.errors[2986102].message }}
    • Profile picture of the author Eager2SEO
      Originally Posted by cheapskatemate View Post

      Thanks for the tip.

      I have found that the download URL is:

      https://adwords.google.com/o/Targeting/file/DownloadAll

      But the post data is :

      6|1|69|https://adwords.google.com/o/Targeting/|11AD40FFE8348DA7C7F13CAA61D5F7D2|_|invoke|3|14u|1 9d|14p|14q|g|TiAction (Search.MatchType.KEYWORD_IDEAS.KeywordCategoryIdS earchParameter)|JRW0sH_uPdUxxqNoPEZ6eC5zy_Y:129185 0568724|197|15b|14x|14z|c|h|i|16n|195|ADVANCED_OPT IONS|CountryTargetSearchParameter|TiImpression|Par ameterInQuery|LanguageTargetSearchParameter|Device TypeSearchParameter|GlobalMonthlySearchesSearchPar ameter|z6|yw|17u|sm|en_GB|tn|105|zf|17l|zg|NEGATIV E_KEYWORDS|IDEA_IN_ADGROUP|KEYWORD|COMPETITION|GLO BAL_MONTHLY_SEARCHES|AVERAGE_TARGETED_MONTHLY_SEAR CHES|TARGETED_MONTHLY_SEARCHES|IDEA_TYPE|AD_SHARE| EXTRACTED_FROM_WEBPAGE|SEARCH_SHARE|KEYWORD_CATEGO RY|NGRAM_GROUP|10i|19e|11s|United Kingdom|GB|10z|11u|English|en|10l|10m|10t|11i|190| 10x|10y|17r|b9|1|2|3|4|1|5|5|6|YtOaQyf6g|8|7|1|8|0 |9|b_FGOS|10|2|GNDki6|D1Ffvy|11|12|13|5|14|15|16|-4|0|0|0|17|5|A|0|0|0|0|0|0|0|18|0|19|1|0|0|0|0|0|0 |0|0|0|0|0|7|0|0|0|20|21|2|22|23|1|24|21|1|25|14|1 5|16|-4|0|0|0|-11|A|0|0|0|0|0|0|0|-12|-13|0|0|0|0|0|0|0|0|0|0|0|7|0|0|0|20|21|2|22|26|1|2 4|-17|14|15|16|-4|0|0|0|-11|A|0|0|0|0|0|0|0|-12|-13|0|0|0|0|0|0|0|0|0|0|0|7|0|0|0|20|21|2|22|27|1|2 4|-17|14|15|16|-4|0|0|0|-11|A|0|0|0|0|0|0|0|-12|-13|0|0|0|0|0|0|0|0|0|0|0|7|0|0|0|20|21|2|22|28|1|2 4|-17|29|15|16|-4|0|0|0|-11|A|0|0|0|0|0|0|0|-12|-13|0|0|0|0|0|0|0|0|0|0|0|7|0|0|0|30|31|86|32|0|33| 34|50|0|35|0|0|36|0|37|11|38|39|38|40|38|41|38|42| 38|43|38|44|38|45|38|46|38|47|38|48|38|49|37|3|-49|38|50|38|51|37|6|52|53|1|54|55|0|1|56|0|57|53|1 |58|59|60|61|62|4|63|64|53|0|0|65|Es|66|194610|67| 68|69|0|0|

      So the post data is quite a hand full. All I'm currently trying to do is download data from each category, with filters set to Exact Match, and global month searches over 300.

      So I need to somehow use a script to identify the apparel button first, then download it and save. Then click the clothing button. then save.

      I have basic Excel VBA skills. But am finding it hard to parse the DOM object, as it appears that the category menu on the left of the keyword tool is populated using javascript.
      Yes, there are a lot of javascript pitfalls but you can avoid most of them. Parsing the DOM is not called for here.
      The 11AD.... string seems to change occasionally. That is something that is generated somehow from complex javascript. Still working on that one. Honestly you are going to be passing that basic string with keyword and token variations.

      Also, That is not the complete POST string. I'm going to suggest now that you download and graduate to something called Fiddler2. It is a free Microsoft product that will give you a much better insight into what is going on. Get very comfortable looking at raw headers and the info passed back and forth. Pay attention to the cookies. Start with a clean, cookieless (private browsing session to see traffic going back and forth).

      This value: JRW----5056872 is a token that will show up when you load the adwords HTML page. You have to extract that with regular expressions or writing a "string chop function."

      You also have do do a "urlencode" on parts of that string so it can be passed properly.

      Then don't forget the cookies!

      I've never worked with VBA, can you load raw html with it? Can you get headers off the HTML?

      Your first step is though is to load the base adwords page. When you get fiddler you are going to see more parameters you need.
      Signature

      Available for article writing or <?php | .net ?> programming work! Article samples available on request.

      {{ DiscussionBoard.errors[2987723].message }}

Trending Topics