Keyword Research Tool In Airtable With DataForSEO API: Creation Guide
Creating your own keyword magic tool might sound daunting, but with Airtable and DataForSEO APIs, it’s easier than you think. Whether you’re an SEO professional looking to reinforce your workflow or a business owner wanting to dig deeper into keyword analytics, this guide will walk you through the process of setting up a powerful, automated tool tailored to your needs. By leveraging the flexibility of Airtable and the power of DataForSEO, you’ll be able to build a robust keyword research solution without writing a single line of code.
Step 1: Set Up the Database
1 Open the pre-configured database by following this link: Airtable Database.
2 Once the database opens, click on the “Copy base” button.
3 Log in to your Airtable account if you’re not already logged in. In the pop-up window, select the desired workspace and click “Add base” to copy the database to your account.
Your database is now copied and ready for customization.
Step 2: Set Up Automation
1 Navigate to the “Automations” menu where the automation interface will appear.
2 Click on “Add trigger” in the center of the screen. From the dropdown menu, select “When a record is created.”
3 In the right-hand panel, choose the “Search requests” table in the “Table” field. Click “Use suggested record” to proceed to the next step.
4 Click on “Add advanced logic or action” in the center of the interface. From the dropdown menu, select “Run script.”
5 On the left panel, click on “Add input variable.”
Enter “id” in the “Name” field and select “Airtable Record Id” in the “Value” field.
6 Copy and paste the following code into the “Code” field, replacing DFS_API_LOGIN and DFS_API_PASSWORD with your DataForSEO API login and password.
See also: Where can I find my API login and password?
const login = 'DFS_API_LOGIN';
const password = 'DFS_API_PASSWORD';
const inputConfig = input.config();
const table = base.getTable('Keyword Ideas');
const inputTable = base.getTable('Search Requests');
const inputRaw = await inputTable.selectRecordAsync(inputConfig.id);
if (
inputRaw
&& inputRaw.getCellValue('Location')
&& inputRaw.getCellValue('Language')
&& inputRaw.getCellValue('Keyword')
) {
let sortBy = [];
if (inputRaw.getCellValue('Sort By')) {
for (let sortByItem of inputRaw.getCellValue('Sort By')) {
switch (sortByItem.name) {
case 'Relevance':
sortBy.push('relevance,desc');
break;
case 'Search Volume-Desc':
sortBy.push('keyword_info.search_volume,desc');
break;
case 'Search Volume-Asc':
sortBy.push('keyword_info.search_volume,asc');
break;
case 'KD-Desc':
sortBy.push('keyword_properties.keyword_difficulty,desc');
break;
case 'KD-Asc':
sortBy.push('keyword_properties.keyword_difficulty,asc');
break;
case 'CPC (USD)-Desc':
sortBy.push('keyword_info.cpc,desc');
break;
case 'CPC (USD)-Asc':
sortBy.push('keyword_info.cpc,asc');
break;
default:
break;
}
}
}
let filters = [];
if (inputRaw.getCellValue('Search Volume')) {
let svFilter = [];
for (let svItem of inputRaw.getCellValue('Search Volume')) {
switch (svItem.name) {
case '100,001+':
svFilter.push(['keyword_info.search_volume', '>', 100000]);
break;
case '10,001-100,000':
svFilter.push([['keyword_info.search_volume', '>', 10000], 'and', ['keyword_info.search_volume', '<=', 100000]]);
break;
case '1,001-10,000':
svFilter.push([['keyword_info.search_volume', '>', 1000], 'and', ['keyword_info.search_volume', '<=', 10000]]);
break;
case '101-1,000':
svFilter.push([['keyword_info.search_volume', '>', 100], 'and', ['keyword_info.search_volume', '<=', 1000]]);
break;
case '11-100':
svFilter.push([['keyword_info.search_volume', '>', 10], 'and', ['keyword_info.search_volume', '<=', 100]]);
break;
case '1-10':
svFilter.push(['keyword_info.search_volume', '<=', 10]);
break;
default:
break;
}
svFilter.push('or');
}
if (svFilter.length) {
svFilter.splice(svFilter.length-1 , 1);
if (svFilter.length === 1) {
svFilter = svFilter[0];
}
filters.push(svFilter);
}
}
if (inputRaw.getCellValue('KD')) {
let kdFilter = [];
for (let kdItem of inputRaw.getCellValue('KD')) {
switch (kdItem.name) {
case 'Very hard 85-100%':
kdFilter.push(['keyword_properties.keyword_difficulty', '>', 84]);
break;
case 'Hard 70-84%':
kdFilter.push([['keyword_properties.keyword_difficulty', '>', 69], 'and', ['keyword_properties.keyword_difficulty', '<=', 84]]);
break;
case 'Difficult 50-69%':
kdFilter.push([['keyword_properties.keyword_difficulty', '>', 49], 'and', ['keyword_properties.keyword_difficulty', '<=', 69]]);
break;
case 'Possible 30-49%':
kdFilter.push([['keyword_properties.keyword_difficulty', '>', 29], 'and', ['keyword_properties.keyword_difficulty', '<=', 49]]);
break;
case 'Easy 15-29%':
kdFilter.push([['keyword_properties.keyword_difficulty', '>', 14], 'and', ['keyword_properties.keyword_difficulty', '<=', 29]]);
break;
case 'Very easy 0-14%':
kdFilter.push(['keyword_properties.keyword_difficulty', '<=', 14]);
break;
default:
break;
}
kdFilter.push('or');
}
if (kdFilter.length) {
kdFilter.splice(kdFilter.length-1 , 1);
if (kdFilter.length === 1) {
kdFilter = kdFilter[0];
}
if (filters.length) {
filters.push('and');
}
filters.push(kdFilter);
}
}
if (inputRaw.getCellValue('Search Intent')) {
let intents = inputRaw.getCellValue('Search Intent').map((item) => item.name);
let intentFilters = [];
intentFilters.push(['search_intent_info.main_intent','in', intents]);
intentFilters.push('or');
for (let key in intents) {
intentFilters.push(['search_intent_info.foreign_intent','=', intents[key]])
intentFilters.push('or');
}
intentFilters.splice(intentFilters.length-1 , 1);
if (filters.length) {
filters.push('and');
}
filters.push(intentFilters);
}
if (inputRaw.getCellValue('Custom Filters')) {
let filterItems = inputRaw.getCellValue('Custom Filters').split("\n");
let fieldName;
let operator;
let value;
for (let filterItem of filterItems) {
[fieldName, operator, value] = filterItem.split(',');
if (filters.length) {
filters.push('and');
}
filters.push([fieldName, operator, value]);
}
}
const requestData = [
{
language_name: inputRaw.getCellValue('Language'),
location_name: inputRaw.getCellValue('Location'),
keywords: [inputRaw.getCellValue('Keyword')],
limit: inputRaw.getCellValue('Limit'),
include_serp_info: true,
order_by: sortBy.length ? sortBy : null,
filters: filters.length ? filters : null
}
];
const token = base64_encode(login + ':' + password);
const response = await fetch('https://api.dataforseo.com/v3/dataforseo_labs/google/keyword_ideas/live', {
method: 'POST',
body: JSON.stringify(requestData),
headers: {
'Content-Type': 'application/json',
'Authorization': 'Basic ' + token,
'User-Agent': 'airtable-app-dfs'
},
});
const data = await response.json();
console.log(data);
const items = ['tasks', '0', 'result', '0', 'items'].reduce((obj, key) => obj?.[key], data);
if (items) {
let svr;
let kdr;
let sf;
let sf_item;
let si;
let si_item;
let sf_item_name;
const sf_field = table.getField('SERP Features');
const choices = sf_field.options.choices.map((item) => item.name)
let records = [];
const itterationsCount = Math.ceil(items.length/50);
let dataPart;
let fields;
for (let i=0; i 100000) {
svr = '100,001+'
} else if (item.keyword_info.search_volume > 10000) {
svr = '10,001-100,000';
} else if (item.keyword_info.search_volume > 1000) {
svr = '1,001-10,000';
} else if (item.keyword_info.search_volume > 100) {
svr = '101-1,000';
} else if (item.keyword_info.search_volume > 10) {
svr = '11-100';
} else {
svr = '1-10';
}
if (item.keyword_properties.keyword_difficulty > 84) {
kdr = 'Very hard 85-100%'
} else if (item.keyword_properties.keyword_difficulty > 69) {
kdr = 'Hard 70-84%';
} else if (item.keyword_properties.keyword_difficulty > 49) {
kdr = 'Difficult 50-69%';
} else if (item.keyword_properties.keyword_difficulty > 29) {
kdr = 'Possible 30-49%';
} else if (item.keyword_properties.keyword_difficulty > 14) {
kdr = 'Easy 15-29%';
} else {
kdr = 'Very easy 0-14%';
}
sf = [];
if (item.serp_info && Array.isArray(item.serp_info.serp_item_types)) {
for (sf_item of item.serp_info.serp_item_types) {
sf_item_name = sf_item.replaceAll('_', ' ');
if (choices.includes(sf_item_name)) {
sf.push({name: sf_item_name});
}
}
}
si = [];
if (item.search_intent_info) {
si = [{name: item.search_intent_info.main_intent}];
if (Array.isArray(item.search_intent_info.foreign_intent)) {
for (si_item of item.search_intent_info.foreign_intent) {
si.push({name: si_item});
}
}
}
fields = {
"Keyword": item.keyword,
"Search Intent": si,
"Search Volume": item.keyword_info.search_volume,
"KD": item.keyword_properties.keyword_difficulty,
"CPC": item.keyword_info.cpc,
"SERP Features": sf,
"Search Requests": [{id: inputConfig.id}],
"Search Volume (Range)": {name: svr},
"KD % (Range)": {name: kdr}
};
records.push({'fields': fields});
}
await table.createRecordsAsync(records);
}
}
}
function base64_encode(s)
{
// the result/encoded string, the padding string, and the pad count
var base64chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
var r = "";
var p = "";
var c = s.length % 3;
// add a right zero pad to make this string a multiple of 3 characters
if (c > 0) {
for (; c < 3; c++) {
p += '=';
s += "\0";
}
}
// increment over the length of the string, three characters at a time
for (c = 0; c < s.length; c += 3) {
// we add newlines after every 76 output characters, according to the MIME specs
if (c > 0 && (c / 3 * 4) % 76 == 0) {
r += "\r\n";
}
// these three 8-bit (ASCII) characters become one 24-bit number
var n = (s.charCodeAt(c) << 16) + (s.charCodeAt(c+1) << 8) + s.charCodeAt(c+2);
// this 24-bit number gets separated into four 6-bit numbers
n = [(n >>> 18) & 63, (n >>> 12) & 63, (n >>> 6) & 63, n & 63];
// those four 6-bit numbers are used as indices into the base64 character list
r += base64chars[n[0]] + base64chars[n[1]] + base64chars[n[2]] + base64chars[n[3]];
}
// add the actual padding string, after removing the zero pad
return r.substring(0, r.length - p.length) + p;
}
7 Click “Finish editing” in the script editor window.
Then, toggle the switch to activate the automation.
Step 3: Use Your Keyword Research Tool
Your database is now fully set up. Now, you can navigate to the “Interfaces” menu, and use your new, user-friendly Keyword Research Tool.
Start by clicking “Search Keyword Ideas” to customize your keyword search.
Our recommendation: To ensure you always retrieve the most relevant keywords, we suggest using the “Sort By Relevance” option in your tool.
Additionally, we recommend applying the following filter to consistently capture the most relevant keyword suggestions:
keyword,regex,(best |movies)
If you’re interested in extracting question-based keywords, consider adding this filter:
keyword,regex,^(how |what |when |who)
By following these guidelines and our recommendations, you can easily create a tool that will streamline your keyword research process with the power of automation from Airtable and DataForSEO API at your fingertips.