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 tab, 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.