Group Locations by Your Own Hierarchy Using a Data Builder Lookup Table

Group Locations by Your Own Hierarchy Using a Data Builder Lookup Table

Info
If your location scorecards are missing stores, or grouping them under the wrong regions or managers, there are only two causes. This article helps you identify which one you have, then walks you through both fixes: enabling missing locations in your data source, and grouping locations by your own internal hierarchy (regional managers, districts, franchise groups) using a lookup table in Data Builder.

First, work out which problem you have

What you're seeing
What it means
A location is completely missing from the report
The location isn't enabled in your data source. Go to Part 1.
All locations appear, but they're grouped under the wrong regions or managers
The source platform doesn't store your org structure, so your report can't group by it yet. Go to Part 2.

These are different problems with different fixes. Platforms like Google Business Profile supply location identifiers (location names, store codes) but have no concept of your regional managers or districts, and Power My Analytics reports exactly what the platform supplies. Which locations reach your reports is controlled by your data source's account selection; how they roll up into groups comes from a mapping you supply.

This article uses Google Business Profile as the example, but the same pattern works for any multi-location or multi-entity source.

Part 1: A location is missing from your reports

Your reports only include locations that are enabled in your data source connection.
  1. In your hub, go to Sources in the left navigation.
  2. Find your Google Business Profile data source and click Manage Accounts under the Action menu.
  3. Click Enable Accounts to open the account selection screen.
  4. Check each location you want in your reports against your current location list. If specific locations have been slipping through, selecting them individually is more reliable than an "all accounts" style selection.
  5. Click Save. The enabled locations begin backfilling data, and the next data sync keeps them current.
Notes
Locations you leave disabled are excluded from everything: reports, exports, and warehoused data. For Google Business Profile, billing is per location rather than per account; see What Are Business Locations? for details.

Part 2: Locations are grouped the wrong way

Google Business Profile doesn't know which stores belong to which regional manager, and neither does any report built directly on it. To group locations by your own structure, you give Power My Analytics that structure as a simple mapping table and join it to your location data with a lookup source in Data Builder. One mapping can supply as many grouping columns as you like (regional manager, district, brand), and it stays reusable across every report built on the data table.

Step 1: Create your mapping spreadsheet

Create a Google Sheet with a header row and at least two columns:
Store Code
Regional Manager
BE490
Gianni Difronzo
192
Celeste Khan

  • Column 1 is the identifier that already appears in your Power My Analytics data (store code, location ID, or location name).
  • Each additional column is a grouping you want to report by (Regional Manager, District, Brand, and so on). No template is required.

Step 2: Connect the sheet as a data source

  1. Go to Sources > + Add new source and choose Google Sheets from the connector gallery.
  2. Sign in with the Google account that has access to your mapping spreadsheet and grant the requested permissions.

Step 3: Build a blended data table

  1. Go to Data Builder and open (or create) a dataset, then click Create Data Table.
  2. On the Sources tab, select Blended.
  3. Add your Google Business Profile source: choose the source, user, and accounts, then click Add Source.
  4. Add your mapping sheet: choose Google Sheets (Beta) as the source, select the authenticated user, pick your spreadsheet and tab under Sheet, then click Create Blend.

Step 4: Convert the sheet to a lookup source

  1. On the Google Sheets card in the Sources panel, click the Convert to lookup source icon.
  2. In the Configure lookup dialog, click + Add left-side sources.
  3. Under JOIN KEYS, select the Source field (the field that carries your store or location code on the Google Business Profile side) and the matching Lookup field (the code column in your sheet).
  4. Under SOURCES, check your Google Business Profile source.
  5. Click Add join, then Save.
Notes
A lookup source doesn't add its own rows to your data table. It enriches the location rows it matches, so every location row picks up your Regional Manager, District, or other grouping columns. Locations whose code isn't in your sheet keep reporting with those columns empty, which makes unmapped locations easy to spot.

Step 5: Choose your fields

  1. On the Fields tab, select your dimensions and metrics from Google Business Profile as usual (for example Date, Store Code, Star Rating, Review Comment).
  2. Add the grouping columns from your sheet; they appear under the Google Sheets field group.
  3. Configure the Options tab as needed, then click Save.

Step 6: Group your report by the new field

Connect the data table to your report through the Power My Analytics connector (for example in Data Studio), then use the mapping column, such as Regional Manager, as a dimension. Your scorecards now roll up by your own hierarchy.

Verify your setup

  • Preview the data table in Data Builder: each location row should show the correct group values from your sheet.
  • Scan for rows where the group columns are empty; those store codes are missing from your mapping sheet (or spelled differently), so add or correct them there.

Keep your mapping current

Your mapping is a snapshot of your organization. When you reorganize, add locations, or change who owns what, update the spreadsheet; the changes flow into your reports automatically. Remember that a brand-new location needs both halves: enable it in the data source (Part 1) and add its code to the mapping sheet (Part 2).

Still have questions about grouping your locations? Submit a ticket and our support team will help you map out the best approach.

    • Related Articles

    • Combining Data from Multiple Sources in Your Report

      Bring data from two or more sources together (different accounts, different businesses, or different platforms entirely) into a single report. Power My Analytics gives you two ways to do it: a reusable blended dataset built in Data Builder, or Data ...

    • Get AI Insights in Data Builder

      Power My Analytics now offers AI Insights for intelligent analysis of your datasets and data tables in our Data Builder. With AI Insights, you can identify performance trends in your report data and find strategies for success in your campaigns, ...

    • Power My Analytics Data Connector User Guide

      The Power My Analytics (PMA) Data Connector is your gateway from Data Builder datasets to your reporting destinations. Design your datasets in Data Builder, then report your data in Data Studio or Sheets or export to destinations like BigQuery and ...

    • Manage and Configure Data Sources

      Power My Analytics has updated the Sources page to make it easier to manage, configure, and monitor your connected data sources. This article will explain the features of the Sources section in your PMA hub and how to use it to manage your data ...

    • ActiveCampaign Data Connector User Guide

      Get valuable insights into your email marketing, automation, and sales performance by connecting your ActiveCampaign account to Power My Analytics. This guide will walk you through setup, reporting capabilities, and important considerations. ...