Transactions QA¶

Transactions QA is a process that validates the quality of interactions records. Use the collection of SQL queries provided by Amperity during the validation process.

Add transaction QA queries¶

Use transactions QA queries to validate the quality of interaction records in the customer 360 database.

The recommended way to add transactions QA queries to your tenant is to use the “Transactions QA” query template. You can do this after you have added the customer 360 database and it contains the required tables.

To add Transaction QA queries

From the Segments page click Create, and then select Add Folder. This opens the Create Folder dialog box.
Enter the name for the folder. For example: Transactions QA.
From the Template drop-down, select Transactions QA.
Click Create. This will create a folder, into which a collection of draft queries are added. Review the queries for more information about how each query works.

Rollup validation queries¶

Run the following rollup queries, download the results, and then share the results with external stakeholders. Modify these queries as necessary to include all monetary fields.

for item-level rollups¶

SELECT
  ,YEAR(order_datetime) AS order_year
  ,MONTH(order_datetime) AS order_month
  ,SUM(item_revenue) AS sum_item_revenue
  -- Uncomment the following lines if the fields are available
  -- ,SUM(item_cost) AS sum_item_cost
  -- ,SUM(item_list_price) AS sum_item_list_price
  -- OR:
  -- ,SUM(unit_list_price*item_quantity)
  -- ,SUM(item_subtotal) AS sum_item_subtotal
  -- ,SUM(item_discount_dollar_amount) AS sum_item_discount_dollar_amount
  ,SUM(item_quantity) AS sum_item_quantity
FROM Unified_Itemized_Transactions
GROUP BY 1, 2

for order-level rollups¶

SELECT
  ,YEAR(order_datetime)
  ,MONTH(order_datetime)
  ,SUM(order_revenue)
  -- Uncomment the following lines if the fields are available
  --,SUM(order_cost)
  --,SUM(order_profit)
  --,SUM(order_subtotal)
  --,SUM(sum_item_discounts)
  --,SUM(order_discount_amount)
  ,SUM(order_quantity)
FROM Unified_Transactions
GROUP BY 1, 2

Prioritize Amperity IDs¶

If multiple Amperity IDs exist for interaction records after the Stitch process or after the use of foreign keys, use SQL to ensure that only one Amperity ID is associated to each interaction record.

To prioritize Amperity IDs by most recent transaction

The following SQL prioritizes the Amperity ID by the most recent order ID:

WITH
  amp_priority AS (
    SELECT DISTINCT
      ut.order_id
      ,ut.datasource
      ,FIRST_VALUE(uc.amperity_id) OVER (
        PARTITION BY ut.order_id, ut.datasource
        ORDER BY uc.update_dt DESC
      ) AS amperity_id
    FROM (SELECT amperity_id, datasource, update_dt FROM Unified_Coalesced) uc
    JOIN (SELECT amperity_id, datasource, order_id FROM Unified_Transactions) ut
    ON uc.amperity_id = ut.amperity_id
  )

SELECT t.* FROM table_name t
JOIN amp_priority ap ON t.order_id=ap.order_id