7.2.2 Contact Mobile Duplicates in Alternate Channel

Table of Contents

Table of Contents
1 Analyst Notes 2 Count of duplicate 'Mobile' in Alternate Channel (09.5.2024)

Analyst Notes

This page contains documentation on analysis, proposed steps to de-dupe phone numbers in Alternate Channels where the phone number already exists in ‘Mobilephone’ filed on Contact object.

Approach:

Phone numbers linked to contact records were isolated and cleaned to remove -

Any special characters, blank spaces etc.,
+61 was replaced with 0
00s & 000s were replace with 0
00011 was replace with blank

Exclusions: Contact Type - ‘Student’

If the cleaned phone data in Mobilephone field exist in Alternate Channel, then delete the Alternate Channel Phone record.

Count of duplicate 'Mobile' in Alternate Channel (09.5.2024)

Count of Mobile	Count of Duplicates in Alternate Channel

Count of Mobile	Count of Duplicates in Alternate Channel
198,854	216,069

with duplicates as(
with contact_ph as
(
select  c.id "Contact SF ID",
        c.mobilephone "Contact Phone",
        c.aqb__phonepreference__c "Phone Preference",
        
        case 
        when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
        when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone",  '^(000)', '0')
        when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone",  '^(00)', '0')
        when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
        when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
        when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
        when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
        when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
        when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
        when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
        when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
        when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
        when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
        else "Formatted Phone"
        end "Formatted Phone"
        from(select cp.*,
                    regexp_replace(lower(trim(cp.mobilephone)), '[^a-z0-9]', '') "Formatted Phone"
                    from salesforce_db.salesforce_sch.contact cp
                    )c
        left join salesforce_db.salesforce_sch.user u on u.id = c.createdbyid
        left join salesforce_db.salesforce_sch.user u1 on u1.id = c.lastmodifiedbyid
        left join salesforce_db.salesforce_sch.account a on a.id = c.accountid
        where c.mobilephone is not null 
        --and c.aqb__type__c <> 'Student'

),


ac_dupes as
(
select ac.id "Alt Channel SF ID:Dupe",
        ac.name "Alt Channel Name:Dupe",
        regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Dupe",
        ac.aqb__contact__c "ContactID:Dupe",
        ac.aqb__account__c "AccountID:Dupe",
        ac.aqb__status__c "Status:Dupe",
        ac.aqb__type__c "Type:Dupe",
        ac.aqb__phone__c "Phone:Dupe",

        case 
        when startswith("Formatted Phone", '0011') then regexp_replace("Formatted Phone", '^(0011)', '')
        when startswith("Formatted Phone", '000') then regexp_replace("Formatted Phone",  '^(000)', '0')
        when startswith("Formatted Phone", '00') then regexp_replace("Formatted Phone",  '^(00)', '0')
        when startswith("Formatted Phone", '612') then regexp_replace("Formatted Phone", '^(612)', '02')
        when startswith("Formatted Phone", '613') then regexp_replace("Formatted Phone", '^(613)', '03')
        when startswith("Formatted Phone", '614') then regexp_replace("Formatted Phone", '^(614)', '04')
        when startswith("Formatted Phone", '617') then regexp_replace("Formatted Phone", '^(617)', '07')
        when startswith("Formatted Phone", '618') then regexp_replace("Formatted Phone", '^(618)', '08')
        when startswith("Formatted Phone", '6102') then regexp_replace("Formatted Phone", '^(6102)', '02')
        when startswith("Formatted Phone", '6103') then regexp_replace("Formatted Phone", '^(6103)', '03')
        when startswith("Formatted Phone", '6104') then regexp_replace("Formatted Phone", '^(6104)', '04')
        when startswith("Formatted Phone", '6107') then regexp_replace("Formatted Phone", '^(6107)', '07')
        when startswith("Formatted Phone", '6108') then regexp_replace("Formatted Phone", '^(6108)', '08')
        else "Formatted Phone"
        end "Formatted Phone:Dupe",
        ac.createddate "CreatedDate:Dupe",
        ac.lastmodifieddate "LastModifiedDate:Dupe",
        u.name "CreatedBy:Dupe",
        u1.name "LastModifiedBy:Dupe",
        ac.aqb__source__c "Source:Dupe",
        ac.aqb__linkedto__c
        --row_number() over(partition by "ContactID:Dupe" order by "CreatedDate:Dupe" desc ) seq
        from(select a.*,
                    regexp_replace(lower(trim(a.aqb__phone__c)), '[^a-z0-9]', '') "Formatted Phone"
                    from salesforce_db.salesforce_sch.aqb__aqaddress__c a
                    )ac
        left join salesforce_db.salesforce_sch.recordtype r on r.id = ac.recordtypeid
        left join salesforce_db.salesforce_sch.user u on u.id = ac.createdbyid
        left join salesforce_db.salesforce_sch.user u1 on u1.id = ac.lastmodifiedbyid
        left join salesforce_db.salesforce_sch.contact c on c.id = ac.aqb__contact__c
        left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
        where --"ContactID:Dupe" is not null --and ac.aqb__account__c is not null
        r.name = 'Phone'
        --and c.aqb__type__c <> 'Student'
),

Dupes as
( select * from (
       select * from contact_ph cp
            )dd
       join ac_dupes acd on left("Contact SF ID",15) = "Linked To:Dupe" 
       and "Formatted Phone" = "Formatted Phone:Dupe"
       order by 2,3,1
)

select 
       "Contact SF ID",
       "Contact Phone",
       --"Phone Preference",
       "Formatted Phone",

       listagg("Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Phones:Dupes",
       listagg("Formatted Phone:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Formatted Phones:Dupes",
       listagg("Alt Channel SF ID:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc)"Alt Channel ID:Dupes",
       listagg("Alt Channel Name:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Alt Channel Names:Dupes",
       listagg("Status:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Status:Dupes",
       listagg("Type:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Type:Dupes",
       listagg("CreatedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedBy:Dupes",
       listagg("CreatedDate:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "CreatedDate:Dupes",
       listagg("LastModifiedBy:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedBy:Dupe",
       listagg("LastModifiedDate:Dupe",',') within group (order by "LastModifiedDate:Dupe" desc) "LastModifiedDate:Dupe",
       listagg("Source:Dupe", ',') within group (order by "LastModifiedDate:Dupe" desc) "Source:Dupes",
       count("Alt Channel SF ID:Dupe") "No of Dupes"
       from dupes d
       group by all
       order by 2,3,1

) select * from duplicates d;

TAP - Advancement Portfolio Product

7.2.2 Contact Mobile Duplicates in Alternate Channel

Analytics

Analyst Notes

Count of duplicate 'Mobile' in Alternate Channel (09.5.2024)

Related content