Versions Compared

Version Old Version 52 New Version Current
Changes made by

Binila Russel

Binila Russel

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

...

...

...

...

...

...

...

...

...

...

...

...

...

Table of Contents

Table of Contents
minLevel1
maxLevel6

...

outlinefalse

...

style

...

decimal

...

typelist

...

printabletrue

Analyst Notes

Panel
panelIconIdatlassian-info
panelIcon:info:
panelIconText:info:
bgColor#E6FCFF

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:

  1. ‘Primary Phone’ created by mulesoft

  2. ‘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.

...

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

...

Expand
titlePhase1: 'Primary Phone' Records NOT created by mulesoft - where No of Duplicates = 1 and Primary Phone was CreatedBy = AQConversion

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

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

...

Expand
titlePhase 3: 'Primary Phone' Records NOT created by mulesoft - where No of Duplicates = 1 and Primary Phone was NOT CreatedBy AQConversion

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

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

...

Expand
titlePhase 4: De-duplicate records not created by mulesoft where No of Duplicates > 1 and 'Primary Phone' NOT created by AQConversion

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

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

...

Expand
titlePhase 4: 'Primary Phone' Records created by mulesoft

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

 

...