/* 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.
*/
--create or replace view sp_advancement_db.tda_sch.altchannel_phone_dedupe_vw 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__statusaccount__c "StatusAccountID:Primary",
regexp_replace(split_part(ac.aqb__typelinkedto__c "Type , '"',2), '/','') "Linked To:Primary",
ac.aqb__phonestatus__c "PhoneStatus: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 "ContactIDLinked To:Primary", "Formatted Phone:Primary" order by "CreatedDate: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__contact__c "Linked To:Primary"
left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
where "ContactID:Primary"
is not null and r.name r.name = 'Phone'
and c.aqb__type__c <> 'Student'
-- and "Status("Linked To:Primary" = 'Good'
--and a.aqb__accounttype__c = 'Household' 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__statuslinkedto__c "Status:Dupe",
, '"',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 "ContactIDLinked To: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 left(c.id,15) = ac.aqb__contact__c "Linked To:Dupe"
left join salesforce_db.salesforce_sch.account a on a.id = ac.aqb__account__c
where "ContactID:Dupe" is not null
and r.name = 'Phone' and ("Linked and c.aqb__type__c <> 'Student'
To:Dupe" like '003%')
--and a.aqb__accounttype__c = 'Household'
),
Dupes as
( select *
from (
select * from ac_primary acp
where acp.seq = 1
)dd
join ac_dupes acd on "ContactIDLinked To:Primary" = "ContactIDLinked To:Dupe"
where "Alt Channel Name:Primary" <> "Alt Channel Name:Dupe"
and "Formatted Phone:Primary" like= "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:Dupes"
from dupes d
--where "CreatedBy:Primary" = 'mule_svc'group by all
group by all
order by 2,3,1
),
Segment_01 as
(
select 'Segment 01' "Segment Name",
d.*, '' "LastModifiedDate:Dupe1" from duplicates d
where "CreatedBy:Primary" <>'mule_svc'
and "CreatedBy:Primary" = 'AQConversion'
and "No of Dupes:Dupes" = 1
),
Segment_02 as
(select 'Segment 02' "Segment Name",
d.*, '' "LastModifiedDate:Dupe1" from duplicates d
where "No of Dupes:Dupes" = 1
and "CreatedBy:Primary" not in ('mule_svc', 'AQConversion')
),
Segment_03 as
(
select 'Segment 03' "Segment Name",
d.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1" from duplicates d
where "No of Dupes:Dupes" > 1
and "CreatedBy:Primary" <> 'mule_svc'
),
Segment_04 as
(
select 'Segment 04' "Segment Name",
d.*, split_part("LastModifiedDate:Dupe", ',', 1) "LastModifiedDate:Dupe1" from duplicates d
where "CreatedBy:Primary" = 'mule_svc'
)
select * from ( select * from Segment_01order 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 "CreatedDate: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 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 "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 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
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 AQ, Dupe Count = 1' "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" 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 = 1' "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",'Contact Primary Phone NOT created by mulesoft, Dupe Count > 1' "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_04 as
(
select '04' "Segment Order",'Contact Primary Phone created by mule_svc' "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_05 as
(
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
select * from Segment_04
union all
select * from Segment_05
union all
select * from Segment_0206
union all
select * from Segment_0307
union all
select * from Segment_0408
); |