How do I calculate the amount of time a lead was in a stage?

In this article, we will walk you through how to calculate the amount of time a lead was in a particular stage in your pipeline. You may find this useful if you have sales agents on your team delivering demos, and wish to measure how long each of their leads was in the first stage of your pipeline before moving to the next stage.

Setting up this calculation requires the following:

  • ActiveCampaign account
  • Zapier Account
  • Prepared Google Sheet (or any other spreadsheet software that integrates with Zapier) containing Contact Fields you wish to capture from ActiveCampaign

The first step to this process is to set up your Deal Pipeline and note the stage you want to measure the amount of time a lead is in. In this example, we want to measure the amount of time a lead is in the “To Contact” stage before moving to the “In Contact With” stage in our pipeline:

to_contact_stage.png

Next, we’ll need to create two custom date-based fields: “To Contact Start Date” and “To Contact End Date.” We will later use these fields to determine how long a lead was in the “To Contact” stage.

contact_date_fields.png

For a reminder on how to create a custom field in your account, click here.

Then, we’ll need to set up an automation that triggers to run when a lead enters our pipeline:

Enters_a_pipeline_trigger.png

select_pipeline_in_trigger.png

Our first action will be “Update Contact.”
click_update_contact.png

Click the “Update Contact” action from the “Add New Action” modal.

In the next pop up, select the field to update by selecting it from the dropdown. In this example, we’re going to select “To Contact Start Date”:

to_contact_start_date.png

In the second dropdown, we’re going to select “Current Time.” That way, when a contact hits that action step, their “To Contact Start Date” field will be updated with the current date and time. Then click “Save.”

current_time.png

Now, we’ll want to add a Goal action to our automation. In this goal, we want the following conditions:

Has Deal in stage > General Pipeline > In Contact With

goal_conditions.png

And set it to wait until conditions are met:

wait_until_conditions_are_met.png

That way, your lead will wait in this Goal step until they move to the next stage in your pipeline.

Now, we’ll want to add another “Update Contact” action. Only this time, we’re going to update the “To Contact End Date” field to reflect the current time:

update_end_date_field.png

The last step of this automation will be to send a webhook. Click on the node and select Conditions and Workflow > Webhook:

post_webhook_action.png

A modal will appear where you will need to enter a webhook URL. That URL will need to come from Zapier.

enter_url.png

In a new tab or window, log into your Zapier account and click “Make a Zap.”

Choose “Webhooks” for your Zap trigger:

choose_webhook_trigger.png

Click “Catch Hook” and click the “Save and Continue” button. Then click “Continue” again.

On the next page, you will be presented with your webhook. Click the “Copy to clipboard” button:

click_copy_to_clipboard.png

Then toggle back to your automation and paste the link into your webhook action. Click the “Save” button:

enter_zapier_hook.png

Your automation will look like this:

sample_automation.png

Make your automation active by clicking the “Active” button on the top right of your automation. Then add a test contact to your automation. This is necessary as Zapier will need to run a test of your webhook.

test_contact_time_in_stage.png

time_in_to_contact_stage_automation.png

View the contact’s path in the automation and skip through the goal step in order to proceed to the webhook step.

Toggle back to your Zap. Click the “OK, I did this” button:

zapier_ok_i_did_this.png

Zapier will then look for the Webhook URL. Once the test is successful, click “Continue.”

test_successful.png


Next, follow the steps to set up your Zapier action. To continue with our example, we want the webhook to add contacts to a Google Sheet that we’ve already prepared, so we’re going to select Google Sheets.

google_sheets_app.png

Choose “Create Spreadsheet Row” and click the “Save & Continue” button.

create_spreadsheet_row.png

Connect your google account if it’s not yet connected. Click the “Save & Continue” button.

select_google_account.png

Select the spreadsheet and worksheet that you will be adding contacts to. The box will then expand where you can map the fields in your sheet with the contact fields in your account. When finished, click the “Continue” button to go to the next step.

map_fields_in_zapier.png

Click “Create & Continue” to complete the set up of your Zap.

create_and_continue.png

Click “Finish.”

finish_test_google_sheet.png

Name your zap and click the toggle to turn it on.

Turn_on_zap.png

Now, as contacts enter your pipeline, they will:

  • Be added to your automation
  • To Contact Start Date will be updated to reflect the current date
  • Wait in your Goal step

Then, as they move to the next stage in your pipeline, they will:

  • Complete the Goal ste
  • To Contact End Date custom field will be updated to reflect the current date
  • The webhook will send the contact’s data to your Google Sheet

time_in_stage_sheet.png

To calculate the number of days a contact is in a stage, you can create a column in your spreadsheet called “Days in Stage” then enter in a formula that is To Contact End Date minus To Contact Start Date.

For example, if we have the following in our spreadsheet:

days_in_stage.png

We will want to create a forumula in the cell, F2 that is:

=E2-D2

 

calculation.png

You will need to periodically copy and paste that formula down your “Days in Stage” column in order to calculate new rows.

Have more questions? Submit a request