Build Queries

A query is SQL that is run from the Queries tab against database tables in the Customer 360 tab. A query returns a refined and filtered subset of useful customer data.

Queries tab

The Queries tab provides an overview of the status of every query. A table shows the status and details. Queries are listed by row. The details include the date and time at which this query last ran, along with the number of records (rows) that were returned during the last completed run.

Queries tab

Query editor

The SQL Query Editor is the user interface for a full SQL query engine based on Presto SQL that interacts with customer database tables in Amperity. The SQL Query Editor relies primarily on using the SELECT statement, along with common table expressions, joins, functions, and other components of Presto SQL to build and design advanced queries.

Use the Query Editor to build SQL queries against tables and columns in your customer 360 database to support any downstream workflow. The Query Editor uses Presto SQL.

Queries tab, SQL Segment Editor

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.

  • indicates the query was created using the Visual Query Editor.

  • indicates the query was created using the SQL Query Editor.

All queries must be activated before they can run as part of a scheduled workflow.

Note

Amperity is a multi-user system and the set of queries for your company is shared across all users. That means that if one user creates a draft query, another can open and edit it, so work can be easily passed between people on your team.

However, it also means that if 2 users are editing the same thing at the same time, their changes will collide. Amperity resolves this by applying the last set of changes saved as a whole. This will always keep the query in a consistent state (it will never be half-implemented). But changes that were saved first will be overwritten. As a result, we strongly recommend that you coordinate changes to specific objects in Amperity with others on your team.

About Presto SQL

Presto is a distributed SQL query engine that is designed to efficiently query vast amounts of data using distributed queries. Presto is used by the Amperity SQL segment editors to define segments, which are SQL queries that return data from stitched data tables.

Amperity queries are built using Presto SQL to define a SELECT statement. Please refer to the Presto SQL.

All queries that are built via the SQL Query Editor are done using a SELECT statement. In some cases, a WITH is used along with the SELECT statement. Each select statement can additional functionality, such as WHERE, LEFT JOIN, GROUP BY, ORDER BY, LIMIT clauses, CASE expressions, functions, operators, and other componetns that are part of Presto SQL, which is the underlying SQL engine for both the Visual Query Editor and SQL Query Editor.

Tip

Follow the recommendations and patterns for indentation, naming conventions, reserved words, and whitespace.

Build 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.

Add query

The SQL Query Editor is the user interface for a full SQL query engine based on Presto SQL that interacts with customer database tables in Amperity. The SQL Query Editor relies primarily on using the SELECT statement, along with common table expressions, joins, functions, and other components of Presto SQL to build and design advanced queries.

To add a query using the SQL Query Editor

  1. From the Queries tab 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.

Preview results

You can preview the results of a SQL query by clicking the Run Query button. This will do one of the following things:

  • Return the first 100 results of the query to the preview pane directly below the query editor.

  • Return an empty table.

  • Return some type of error.

Use the preview results pane to fine-tune your queries, to make sure they return the data you want, and to make sure they don’t contain any errors.

Tip

Query editor SQL queries often evaluate millions of records. This means they may take a few minutes to run. You may use other areas of Amperity while a query is being run.

For draft queries, setting a LIMIT 100 while developing a query is often enough for testing and validating query results against very large data sets.

To preview query results

  1. From the Queries tab, open the menu for a query, and then select View. This opens a query editor.

  2. Click Run Query to run the query. Wait for it to return results.

  3. Example the columns and the data that is in them.

  4. Adjust your query as necessary until it runs correctly.

  5. Click Activate.

Validate query

SQL queries are validated from the SQL Query Editor by clicking the Run Query button. The results of the query are returned in the results window. The quality of the results can be inspected, and then fine-tuned. Errors in the syntax are reported in the results window.

Add to orchestration

Use the Orchestration option to define a schedule for a query.

To add a query to an orchestration

  1. From the Queries tab, open the menu for a query, and then select View. This opens a query editor.

    Tip

    The query does not need to be in edit mode to configure an orchestration.

  2. Under Being Sent To click Add one now. This opens the Add Orchestration dialog box.

  3. Follow the steps to add an orchestration. The steps will vary depending on the destination, the data template, and the orchestration.

  4. Click Save.

Example queries

Count loyalty by state

The following example counts customers in the United States, and then also in California, Oregon, Washington, Alaska, and Hawaii who also belong to the loyalty program (which is indicated when loyalty_id is not NULL):

SELECT
  state
  ,COUNT(amperity_id) AS TotalCustomers
FROM
  Customer360
WHERE (UPPER("country") = 'US'
AND UPPER("state") in ('AK', 'CA', 'HI', 'OR', 'WA')
AND LOWER("loyalty_id") IS NOT NULL)
GROUP BY state

Days to second purchase

The following example shows how to return the days to second purchase starting from a date range for the first order.

SELECT
  days_to_second_purchase
  ,COUNT(DISTINCT amperity_id) AS "customer_count"
FROM (
  SELECT
    amperity_id
    ,first_order_datetime
    ,second_order_datetime
    ,DATE_DIFF('day', first_order_datetime, second_order_datetime) AS "days_to_second_purchase"
  FROM Transaction_Attributes
  WHERE (
    (
      "amperity_id" IN (
        SELECT DISTINCT "t0"."amperity_id"
        FROM "Transaction_Attributes_Extended" "t0"
        WHERE (
          "t0"."first_order_datetime" BETWEEN TIMESTAMP '2019-01-01'
          AND TIMESTAMP '2022-01-01'
          AND "t0"."second_order_datetime" IS NOT NULL
        )
      )
    )
  )
  GROUP BY amperity_id, first_order_datetime, second_order_datetime
)
GROUP BY 1
ORDER BY 1 ASC

Missing transaction IDs

Use the following example to find missing transaction IDs. The query calculates the percentage of missing Amperity IDs and customer IDs in the Unified_Transactions table. Change customer_id AS customer_id to match how customer IDs are defined in your database.

SELECT
  COUNT(DISTINCT CASE WHEN amperity_id IS NULL THEN order_id END) orders_missing_amp_id
  ,COUNT(DISTINCT CASE WHEN customer_id IS NULL THEN order_id END) orders_missing_cust_id
  ,1.0000*COUNT(DISTINCT CASE WHEN amperity_id IS NULL THEN order_id END)/COUNT(distinct order_id) pct_orders_missing_amp_id
  ,1.0000*COUNT(DISTINCT CASE WHEN customer_id IS NULL THEN order_id END)/COUNT(distinct order_id) pct_orders_missing_cust_id
FROM (
  SELECT DISTINCT
    ,order_id
    ,amperity_id
    ,customer_id AS customer_id
  FROM Unified_Transactions
)

One and dones, by category

The following example shows how to return all one-and-done purchasers for a single calendar year by product category:

WITH new_in_21 AS (
  SELECT
    amperity_id
    ,one_and_done
  FROM Transaction_Attributes
  WHERE YEAR(first_order_datetime) = 2021
),

product_categories AS (
  SELECT DISTINCT
    new_in_21.amperity_id
    ,one_and_done
    ,product_category
  FROM Unified_Itemized_Transactions uit
  INNER JOIN new_in_21 ON new_in_21.amperity_id=uit.amperity_id
)

SELECT
  product_category
  ,COUNT(distinct amperity_id) customer_count
  ,1.0000*SUM(CASE when one_and_done THEN 1 ELSE 0 END) / COUNT(DISTINCT amperity_id) pct_one_done
FROM product_categories
GROUP BY 1
ORDER BY 3 DESC

One and dones, by year

The following example shows how to return all one-and-done purchasers for a single calendar year:

WITH one_and_dones_2022 AS (
  SELECT
    amperity_id
  FROM Transaction_Attributes
  WHERE one_and_done AND YEAR(first_order_datetime) = 2022
)

SELECT
  COUNT(*) one_and_dones_2022
FROM
  one_and_dones_2022

Order revenue, 7-day rolling window

The following example shows a rolling seven day window for order revenue.

SELECT
  *
FROM (
  SELECT
    purchase_channel
    ,order_day
    ,SUM(order_revenue) OVER (PARTITION BY purchase_channel ORDER BY order_day ROWS BETWEEN 6 preceding AND current row) rolling_7_day_revenue
  FROM (
    SELECT
      purchase_channel
      ,DATE(order_datetime) order_day
      ,SUM(order_revenue) order_revenue
    FROM Unified_Transactions
    WHERE amperity_id IS NOT NULL
    AND order_datetime > (CURRENT_DATE - interval '36' day)
    GROUP BY 1,2
  )
)
WHERE order_day > (CURRENT_DATE - interval '30' day)
ORDER BY 1,2

Partition pCLV by brand

Use the NTILE() function to partition by brand, and then order by predicted customer lifetime value.

NTILE(100) OVER (PARTITION BY brand ORDER BY predicted_clv desc, _uuid_pk)

Percent rank of purchases

Use the PERCENT_RANK() function to return the percent rank of all purchases.

SELECT DISTINCT
  amperity_id
  ,order_revenue
  ,PERCENT_RANK() OVER (ORDER BY order_revenue) pct_rank
FROM Unified_Transactions
WHERE order_datetime > DATE('2021-04-01') AND amperity_id IS NOT NULL
ORDER BY pct_rank DESC

Rank by Amperity ID

Use the RANK() function to find the largest transactions by Amperity ID, and then return them in ascending order:

WITH ranked_transactions AS (
SELECT
  t.orderid
  ,t.amperity_id
  ,t.transactiontotal
  ,RANK() OVER (PARTITION BY t.amperity_id ORDER BY t.transactiontotal DESC) AS rank
FROM
  TransactionsEcomm t
ORDER BY t.amperity_id, rank ASC
LIMIT 100
)
SELECT * FROM ranked_transactions WHERE rank = 1

Return percentiles

Return 10th percentiles:

SELECT
  tier*.1 AS tier
  ,MIN(lifetime_order_revenue) lifetime_revenue_amount
FROM (
  SELECT
    amperity_id
    ,lifetime_order_revenue
    ,NTILE(10) OVER (ORDER BY lifetime_order_revenue) tier
  FROM Transaction_Attributes
)
GROUP BY 1
ORDER BY 1

Return percentiles by 4:

SELECT
  tier*.25 tier
  ,MIN(lifetime_order_revenue) lifetime_revenue_amount
FROM (
  SELECT DISTINCT
    amperity_id
    ,lifetime_order_revenue
    ,NTILE(4) OVER (ORDER BY lifetime_order_revenue) tier
  FROM Transaction_Attributes
)
GROUP BY 1
ORDER BY 1

Revenue opportunity, 1 group

Use the following example to size revenue opportunity for a single group, such as for “one-and-done” or for “repeat purchaser”. This query is most effective when run against the Transaction_Attributes_Extended table, but may be useful when run against other tables. Customize the WHERE clause in the WITH statement to define the group. Customize the main SELECT statement to define the time periods to measure.

WITH customers AS (
  SELECT
    amperity_id
  FROM Transaction_Attributes_Extended
  WHERE one_and_done
)

SELECT
  COUNT(DISTINCT tae.amperity_id) AS num_customers
  ,AVG(lifetime_average_order_value) lt_aov
  ,AVG(L12M_average_order_value) l12m_aov
  ,AVG(lifetime_order_revenue) lt_rev
  ,AVG(l12m_order_revenue) l12m_rev
  ,AVG(lifetime_average_item_price) lt_aur
  ,AVG(l12m_average_item_price) l12m_aur
  ,AVG(lifetime_order_frequency) avg_lt_orders
  ,AVG(l12m_order_frequency) avg_l12m_orders
  ,SUM(l12m_order_revenue) AS total_l12m_revenue
FROM customers cust
INNER JOIN Transaction_Attributes_Extended tae
ON cust.amperity_id=tae.amperity_id

Revenue opportunity, x groups

Use the following example to size revenue opportunity for multiple groups, such as moving from “one-and-done” to “repeat purchaser”. This query is most effective when run against the Transaction_Attributes_Extended table, but may be useful when run against other tables. Customize the WITH statement to define a list of fields to be used for comparing movements between groups. Customize the main SELECT statement to define the time periods for which this movement will be measured.

WITH customers AS (
  SELECT
    amperity_id
    ,multi_purchase_channel
  FROM Transaction_Attributes_Extended
)

SELECT
  cust.multi_purchase_channel
  ,COUNT(DISTINCT tae.amperity_id) AS num_customers
  ,AVG(lifetime_average_order_value) lt_aov
  ,AVG(L12M_average_order_value) l12m_aov
  ,AVG(lifetime_order_revenue) lt_rev
  ,AVG(l12m_order_revenue) l12m_rev
  ,AVG(lifetime_average_item_price) lt_aur
  ,AVG(l12m_average_item_price) l12m_aur
  ,AVG(lifetime_order_frequency) avg_lt_orders
  ,AVG(l12m_order_frequency) avg_l12m_orders
  ,SUM(l12m_order_revenue) AS total_l12m_revenue
FROM customers cust
INNER_JOIN Transaction_Attributes_Extended tae
ON cust.amperity_id=tae.amperity_id
GROUP BY 1

Sort total revenue

Use the SUM() function to return total revenue, total returns, and total quantity by brand, and then sort in descending order:

SELECT
  purchase_brand
  ,SUM(order_revenue) AS total_revenue
  ,SUM(order_returned_revenue) AS total_returns
  ,SUM(order_quantity) AS total_quantity
FROM Unified_Transactions
GROUP BY 1
ORDER BY 1 DESC

Transactions by day

Use the following example to generate a breakdown of customer and order metrics by day. This query must be run against the Unified_Transactions table. Export the results of this query to BI tools like Tableau or desktop tools like Google Sheets or Microsoft Excel.

SELECT
  DATE(order_datetime) AS day_of_order
  ,purchase_brand
  ,purchase_channel
  ,SUM(order_revenue) AS total_revenue
  ,AVG(order_revenue) AS aov
  ,COUNT(DISTINCT order_id) AS orders
  ,COUNT(DISTINCT amperity_id) AS customers
  ,SUM(order_quantity) AS total_items
  ,AVG(order_quantity) AS avg_items
  ,SUM(order_returned_revenue) AS total_returned_revenue
FROM Unified_Transactions
GROUP BY 1,2,3
ORDER BY 1,2,3