Updating sync keys in Amazon Ads Sponsored Brand Reports

We have updated how we extract Amazon Ads Sponsored Brands Reports data so that campaign name and ad group name are no longer part of the sync key. This was causing duplicate data issues when a campaign or ad group name was updated.

The effect of this change is that your Amazon Ads reporting in Daasity will align more closely with what you see in your Amazon Ads admin.

We have updated our Amazon Ads Sponsored Brands transform script to include a section that will update historical sync keys and remove any duplicate historical data.

For reference, the following is the code that we are running to update historical sync key data:

-- Clear out test table if it already exists
DROP TABLE IF EXISTS amazon_ads.sponsored_brands_reports_test
;
-- Clone the brands report table into a test table
CREATE TABLE IF NOT EXISTS amazon_ads.sponsored_brands_reports_test
CLONE amazon_ads.sponsored_brands_reports
;
-- Update the sync keys for different report types
-- campaigns
UPDATE amazon_ads.sponsored_brands_reports_test
SET __sync_key = MD5(ARRAY_TO_STRING(ARRAY_CONSTRUCT(
      'campaigns'
      ,COALESCE(campaign_id::STRING, '')
      ,report_date::DATE
      ,profile_id::STRING
    ), ':'))
WHERE 1=1
  AND report_type = 'campaigns'
;
-- ad_groups
UPDATE amazon_ads.sponsored_brands_reports_test
SET __sync_key = MD5(ARRAY_TO_STRING(ARRAY_CONSTRUCT(
      'ad_groups'
      ,COALESCE(campaign_id::STRING, '')
      ,COALESCE(ad_group_id::STRING, '')
      ,report_date::DATE
      ,profile_id::STRING
    ), ':'))
WHERE 1=1
  AND report_type = 'ad_groups'
;
-- keywords
UPDATE amazon_ads.sponsored_brands_reports_test
SET __sync_key = MD5(ARRAY_TO_STRING(ARRAY_CONSTRUCT(
      'keywords'
      ,COALESCE(campaign_id::STRING, '')
      ,COALESCE(ad_group_id::STRING, '')
      ,COALESCE(keyword_id::STRING, '')
      ,COALESCE(keyword_text::STRING, '')
      ,COALESCE(match_type::STRING, '')
      ,report_date::DATE
      ,profile_id::STRING
    ), ':'))
WHERE 1=1
  AND report_type = 'keywords'
;
-- Delete duplicate sync keys from test table
DELETE FROM amazon_ads.sponsored_brands_reports_test
WHERE
 (__sync_key||__synced_at) IN (
    SELECT derived_key FROM (
        SELECT
            __sync_key||__synced_at AS derived_key
            ,row_number() OVER (PARTITION BY __sync_key ORDER BY __synced_at DESC) as rn
            ,*
        FROM amazon_ads.sponsored_brands_reports_test
        ORDER BY rn DESC
    )
    WHERE rn > 1
)
;
-- Clone the brands report table into a backup table
CREATE TABLE IF NOT EXISTS amazon_ads.sponsored_brands_reports_backup
CLONE amazon_ads.sponsored_brands_reports
;
-- Remove all records from production table
DELETE FROM amazon_ads.sponsored_brands_reports
WHERE 1=1
;
-- Insert the clean data from the test table into the production table
INSERT INTO amazon_ads.sponsored_brands_reports
SELECT *
FROM amazon_ads.sponsored_brands_reports_test
;