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:
|
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!
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!
Hi, what do you feel around free new movies? Pretty impressive idea, isn’t it?