Stitch QA queries

The SQL Segment Editor is an optional interface for building attribute profiles using SQL. Start with a SELECT statement that returns the Amperity ID, and then apply a series of WHERE statements to define one or more attribute groups that match specific conditions and values.

Add queries

Queries may be authored using the visual Query Editor or the SQL Query Editor. Click Create and then select the query editor to open. Queries that are already created have an icon that shows from which query editor they were authored. All queries must be activated before they can run as part of a scheduled workflow.

To add a SQL query

  1. From the Queries page, click Create, and then select SQL Query. This opens the SQL Query Editor.

  2. Under Database, select a database. The Customer 360 database is selected by default.

  3. Define the query against the selected database.

  4. Click Run Query and debug any issues that may arise.

  5. Click Activate.

Semantic values

Use regular expressions to create additional fields based on semantic values and to clean up semantic values, as necessary.

To keep only alphabetical characters

SELECT
  REGEXP_REPLACE(AddressLine1, '(\s*)([^a-zA-Z0-9])', '')
  ,AddressLine1
FROM Customer360
LIMIT 100

To replace characters after + symbol

,REGEXP_REPLACE(LOWER(email), '\+(.*?)\@', '@') AS email

To keep characters before @ symbol

,SPLIT(REGEXP_REPLACE(LOWER(email), '\+(.*?)\@', '@'), '@')[1] AS email_username

To keep characters after @ symbol

,REPLACE(LOWER(email), SPLIT(REGEXP_REPLACE(LOWER(email), '\+(.*?)\@', '@'), '@')[1], '') AS email_domain

To clean up semantic values

,REGEXP_REPLACE(LOWER(phone), '[^0-9]+', '') AS phone
,REGEXP_REPLACE(LOWER(address), '[.]+', '') AS address
,REGEXP_REPLACE(LOWER(address2), '[.]+', '') AS address2
,REGEXP_REPLACE(LOWER(city), '[^a-zA-Z]+', '') AS city
,REGEXP_REPLACE(LOWER(state), '[^a-zA-Z]+', '') AS state

Stitch QA queries

Use the following queries to build the initial Stitch QA database