How to Scrape Google Results into Airtable

Alex Barron -

Over the years, we've received numerous questions about how to integrate SerpApi's search results into Airtable. In this tutorial, I'll show you how to use Make.com to fetch search results data from SerpApi and send that data to Airtable.

Specifically, we'll imagine we're a startup selling software to car dealerships in the US. We want to find car dealership leads in various cities to target as potential customers. We'll use SerpApi's Google Maps API to find those leads.

This solution will work for any of our APIs; not just Google Maps. If your use case requires Google Search, Google Jobs, Google Shopping, or any of the other search engines we support, this guide will help you get that data into Airtable with only a few modifications.

Let's dive in.

Scraping Google Maps

We'll start by locating the source data we need from the Google Maps API.

Our search query will be structured as car dealerships in {city}. We'll start with a single city to ensure our pipeline works. Later in the tutorial, we'll look at how to deal with multiple cities.

Using the SerpApi Playground, we can test out a query for car dealerships in achorage, ak. The JSON response body includes a local_results array containing a list of car dealerships in the Anchorage area.

We want the following data for each car dealership:

  • Name from the title
  • Number of reviews from reviews
  • Business type from type
  • Address from address
  • Phone number from phone

For reference, here's the full sample data from one car dealership result:

 {

    "position": 1,
    "title": "Anchorage Chrysler Dodge Jeep Ram",
    "place_id": "ChIJPQl0u0GWyFYR4ZQhSzSsi3w",
    "data_id": "0x56c89641bb74093d:0x7c8bac344b2194e1",
    "data_cid": "8974456023064614113",
    "reviews_link": "https://serpapi.com/search.json?data_id=0x56c89641bb74093d%3A0x7c8bac344b2194e1&engine=google_maps_reviews&hl=en",
    "photos_link": "https://serpapi.com/search.json?data_id=0x56c89641bb74093d%3A0x7c8bac344b2194e1&engine=google_maps_photos&hl=en",
    "gps_coordinates":
    {
        "latitude": 61.217796,
        "longitude": -149.832575
    }
    ,
    "place_id_search": "https://serpapi.com/search.json?engine=google_maps&google_domain=google.com&hl=en&place_id=ChIJPQl0u0GWyFYR4ZQhSzSsi3w",
    "provider_id": "/g/1tdr7y0b",
    "rating": 4.4,
    "reviews": 1667,
    "type": "Used car dealer",
    "types":
    [
        "Used car dealer",
        "Auto parts store",
        "Chrysler dealer",
        "Dodge dealer",
        "Jeep dealer",
        "Ram dealer",
        "Tire shop",
        "Truck dealer",
        "Car dealer",
        "Used truck dealer"
    ]
    ,
    "type_id": "used_car_dealer",
    "type_ids":
    [
        "used_car_dealer",
        "auto_parts_store",
        "chrysler_dealer",
        "dodge_dealer",
        "jeep_dealer",
        "ram_dealer",
        "tire_shop",
        "truck_dealer",
        "car_dealer",
        "used_truck_dealer"
    ]
    ,
    "address": "2601 E 5th Ave, Anchorage, AK 99501",
    "open_state": "Closed ⋅ Opens 8:30 AM",
    "hours": "Closed ⋅ Opens 8:30 AM",
    "operating_hours":
    {
        "thursday": "8:30 AM–9 PM",
        "friday": "8:30 AM–9 PM",
        "saturday": "9 AM–9 PM",
        "sunday": "10 AM–8 PM",
        "monday": "8:30 AM–9 PM",
        "tuesday": "8:30 AM–9 PM",
        "wednesday": "8:30 AM–9 PM"
    }
    ,
    "phone": "(888) 988-6940",
    "website": "https://anchoragechryslercenter.com/?utm_source=google&utm_medium=organic&utm_campaign=gmb-main",
    "service_options":
    {
        "in_store_shopping": true,
        "in_store_pickup": true,
        "delivery": true
    }
    ,
    "thumbnail": "https://lh5.googleusercontent.com/p/AF1QipOqk02D6bBUQsz8-91g5iLRwqbNWA_Reaylnjqn=w138-h92-k-no"

}

Airtable Setup

Create or log into your Airtable account. From the Home dashboard, create a new blank Base from scratch.

You should be taken to the Data tab of your new Base. You can optionally rename the Base and Table. I've set the Base name to SerpApi Tutorial and the Table to Dealerships.

The Table will have a few default fields. Feel free to delete everything except Name. I've left them as is since they may be useful in the future.

Next, we'll create new fields in our Table to house our SerpApi data. Create the following fields with the appropriate field type value:

  • Address as Single line text
  • Phone as Phone number
  • Website as URL
  • Review Count as Number (set to no decimals)
  • Type as Single line text

The Table should look like this when you're done:

Personal Access Token

Since we'll be integrating Airtable into a Make Scenario, we also need to generate a Personal Access Token. This will allow Make to execute API calls against your Airtable instance.

From the upper right menu with your first name initial, select "Builder hub".

Then hit the "Create new token" button.

Give the token a descriptive name and add the following scopes:

  1. data.records:read See the data in records
  2. data.records:write Create, edit, and delete records
  3. schema.bases:read See the structure of a base, like table names or field types

Set Access to: All current and future bases in all current and future workspaces

Your token settings should look like this:

After you create your token, a pop-up will show your token. This is shown only once. Copy it and store it safely. If you lose it, however, you can always create another one.

Add SerpApi to Make

Start with a new Scenario and search for the SerpApi App. Select the Search Google Maps Module.

Add a SerpApi Connection or use an existing one. If you're unsure how to add a Connection, please see the guide here.

To keep things simple at the start, we'll search for car dealerships in a single city. Let's start with Anchorage, AK.

Let's run this to ensure we get the results we want. Hit the "Run once" button on the bottom left.

We should get a single Output Bundle with a Local Results array containing a list of car dealerships.

If you get something similar, you can move on to iterating over the results.

Iterate Over Results

As you saw, the list of car dealerships comes back as an array. In order to select fields from each result, we first need to add a Module to iterate over the array.

Within the Flow Control App, look for the Iterator Module.

Click within the "Array" form box to show what fields are available for mapping. Click the blue Local Results[] element from the SerpApi Module.

The end result should look like this:

Add Airtable to Make

We'll be adding two Airtable Modules to our Scenario. The first will be to search for existing records in our Airtable database. The second will be to upsert the car dealership into Airtable. Upsert means insert the record if it's new or update it if it already exists.

The first step is optional. We'll add it so we can prevent duplicate records from being created. Additionally, it will allow us to update records that may have changed since we first scraped that record.

If you don't care about duplicates or updating existing records, feel free to skip this step. It will save you Make Operations.

Search Airtable Records (Optional)

Add the Search Records Module from the Airtable App.

Create a new Connection or use an existing one.

Select "Airtable Token or Key" and ensure "Token Type" is set to "Personal Access Token." Paste your token from the Airtable Setup Personal Access Token step and hit "Save".

Once connected, you should see the following:

Select the appropriate Base and Table that you created previously.

Next, we'll add a "Formula" to search for records by name. You can copy this formula: FIND("{{2.title}}", {Name})

Note that if your Iterator Module has a different ID than 2, you'll need to adjust your formula to use the correct ID for your Scenario.

The final result should look like the screenshot below.

You can test this setup by hitting the "Run once" button again.

Note that it will use a Make Operation for each car dealership result. To save Operations, I recommend stopping the Scenario after it searches a couple records so you don't use so many.

Here is what the Output Bundles should look like.

Upsert Airtable Records

Now we can add the Upsert a Record Module.

Use the same Connection from the previous step.

Using the Airtable Search Records Output, populate "Record ID" with ID. This will ensure the correct Airtable record is updated if it already exists. If it doesn't exist, there will be no ID present and will be created as a new record.

If you skipped the Search Records step, you can leave "Record ID" blank.

After that, map the fields from the Iterator Output to send those fields to Airtable. Follow the mapping in the screenshot below.

The final form should look like this:

Finally, you can run the Scenario and you should see 20 car dealerships from Anchorage, AK show up in Airtable!

Get More Results

The Scenario is currently configured to only get 20 car dealerships from Anchorage, AK. This is certainly not enough leads for our car dealership software startup.

Google Maps Pagination

The first thing we can do is paginate through the Google Maps results to get more dealerships in the same market. You can do this by incrementing the Pagination Limit value in the SerpApi Module.

A few things to note about pagination:

  1. The more pages you request, the lower the quality of results may be. A city only has so many car dealerships after all. Google Maps may even start to show duplicates after a certain number of pages.
  2. Each page of results from SerpApi consumes a search credit. Be mindful of your plan's allowance.
  3. Your Make account is limited 1,000 Operations per month on the free plan. Paid plans have much higher limits. In either case, you'll need to be mindful of your plan's limit. Each page offers 20 results. Since we search for the record in Airtable first and then upsert it, 20 results means a total of 40 Operations for each page of results.

Handle Multiple Cities

The second thing we can do is include extra cities.

At the moment, we've manually added Anchorage, AK to our query. Trouble is if we want to fetch car dealerships for all the major cities in the US, we would have to manually update the query and run the Scenario for each city. A better strategy would be to feed a list of cities into the Scenario and iterate through each one.

There are several approaches to get the city list into Make. A good option would be to list the cities in a Google Sheet and then use the Google Sheet as the source data in Make. I've documented how to do that in this blog post about connecting Google Sheets and SerpApi.

Since I've covered that already, let's look at a different approach; using the Parse CSV Module.

This Module will need to go at the beginning of our Scenario so we'll hit the "+" button at the bottom of the Scenario Editor to add an unlinked Module.

Search for csv and select the CSV App's Parse CSV Module. Make sure it's the CSV App, not EasyCSV.

The Module will appear unlinked. Drag it to the beginning of the Scenario and connect it to the SerpApi Module so it's the first Module in the Scenario.

This Module works by parsing raw CSV data. You don't actually upload a CSV file itself. You'll paste your CSV data in the CSV form box.

Since we're using American city names that generally are displayed with commas, you can either change the "Delimiter character" to something other than a comma or wrap quotes around the city names. I've opted for quotes.

Here's the city list you can copy and paste:

city
"San Jose, CA"
"Tampa, FL"
"Boise, ID"
"Austin, TX"
"Charlotte, NC"

Next, we need Make to recognize the output data of the CSV before we can reference it in other Modules. Click on the Module to get the pop-up menu and click "Run this module only".

You should get Output Bundles like the following:

Then edit the SerpApi Module to replace anchorage, ak with the city field from the CSV Module.

It should look like this:

The final Scenario will look like this:

With that configured, we can run our Scenario again. The Scenario will loop through each city to find car dealerships through the Google Maps API and then upsert those records into Airtable.

Warning: Each city requires about 40 Make Operations to process. Our list of 5 cities therefore requires a little over 200 Make Operations. Please be mindful of your Make plan's limits before testing with multiple cities. You can still validate the setup works by setting only one city in the CSV Module.

If we check Airtable, we see car dealerships from our new list of cities have been added.

Want to Import This?

If you'd like to skip all this manual configuration, you can download the blueprint of this Scenario and then import it into an empty Scenario in your Make account. All you'll need to do is configure your own SerpApi and Airtable connections. Then update the Airtable Modules to reference the objects in your Airtable account.

serpapi_airtable_tutorial
serpapi_airtable_tutorial.json
37 KB
download-circle