How to get the most from your Google Business Profile Data

One of the most common questions I get asked when talking about a data warehouse is, “what do you do with it?”. This is an understandable question as most people don’t have experience with the long-term storage of data as most reports make live fetches of data directly from the source.

A common misconception is that a data warehouse and reporting tools are interchangeable. They are not the same thing. A data warehouse is like a vast, organized bookshelf holding an extensive collection of books, while reports are the carefully crafted summaries of selected books, designed to convey key insights and narratives.

What’s great about a data warehouse is that you can get rich insights from it, but most people give up too quickly because they don’t have a system or a process in place to do this.

A data warehouse is only as good as the data it holds and how you query it. Here's how to make sure you're not losing out.

In my experience, some of the most common mistakes made when approaching reporting are putting together charts in a Looker Studio report that shows some key metrics and calling it a day, or putting so many charts on a page that it looks like data vomit. Reporting is a crucial opportunity to showcase your value in both the work you have done and what you plan to do, so approach your reports in a way that not only tells a story but conveys it in the language and format that the consumers of the report are comfortable looking at.

‍

What the hell is BigQuery and why do we need to store data in it?

Utilizing BigQuery to store your Google Business Profile data might seem like a daunting task, but with our pipelines taking less than a minute to set up, you can take advantage of the powerful data warehouse capabilities that are purpose-built for analytics and reporting. Whilst also storing data for long-term analysis as most systems only allow 18 months of historical data to be fetched via an API.

BigQuery's seamless integration with Looker Studio makes it an ideal choice as the experience of setting up a report is no different from the normal connectors that are on offer, but with several extra advantages like increased speed through Google’s caching system BI Engine and Structured Query Language (SQL) for any custom aggregation or data manipulation.

‍

Data Warehouse limitations

When fetching and storing data every day, there are a few considerations that you need to consider. The most typical one when looking at Google Business Profile data is that values that change over time can catch you out if you use them as filters. For example, if the Location Name or Storecode changes then using this as a filter control will only show you data for the corresponding rows. This can be easily overcome through either of the approaches we will explore.

We will cover two different ways you can customize the data in Looker Studio.

1. Calculated fields
   ‍Great for combining data, manipulating strings or extracting data from nested fields. Refer to the full docs here. 

2. Structured Query Language (SQL)
   Useful for aggregating and combining data in ways that are not possible through calculated fields.

‍

Show me the formulas

Having the data is all well and good but sometimes it’s stored in a way that requires adjustment to manipulate into a format that is desired. Here are some common formulas that you can use directly in your Looker Studio report. These all use calculated fields, if you haven’t created one of these before they are very simple and can be created directly in a report as shown below.

Creating a Calculated field in Looker Studio

Note: Calculated fields can be created at the individual chart level or the data source level. If you need to use the same field multiple times in your report, which is useful for fields that manipulate the Location Name or that are used in multiple charts, we recommend creating them at the data source level.

‍

Profile Name with City

What it does: This formula combines the location name with the city. This is useful when you have multiple profiles with the same name as Looker Studio will only show different values and it appears that there is only one profile.

Formula: <code>CONCAT(locationName, " - ", REGEXP_EXTRACT(address,'"locality":.\"(.*?)\"'))<code>

‍

Profile Name with City and Store Code

What it does: If there are several profiles within the same locality or there isn’t a locality because they are SAB (Service Area Business) Profiles you can include the Storecode.

Formula: <code>CONCAT(locationName, " - ", REGEXP_EXTRACT(address,'"locality":.\"(.*?)\"'), " (", storeCode, ")")<code>

‍

Profile Name with City/Location ID and Address

What it does:

Formula: <code>CONCAT(LocationName, " - ", IF(CONTAINS_TEXT(REGEXP_EXTRACT(Address,'"locality":.\"(.*?)\"'),""), REGEXP_EXTRACT(Address,'"locality":.\"(.*?)\"'), LocationId), " - ", REGEXP_EXTRACT(Address,'"addressLines":..\"(.*?)\"'))<code>

‍

State

What it does: This extracts the value of the administrativeArea, which depending on your country will be the state or equivalent. 

Formula: <code>REGEXP_EXTRACT(address, '"administrativeArea":.\"(.*?)\"')<code>

‍

Options

In some scenarios the administrative areas may have different names that you want to combine. To do this we will use a Case Statement to declare what the value should be. In the example below all instances of “SA” will be merged into “South Australia”.

Formula:

<code>CASE<code>

  <code>WHEN REGEXP_EXTRACT(address, '"administrativeArea":.\"(.*?)\"') = "SA" THEN "South Australia"<code>

  <code>ELSE REGEXP_EXTRACT(address, '"administrativeArea":.\"(.*?)\"')<code>

<code>END<code>

‍

Branded Keywords

One of the most underutilized data points in Business Profile’s is the ability to see which keywords are triggering the profile in the SERP. Google provides this data at the monthly level and depending on the prominence of the profile it can be several thousand keywords. As we are using BigQuery, it’s able to handle large volumes of data over a long time period so this pivot table is still quick to load.

‍

What it does: Creates a column for the number of keywords that had the brand name in them so that you can breakdown the visibility between brand and non-brand keywords.

Formula:‍

<code>CASE<code>

  <code>WHEN  CONTAINS_TEXT(LOWER(searchKeyword), 'ACME') THEN 'Brand'<code>

  <code>ELSE 'Non-Brand'<code>

<code>END<code>

‍

I’ve heard of SQL but don’t know how to use it.

Structured query language (SQL) and regex (Regular expression) are powerful ways of formatting your data but can be quite daunting if you have never had to use them. The good news is that you don’t have to learn how to write SQL or regex to make your reports since we are going to cover some typical use cases and you can directly copy and paste the formulas into Looker Studio.

‍

AI can do that for you

With the ever-increasing power of generative AI, you can also leverage ChatGPT, Gemini (Whoever thought Bard was a good name to begin with), or whatever CoPilot variation Microsoft is calling it this week.

‍

SQL Example 1 - Latest Review per Profile (Basic)

Let’s say that you manage a few profiles and you would like to create a table that shows when the last review was received for each profile along with the percentage of reviews with a reply.

This would be very impractical with a live-fetch connector due to the amount of data that needs to be processed but is trivial with some SQL. If just the thought of SQL is giving you anxiety or it sounds too complicated then bare with me, as with the recent advancements in Artificial Intelligence (AI) you can write natural language and get it to generate the SQL for you.

‍

Here is a prompt that I gave GPT-4 to create this table:

You might notice that I had to give it a little hint about the date range parameters to use. This is because the query we are making is going to utilise the in-built data range functionality of your Looker report. This allows the query to respect the date ranges you set and update if you add a date range option to your report. If you don’t need this flexibility you can use static dates and the query becomes a lot simpler.

Here is the Custom SQL Query that you can use if you would like to try it for yourself:

-- Define parameters to adjust the query with the dates of your chart
WITH params AS (
  SELECT
    PARSE_DATE('%Y%m%d', @DS_START_DATE) AS start_date,
    PARSE_DATE('%Y%m%d', @DS_END_DATE) AS end_date
)

SELECT
  locationName,
  MAX(CASE WHEN reviewId IS NOT NULL THEN date END) AS LastReviewDate,
  SAFE_DIVIDE(
    SUM(CASE WHEN reviewReply IS NOT NULL THEN 1 ELSE 0 END),
    COUNT(CASE WHEN reviewId IS NOT NULL THEN 1 ELSE 0 END)
  ) * 100 AS PercentageOfRepliedReviews
FROM
  `your-project.your-dataset.jepto_gmb_data`,
  params
WHERE
  date BETWEEN params.start_date AND params.end_date
GROUP BY
  locationName;

‍

SQL Example 2 - Custom Performance Metrics (Advanced) 

For this more complex example we are going to create a formula that combines multiple data points into a single metric that can be used to interpret performance data.

Considerations: 

• The chosen metric needs to account for profiles in high population areas that will receive more impressions and actions than those in less populated areas.
• The search volume should be a spread of search keywords rather than just branded ones.
• Not all performance metrics are equal
• Two metrics. One for insights data, Engagement Score, and one for reviews, Review Score

This approach offers executives a clear, single metric that encapsulates various aspects of their online presence and customer engagement, making it easier to gauge overall performance at a glance and make informed decisions. The specific weights and components of the "Business Health Index" can be tailored to reflect the unique priorities and goals of your business.

‍

Engagement Score:

The Engagement Score provides an overall measure of user engagement with the Google Business Profile. It is calculated based on various interaction metrics, with different weights associated to each metric to suit your business.

• <code>Action Rate 50%<code>: Total user actions such as clicks, calls, and direction requests divided by the total impressions generated by the profile.
• <code>Search Volume 40%<code>: Total search volume attributed to the profile.
• <code>Keyword Count 10%<code>: Number of keywords generating the search volume.
  

Review Score:

The Review Score measures the impact of user reviews on the Google Business Profile. It considers the Profile’s Rating, the number of New Reviews and the Average Star Rating received within a specified time period.

• <code>Location Rating 40%<code>: The rating of the profile
• <code>Total Review 20%<code>: Number of total reviews
• <code>Average Star Rating 30%<code>: Average rating received from user reviews
• <code>New Reviews 10%<code>: Number of new reviews received within the period
  

Here is the Custom SQL Query that you can use if you would like to try it for yourself:

-- Define parameters to adjust the query with the dates of your chart
WITH params AS (
  SELECT
    PARSE_DATE('%Y%m%d', @DS_START_DATE) AS start_date,  -- Parsing the start date parameter
    PARSE_DATE('%Y%m%d', @DS_END_DATE) AS end_date       -- Parsing the end date parameter
),


-- Calculate the Review Score for each location
ReviewScore AS (
  SELECT
    locationName,
    -- Location Rating: The rating of the profile
    ARRAY_AGG(locationAverageRating IGNORE NULLS ORDER BY date DESC LIMIT 1)[OFFSET(0)] AS LocationRating,
    -- Total Review: Number of total reviews
    ARRAY_AGG(totalReviewCount IGNORE NULLS ORDER BY date DESC LIMIT 1)[OFFSET(0)] AS totalReviews,
    -- Average Star Rating: Average rating received from user reviews
    AVG(starRating) AS averageStarRating,
    -- New Reviews: Number of new reviews received within the period
    COUNT(DISTINCT reviewId) AS newReviews,
    -- Calculate the Review Score as per the given weights
    ((ARRAY_AGG(locationAverageRating IGNORE NULLS ORDER BY date DESC LIMIT 1)[OFFSET(0)] * 0.4) + (ARRAY_AGG(totalReviewCount IGNORE NULLS
      ORDER BY date DESC LIMIT 1)[OFFSET(0)] * 0.2) + (AVG(starRating) * 0.3) + (COUNT(DISTINCT reviewId) * 0.1)) AS ReviewScore
  FROM
    `your-project.your-dataset.jepto_gmb_data`
  CROSS JOIN
    params
  WHERE
    date BETWEEN params.start_date AND params.end_date
  GROUP BY
    locationName
),

-- Calculate the Engagement Score for each location
EngagementScore AS (
  SELECT
    locationName,
    -- Actions: Total user actions such as clicks, calls, and direction requests
    SUM(IFNULL(callClicks, 0) + IFNULL(directionRequests, 0) + IFNULL(webClicks, 0)) AS actions,
    -- Impressions: Total user impressions across search and maps 
    SUM(IFNULL(searchImpressions, 0) + IFNULL(mapsImpressions, 0)) AS impressions,
    -- Action Rate: Total user actions such as clicks, calls, and direction requests divided by the total impressions generated by the profile
    SAFE_DIVIDE(SUM(IFNULL(callClicks, 0) + IFNULL(directionRequests, 0) + IFNULL(webClicks, 0)), SUM(IFNULL(searchImpressions, 0) + IFNULL(mapsImpressions, 0))) * 0.5 AS actionRate,
    -- Search Volume: Total search volume attributed to the profile
    SUM(searchVolume) AS searchVolume,
    -- Keyword Count: Number of keywords generating the search volume
    COUNT(DISTINCT searchKeyword) AS keywordCount,
    -- Calculate the Engagement Score as per the given weights
    ((SAFE_DIVIDE(SUM(IFNULL(callClicks, 0) + IFNULL(directionRequests, 0) + IFNULL(webClicks, 0)), SUM(IFNULL(searchImpressions, 0) + IFNULL(mapsImpressions, 0))) * 0.5) + (SUM(searchVolume) * 0.4) + (COUNT(DISTINCT searchKeyword) * 0.1)) AS EngagementScore
  FROM
    `your-project.your-dataset.jepto_gmb_data`
  CROSS JOIN
    params
  WHERE
    date BETWEEN params.start_date AND params.end_date
  GROUP BY
    locationName
)

-- Main query combining both scores
SELECT
  rs.locationName AS locationName,
  rs.LocationRating AS LocationRating,
  rs.totalReviews AS TotalReviews,
  rs.newReviews AS NewReviews,
  rs.averageStarRating AS AverageStarRating,
  es.impressions AS Impressions,
  es.actions AS Actions,
  es.actionRate AS ActionRate,
  es.searchVolume AS SearchVolume,
  es.keywordCount AS KeywordCount,
  rs.ReviewScore,
  es.EngagementScore AS EngagementScore
FROM
  ReviewScore rs
JOIN
  EngagementScore es ON rs.locationName = es.locationName;

‍

‍If you need any help setting up a data warehouse for your account or want some advice on taking advantage of these powerful data warehouse capabilities, please get in touch with our support team via the chat widget in the bottom right corner of the App.