What you'll need to follow along

  • Basic understanding of Google Sheets
  • Google Maps API Key ($200 credit available)
  • SmartyStreets API Key (first 250 lookups free)
  • Twilio API Key ($15 credit available, $0.07 per lookup)

What is Google Maps Spam?

What kind of listings fall into the category of spam?  

Businesses shown on Google Maps should allow customers or clients to physically visit that location, so as a general rule, anything that doesn't meet that requirement should be removed from the map.  

Service area businesses are an exception to this rule, but for this post, we're going to look at categories that should meet this condition.

Let's walk through a few different categories of spam, and then we'll dive into how we can quickly pinpoint each of them, using just Google Sheets and a sprinkling of Apps Script.

Virtual offices

Google is likely to view a listing that uses a virtual office (i.e. Regus) as spam.  Taking a quote from the official guidelines for Googe My Business, there doesn't seem to be much room for interpretation:

If your business rents a temporary, "virtual" office at a different address from your primary business, don’t create a page for that location.

You might think that having a virtual office as a primary location is acceptable, but the bottom line is that these listings are frequently suspended, and getting them reinstated is difficult.

Ben Fisher, platinum product expert and owner of Steady Demand, explains this on the Google support forums.  

Fake addresses

Believe it or not, Google does not require a valid address to create a listing.  Google will guide the user toward creating a valid address, but if a valid address isn't found, the user always has the option of simply dropping a map pin.

Why would a business owner use a fake address?  This often comes up when an owner is trying to conceal the fact that they're using a virtual office.  This becomes pretty obvious when the address comes back invalid from a validation tool, but a virtual office is either in the same building or just around the corner.

Just as an example, take this legal practitioner's given address:

Now, if we run this address through the SmartyStreets validator, it comes back marked as inactive, meaning USPS no longer delivers to this address, if it ever did.

Removing the "12th floor" suffix, however, returns a match for a Davinci virtual office location.

Generic addresses

Much like with fake addresses, dubious listings will sometimes have an address that is missing a suite or street number.  Visiting the spot with Streetview will most likely leave you looking at a strip-mall, but if you take a moment to explore, you might come across a UPS or FedEx store very nearby.

Here's an example of a personal injury attorney that seems to do business from the middle of a road.

Just a stone's throw away, sure enough, is a FedEx print shop.  As an extra step, we can run the address through SmartyStreets and see what comes up.

Finding Spam with Google Sheets

You can certainly track down listings that use these spam tactics one by one, but it's a pretty time consuming process.  After all, there are a lot of listings that are completely legitimate, so researcing them one by one is going to take some time.

What if we could narrow down what needs to be researched?  An automated process is never going to be 100% accurate, but it can quickly lead us to a small set of suspect listings, so that our time is better spent.

Introducing the Local Spam Finder

Instead of using tools like SmartyStreets manually, we can combine them in one place and tie into their API via Apps Script.  Enter a latitude, longitude, radius, and a search term – then the script uses the Google Maps API to find matching listings, which are then fed into the SmartyStreets and Twilio APIs.

SmartyStreets can help us find invalid addresses, private mailboxes (i.e. FedEx and UPS stores, or virtual offices), and P.O. boxes.  The Twilio API is used to perform a reverse phone lookup, so that we know who might be behind a listing.

With this information we can narrow down what needs to be researched.  Business locations that are classified as residential addresses are a good place to start, as are addresses that come back "Unknown" to SmartyStreets.

Anything marked as using a CMRA (Commercial Mail Receiving Agent) should be investigated.  These are most likely going to be either a UPS store, or a virtual office location.

Getting started with this is fairly easy – you'll need a spreadsheet with an "API Keys" and "Locations" sheet.  The "API Keys" sheet must contain the API keys for Google Maps, SmartyStreets, and Twilio, in the same order as shown above.

You can copy my original spreadsheet if you'd like to get started quickly.  Just be aware that Google will display an "unverified app" warning if you copy the original, because the Apps Script code hasn't gone through their verification process.

The actual Apps Script code is pretty straightforward, and mostly involves calling the above APIs.  

I'll share the code below.  If you'd like to include this in a Google Sheet, simply click the Tools menu and select Script Editor.

function reversePhoneLookup(acctSID, authToken, phoneNumber) {
  var lookupApi = `https://lookups.twilio.com/v1/PhoneNumbers/${phoneNumber}/`;
  var authHeader = "Basic " + Utilities.base64Encode(acctSID + ':' + authToken);
  
  lookupApi += "?AddOns=ekata_reverse_phone";
  
  var options = {
    headers: {Authorization: authHeader},
  };
  
  options.payload = JSON.stringify();
    
  var response = UrlFetchApp.fetch(lookupApi, options);
  var responseData = JSON.parse(response.getContentText());
  return responseData.add_ons.results.ekata_reverse_phone.result;
}

function placeSearch(apiKey, nextToken, searchQuery, searchRadius, lat, lng) {
  var searchApi = "https://maps.googleapis.com/maps/api/place/nearbysearch/json";
  var ui = SpreadsheetApp.getUi();

  searchApi += `?key=${encodeURIComponent(apiKey)}&`;
  searchApi += `keyword=${encodeURIComponent(searchQuery)}&`;
  searchApi += `location=${encodeURIComponent(lat + "," + lng)}&`;
  searchApi += `radius=${searchRadius}`;
  
  if (nextToken) {
    searchApi += `&pagetoken=${encodeURIComponent(nextToken)}`;
  }
  
  var response = UrlFetchApp.fetch(searchApi, {'muteHttpExceptions': true});
  var responseData = JSON.parse(response.getContentText());

  if (responseData.status != "OK") {
    ui.alert(`Google Maps API returned error code: ${responseData.status}`);
  } else {
    return responseData;
  }
}

function placeDetail(apiKey, placeId) {
  var detailApi = "https://maps.googleapis.com/maps/api/place/details/json";
  
  detailApi += `?key=${encodeURIComponent(apiKey)}&`;
  detailApi += `place_id=${encodeURIComponent(placeId)}&`;
  detailApi += `fields=formatted_address,formatted_phone_number,international_phone_number,url`;
  
  var response = UrlFetchApp.fetch(detailApi, {'muteHttpExceptions': true});
  return JSON.parse(response.getContentText()).result;
}

function addressInfo(apiKey, authToken, gmbAddress) {
  var smartyApi = "https://us-street.api.smartystreets.com/street-address";
  var ui = SpreadsheetApp.getUi();
  
  smartyApi += `?auth-id=${encodeURIComponent(apiKey)}&`;
  smartyApi += `auth-token=${encodeURIComponent(authToken)}&`;
  smartyApi += `street=${encodeURIComponent(gmbAddress)}`
  
  var response = UrlFetchApp.fetch(smartyApi, {'muteHttpExceptions': true});
  
  if (response.getResponseCode() == 401) {
    ui.alert("SmartyStreets API responded with access denied.  Are credentials correct?");
  } else if (response.getResponseCode() == 402) {
    ui.alert("SmartyStreets API responded with 'Payment Required'.");
  } else if (response.getResponseCode() != 200) {
    ui.alert(`SmartyStreets API responded with code ${response.getResponseCode()}.`); 
  } else {
    return JSON.parse(response.getContentText());
  }
}

function scanForPlaces() {
  var apiSheet = SpreadsheetApp.getActive().getSheetByName("API Keys");
  var locationSheet = SpreadsheetApp.getActive().getSheetByName("Locations");
  
  var data = apiSheet.getDataRange().getValues();
  
  var mapsApiKey = data[0][1];
  var smartyApiKey = data[1][1];
  var smartyAuthToken = data[2][1];
  var twilioAccountSID = data[3][1];
  var twilioAuthToken = data[4][1];
  var lat = data[5][1];
  var lng = data[6][1];
  var searchRadius = data[7][1];
  var searchQuery = data[8][1];
  var nextToken = data[9][1];
  
  var responseData = placeSearch(mapsApiKey, nextToken, searchQuery, searchRadius, lat, lng);
  
  // Stop here if we didn't get a valid response back from the Maps API.
  if (!responseData) {
    return; 
  }
  
  apiSheet.getRange("B10").setValue(responseData.next_page_token);

  for (var place of responseData.results) {
    var detail = placeDetail(mapsApiKey, place.place_id);
    if (!detail.formatted_address) {
      continue; 
    }

    var row = [detail.url, place.name, detail.formatted_address, detail.formatted_phone_number];
    var addrInfo = addressInfo(smartyApiKey, smartyAuthToken, detail.formatted_address);
    var phoneInfo = reversePhoneLookup(twilioAccountSID, twilioAuthToken, detail.international_phone_number);

    var rdi = "Unknown";
    var cmra = "Unknown";
    
    if (addrInfo.length) {
      rdi = addrInfo[0].metadata.rdi;
      cmra = addrInfo[0].analysis.dpv_cmra;
    }
    
    var belongsTo = phoneInfo.belongs_to;
    var isCommercial = phoneInfo.is_commercial ? "Y" : "N";
    var lineType = phoneInfo.line_type;
    var isPrepaid = phoneInfo.is_prepaid ? "Y" : "N";

    var belongsToName = "";
    if (belongsTo) {
      belongsToName = belongsTo.name;
    }
    
    row.push(belongsToName);
    row.push(isCommercial);
    row.push(isPrepaid);
    row.push(lineType);
    row.push(rdi);
    row.push(cmra);
    
    locationSheet.appendRow(row);
  }
}

If you'd like to start spam hunting today, but don't want to set all of this up yourself, you can try it out inside Persuaded.io.

Start spam hunting now.