How To Get Your Business’s Google Reviews into BigQuery & Looker Studio

Customer reviews are invaluable resources that can provide keen insights into customer satisfaction, business performance, and areas for improvement. For businesses using Google My Business, the wealth of Google review data is often untapped when it comes to analytics. One powerful way to analyze your Google review data (or the Google review data of your clients) is to load that data into BigQuery and then analyze or visualize it in the tool of your choice. You can even get ChatGPT to help you summarize insights in the data!

Photo by henry perks on Unsplash

In this blog post, we’ll explore how to collect Google Business Reviews via API and push them into Google BigQuery for further analysis.

Prerequisites

• Google My Business Account
• Google Cloud Account with BigQuery enabled
• Basic understanding of APIs
• Basic SQL knowledge

Steps to Collect Google Business Reviews

Step 1: Obtain Google My Business API Access

1. Go to the Google Cloud Console.
2. Create a new project or select an existing one.
3. You’ll need to request access to the Google My Business API before you can begin using it to access data.
4. After access has been granted, navigate to “APIs & Services” -> “Library” and search for the Google My Business API.
5. Click “Enable” and create credentials (API Key, Client ID, and Client Secret).

Step 2: Collecting Reviews Using the API

To collect reviews, you will need to make HTTP requests to the Google My Business API.

Here’s the Python script we used to get reviews out of the API.

First, plenty of packages to install and import:

import sys 
import json
from pprint import pprint
from googleapiclient import sample_tools
from googleapiclient.http import build_http
from googleapiclient.errors import HttpError
import oauth2client
import pandas as pd
import IPython
import os

Then, the heart of the code below will grab all business accounts associated with your google profile, grab all locations associated with those business accounts, and finally, grab all reviews associated with each of those locations. There are some commented out print statements that can help you with debugging if you run into any issues.

#Business file exported from the API Console project page.
## Feel free to email nick@southshore.llc for this document.

discovery_doc = "myBusiness_discovery.json"
def account():
    # Use the discovery doc to build a service that we can use to make
    # MyBusiness API calls, and authenticate the user so we can access their
    # account
    try:
        path  = os.path.abspath('')+'\\'
        MyBusinessAccount, flags = sample_tools.init(['Google Reviews.ipynb'], "mybusinessaccountmanagement", "v1", __doc__, path, scope="https://www.googleapis.com/auth/business.manage")
        # mybusinessaccountmanagement
        output = MyBusinessAccount.accounts().list().execute()
        # print("List of Accounts:\n")
        # print(json.dumps(output, indent=2) + "\n")
        account_list = output['accounts']
        print(output)
        return account_list
    except HttpError as e:
        print(f"An HTTP error {e.resp.status} occurred:")
        print(e.content)

account()

def locations(account):
    # Use the discovery doc to build a service that we can use to make
    # MyBusiness API calls, and authenticate the user so we can access their
    # account
    #currentNotebook = IPython.notebookname
    firstAccount = account
    path  = os.path.abspath('')+'\\'
    MyBusinessAccount, flags = sample_tools.init(['Google Reviews.ipynb'], "mybusinessbusinessinformation", "v1", __doc__, path, scope="https://www.googleapis.com/auth/business.manage")
    
    request = MyBusinessAccount.accounts().locations().list(parent=firstAccount,readMask="name,title")
    locations = []

    # pagination
    # https://developers.google.com/api-client-library/python/guide/pagination
    while request is not None:
      try:
        response = request.execute()
        print(response)
        # Do something with the activities
        try:
            locations += response["locations"]
        except KeyError:
            print("Key 'locations' not found in response.")

        request = MyBusinessAccount.accounts().locations().list_next(request,response)
        # request = None # first page only
      except HttpError as e:
        print(f"An HTTP error {e.resp.status} occurred:")
        print(e.content)
    
    return locations

def reviews():
    path  = os.path.abspath('')+'\\'
    MyBusinessAccount, flags = sample_tools.init(['Google Reviews.ipynb'], "mybusiness", "v4", __doc__, path, scope="https://www.googleapis.com/auth/business.manage", discovery_filename=discovery_doc)
    # mybusinessaccountmanagement
    firstAccount = '' # Hardcode or adapt code to loop through your accounts.
    businesses = locations(firstAccount) 

    reviews=[]

    for location in businesses:        
        location_id = location['name']
        request = MyBusinessAccount.accounts().locations().reviews().list(parent=firstAccount+'/'+location_id)

        # pagination
        # https://developers.google.com/api-client-library/python/guide/pagination
        while request is not None:
            response = request.execute()
            print(response)
            try:
                review = {}
                review['data'] = response['reviews']
                review['locationName'] = location['title']
                reviews.append(review )
            except:
                print(response)
            request = MyBusinessAccount.accounts().locations().reviews().list_next(request,response)

    return reviews

reviews_data = reviews()

With those code, you’ll store all of your reviews data in the reviews_data variable which can be manipulated before being pushed into BigQuery for further use.

Step 3: Push Reviews Data to Google BigQuery

Before pushing data to BigQuery, you must:

1. Create a BigQuery Dataset
2. Create a Table within the dataset to hold the review data.
3. You’ll also need to create a service account before you can successfully push data to BigQuery.

Here is sample Python code to insert data into BigQuery using the google-cloud-bigquery library:

reviews_df = pd.DataFrame()

for i in reviews_data:
    df = pd.json_normalize(i['data'])
    df['locationName'] = i['locationName']
    reviews_df = reviews_df.append(df,ignore_index=True)

# Replace periods with underscores in column names
reviews_df.columns = [col.replace('.', '_') for col in reviews_df.columns]

print("DataFrame with Updated Column Names:")
reviews_df.head()

from google.cloud import bigquery

client = bigquery.Client.from_service_account_json("service_account.json")

# Push data to BigQuery
reviews_df.to_gbq(f'{client.project}.your_dataset_name.your_table_name', project_id=client.project, if_exists='replace')

Data Analysis in BigQuery / Looker Studio

Once your review data is in BigQuery, you can perform SQL analysis on your data, or even prep your data to be used in a visualization tool like Looker Studio / Looker, Tableau, or others. You’ll be able to analyze:

• How many total reviews each business got in the last year
• How your trailing 90-day avg. rating compares to the prior 90 days
• Feed your data into ChatGPT to generalize the most common themes among reviewers who left 3 or fewer stars

Sample Google Reviews data visualized in Looker Studio

Make It Happen

Collecting Google Business Reviews and performing analysis using BigQuery is a powerful way to extract meaningful insights from customer feedback. By automating the process, you can continuously update your data and gain real-time insights into how your business is performing in the eyes of the customer.

Now go turn those 3 stars into 4 stars and those 4 stars into 5 stars!