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
AQ Activity Report Current State
- Liz Enright
- sankar.murugan
Summary |
|---|
Activity Report data must be reviewed and cleaned prior to moving to the Salesforce Activity object. There is inconsistency across how many core fields are used and business processes need to be reassessed prior to the configuration of Activity. |
Activity Reports Overview
Activity Reports is an Affinaquest object designed to perform the same function as the Salesforce Activity objects. Activity Reports share many of the same fields as Activity and are intended to track one-on-one engagement with Alumni, Donors, and Prospects.
Raw Data
This data was pulled on 26/1/24 from Production and analysed in PowerBI.
Fields on Activity Report Page Layouts
There are 354,790 Activity Reports as of 26/1/24.
Field | Purpose | Usage | Field Type | Page Layout | API Name |
|---|---|---|---|---|---|
Subject | Mandatory - A short outline for the engagement | Almost completely populated. 82 records with a blank subject. | Free Text | Call, Task, Event | AQB__Subject__c |
Date | Mandatory - The date on which this Activity is scheduled or due. | 703 records with no date. There are many records with dates in the mid/late-1900s as Planned Giving appear to have used this date for the probate date. | Date | Call, Task, Event | AQB__Date__c |
Substantive Activity | Indicates that this activity is substantive in moving this opportunity forward. Generally speaking, only activities checked as "Substantive Activity" are included in Opportunity metics. | Only 2.75% (9,755) ARs have Substantive Activity active. | Checkbox | Call, Task, Event | AQB__EssentialMove__c |
Report Status | Mandatory - This determines the report status, either Planned, Draft or Final. | The vast majority of ARs have the Final status. 844 are Planned and 84 are Draft. Most of the Planned and Draft ARs were created in 2021 and 2022 in the early days of Jarvis. | Picklist | Call, Task, Event | AQB__ReportStatus__c |
Status | Mandatory - The status of this Activity Report. | The vast majority of ARs have the Completed status. 379 are In Progress, 197 are Open, 69 are Cancelled, and 14 are Planned. | Picklist | Call, Task, Event | AQB__Status__c |
Result | A description of the result of the activity. This is the content of the AR. | 21,515 ARs have nothing in this field. | Rich Text Area(32768) | Call, Task, Event | AQB__Result__c |
Created By | The User who created the AR. | System Generated, every record has one. | Lookup(User) | Call, Task, Event | N/A - field label is Created By |
Activity Report Number | The unique number for each AR. | System Generated, every record has one. | Auto Number | Call, Task, Event | N/A - field label is Activity Report Number |
Record Type | The unique record type for each AR. The values are Call, Event, and Task. | System Generated, every record has one. Call Reports = 83,165 Event Reports = 38,861 Task Reports = 232,943 | Record Type | Call, Task, Event | N/A - field label is Record Type |
Type | Mandatory - The type of Activity. | 4k ARs have no Type. The majority are Email (181k), Letter (50k), and Telephone (33k). This field has many values that confuse its purpose eg Correspondence, 16/2 Strategic Move, Other. | Picklist | Call, Task, Event | AQB__Type__c |
Purpose | Mandatory - The purpose of the call or visit. | 7k ARs have no Purpose. The majority are Engagement (101k), Stewardship (90k), and Cultivation (55k). This field contains many values that are trying to achieve different purposes such as GIW values. | Picklist | Call, Task, Event | AQB__Purpose__c |
Sub-Purpose | Sub-categories for Purpose field. Generally used by ASR Team. | There are only 9.5k ARs with a Sub-Purpose. The majority of these are Meeting Request (4k), Meeting (1k), Bespoke Event Invitation (1k), and Other (1k). While this field was created for the Alumni team, over 120 users have created ARs with a completed sub-purpose. | Picklist | Call, Task, Event | USYD_Sub_Purpose__c |
Account | Mandatory - The primary account participating in this Activity. | All ARs have an Account referenced. There are ARs on 110k Accounts in Jarvis (there are 676k Account total). | Lookup | Call, Task, Event | AQB__Account__c |
Account 2 | This is a secondary account associated with an activity. Usually the giving vehicle of high value donors. | Only 1,645 ARs have an Account 2 referenced. The Account 2 field is used for 638 unique Accounts. | Lookup | Call, Task, Event |
Usyd_Account_2__c |
Opportunity | The Opportunity about which this Activity took place. | 15,737 ARs reference an Opportunity record. These ARs are on 4,496 unique Opportunities. | Lookup | Call, Task, Event | AQB__Opportunity__c |
Last Modified By | The User who last created or updated the AR. | System Generated, every record has one | Lookup(User) | Call, Task, Event | N/A - field label is Last Modified By |
Owner | The User who owns the record. This can impact read/write permissions depending on system wide settings. | System Generated, every record has one | Lookup(User,Group) | Call, Task, Event | N/A - field label is Owner |
Due Date | The date on which this Activity is scheduled or due. | 63k ARs have no Due Date. It looks like this field was used in the initial mapping from Advance CRM to Salesforce and was dropped from the Task page layout in the 2022 refresh. | Date | Event, Email | AQB__ActivityDate__c |
Priority | An indicator of the Priority of the Activity. | 1.9k ARs have a priority value. High (133), Normal (1.7k), Low (64). | Picklist | Event | AQB__Priority__c |
Summary | Short summary of this activity intended for display on related lists to facility review of all activity information. | 34k ARs have no Summary data. This data is often identical to the Result field but there are many records where they are different. This field may need manual review prior to conversion. | Text Area | Event | AQB__ActivitySummary__c |
Event | Link to the event | 15,907 ARs reference an Event (this is the AQB_Event_c object). 41 unique events are referenced in this field. | Lookup | Event | USYD_Event__c |
Objective | The objective for this activity. | 3,309 ARs have data in Objective. Many of these seem to be the same as the Result field. The volume is small so this field could be reviewed manually to preserve useful data. | Rich Text Area | Event | AQB__Objective__c |
Description | This field may be used for a report on the plans or results of the Activity. (32,000 characters). | 60k ARs have no Description data. It looks like this field was used in the initial mapping from Advance CRM to Salesforce and was dropped from page layouts in the 2022 refresh. | Long Text Area | Event | AQB__Description__c |
Fields not on Page Layouts
Field | Purpose | Usage | Field Type | API Name |
|---|---|---|---|---|
If checked, the Event is designated as an all-day event. | No Data | Checkbox | AQB__IsAllDayEvent__c | |
Was used in the conversion from Advance CRM to Salesforce. | 289k ARs with this field. | Text(255) (External ID) (Unique Case Insensitive) | AQCV_CONVERSIONId__c | |
When this Task is a call, this field may be used to record the duration. | No Data | Number(8, 0) | AQB__CallDurationInSeconds__c | |
Any free text you wish to use to describe or identify the purpose, objective or subject matter for the call. | 55 ARs with this field. | Text(255) | AQB__CallObject__c | |
Any brief, free text you wish to add to describe the result of feedback from the call. | 210k ARs with data in this field. 209k of those are a single random character eg e, o, :, @. Does this data need to be deleted? | Text(255) | AQB__CallDisposition__c | |
No help text | No Data | Lookup(User) | AQB__CompletedBy__c | |
Confidential comment visible only to those granted permission to view such comments. Permission is granted via the "View Confidential Comments" permission set. | 25 ARs with Confidential Comments. Some of these values are the same as the Description field. These could be reviewed manually and migrated elsewhere. | Text Area(255) | AQB__ConfidentialComment__c | |
No help text | 283k ARs have values in the Contacts field. This is used in the ‘Activity report contact participant’ flow. What is this used for? | Long Text Area(3000) | USYD_contact__c | |
AQ added to API name for clarity since subject__c was used previously. | No Data | Picklist | AQB__SubjectAQ__c | |
The expected duration of the Activity. | 23 ARs have a Duration. Does this data need to be migrated? | Number(8, 0) | AQB__DurationInMinutes__c | |
The scheduled End date and time for the Activity. | 27 ARs have an End. Does this data need to be migrated? | Date/Time | AQB__EndDateTime__c | |
No help text. Created by USYD. | 173 records have a flag value = 2. This isn’t used in any flows. What is this field? | Text(4) | flag__c | |
Select items indicating necessary follow-up activities. | 19 ARs have the Follow-up value = Task One. Does this data need to be migrated? | Picklist (Multi-Select) | AQB__Followup__c | |
Location where the Activity took place. | 237 ARs have a Location value. | Text(255) | AQB__Location__c | |
No help text. The formula is (Owner:User.FirstName & Owner:User.MiddleName & Owner:User.LastName) | All ARs have a value in this field. There are 680 unique owner names, most are not Jarvis users. Was this field used to convert Contact Report data from Advance to Activity Report data in Jarvis? | Formula (Text) | USYD_owner_name__c | |
Designates the Unit of the User that created this record. This field is intended for use in creating Sharing Rules for the purpose of controlling access to the record. | No Data | Picklist | AQB__OwnerUnit__c | |
If checked, the Activity is to be a private event. | No AR has this field active. | Checkbox | AQB__IsPrivate__c | |
If checked, is visible in Self Service | No AR has this field active. | Checkbox | AQB__IsVisibleInSelfService__c | |
This is the first 100 characters of the Objective if the Report Status is Planned, otherwise it is the first 100 characters of the Result. | 334,168 ARs have values in this field. This field must have something automatically populating it because its got data as recent as 2024. Why is it missing 20k ARs? | Text(120) | AQB__ReportSummary__c | |
The scheduled Start date and time for the Activity. | 289k ARs have this value. Suspect this was used when converting from Advance to SF because data stops after 2020. | Date/Time | AQB__StartDateTime__c | |
The Stewardship record to which this Activity is linked. | No Data | Lookup(Stewardship) | AQB__Stewardship__c | |
If the Activity is an Event, this is the time of day at which it is to occur. | 289k ARs have this value. Suspect this was used when converting from Advance to SF because data stops after 2020. | Date/Time | AQB__ActivityDateTime__c |
Recommendations
Purposes of fields need to be revised for clarity. Fields such as Type and Purpose are trying to serve too many functions to provide reporting insights.
There is a large number of Activity Reports (~120k) that should be reviewed and created as campaigns instead as they are mass engagement activities. Many of these are historical as the only way to capture communications with a Contact in the old CRM was with the old Contact Report table.
Questions to Answer
For mapping we’ll need to consider what Contact we assign it to