Places Insights Data Studio visualization

Overview

This document describes how to build dynamic geospatial reports using Places Insights and Data Studio. Unlock the value of your location data by empowering non-technical stakeholders to answer their own questions. This guide shows you how to turn static reports into interactive, heatmap-style tools for market analysis, without having to write SQL for every request. Enable access to complex location data, bridging the gap between data engineering and business intelligence.

Adopting this architectural pattern unlocks several key benefits:

• Visual Data Representation: Transforms Places Insights data into interactive maps and charts that immediately communicate spatial density and trends.
• Simplified Exploration without SQL: Enables team members, such as market analysts or real estate planners, to dynamically filter data using predefined parameters (e.g., changing "City" or "Time of Day" using dropdowns). They can explore the data without ever writing a single line of SQL.
• Seamless Collaboration: Standard Data Studio sharing features allow you to securely distribute these interactive insights.

Solution Workflow

The following workflow establishes a performant reporting architecture. It moves from a static baseline to a fully dynamic application, ensuring data correctness before introducing complexity.

Prerequisites

Before you begin, follow these instructions to set up Places Insights. You will need access to Data Studio, which is a no-cost tool.

Step 1: Establish a Static Geospatial Baseline

Before introducing interactivity, establish a base query and ensure it renders correctly in Data Studio. Use Places Insights and BigQuery's geospatial capabilities to aggregate data into hexagonal grids using the H3 indexing system. This will produce a query output that can be used with Data Studio's filled map cart type for visualization.

1.1 Connect Data

Use the following static query to establish the initial connection. It targets a fixed location (London) and category (Restaurants) to validate the data pipeline.

SELECT
  h3_index,
  `carto-os.carto.H3_BOUNDARY`(h3_index) AS h3_geo,
  restaurant_count
FROM (
  SELECT WITH AGGREGATION_THRESHOLD
    `carto-os.carto.H3_FROMGEOGPOINT`(point, 8) AS h3_index,
    COUNT(*) AS restaurant_count
  FROM
    -- Note: Change 'gb' to your target country code (e.g., 'us')
    `places_insights___gb.places`
  WHERE
    'London' IN UNNEST(locality_names)
    AND 'restaurant' IN UNNEST(types)
  GROUP BY
    h3_index
)
ORDER BY
  restaurant_count DESC;

Note on Spatial Aggregation

This query uses a function from the CARTO Analytics Toolbox (carto-os) available publicly in Google Cloud BigQuery. The H3_FROMGEOGPOINT function converts specific location points into H3 cells, a system that divides the world into hexagonal grid cells.

We use this transformation because Data Studio's Filled Map requires polygons (shapes) to render colors. By converting points into hexagonal shapes, we can visualize the density of businesses in a specific area, rather than plotting thousands of overlapping dots.

Note on Aggregation Threshold

All Places Insights queries require the WITH AGGREGATION_THRESHOLD clause. This privacy protection ensures that data is only returned if the aggregated count is 5 or higher.

In the context of this visualization, if a H3 grid cell contains fewer than 5 restaurants, that cell is omitted from the result set entirely and will appear empty on your map.

To implement this in Data Studio:

1. Create a new Blank Report.
2. Select BigQuery as the data connector.
3. Choose CUSTOM QUERY from the left-hand menu and select your billing Project ID.
4. Paste the Static Base Query above into the editor.
5. Clear Use Legacy SQL, Enable date range, and Enable viewer email address parameters.
6. Click Add.

1.2 Configure Geospatial Visualization

Once the data is connected, configure Data Studio to recognize the H3 boundary data correctly:

1. Add a Filled Map visualization to the report canvas, from the Add a chart menu.
2. Make sure that your h3_geo field, which contains the polygon geometry, is set to the Geospatial data type.
   1. Click the Edit data source (pencil) icon next to your connection name.
   2. If h3_geo is set to Text (ABC), use the drop-down menu to select Geo > Geospatial,
   3. Click Done.
3. Map the h3_index field to Location (acting as the unique identifier).
4. Map the h3_geo field to Geospatial Field (acting as the polygon geometry).
5. Map the restaurant_count field to Color metric.

This will render a map of restaurant density by H3 cell. The darker blue (default color option) indicates a cell with a higher restaurant count.

Step 2: Implement Dynamic Parameters

To make the report interactive, we will add controls to the report that allow the user to select from the following options:

• Locality: Controls the city the report focuses on.
• Day of the week: Filters places based on the day they are open, leveraging the regular_opening_hours record in the schema.
• Hour of the day: Filters places based on their operational hours by comparing against the start_time and end_time fields.

To achieve this, you will pass user-selected parameters directly into a modified Places Insights query at runtime. In Data Studio's data source editor, you must explicitly define these parameters as typed variables.

In Data Studio, select the Resource menu, then click Manage added data sources. Within the panel that appears, select EDIT against the BigQuery Custom SQL data source we added earlier.

Within the Edit Connection window, select ADD A PARAMETER. We are going to add three parameters, with the values below.

Example configuration for the p_hour_of_day parameter.