Site icon DataForSEO

Integrating Search Volume Data into Airtable with Keyword Data API

Irene T.

Looking to harness the power of search volume data to elevate your keyword research? DataForSEO has got you covered. With our cutting-edge APIs and databases, businesses and individuals alike gain powerful tools for streamlining keyword research and optimizing marketing strategies.

This guide is your gateway to integrating search volume data into Airtable, a popular spreadsheet-database cloud collaboration service. We’ll show you how to leverage our Keyword Data API to incorporate comprehensive Google Ads insights into your workflow.

Initial setup

1 Register at DataForSEO.

Create a free DataForSEO account if you haven’t already done so. It does not require your credit card information and gives you $1 in account funds to test our data on any platform for an unlimited period of time.

2 Prepare your table in Airtable.

Log in to your Airtable account, and create a table with the following columns:

In our examples, the table will be called “Search Volume API”.

Now, you can follow one of the two approaches:

➤ Refresh search volume data within specific rows.
➤ Update all rows with the latest search volume data..

Getting search volume for all rows in Airtable with DataForSEO API

This approach is perfect for maintaining up-to-date search volume information across your entire set of keywords. It involves setting up a script that retrieves search volume data for all rows in bulk.

Following the three simple steps below, you will obtain search volume from Google Ads via Keyword Data API for all keywords in a table.

1 Open extensions panel.

Open the Extensions panel, and click “Add an extension”.

In the window that opens, select Scripting.

Then click “Add extension”.

2 Insert and customize the script.

In the window that opens, insert the script provided below. Replace the following placeholder values in the script with your information beforehand:

See also: Where can I find my API credentials (login and password)?

Here’s the script.

let login = 'DATAFORSEO_LOGIN';
let password = 'DATAFORSEO_PASSWORD ';
let tableName = 'TABLE_NAME';
let outputVolumeColumnName = 'OUTPUT_VOLUME_COLUMN_NAME';
let outputCPCColumnName = 'OUTPUT_CPC_COLUMN_NAME';
let outputCompetitionColumnName = 'OUTPUT_COMPETITION_COLUMN_NAME';
let locationCoulumnName = 'LOCATION_COLUMN_NAME';
let keywordCoulumnName = 'KEYWORD_COLUMN_NAME';


let token = btoa(login + ':' + password);


let table = base.getTable(tableName);
let { records } = await table.selectRecordsAsync();


let volume;
let cpc;
let competition;


for (let record of records) {
    
    volume = null;
    cpc = null;
    competition = null;


    if (
        record.getCellValue(locationCoulumnName)
        && record.getCellValue(keywordCoulumnName)
    ) {
        let token = btoa(login + ':' + password);


        let requestData = [
            {
                "location_name": record.getCellValue(locationCoulumnName),
                "keywords": [record.getCellValue(keywordCoulumnName)]
            }
        ];


        let response = await fetch('https://api.dataforseo.com/v3/keywords_data/google_ads/search_volume/live', {
            method: 'POST',
            body: JSON.stringify(requestData),
            headers: {
                'Content-Type': 'application/json',
                'Authorization': 'Basic ' + token
            },
        });


        let data = await response.json();


        if (data.tasks !== undefined && Array.isArray(data.tasks) 
                && data.tasks[0].result !== undefined && Array.isArray(data.tasks[0].result)
            ) {
                volume = data.tasks[0].result[0].search_volume;
                cpc = data.tasks[0].result[0].cpc;
                competition = data.tasks[0].result[0].competition;
            }
    }


    await table.updateRecordAsync(record, {
        [outputVolumeColumnName]: volume,
        [outputCPCColumnName]: cpc,
        [outputCompetitionColumnName]: competition
    });
}

When you’re done, close the code window by clicking on “Finish editing”.

3 Run the script.

That’s it. You can now run this script by clicking the “Run” button from various interfaces of the application, and it will quickly update all search volume data for your table.

Getting search volume for separate rows in Airtable with DataForSEO API

This approach provides more granularity and flexibility. It is recommended for cases when you only need to update or check search volume for specific entries. It involves setting up a system where the API is called for each specific keyword in a table based on a condition (a checkbox being checked in our case).

Following the seven simple steps below, you will obtain search volume from Google Ads via Keyword Data API for selected keywords in a table.

1 Add a checkbox column.

Add a new column to the table you created at the Setup stage with the type “Checkbox”. In our example, this column will be named “Get Search Volume Data?”.

Checking this box will trigger an API call after you complete all of the steps.

2 Create an automation.

Navigate to the “Automations” menu, and click “Create Automation” in the bottom left corner.

Select “When a record matches conditions” as the trigger.

In the menu that opens on the right, navigate to the Table field and select the table where you have added a Checkbox field. In the Conditions field, click “Add condition” and select the checkbox field (Get Search Volume Data?), then set it to checked.

In the “Test step” section, click “Choose record” and select a suitable record from the table. Make sure the Keyword and Location table fields are filled, and the checkbox field (Get Search Volume Data?) is set to checked.

3 Add an action.

After you see that the step ran successfully, in the central part of the screen, click “Add advanced logic or action”. Then select “Run script” from the list.

4 Add input variables.

Add variables for the script we’ll use in the next step by clicking “Add input variable” on the left side of the screen.

For Name fields, enter the variable name, and for the Value fields, select a value from the list after clicking on the + (plus) sign.

Enter the following variables:

5 Insert and customize the script.

In the code field, insert the script provided below. Replace the following placeholder values in the script with your information beforehand:

See also: Where can I find my API credentials (login and password)?

let login = 'DATAFORSEO_LOGIN';
let password = 'DATAFORSEO_PASSWORD';
let tableName = 'TABLE_NAME';
let outputVolumeColumnName = 'OUTPUT_VOLUME_COLUMN_NAME';
let outputCPCColumnName = 'OUTPUT_CPC_COLUMN_NAME';
let outputCompetitionColumnName = 'OUTPUT_COMPETITION_COLUMN_NAME';


let inputConfig = input.config();


let volume = null;
let cpc = null;
let competition = null;


if (inputConfig.location && inputConfig.keyword) {
    let token = base64_encode(login + ':' + password);


    let requestData = [
        {
            "location_name": inputConfig.location,
            "keywords": [inputConfig.keyword],
        }
    ];


    let response = await fetch('https://api.dataforseo.com/v3/keywords_data/google_ads/search_volume/live', {
        method: 'POST',
        body: JSON.stringify(requestData),
        headers: {
            'Content-Type': 'application/json',
            'Authorization': 'Basic ' + token
        },
    });


    let data = await response.json();


    if (data.tasks !== undefined && Array.isArray(data.tasks) 
            && data.tasks[0].result !== undefined && Array.isArray(data.tasks[0].result)
        ) {
            volume = data.tasks[0].result[0].search_volume;
            cpc = data.tasks[0].result[0].cpc;
            competition = data.tasks[0].result[0].competition;
        }
}


let table = base.getTable(tableName);
await table.updateRecordAsync(inputConfig.id, {
    [outputVolumeColumnName]: volume,
    [outputCPCColumnName]: cpc,
    [outputCompetitionColumnName]: competition
});


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;
}

6 Test the script.

Click the “Test” button in the top right corner. If the script run is successful, a message “Test run successfully” will appear, and the table will be updated with the API results (if they exist for the selected data).

When you’re done, click “Finish editing” in the top right corner above the Test button.

7 Activate the automation.

Switch the automation to the ON position.

That’s it. The automation will now trigger each time you check the checkbox for a row.

By following a suitable approach from those we’ve covered in this guide, you can seamlessly retrieve and store Google search volume via Keyword Data API in the Airtable interface. If you have any questions or encounter any issues with our API, feel free to reach out to our 24/7 support team for assistance.

Exit mobile version