Create your own filters: how to use the query builder

Introduction

The RetailSonar query builder offers an easy way to create and adjust advanced filters using an SQL-like syntax. Currently, it's available for editing the Location perspective and Zone filter. Access it by clicking on the icon.

Screen display

Filter Management (1):

• Apply: Add the filter to the working view
• Clear: Reset to default (own brand group for locations, all zones for zone filters).
• Cancel: Undo changes and revert to the filter before opening the query builder.

Operators (2) and Filters (3):

• Use operators and filters to create subsets of locations or zones and apply conditional filters to those subsets.
• Combine subsets using set operators: union, except, or intersect.

Syntax Window (4):

• Textual representation of your filter, editable directly in this window.

How to create a view in the query builder

SELECT statement

Start with a select statement to choose locations or zones.

• For locations: Add brand groups, brands, or locations by name to the filter.
  • Click on the name of a brand group, brand, or location to add them to the filter.
  • Combine groups, brands, and locations in the SELECT clause.
  • In this example, locations from brand group "Direct competitors," brand "Carpetright," and a single location "My Company Alkmaar" were added to the filter.

SELECT "Direct competitors", "Carpetright", "My Company Alkmaar"

• For zones: select an environment and specify the locations around which to apply these environments.
  • Click the environment filter.
  • Use the AROUND operator.
  • Select the specific locations for the environment filter.
  • In this example, the primary catchment area was applied around all locations of the Company brand.

SELECT primary catchment area AROUND "My Company locations" 

CONDITIONAL statement

After the select statement, add conditional statements to include or exclude zones/locations based on characteristics.

• For locations:
  • Use the WITH operator to create conditional statements based on location characteristics.
  • In the example, we refine the selection to locations with consistently insufficient parking and a daily passage of more than 1000 cars, utilizing the WITH and AND operators in the SELECT statement.

SELECT "Direct competitors", "Carpetright", "My Company Alkmaar" 
WITH "Parking" = 'Always insufficient' 
AND "Passage (location)" > 1000

• For zones:
  • Use the WHERE operator to create conditional statements based on zone characteristics (KPIs with the "usable in zone query builder" flag).
  • In the example, we narrowed down zones to those with over 10,000 inhabitants.

SELECT primary catchment area AROUND "My Company" 
WHERE "Inhabitants" > 10000

Combine filter queries

You can combine multiple filter queries (select statements with or without conditional statements) by using the SET operators UNION, EXCEPT, and INTERSECT.

• In the example below we combined all direct competitors with all own company location that have good visibility.

SELECT "Direct competitors" 
UNION 
SELECT "My Company" WITH "Visibility" = 'Good' 

Extra information on filters and operators

• Clicking on a grey folder icon next to a zone, location or characteristic name will expand the folder. Clicking on the name of a zone, location or characteristic will directly add it to the filter.

•  The RS query builder will limits the operators and filters you can click on to those that makes sense from a syntax perspective. For example, you can only click on the WITH operator after you generated a valid SELECT statement.
• Underneath the syntax window red error messages will inform you whenever the syntax is compromised and it will guide you to a correct syntax.
• Note that
  • the location characteristics are, in fact, visible (!) attributes. So these are not KPIs.
  • the zone characteristics, however, are KPIs.