Proposal Summary
This proposal summarizes the functionality of implementing the CRM report in dashboard.
Requirement
The client would like to display the customized CRM report based the date range given in dashboard
Deliverables
- We will create the script and offer choices to pinpoint the latest task that was modified for each client. This is intended to retrieve detailed info of the task with the most recent modification date, ensuring that only one task record per customer is identified.
- We will create custom saved searches for the CRM dashboard reports and place them in the dashboard, where the user can input the date range whithin which the data should be displayed in the report.
- The CRM pivot reports will be based on the report results : NLFC CRM Dashboard Appointment Report
- The date range will be determined by the ‘Date Referral Received’. When a date range is entered, the report will display results based on client records with a ‘Date Referral Received’ within the specified range.
- The four saved search reports are; (1) Report based on sites booked, (2) Report based on Doctors assigned, (3) Report based on Referral Type
Report – 1:Report – 2:
Report – 3:
Report – 4:
- The Distribution of Referral report is derived by summing the values in corresponding cells from Reports 2 (Doctor-assigned Report) and 3 (Referral Type Report). Unfortunately, the creation of the Distribution of Referral report is currently not feasible. Combining these two distinct saved search reports into a single report is challenging because Report 2 relies on Doctors assigned from client records, while Report 3 is based on Referral Type from task records.
- In the above three reports, the column values are calculated based on the count of task titles (The ‘TASK TITLE’ from task record, internalid: custevent1) and are determined in a manner such that:
- Booked : The sum of count of customers having task title ‘Booked’ and ‘Rebook’.
- Busy Tone: counts of ‘Busy tone’ customers.
- Left voice message: counts of ‘Left Voice Message’ customers.
- Will call back: Sum of counts of ‘Patient will call back’ and ‘NP will call back patient’.
- Discard Referral: count of ‘Discard Referral’ customers.
- Cancelled: Sum of counts of ‘No Show’, ‘Cancelled’ and ‘Expired’.
- No Contact: Sum of counts of ‘No voice mail’, ‘Voice mail not activated’, ‘Voice mail full’ and ‘Not in service’.
Yearly Tasks Summary Report:
- The sample ‘Yearly Tasks Summary Report’:
- We will build the saved search report to generate this report, which will be placed on the dashboard.
- For this report, there won’t be a filter for entering the date range. Instead, the data will be displayed for the last 5 years from the day the report is viewed.
- In this report, the total count of tasks in each month per year will be displayed.
- The ‘Booked’ and ‘Received’ counts will be shown only for the tasks created in the last 2 years.
- To count the tasks ‘Booked’ and ‘Received’ in each month of the last 2 years, we will consider the ‘Last Modified Date’ of the task record. This is because we need to add the count of the task record to the month in which the task title is set as ‘Booked’ or ‘Re-Booked,’ in the case of the ‘Booked’ column.
- We assume that the ‘Booked’ column should include the count of task records with ‘TASK TITLE’ values ‘Booked’ and ‘Re-Booked.’ We also assume that the ‘Received’ column lists the count of tasks with ‘Task Title’ other than ‘Booked’ and ‘Re-Booked.’ If this assumption is incorrect, please let us know.
- Adding the additional row for Increase’ to the report is not possible.
Assumptions
- We assume that for building the report 1, we need to group the search result based on the ‘Sites Booked’, which is the ‘LOCATION BOOKED’ in the client record.
- We assume that for building the report 2, we need to group the search result based on the ‘Doctors Assigned’ which is the ‘ASSOCIATE DOCTOR’ in client record. Also, an addition filter needs to be applied here, that the referral type should be any of ‘New life Referral’ and ‘Hart Referral’
- We assume that for the report 3, we need to group the search result based on the ‘Referral type’, which is ‘REFERRAL TYPE’ from the Task record.
- The ‘Distribution of Referral report’ will be omitted from this proposal.
- In the ‘Yearly Tasks Summary Report’, the data of last 5 years will be displayed.
- If the user requests a report for a lengthy date range like spanning two years or more, please note that there may be a slight delay in generating and displaying the report. This is because processing such a large volume of data can take some time.
- Also, in this report, the ‘Booked’ and ‘Received’ column data will be displayed for last 2 years from the date on which the report is viewed.
- To count the tasks ‘Booked’ and ‘Received’ in each month of the last 2 years, we will consider the ‘Last Modified Date’ of the task record.
- We assume that the ‘Booked’ column should include the count of task records with ‘TASK TITLE’ values ‘Booked’ and ‘Re-Booked.’ We also assume that the ‘Received’ column lists the count of tasks with ‘Task Title’ other than ‘Booked’ and ‘Re-Booked.’ If this assumption is incorrect, please let us know.
- The inclusion of the “Increase” raw in the ‘Yearly Tasks Summary Report’ will not be taken into consideration.
Estimate Time
Note: The estimates may vary depending on the complexity of development of scripts and reports
| Analysis | 3hr |
| Development of script | 13hr |
| Development of 3 pivot CRM Reports | 30hr |
| Development of Yearly Tasks Summary Report | 12hr |
| Testing/QA | 5hr |
| Documentation and production movement | 2hr |
| TOTAL | 65 hours |