This customization will allow your company to report on how many Minutes it took from the time a Lead Source is created in the system to the time the Prospect is called from the Call Queue. NOTE: If you are not using improveit 360's Call Queue functionality this article will not apply to your business.
Create a Custom Report Type
Navigate to the Setup menu
Under the Create menu, choose Report Types
Click New Report Type
The Report type will need to include: Lead Sources with Marketing Opportunities with Campaign Members with Campaign Member Actions. You can abbreviate the Report Type Label with "Lead Sources w Marketing Opps w Campaign Actions."
When creating the Report Type be sure A records have at least one related B record, and B records have at least one related C record, and C records have at least one related D record.
This means, you will only query Lead Sources where the Prospect has been actioned from the Call Queue (only Lead Sources with a Marketing Opp, with a Campaign Member, AND an Action).
Create four (4) New Custom Formula Fields
On the Lead Source object we will need to build the following 3 fields:
- Day of the Week
- Created Date (date only)
- Created On (date/time)
On the Campaign Member Action object we need to build the following field.
- Time to Dial (minutes)
For Day of the Week formula to work, we need to first turn the Lead Source Created Date/Time field to just a date field.
Go to Setup > Create > Objects
Go to the Lead Source object
In the Custom Fields and Relationships section, click the New button
Choose a Formula, then select a Output Data Type of Date, and name the Field, "Created Date (date only)"
The formula you will use is:
DATEVALUE( CreatedDate)
Click Save & New to create another new Field.
Next, create a Day of the Week formula field.
Choose Formula, then select an Output Data Type of Text, and name the Field "Day of the Week"
The formula you will use is:
CASE(
MOD(Created_Date_Only_Date__c -DATE(1900,01,01), 7),
0, "Monday",
1, "Tuesday",
2, "Wednesday",
3, "Thursday",
4, "Friday",
5, "Saturday",
6, "Saturday",
"")
Click Save & New to create another new Field.
Next, we need to display the Created Date AND Time in the report. We will create a formula field to pull that value.
Choose Formula, then select a Output Data Type of Date/Time, and name the Field, "Created On (date/time)"
The formula you will use is:
CreatedDate
Click Save and next we will need to switch Objects and add a field to the Campaign Member Action object.
Navigate to Setup > Create > Objects
Select the Campaign Member Action object
In the Custom Fields and Relationships section, click the New button
Choose a Formula, then select a Output Data Type of Number, and name the Field, "Time to Dial (minutes)"
The formula you will use is:
(i361__Campaign_Member__r.i361__First_Action_Date__c - i361__Campaign_Member__r.i361__Marketing_Task__r.i360__Lead_Source__r.CreatedDate ) *1440
Click Save.
Create new Custom Report
Navigate to the Reports Tab.
Click the New Report button
Select the Report Type you created in the first step of this Article, then click Create.
Next you will change the Filters to Show: All Lead Sources
And select your Date Range using the Created Date as your Date Field.
Use the Range dropdown to adjust your date range.
You will also need to set a Field Filter to isolate only the First time a the Campaign Member is being called. Click on Add next to Filters
Choose the Campaign Member Action Name field, then select an Operator of "contains" and enter a 0 in the value field.
The Filter will look like this, when finished click OK.
You can also add Filters to isolate specific Campaigns. In this example we are trying to exclude our Rehash and Annual Maintenance Campaigns, so we have added a Filter that says "Campaign Name 'contains' either the word "Pending" or "Today" or "Fresh". Here is what that Filter looks like:
Because we expect to see multiple Campaigns in this Report, we will want to Group the Report by the Campaign Name.
First, you'll need to change the Report Format from Tabular to Summary using the dropdown in the Preview pane.
Next, you'll need to drag and drop the Campaign Name field from the fields on the left, into the Preview pane in the row where it reads "Drop a field here to create a grouping"
Next, you will locate the fields you created in step 2 of this Article and drap and drop each of those fields into the Report.
After you drag and drop the Time to Dial (minutes) field, you will want to Average this field. This allows you to see the Average Time to Dial per Campaign.
Click the arrow on the field and choose Summarize this Field...
Choose the Average.
This Averages per Campaign will look something like this:
Save your new Report!!