Statistics – combined

Use this query to return an overview of Stitch statistics that contains the number of records, accounts, Amperity IDs, singletons, clusters with more than one record, overall deduplication rate, and the deduplication rate across all data sources.

Configure query

1. From the Queries page, open the Stitch QA folder, and then select this query.

   Tip

   Add the Stitch QA queries template folder if it does not already exist.

2. If customer keys are available, change the following line in the common table expression:

   WITH overall_dedupe_keys AS (
     SELECT DISTINCT
       amperity_id
       ,CONCAT(datasource, pk) AS dedupe_key
    FROM Unified_Coalesced
   

   to:

   WITH overall_dedupe_keys AS (
     SELECT DISTINCT
       amperity_id
       ,CONCAT(datasource, coalesce(ck, pk)) AS dedupe_key
    FROM Unified_Coalesced
   

   and then change the following line in the table_dedupe_keys SELECT statement:

   ,table_dedupe_keys AS (
     SELECT DISTINCT
       datasource
       ,amperity_id
       ,pk AS dedupe_key
     FROM Unified_Coalesced
   

   to:

   ,table_dedupe_keys AS (
     SELECT DISTINCT
       datasource
       ,amperity_id
       ,COALESCE(ck, pk) AS dedupe_key
     FROM Unified_Coalesced
   

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

4. Click Activate.