Reporting Recipes: Average Inactive Days and Time to Contact

What is this measuring?

  • The average number of inactive days for your leads
  • The time it takes from when a Lead is created to the time it takes to first contact a Lead

Why is this important?

Proper lead management ensures you have the right interaction cadence with your leads, ultimately resulting in more conversions and generate revenue for your business.

Lead Insights - Average Inactive Days & Time to Contact will allow you to visually see how your team members are interacting with Leads individually and as a whole. This can expose insights that will identify potential workflow improvements. For example, if your top performer has an average inactive days with Leads of 5 days, then you can set this as a target for the rest of the team. You will also be able to identify the appropriate time to contact a Lead and set a precedent moving forward.

Create the 'Average Inactive Days' report

Please Note: This report will require the Copper Custom Report Builder, Google Sheets, and Copper

  1. Use custom report builder to import Leads into a Google Sheet 
  2. Import Leads
  3. Create new Sheet labeled 'Queries' or 'Formulas'
  4. In the 'Queries' sheet, be sure to have three columns labeled as follows (image below):
    1. Sum of Inactive Days
    2. Count of Leads
    3. Average Inactive Days

  1. Under 'Sum of Inactive Days,' type the following formula: =SUM('All Leads - Leads'!P:P)
  2. Under 'Count of Leads,' type the following formula: =COUNT('All Leads - Leads'!P:P)
  3. Finally, under 'Average Inactive Days,' calculate the 'Sum of Inactive Days' divided by 'Count of Leads' to pull in the average. Formula should look like: =SUM(A3/B3)*  *=Sum(Sum of Inactive Days/Count of Leads)

Note: In the above example, 'P' represents the Column that contains 'Inactive Days.'  

Create the 'Time to Contact' report

Please note: This report will require Zapier, Google Sheets, & Copper. This will require setting up two Zaps - one for tracking when a lead is created, the 2nd for tracking when the status updates to “Contacted”.

Part 1: Start by setting up a Zapier account

  1. Once the Zapier account is activated, click ‘Make a Zap’
  2. For your Trigger, select Copper
  3. Select Copper Trigger:  “New Lead”
  4. Set up your Action, select Google Sheets
  5. Select “Create Spreadsheet Row”
  6. Create a new, blank spreadsheet in Google Sheets
  7. Connect the Spreadsheet to your Zapier Account
    1. Spreadsheet will be the name of your Google Sheet (Example:  Lead Management - Time to Contact Report)
    2. Worksheet will be the name of the tab (Example:  Sheet 3)
  8. Add headers in the Google Sheet - copy & paste the headers from your Leads import if you’d like - most important fields to map are:
    1. First Name
    2. Last Name
    3. ID
  9. Last Status Update
  10. Follow steps in Zapier and Send test to Google Sheets

You’ve now completed Part 1.

Part 2: Tracking Updated Lead Date

  1. In Copper, go into Settings > Lead Management > Add New Status. Create a status called 'Contacted.'
  2. Since you already have your Zapier account set up, click ‘Make a Zap.’
  3. For your Trigger, select Copper.
  4. Choose 'Updated Lead Status' - triggers when lead status is changed.
  5. Connect your Copper account with your API token, which can be found under System Settings > Integrations > Zapier > Generate Token.
  6. Once your account is connected, click on 'Test' to ensure it’s connected successfully.
  7. Click 'Filter' under 'Add a Filter or Action.'
    1. Select:  Only continue if…
    2. Status > (Text) is in > 'Contacted'

3. Update Spreadsheet Row

      • Connect to your Google Sheet (same from Part 1)
      • Link the appropriate Spreadsheet and Worksheet
      • In the screenshot below, click on ‘Add a Search Step’ - this will lookup the correct field, i.e: our lead created date
      • Click “Ok, add it” when the modal pops up after clicking “Add a Search Step”
      • Fill out Lookup Column - will be Copper ID
      • Lookup Value will be ID

4. Update Spreadsheet Row (See screenshot below)

      • Connect to your Sheet again
      • Under ‘Row’, “Use a Custom Value” should already be selected
      • “Step 3 - Row” should already be populated
      • Map Name from Step 1
      • Map Copper ID from Step 1
      • Map Contacted Date - fill in as “Last Status Update”
      • Hit Continue and Save your Zap. Make sure it’s turned on.
      • To test it out, go into your Copper account, create a lead and check your spreadsheet. Your new Lead information should fill in, including “Created Date”.
      • Then, change your Lead in Copper to “Contacted” status. Check your spreadsheet again - you should see the “Contacted Date” populate.

Part 3: Reporting Insights

Since we’re looking to surface the time to first contact, we can simply add in a formula that takes the Status Update Date minus the Created Date. In a new column (G for our example below), type: =(F2-E2) in cell G2 and hit enter to get the days to contact. Click in the corner of cell G2 and drag it down so that the formula carries down for all of your rows (See column highlighted in green)

To get additional metrics, (i.e. the average time it takes to first contact), you can type the following formula in a new column:

=AVERAGE(G:G) - see column 'H' highlighted in yellow

Note: “G” represents the column that houses your “Time to Contact” values.

Reporting Examples

Average Inactive Days 

Time to Contact

Potential Reporting Enhancements

  • You may want to divide up the 'Time to Contact' report by 'Lead Owner' to see how your team stacks up against each other.
  • You can also add in a 'Target Time to Contact' column so that your team can see how their 'Time to Contact' is tracking towards their goal.

Still have questions?

Still have questions? Ask in our Community, and get answers from our Customer Success Team as well as fellow users.

Was this article helpful? 0 out of 0 found this helpful