• Skip to main content
  • Skip to primary sidebar

PPC Scripts

by Lynt services

  • About the Author
  • PPC Services
  • Workshops for PPC companies
  • English
  • Czech
You are here: Home / Uncategorized / OpenRefine: a fast and easy way to obtain keyword data from autocomplete

OpenRefine: a fast and easy way to obtain keyword data from autocomplete

February 15, 2017 od Jakub Kašparů 2 Comments

In my post we will look at how you can use OpenRefine to retrieve data from Google’s autocomplete. Using this method, you can get more usable keyword ideas, which can be used for classification analysis of keywords or can even be incorporated into your PPC campaigns.

Ok, enough talking. Let’s get started!

What will you need?
OpenRefine Tool. I recommend the latest beta version 2.6, which has fixed a lot of the mistakes from the previous versions.
The list of keywords that we want to choose from. Initially we will draw from the following sources:
Search queries in AdWords.
Keyword suggestion tool in AdWords.
Keywords from Google Webmaster Tools.

For the purpose of this post I will be using the 701 keyword ideas generated by the AdWords Keyword Planner for the term “refrigerator”. This doesn’t represent a complete list of all available keywords, but it will be sufficient for this example. The same idea can be applied to larger sets of data.

Import data into OpenRefine

Once we have our initial keyword idea list, we will use this to generate more search suggestions by uploading it in OpenRefine. So, we do this by selecting “Create Project” > “Clipboard” and then pasting the 701 keywords and clicking “Next”.

There are lots of useful settings here, but for now we will just click “Create Project” to continue.

Apply the script to generate more keywords

Once we have created the project, click the “Undo/Redo” tab and select “Apply…”.

A box will pop-up allowing you to paste the following script:

JavaScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
[
  {
    "op": "core/column-addition",
    "description": "Create column Data at index 1 based on column Column 1 using expression grel:value.escape('url')",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "Data",
    "columnInsertIndex": 1,
    "baseColumnName": "Column 1",
    "expression": "grel:value.escape('url')",
    "onError": "set-to-blank"
  },
  {
    "op": "core/column-addition-by-fetching-urls",
    "description": "Create column Load Data at index 2 by fetching URLs based on column Data using expression grel:\"http://suggestqueries.google.com/complete/search?&client=chrome&hl=de&q=\"+ value",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "Load Data",
    "columnInsertIndex": 2,
    "baseColumnName": "Data",
    "urlExpression": "grel:\"http://suggestqueries.google.com/complete/search?&client=chrome&hl=de&q=\"+ value",
    "onError": "set-to-blank",
    "delay": 1000
  },
  {
    "op": "core/column-addition",
    "description": "Create column Suggestions at index 3 based on column Load Data using expression grel:value.parseJson()[1].join(';')",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "newColumnName": "Suggestions",
    "columnInsertIndex": 3,
    "baseColumnName": "Load Data",
    "expression": "grel:value.parseJson()[1].join(';')",
    "onError": "set-to-blank"
  },
  {
    "op": "core/multivalued-cell-split",
    "description": "Split multi-valued cells in column Suggestions",
    "columnName": "Suggestions",
    "keyColumnName": "Column 1",
    "separator": ";",
    "mode": "plain"
  }
]

 

Then click “Perform Operations” and the script will begin generating a whole list of new keywords.

Once the process is complete, you will immediately see that the number of keywords has increased greatly, in this case from 701 to 11,250. And if you scroll to the right you will see a column called “Suggestions” which is where the new keywords are listed.

 

Now, you can simply export the new data by selecting “Export” and then selecting “Excel”. And there you have it!

Filed Under: Uncategorized

Reader Interactions

Comments

  1. Amber says

    February 21, 2018 at 11:05 am

    Thanks on your marvelous posting! I actually enjoyed reading it, you may be a great author.I will always bookmark your blog and will eventually come back later in life. I want to encourage you continue your great posts, have a nice evening!

    Reply
  2. how to make a blog says

    February 21, 2018 at 10:26 pm

    Hi, what do you feel around free new movies? Pretty impressive idea, isn’t it?

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *


Primary Sidebar

Google Partner

Tools

AWQL Generator
Keyword Combinator
Datastudio Case generator

Recent Posts

  • Adwords script for better A/B testing of Ads February 15, 2017
  • OpenRefine: a fast and easy way to obtain keyword data from autocomplete February 15, 2017
  • MCC script for controlling account performance and trends February 15, 2017

Recent Comments

  • how to make a blog on OpenRefine: a fast and easy way to obtain keyword data from autocomplete
  • movie download sites on New columns in AWQL reports based on Adwords API v201601
  • latest movie download on Script for PLA campaign history audit

© Copyright 2016 Lynt services s.r.o.