/* 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 spSP_advancementADVANCEMENT_dbDB.tdaTDA_schSCH.altchannelALTCHANNEL_phonePHONE_dedupeDEDUPE_vwVW
as
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 "CreatedDateLastModifiedDate: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 "CreatedDateLastModifiedDate: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 "CreatedDateLastModifiedDate: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 "CreatedDateLastModifiedDate: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 createdCreated by AQ, Dupe Count = 1User' "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')
and "CreatedBy:Primary" = 'AQConversion'
and "No of Dupes"
= 1
),
Segment_02 as
(
select '02' "Segment Order",'Contact Primary Phone NOT created by AQ/mule_svc Dupe Count = 1integration' "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', 'AQConversion', 'Student Integration User')
),
Segment_03 as
(
select '03' "Segment Order",'ContactAccount Primary Phone NOT createdCreated by mulesoft, Dupe Count > 1User' "Segment Name",
acdaad.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', 1) "Alt Channel ID:Dupe1"
from aq_contactaccount_duplicates acdaad
where --"No of Dupes" >= 1
and
"CreatedBy:Primary" not in ('mule_svc', 'Student Integration User')
),
Segment_04 as
(
select '04' "Segment Order",'ContactAccount Primary Phone created by mule_svc' "Segment Name",
acdaad.*,
split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1",
split_part("Alt Channel ID:Dupes", ',', -1) "Alt Channel ID:Dupe1"
from aq_contactaccount_duplicates acdaad
where "CreatedBy:Primary" in('mule_svc', 'Student Integration User', 'mule_svc')
),
select Segment_05* asfrom ( select '05' "Segment Order", 'Account Primary Phone created by AQ, Dupe Count = 1' "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" not in ('Student Integration User','mule_svc')
and "CreatedBy:Primary" = 'AQConversion'
and "No of Dupes" = 1
),
Segment_06 as
(select '06' "Segment Order",'Account Primary Phone NOT created by AQ/mule_svc Dupe Count = 1' "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
and "CreatedBy:Primary" not in ('mule_svc', 'AQConversion', 'Student Integration User')
),
Segment_07 as
(
select '07' "Segment Order",'Account Primary Phone NOT created by mulesoft, Dupe Count > 1' "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
and "CreatedBy:Primary" not in('Student Integration User','mule_svc')
),
Segment_08 as
(
select '08' "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
* from Segment_01
union all
select * from Segment_0402
union all
select * from Segment_0503
union all
select * from Segment_06
union all
select * from Segment_07
union all
select * from Segment_0804
); |