How to Pull Search Rank into Airtable with DataForSEO SERP API

Looking to pull search ranking data into Airtable? DataForSEO has got you covered.

We offer customizable APIs and extensive databases for SEO and marketing, empowering businesses and individuals with valuable SEO and marketing insights. Our mission is to help you deliver quality decision-making tools faster and manage data more efficiently.

That is why we’ve created this guide to walk you through the process of using our SERP API, an industry-leading search engine data collection tool, to pull rankings into Airtable, a popular spreadsheet-database cloud collaboration service. We hope this easy-to-follow instruction helps you to quickly integrate our comprehensive SEO data into your workflows.

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:

• Keyword (Type: Single Line Text)
• Location (Type: Single Line Text)
• Language (Type: Single Line Text)
• Target (Type: Single Line Text)
• Position (Type: Number) – This column will store the result from the API.

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

Now, you can follow one of the two approaches:

➤ For selective updates to ranking data within specific rows.
➤ For comprehensive updates to ensure all rows have the latest ranking data.

Getting search rankings with SERP API for all rows in Airtable via script extension

This approach is perfect for maintaining up-to-date ranking information across your entire dataset. It involves setting up a script that retrieves ranking data for all rows in bulk.

Following the three simple steps below, you will obtain position (rank group) from search engine results via SERP API for all target domains 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:

• DATAFORSEO_LOGIN: Your DataForSEO API login.
• DATAFORSEO_PASSWORD: Your DataForSEO API password.
• TABLE_NAME: The name of the table you created at step 2 of the Setup stage.
• OUTPUT_COLUMN_NAME: The name of the column where the result will be recorded (Position in our example).
• LANGUAGE_COLUMN_NAME: The name of the column with the language.
• LOCATION_COLUMN_NAME: The name of the column with the location.
• KEYWORD_COLUMN_NAME: The name of the column with the keyword.
• TARGET_COLUMN_NAME: The name of the column with the target.

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 outputColumnName = 'OUTPUT_COLUMN_NAME';
let languageColumnName = 'LANGUAGE_COLUMN_NAME';
let locationColumnName = 'LOCATION_COLUMN_NAME';
let keywordColumnName = 'KEYWORD_COLUMN_NAME';
let targetColumnName = 'TARGET_COLUMN_NAME';

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

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

for (let record of records) {
    let position = null;

    if (record.getCellValue(languageColumnName) && 
        record.getCellValue(locationColumnName) && 
        record.getCellValue(keywordColumnName)) {

        let requestData = [
            {
                "language_name": record.getCellValue(languageColumnName),
                "location_name": record.getCellValue(locationColumnName),
                "keyword": record.getCellValue(keywordColumnName),
                "target": record.getCellValue(targetColumnName)
            }
        ];

        let response = await fetch('https://api.dataforseo.com/v3/serp/google/organic/live/advanced', {
            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) && 
            data.tasks[0].result[0].items !== undefined && Array.isArray(data.tasks[0].result[0].items)) {
                position = data.tasks[0].result[0].items[0].rank_group;
        }
    }

    await table.updateRecordAsync(record, {
        [outputColumnName]: position,
    });
}

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 ranking data for your table.

Getting search rankings with SERP API for separate rows in Airtable via automation

This approach is recommended for cases when you only need to update or check search rankings for specific entries. It involves setting up a system where the API is called for each specific row in a table based on a condition (a checkbox being checked in our case). Providing more granularity and flexibility, this approach will come in handy for on-demand updates or focusing on high-priority keywords.

Following the seven simple steps below, you will obtain position (rank group) from search engine results via SERP API for selected rows 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 position?”.

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 position?), then set it to checked.

In the “Test step” section, click “Choose record” and select a suitable record from the table. Make sure all table fields mentioned in step 2 of the Setup stage are filled, except for Position.

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:

• Name: id, Value: Airtable record ID
• Name: language, Value: Language field
• Name: location, Value: Location field
• Name: keyword, Value: Keyword field
• Name: target, Value: Target field

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:

• DATAFORSEO_LOGIN: Your DataForSEO API login.
• DATAFORSEO_PASSWORD: Your DataForSEO API password.
• TABLE_NAME: The name of the table you created at step 2 of the Setup stage (SERP API in our example).
• OUTPUT_COLUMN_NAME: The name of the column where the result will be recorded (Position in our example).

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 outputColumnName = 'OUTPUT_COLUMN_NAME';

let inputConfig = input.config();

let position = null;

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

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

    let response = await fetch('https://api.dataforseo.com/v3/serp/google/organic/live/advanced', {
        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)
            && data.tasks[0].result[0].items !== undefined && Array.isArray(data.tasks[0].result[0].items)) {
        position = data.tasks[0].result[0].items[0].rank_group;
    }
}

let table = base.getTable(tableName);
await table.updateRecordAsync(inputConfig.id, {
    [outputColumnName]: position,
});

function base64_encode(s) {
    var base64chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
    var r = ""; 
    var p = ""; 
    var c = s.length % 3;

    if (c > 0) { 
        for (; c < 3; c++) { 
            p += '='; 
            s += "\0"; 
        } 
    }

    for (c = 0; c < s.length; c += 3) {
        if (c > 0 && (c / 3 * 4) % 76 == 0) { 
            r += "\r\n"; 
        }

        var n = (s.charCodeAt(c) << 16) + (s.charCodeAt(c+1) << 8) + s.charCodeAt(c+2);
        n = [(n >>> 18) & 63, (n >>> 12) & 63, (n >>> 6) & 63, n & 63];
        r += base64chars[n[0]] + base64chars[n[1]] + base64chars[n[2]] + base64chars[n[3]];
    }

    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 easily retrieve and store search results positions with DataForSEO SERP 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.