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:
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.
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:
Our first action will be “Update Contact.”
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”:
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.”
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
And set it to wait until conditions are met:
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:
The last step of this automation will be to send a webhook. Click on the node and select Conditions and Workflow > Webhook:
A modal will appear where you will need to enter a webhook URL. That URL will need to come from Zapier.
In a new tab or window, log into your Zapier account and click “Make a Zap.”
Choose “Webhooks” for your Zap trigger:
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:
Then toggle back to your automation and paste the link into your webhook action. Click the “Save” button:
Your automation will look like this:
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.
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 will then look for the Webhook URL. Once the test is successful, click “Continue.”
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.
Choose “Create Spreadsheet Row” and click the “Save & Continue” button.
Connect your google account if it’s not yet connected. Click the “Save & Continue” button.
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.
Click “Create & Continue” to complete the set up of your Zap.
Name your zap and click the toggle to turn it on.
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
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:
We will want to create a forumula in the cell, F2 that is:
You will need to periodically copy and paste that formula down your “Days in Stage” column in order to calculate new rows.