Write a query using brands data

Places Insights provides brand information for many categories of places. For example:

• For the category of "ATMs, Banks, and Credit Unions", the brands data contains an entry for each of the brands PNC, UBS, and Chase banks.
• For the category "Automotive Rentals", the data contains an entry for each of the brands Budget, Hertz, and Thrifty.

A typical use case for querying the brands dataset is to join it with a query on the place data to answer questions such as:

• What is the count of all stores by brand in an area?
• What is the count of my top three competitor brands in the area?
• What is the count of brands of a specific category, such as "Fitness" or "Gas Station", in the area?

About the brands dataset

The brands dataset for the US is named places_insights___us.brands.

Brands dataset schema

The schema for the brands dataset defines three fields:

• id: The brand ID.
• name: The brand name, such as "Hertz" or "Chase".
• category: The brand type, such as "Gas Station", "Food and Drink", or "Lodging". For a list of possible values, see Category values.

Use brands dataset in a query

The places dataset schema defines the brand_ids field. If a place in the places dataset is associated with a brand, then the brand_ids field for the place contains the corresponding brand ID.

A typical query that references the brands dataset performs a JOIN with the places dataset based on the brand_ids field.

For example, to find the count of the number of McDonald's restaurants within 2000 meters of the Empire State Building in New York City:

SELECT WITH AGGREGATION_THRESHOLD
 COUNT(*)
FROM PROJECT_NAME.places_insights___us.places places, UNNEST(brand_ids) AS brand_id
LEFT JOIN PROJECT_NAME.places_insights___us.brands ON brand_id = brands.id
WHERE
ST_DWITHIN(ST_GEOGPOINT(-73.9857, 40.7484), point, 2000)
AND brands.name = "McDonald's"
AND business_status = "OPERATIONAL"

The next query returns the count of the number of banks in New York City that belong to a brand, grouped by brand name:

SELECT WITH AGGREGATION_THRESHOLD
  brands.name,
  COUNT(*) AS store_count
FROM PROJECT_NAME.places_insights___us.places places, UNNEST(brand_ids) AS brand_id
LEFT JOIN PROJECT_NAME.places_insights___us.brands ON brand_id = brands.id
WHERE brands.category = "ATMs, Banks and Credit Unions"
AND "bank" IN UNNEST(places.types)
AND business_status = "OPERATIONAL"
GROUP BY brands.name
ORDER BY store_count DESC;

The following image shows the counts by brand:

Category values

The category field for a brand can contain the following values: