7.2.1 Alternate Channel Phone De-duplication

Table of Contents

Table of Contents
1 Table of Contents 2 Analyst Notes 3 Count of 'Primary Phone' in Alternate Channel with duplicates (09.5.2024) 4 Segment 1 : De-duplication of Alternate Channel Phone records linked to Contacts - Not Created by Mulesoft 5 Segment 2 : De-duplication of Alternate Channel Phone records linked to Contacts - Created by Mulesoft (In progress) 6 Appendix

Analyst Notes

This page contains documentation on analysis, proposed steps to de-dupe phone numbers in Alternate Channels (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

Most recently created record of a unique phone number on each contact was considered ‘Primary Phone’ and any previous occurrence of the same number was considered ‘Duplicates’.

‘Primary Phone’ was used as an anchor to look up potential duplicates for each ‘Primary Phone’.

Duplicates found were grouped by ‘Primary Phone’ and segmented into two:

‘Primary Phone’ created by mulesoft
‘Primary Phone’ not created by mulesoft

Exclusions: Contact Type - ‘Student’ , 'Graduand'

Overarching Rule:
Given the Status of Primary Record is Former/Good:

For records that have been modified:

Given a TAP user modified any of the duplicate records and the last modified date of that record is the latest, the record with the latest last modified date will be retained.
For records that have never been modified:

Given a TAP user created the most recent record, the data will be considered as recently validated by a user and will be retained.
Given, the record was created by a System user and not modified by a TAP user, the oldest record will be retained.

Count of 'Primary Phone' in Alternate Channel with duplicates (09.5.2024)

Segment	Count of Unique Phone	Count of Duplicates	Total Records in Alternate Channel (Contact)

Segment	Count of Unique Phone	Count of Duplicates	Total Records in Alternate Channel (Contact)
'Primary Phone' Created by mulesoft	77,114	109,693	186,807
'Primary Phone' Not created by mulesoft	156,086	223,790	379,876
Grand Total	165,242	236,145	566,683

Segment 1 : De-duplication of Alternate Channel Phone records linked to Contacts - Not Created by Mulesoft

De-duplication of Alternate Channel Phone records linked to Contacts that are not Created by Mulesoft will be done in number of phases as follows due to the complexity in data format(Status, last modified etc.,)

Rule:

If ‘Primary Phone' was created by AQConversion and was never modified by a user since time of record creation, retain the 'Duplicate Phone’.

If ‘Primary Phone' was created by AQConversion and was later modified by a user since time of record creation, retain 'Primary Phone’

De-duplication rule is based on Status for ‘Primary Phone’ numbers
Records were not created by mulesoft and only one duplicate entry was found
‘Primary Phone’ was created by AQConversion
“Duplicate Phone' was created a by User
Record Count = 83,400 (08.5.2024)

	Primary Phone Status(Newest)	Duplicate phone Status(Oldest)	Count	Record to Keep	Analyst Comments

	Primary Phone Status(Newest)	Duplicate phone Status(Oldest)	Count	Record to Keep	Analyst Comments
1	Former	Good	77190	Oldest Record
2	Former(User Modified)	Good	89	Newest Record
3	Former	Bad	48	Oldest Record	Change Status to Former
4	Former	Last Known	2	Oldest Record	Change Status to Former
5	Former	Needs Verification	1	Oldest Record	Change Status to Former
6	Good	Good	232	Oldest Record
7	Good(User Modified)	Good	206	Newest Record
8	Good	Bad	42	Oldest Record	Change Status to Former
9	Good(User Modified)	Bad	55	Newest Record
10	Good	Needs Verification	3	Oldest Record	Change Status to Former
11	Good(User Modified)	Needs Verification	2	Newest Record
12	Deceased	Deceased	462	Oldest Record
13	Bad	Bad/Good	2	Newest Record

Approver Sign-off:

Approvers are kindly required to check the tick box and comment ‘Approved’ to sign-off.

Alain Gasquet

Jessica Wood

De-duplication rule based on Status for ‘Primary Phone’ numbers not created by mulesoft or AQConversion and only one duplicate entry was found

Record Count = 8309(13.4.2024)

	Primary Phone Status(Newest)	Duplicate Phone Status(Oldest)	Count	Record to Keep	Analyst Comments

	Primary Phone Status(Newest)	Duplicate Phone Status(Oldest)	Count	Record to Keep	Analyst Comments
1	Former	Good	5257	Newest record
2	Former	Former	629	Oldest record
3	Former	Bad	113	Oldest record
4	Former	Needs Verification	4	Newest record
5	Former	Last Known	1	Newest record
6	Former	Deceased	4	Oldest record
7	Good	Good	606	Newest record
8	Good	Former	11	Newest record
9	Good	Bad	460	Newest record
10	Good	Needs Verification	1	Newest record
11	Bad	Bad	129	Oldest record	Change Status to Former
12	Bad	Good	45	Newest record	Change Status to Former
13	Bad(Modified recently)	Good	12	Newest record	Change Status to Former
14	Bad(Modified recently)	Bad	20	Oldest record
15	Bad	Bad(Modified recently)	87	Oldest record	Retain oldest to keep recent validation. Change Status to Former
16	Bad	Good(Modified recently)	20	Oldest record	Retain oldest to keep recent validation.
17	Deceased	Deceased	33	Oldest record
18	Last Known	Good	1	Newest record

Approver Sign-off:

Approvers are kindly required to check the tick box and comment ‘Approved’ to sign-off.

Alain Gasquet

Jessica Wood

De-duplication rule based on Status for ‘Primary Phone’ numbers not created by mulesoft and more than 1 duplicate entries were found

Rule: Retain the record with the most recent LastModifiedDate.

Record Count: 64,292 (08.05.2024)

	Primary Phone Status(Newest)	Primary record Modified after Dupes by TAP User?	Count	Record to Keep	Analyst Comments

	Primary Phone Status(Newest)	Primary record Modified after Dupes by TAP User?	Count	Record to Keep	Analyst Comments
1	Former	Yes	62,466	Newest Record
2	Former	No	564	Modified Record	Keep the most recently modified record
3	Good	Yes	118	Newest record
4	Good	No	17	Modified Record	Keep the most recently modified record
5	Deceased	No	44	Oldest Record

Approver Sign-off:

Approvers are kindly required to check the tick box and comment ‘Approved’ to sign-off.

Alain Gasquet

Jessica Wood

Segment 2 : De-duplication of Alternate Channel Phone records linked to Contacts - Created by Mulesoft (In progress)

Records Created by Mulesoft will be de-duped separately as SITS integration(mulesoft) upserts records previously created by itself in Alternate Channels. These records will be reviewed in detail and include Alternate channel History object records as part of the analysis to enable us to determine the record to be retained.

Rule: Isolate for manual review. If the Primary Phone Status is ‘Former’ mule_svc updated Phone on Contact and archived the old value on Contact as 'Former'.
Retain most recently created record by CreatedDate

Record Count = 97,743 (13.05.2024)

Newest Status	Oldest Records	Record to keep	Count
Former	Former/Good	Keep Newest as Former	37,197
Former	Bad	Keep Oldest record with Status Bad	2,541
Deceased	Deceased	Keep Oldest as Deceased	12
Good	Good	Keep Newest as Good	46,215
Good	Former	Keep Oldest record with Status Former	11,274
Good	Needs Verification	Keep Oldest record with Status Needs Verification	64
Good	Deceased	Keep Oldest record with Status Deceased	8

Appendix

Snapshot of Segment Summary taken from Snowflake -

Snowflake Script used for data prep, analysis & cleanse:

/* Alternate Channel De-Duplication - Marketing Automation/Preference Centre

Business Owners: Jessica Wood, Alain Gasquet

Developer Notes:
Records were trimmed and cleaned using regular expression replace
If number starts with +612, +6102, +613, +6103, +614, +6104, +617, +6107, +618, +6108 the numbers were considered Australian and +61 was replaced by 0. 
For each unique formatted phone number per contact, sorted in descending order, dupliacate records were identified if there was an exact match.
-- Modified to monitor new duplicate records created in Alternate Channel
*/


--create or replace view SP_ADVANCEMENT_DB.TDA_SCH.ALTCHANNEL_PHONE_DEDUPE_VW


with aq_contact_duplicates as(
with ac_primary as
(
select ac.id "Alt Channel SF ID:Primary",
        ac.name "Alt Channel Name:Primary",
        ac.aqb__contact__c "ContactID:Primary",
        ac.aqb__account__c "AccountID:Primary",
        regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Primary",
        ac.aqb__status__c "Status:Primary",
        ac.aqb__type__c "Type:Primary",
        ac.aqb__phone__c "Phone:Primary",
        
        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:Primary",
        ac.createddate "CreatedDate:Primary",
        ac.lastmodifieddate "LastModifiedDate:Primary",
        u.name "CreatedBy:Primary",
        u1.name "LastModifiedBy:Primary",
        ac.aqb__source__c "Source:Primary",
        row_number() over(partition by "Linked To:Primary", "Formatted Phone:Primary" order by "LastModifiedDate:Primary" 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 left(c.id,15) = "Linked To:Primary"
        left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
        where c.aqb__type__c not in ('Student', 'Graduand') and
        r.name = 'Phone' and ("Linked To:Primary"  like '003%')
        order by 3,7,14

),


ac_dupes as
(
select ac.id "Alt Channel SF ID:Dupe",
        ac.name "Alt Channel Name:Dupe",
        ac.aqb__contact__c "ContactID:Dupe",
        regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To: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 "Linked To:Dupe" order by "LastModifiedDate: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 left(c.id,15) = "Linked To:Dupe"
        left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
        where c.aqb__type__c not in ('Student', 'Graduand') and
        r.name = 'Phone' and ("Linked To:Dupe"  like '003%')
        
),

Dupes as
( select * from (
       select * from ac_primary acp 
       where acp.seq = 1
            )dd
       join ac_dupes acd on "Linked To:Primary" = "Linked To:Dupe" 
       where "Alt Channel Name:Primary" <> "Alt Channel Name:Dupe"
      and "Formatted Phone:Primary" = "Formatted Phone:Dupe"
       order by 2,3,1
)

select "Alt Channel SF ID:Primary",
       "Alt Channel Name:Primary",
       "ContactID:Primary",
       "AccountID:Primary",
       "Phone:Primary",
       "Formatted Phone:Primary",
       "Status:Primary",
       "Type:Primary",
       "LastModifiedDate:Primary",       
       "CreatedDate:Primary",
       "LastModifiedBy:Primary",
       "CreatedBy:Primary",
       "Source:Primary",       
       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("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("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

),


------------------------------------------
/* Account Phone duplicates in Alternate Channel */

aq_account_duplicates as(
with account_primary as
(
select ac.id "Alt Channel SF ID:Primary",
        ac.name "Alt Channel Name:Primary",
        ac.aqb__contact__c "ContactID:Primary",
        ac.aqb__account__c "AccountID:Primary",
        regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To:Primary",
        ac.aqb__status__c "Status:Primary",
        ac.aqb__type__c "Type:Primary",
        ac.aqb__phone__c "Phone:Primary",
        
        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:Primary",
        ac.createddate "CreatedDate:Primary",
        ac.lastmodifieddate "LastModifiedDate:Primary",
        u.name "CreatedBy:Primary",
        u1.name "LastModifiedBy:Primary",
        ac.aqb__source__c "Source:Primary",
        ac.aqb__linkedto__c,
        row_number() over(partition by "Linked To:Primary", "Formatted Phone:Primary" order by "LastModifiedDate:Primary" 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 left(c.id,15) = ac.aqb__linkedto__c
        left join salesforce_db.salesforce_sch.account a on left(a.id,15) = "Linked To:Primary"
        where c.aqb__type__c not in ('Student', 'Graduand') and r.name = 'Phone' and ("Linked To:Primary"  like '001%')
        order by 3,7,14

),


account_dupes as
(
select ac.id "Alt Channel SF ID:Dupe",
        ac.name "Alt Channel Name:Dupe",
        ac.aqb__contact__c "ContactID:Dupe",
        ac.aqb__account__c "AccountID:Dupe",
        regexp_replace(split_part(ac.aqb__linkedto__c , '"',2), '/','') "Linked To: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 "Linked To:Dupe" order by "LastModifiedDate: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 left(c.id,15) = ac.aqb__linkedto__c
        left join salesforce_db.salesforce_sch.account a on left(a.id,15) = "Linked To:Dupe"
        where c.aqb__type__c not in ('Student', 'Graduand') and
        r.name = 'Phone' and ("Linked To:Dupe"  like '001%')
),

Dupes as
( select * from (
       select * from account_primary acp 
       where acp.seq = 1
            )dd
       join account_dupes acd on "Linked To:Primary" = "Linked To:Dupe" 
       where "Alt Channel Name:Primary" <> "Alt Channel Name:Dupe"
       and "Formatted Phone:Primary" = "Formatted Phone:Dupe"
       order by 2,3,1
)

select "Alt Channel SF ID:Primary",
       "Alt Channel Name:Primary",
       "ContactID:Primary",
       "AccountID:Primary",
       "Phone:Primary",
       "Formatted Phone:Primary",
       "Status:Primary",
       "Type:Primary",
       "LastModifiedDate:Primary",       
       "CreatedDate:Primary",
       "LastModifiedBy:Primary",
       "CreatedBy:Primary",
       "Source:Primary",       
       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("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("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
       --where "CreatedBy:Primary" = 'mule_svc'
       group by all
       order by 2,3,1

),



Segment_01 as
(select '01' "Segment Order",'Contact Primary Phone Created by User' "Segment Name",
acd.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_contact_duplicates acd
where -- "No of Dupes" = 1 and
"CreatedBy:Primary" not in ('mule_svc', 'Student Integration User')
),


Segment_02 as
(
select '02' "Segment Order",'Contact Primary Phone created by integration' "Segment Name",
acd.*,
split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', -1) "Alt Channel ID:Dupe1"
from aq_contact_duplicates acd
where  "CreatedBy:Primary" in('mule_svc', 'Student Integration User')
),


Segment_03 as
(select '03' "Segment Order",'Account Primary Phone Created by User' "Segment Name",
aad.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_account_duplicates aad
where  --"No of Dupes" = 1
"CreatedBy:Primary" not in ('mule_svc', 'Student Integration User')
),


Segment_04 as
(
select '04' "Segment Order",'Account Primary Phone created by mule_svc' "Segment Name",
aad.*,
split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', -1) "Alt Channel ID:Dupe1"
from aq_account_duplicates aad
where  "CreatedBy:Primary" in('Student Integration User', 'mule_svc')
)

select * from ( select * from Segment_01
                union all
                select * from Segment_02
                union all
                select * from Segment_03
                union all 
                select * from Segment_04
                );

TAP - Advancement Portfolio Product

7.2.1 Alternate Channel Phone De-duplication

Analytics

Table of Contents

Table of Contents

Analyst Notes

Count of 'Primary Phone' in Alternate Channel with duplicates (09.5.2024)

Segment 1 : De-duplication of Alternate Channel Phone records linked to Contacts - Not Created by Mulesoft

Segment 2 : De-duplication of Alternate Channel Phone records linked to Contacts - Created by Mulesoft (In progress)

Appendix

Related content