Attention: Confluence is not suitable for the storage of highly confidential data. Please ensure that any data classified as Highly Protected is stored using a more secure platform.
If you have any questions, please refer to the University's data classification guide or contact ict.askcyber@sydney.edu.au
2. Data Quality Rules and DQ Dashboards
- albie.roets
- Manpreet Sidhu
DQ Dashboards and Reports setup structure
Data quality rules are stored in dashboards that represent the natural life cycle.
Contacts and Accounts | Mailing Addresses | Opportunities | Campaigns | Education | Charts of Accounts | Gift And Pledges |
|---|---|---|---|---|---|---|
https://sydneyuni-adv.lightning.force.com/lightning/r/Dashboard/01Z8v0000005ayGEAQ/view | https://sydneyuni-adv.lightning.force.com/lightning/r/Dashboard/01Z8v0000005ayLEAQ/view | https://sydneyuni-adv.lightning.force.com/lightning/r/Dashboard/01Z8v0000005ayfEAA/view |
Data Quality Reports are stored in folders corresponding to the dashboards
(See Report-of-Reports for full details of reports in folders: https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KPHKEA4/view?queryScope=userFolders )
New Data Quality Rules Requested
Dashboard / Object | Rule / Logic | Requested By / When | RACI | Status / Comments |
|---|---|---|---|---|
Contacts and Accounts - DQ | Duplicate Contacts | @Yeng Sembrano , Feb 20, 2023 | @albie.roets | DONE (DQ_01_007 - Feb 28, 2023) |
Contacts and Accounts - DQ | Duplicate Alternate Channels | @Yeng Sembrano , Feb 20, 2023 | @albie.roets | IN PROGRESS |
Mailing Addresses - DQ | Telephone Preference | @albie.roets , Mar 2, 2023 | @albie.roets | NOT STARTED |
Mailing Addresses - DQ | Telephone DQ - Populated but < 9 Characters | @albie.roets , Mar 2, 2023 | @albie.roets | NOT STARTED |
Mailing Addresses - DQ | Address Type: PO Box <> Home Address | @AlainGasquet , Mar 2, 2023 | @albie.roets | NOT STARTED - Catch up with @Manpreet Sidhu & @satparkash.kaushik tomorrow |
Data Quality Rules
Dashboard / Object | Rule Number | Rule Name | Business Logic | Business Rule / How to fix | Responsible | Freq | Jarvis Report Link | Cause | Outstanding issues / Next Steps |
|---|---|---|---|---|---|---|---|---|---|
Contacts and Accounts - DQ | DQ_01_001 | Contactable Alumni Report | Principle Report: ALUMNI (make sure Alumni numbers are correct) - DECEASED - DO NOT CONTACT - LOST (phone + email + address isnull) - BAD DATA (phone + email + address is bad)= CONTACTABLE ALUMNI |
| N/A - Report Only: All Data Quality updates that we do will impact on the Contactable Alumni | Ad-Hoc | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KIfiEAG/view | N/A | N/A |
Contacts and Accounts - DQ | DQ_01_002 | SITS Integration Errors | Integration Errors: SITS |
| Pat Dennis | Daily | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KGXOEA4/view | SITS Integration | |
Contacts and Accounts - DQ | DQ_01_003 | SWIFT Integration Errors | Integration Errors: SWIFT |  | @Binila Russel | Daily | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KKyoEAG/view | SWIFT Integration | |
Contacts and Accounts - DQ | DQ_01_004 | USYD Duplicate Accounts | Accounts: Duplicates |
 | GA Team | Weekly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KQ0aEAG/view | Duplicate Accounts seems to be created by Gift Admin team when they create a new donor - | Clear False Positives from Duplicate Accounts Report, @albie.roets , Mar 3, 2023 Analyse how do 'Duplicate Record Set' functionality work, List Rules, Audit the rules to make sure they are accurate, https://sydneyuni.atlassian.net/wiki/spaces/PHOEN/pages/2895119841 @Manpreet Sidhu , Mar 10, 2023. Analyse False Positive on Duplicate Accounts for input to https://sydneyuni.atlassian.net/wiki/spaces/PHOEN/pages/2895119841, @Manpreet Sidhu , Mar 8, 2023 Process for getting GA Team to action duplicate accounts, @albie.roets , Mar 10, 2023 Action Duplicate Accounts Process - i.e. load to cases and mark actioned, @Binila Russel Mar 17, 2023 |
Contacts and Accounts - DQ | DQ_01_005 | Missing Account Types | Accounts: Missing Account Types | @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2RlEAK/view | User Error: Manual Account Creation | None - Data Steward Regular Processing | |
Contacts and Accounts - DQ | DQ_01_006 | Households without a Primary/Sec contact | Accounts: (Household) account does not have a Primary or Secondary Contact assigned error: | All Household accounts needs to have a Primary / Secondary contact attached to it - (Not needed for Business Accounts) - refer to business team to fix. | @Miranda Adams | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2ThEAK/view | User Error: Manual Account Creation | None - Data Steward Regular Processing |
Contacts and Accounts - DQ | DQ_01_007 | USyd Duplicate Contacts | Accounts: Duplicates |
| GA Team | Weekly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KQ0fEAG/view | Duplicate Accounts seems to be created by Gift Admin team when they create a new donor - | |
Contacts and Accounts - DQ | DQ_01_008 | Contacts where Primary Contact Type is NULL | Contact Type: Where Contact Type isnull | @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2k4EAC/view | User Error | None - Data Steward Regular Processing | |
Contacts and Accounts - DQ | DQ_01_009 | Contacts who donated and not Donor | Contact Type: Should be Donor as Primary or Secondary Contact Type | GA Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K6WkEAK/view | User Error | ||
Contacts and Accounts - DQ | DQ_01_010 | Contact with Grad Year but not Alumni | Contact Type: Should it be Alumni - Contact with Grad Year but not Alumni | @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2jaEAC/view | User Error | None - Data Steward Regular Processing | |
Contacts and Accounts - DQ | DQ_01_011 | Age <18 | DOB: Check DOB | @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2jzEAC/view | User Error | None - Data Steward Regular Processing | |
Contacts and Accounts - DQ | DQ_01_012 | Age >= 105 (Deceased?) | Deceased Contacts: Presumed Deceased at 105 - needs to be marked "Do Not Contact" + Deceased in the System |  | GA Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2juEAC/view | Non Processing of death data | |
Contacts and Accounts - DQ | DQ_01_013 | Non Deceased Contacts with Death Data(i) | Deceased Contacts: Non Deceased Contacts with Death Data with Student Id (Interfaced from SITS) | | GA Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KNPEEA4/view | Non Processing of death data | |
Contacts and Accounts - DQ | DQ_01_014 | Living Contact Attached to an Estate | Deceased Contacts: List of Living Contact Attached to an Estate (Not the Executor/Solicitor) | GA Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2jkEAC/view | Non Processing of death data | ||
|
|
|
|
|
|
|
|
|
|
Mailing Addresses - DQ | DQ_02_001 | Alumni w @uni.sydney.edu.au email | Email DQ: Once a student graduates and becomes and Alumni Student Admin disables email account. |
| @Binila Russel | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K9u0EAC/view |
| None - Data Steward Regular Processing |
Mailing Addresses - DQ | DQ_02_002 | Email entered into the wrong field | Email DQ: Email entered into the wrong field - Non Editable field Email is a vlookup field based on the Email Preference that pulls the value from one of the 4x fields. |
| @Binila Russel | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K5sfEAC/view |
| |
Mailing Addresses - DQ | DQ_02_003 | Blank Email Preference | Email preference field needs to be populated to allow for email calculated email value (in the field below) to be appropriately populated. Attached is a list of the correct Email preferences that needs to be uploaded into Jarvis. (Based on which field the email value were found in) |
| @Binila Russel | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K5TkEAK/view |
| |
Mailing Addresses - DQ | DQ_02_004 | Email_Typos | Email addresses with typo’s in them, i.e:
|
| @Binila Russel | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K6FAEA0/view |
| None - Data Steward Regular Processing |
Mailing Addresses - DQ | DQ_02_005 | Bounced e-mail addresses | Reporting Only: All active email addresses on the contact page to analyse bounced emails |  | @Binila Russel | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KPH0EAO/view | Natural Attrition | None - Data Steward Regular Processing |
Mailing Addresses - DQ |
| Telephone Preference |
| Business Rule:
| @Binila Russel | Monthly |
| iMods integration | |
Mailing Addresses - DQ | DQ_02_006 | Blank Contact Mailing Address Type | Address DQ: Blank Contact Mailing Address Type (Business Rule: Default to 'Home Address' for Household and 'Business Address' for Organisations) |
Rules:
| @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2kOEAS/view | User error | |
Mailing Addresses - DQ | DQ_02_007 | Primary Contract - Address Preference | Address DQ: Primary Contact where Address Preference is not Account Mailing | Rules:
| @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2jQEAS/view | User error |
|
Mailing Addresses - DQ | DQ_02_008 | Blank Contact Mailing Address Preference | Address DQ: Blank Contact Mailing Address Preference (Business Rules: Household = Auto Defaulted to ‘Account Mailing’ / Corporation = Business Mailing) | Rules:
| @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2kEEAS/view | User error | |
Mailing Addresses - DQ | DQ_02_009 | Blank Contact Mailing Address Status | Address DQ: Blank Contact Mailing Address Status | @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2kJEAS/view | Historic Data Clean-up / User Error | None - Data Steward Regular Processing | |
Mailing Addresses - DQ | DQ_02_010a | Unit Addresses for Reformatting | Address DQ: Street - Contact Mailing Addresses with 'Unit' or 'U' in the contact mailing street name. | Rules:
| @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KP3hEAG/view | User error / integrations | |
Mailing Addresses - DQ | DQ_02_010b | Contact Mailing Address w Corrupt St | Address DQ: Street | Rules:
| @Manpreet Sidhu @pat.dennis | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KP3IEAW/view | User Error / Integrations /Historical data | DQ Software Procurement, @Brad Fernandes (Unlicensed) , Mar 31, 2023 |
Mailing Addresses - DQ | DQ_02_012 | Contact Mailing Address w/o City | Address DQ: City | Rules:
https://sydneyuni.atlassian.net/browse/TAP-110 | @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KP3SEAW/view | SITS Data | DQ Software Procurement, @Brad Fernandes (Unlicensed) , Mar 31, 2023 |
Mailing Addresses - DQ | DQ_02_013 | Contact Mailing Address w Corrupted City | Address DQ: City | Rules:
| @Manpreet Sidhu @pat.dennis | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KP3NEAW/view | User Error/ SITS Data / Historical data | DQ Software Procurement, @Brad Fernandes (Unlicensed) , Mar 31, 2023 |
Mailing Addresses - DQ | DQ_02_014 | Contact Mailing Address w Corrupted Stat | Address DQ: State - This report looks for International Postals with corrupted Australian states. Note: WA is omitted as WA is state abbreviation for Washington in United States | Rules:
| @Manpreet Sidhu @pat.dennis | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K9NQEA0/view | SITS Data | DQ Software Procurement, @Brad Fernandes (Unlicensed) , Mar 31, 2023 |
Mailing Addresses - DQ | DQ_02_015 | Contact Mailing Address w/o State | Address DQ: State | Rules:
| @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KP3cEAG/view | Historic Data / User Error | DQ Software Procurement, @Brad Fernandes (Unlicensed) , Mar 31, 2023 |
Mailing Addresses - DQ | DQ_02_016 | Contact Mailing Address w/o Post Code | Address DQ: Post Code | Rules:
| @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KP3XEAW/view | Historical Data | DQ Software Procurement, @Brad Fernandes (Unlicensed) , Mar 31, 2023 |
Mailing Addresses - DQ | DQ_02_017 | Corrupted Country | Address DQ: Country | Rules:
| @Manpreet Sidhu @pat.dennis | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K9dYEAS/view | SITS Data / Historic Data / User Error | DQ Software Procurement, @Brad Fernandes (Unlicensed) , Mar 31, 2023 |
Mailing Addresses - DQ | DQ_02_018 | Contact Mailing Address w/o Country | Address DQ: Country | Rules:
| @Manpreet Sidhu | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K8VlEAK/view | Historic data | DQ Software Procurement, @Brad Fernandes (Unlicensed) , Mar 31, 2023 |
Mailing Addresses - DQ | DQ_02_020 | Student Accommodation to Delete | Address DQ: At the point that contact type moves from Graduand to Alumni, all Student Accommodation addresses should be inactivated / deleted or updated to their new address from the main page. | Rules:
| @Manpreet Sidhu @pat.dennis | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K9G0EAK/view | SITS Data / Historic Data | None - Data Steward Regular Processing |
|
|
|
|
|
|
|
|
|
|
Opportunities - DQ | DQ_03_001 | Ongoing Opportunities with gift Amount | Opportunities NOT Closed Won NOT Gift Handover and NOT Verbal Acceptance WITH Gift Amount documented |
| Opportunity Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4GaEAK/view | User Error | |
Opportunities - DQ | DQ_03_002 | Opps at Sol stage w/o Prospect Pool | Opportunities with NO Prospect Pool at solicitation stage and beyond |
| Opportunity Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4GfEAK/view | User Error | ditto |
Opportunities - DQ | DQ_03_003 | Closed Won Opps without Gift | Closed Won Opps (G/P) with no Gift/Pledge attached |
| Opportunity Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4GpEAK/view | User Error | ditto |
Opportunities - DQ | DQ_03_004 | Closed Won Opps w Close date in future | Closed Won Opps (G/P) with no Close date in future |
| Opportunity Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4GuEAK/view | User Error | ditto |
Opportunities - DQ | DQ_03_005 | Ongoing Opps w Close date in Past | Ongoing Opps (G/P) with no Close date in Past |
| Opportunity Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4GzEAK/view | User Error | ditto |
|
|
|
|
|
|
|
|
|
|
Education - DQ | DQ_05_001 | Alumni no primary degree | Alumni with education record but no Primary degree |
| @Manpreet Sidhu | Monthly | User Error / Historical data | ||
Education - DQ | DQ_05_002 | Alumni: Education with Empty School | This report contains degree-earning education records without schools to be investigated |
| @Manpreet Sidhu | Monthly | User Error / Historical data | ||
|
|
|
|
|
|
|
|
|
|
Charts of Accounts - DQ | DQ_06_001 | Designation Acc w NO Classification | All Designation accounts with designation = Other" |
| Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4ZrEAK/view | User Error | |
Charts of Accounts - DQ | DQ_06_002 | Designation Acc w Classification "Other" | All Designation accounts with designation = Other" |
| Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4ZmEAK/view | User Error | ditto |
Charts of Accounts - DQ | DQ_06_003 | Gift Record Type Vs Designation | Gift Record Type Vs Designation Type |
| Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KCpBEAW/view | User Error | ditto |
|
|
|
|
|
|
|
|
|
|
Gift And Pledges - DQ | DQ_07_001 | Finite Active Pledges - Inconsistent Amn | List of Active pledges (non-recurring) where Pledge amount is not equal to funds raised | GA Team Processes | Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4NWEA0/view | User Error | |
Gift And Pledges - DQ | DQ_07_002 | Transactions with Blank Acknowledgement |
| GA Team Processes | Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4NREA0/view | User Error | |
Gift And Pledges - DQ | DQ_07_003 | GIK transaction data integrity check | This checks that gifts in kind have been entered as either personal or real property | GA Team Processes | Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K9LZEA0/view | User Error | |
Gift And Pledges - DQ | DQ_07_004 | Transactions w/o Designation Account No |
| GA Team Processes | Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4NlEAK/view | User Error | |
Gift And Pledges - DQ | DQ_07_005 | Pledges Paid with a Balance | All Gift / Pledges paid with a remaining balance due | GA Team Processes | Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4NMEA0/view | User Error | |
Gift And Pledges - DQ | DQ_07_006 | T project code not recorded as Endowed | Used to check if there are chart of Accounts that have a 'T' prefix project code that do not have an Account Type of 'Endowment' | GA Team Processes | Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K9LjEAK/view | User Error | |
Gift And Pledges - DQ | DQ_07_007 | Planned Gift with Incorrect Record Types |
| GA Team Processes | Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4NgEAK/view | User Error | |
Gift And Pledges - DQ | DQ_07_008 | Inconsistent Campaign Appeal | List of Transactions where Campaign Appeal is inconsistent with Gift/Pledges Campaign Appeal. Transaction Creation Date is on of after January 25, 2022. | GA Team Processes | Gift Admin Team | Monthly | https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K4MJEA0/view | User Error |
Meeting Minutes / Actions:
Date | Description | Comments |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|