Video

Adding days in stage to a Report or Insight [video]

  • 26 January 2022
  • 10 replies
  • 584 views

Userlevel 7
Badge +7

Hello Copper community! We often get this question: can I add the number of days an opportunity has spent in a current stage to my Insight?

The answer is yes, but there’s a little bit of setup work involved. Check out the video below for a tutorial.

Note: As of March 2022, Opportunities in the Copper left-hand menu is changing to Pipelines. Click here for more information.

 

Broadly, the steps are:

  1. Create a field to store the # of days in stage,
  2. Create a workflow automation to populate the days in stage and refresh it daily, and
  3. Pull that field into the reporting system.

Here is the formula I used in the workflow automation for days in stage:

DAYS(date_stage_changed,CURRENTDATE())

And here’s a list of all default variables and functions.

 

Let me know in the comments if you have any questions!


10 replies

I was the one to request this info. Great video, but as a person new to Copper, I would be appreciative if you could explain to me how do you add the custom field, especially which formula do you include in the custom field. 

Userlevel 7
Badge +7

Hi @NICKY, that’s great feedback - thanks! Sometimes I’m not sure how to balance the level of detail vs the video length so your comment is really helpful.

I’d be happy to provide more guidance around adding the custom field and plugging in the formula. My day is stacked so it might take bit longer than usual though.

 

--

 

While I’m here, I’d to answer a question I got about the formula/workflow automation for this:

wouldn’t this workflow automation track the number of days the opp is open, not the days it’s been in a stage?

Not quite - The trigger specifies when an opp is open. But the action uses the variable date_stage_changed. This means as long as an opp has an Open status, it will check the date that the stage was last changed.

In plain language, this is what the workflow automation rule says:

  • Trigger: Every day, check if each opportunity is Open. If it is open…
  • Action: …figure out how long ago the stage last changed and store it in Days in Stage Rpt

Is it possible to create a report to see average time of all opportunities to spend in that stage each stage? Trying to create a report to see where bottlenecks are happening in my pipelines. Thanks!

Userlevel 7
Badge +7

Hi @allison_pearmill, thanks for commenting! That sort of report is possible, however it requires a bit of setup. Essentially, you would follow the steps in the video, but in the last part where you build the report, you make a table of averages for each pipeline stage. I’m thinking you’re looking for something like this?

Stage Ave number of days
Initial contact 3.1
Demo 5.3
Final negotiation 14.0

 

Let me know if that’s what you’re looking for and I’ll be happy to provide more guidance. Alternatively, you can attend one of our reporting Q&As where my colleague Kevin can guide you through it live.

@Michelle from Copper that is exactly what I am looking for and I’d love more guidance on how to create that report. Unfortunately, I usually cannot make the Q&A times. Thank you!

Userlevel 7
Badge +7

@allison_pearmill here is a video ☝️

Basically, follow the original video in this thread until the 9 minute mark, then go ahead and follow the instructions in this one.

My voice is a bit hoarse today so I hope you can hear me clearly. You can turn on captioning in the player if my voice is unclear. Let me know if this is helpful or if you have any questions!

Hello, was there an explanation added somewhere how to create the custom field needed for this ‘time in each stage spent’ to work?

I watched both videos but they are mainly concentrating on how to create the report. I would need more guidance on how to create the field/fields and the workflow to get the days spent in each stage, not just the current one where the opportunity is. Thanks.

Userlevel 7
Badge +7

I totally understand, and I am hoping to do another video in the future that addresses that aspect more directly. 🙂

Are you posting that video Michelle? 

Would love to have that video @Michelle from Copper ! Also, thanks for the tutorial videos!

 

Reply