Reporting Recipes: Average Days per Stage

What is this measuring?

Captures the average number of days an opportunity is in each stage of the pipeline.

Why is this important?  

Understanding sales velocity allows you to forecast and identify avenues for process improvement. Tracking how opportunities progress through the pipeline enables management to identify which segments of your sales process are fluid and where inefficiencies exist.  

Create the Average days per Stage Report:

*Requires Zapier and Google Sheets

Create a Google Sheet with the appropriate fields you are reporting on.

For example: Copper ID, Owner, Created Date, Time Stamp Field (for each stage in your pipeline), Close Date, etc.Screen_Shot_2018-02-20_at_1.51.24_PM.png

In this reporting example, we are pulling from a Sales Pipeline created in Copper with the following stages: Qualified (first stage upon Opportunity Creation) > Quote > Follow Up > Contract Pending


Zapier 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 Opportunity’
  4. Test this step and click ‘Continue’
  5. When the trigger setup is complete, click ’+’ to add a filter:

Screen_Shot_2018-02-20_at_1.53.47_PM.png

 

6. Click 'Filter' under 'Add a Filter or an Action.'

  • Select: Only continue if…
  • Pipeline Name > (Text) Exactly matches > Sales

Screen_Shot_2018-02-20_at_2.07.56_PM.png

7. Create an Action in Zapier: Create Spreadsheet Row

Screen_Shot_2018-02-20_at_2.11.02_PM.png

8. Connect to your Google Sheet

9. Link the appropriate Spreadsheet and Worksheet

  • Spreadsheet will be the name of your Google Sheet (Example: Opportunity Management - Average days in stages report)
  • Worksheet will be the name of the tab (Example: Sheet 1)

10. Map the fields from your Google Sheet in Zapier. Important fields to map are: Copper ID, Name, Owner and Created Date. Note: Make sure to map Created date to Stage Updated

Screen_Shot_2018-02-20_at_2.14.48_PM.png

11. Follow steps in Zapier and run tests. Make sure your Zap is turned on. Note: The initial Zap will populate the appropriate fields in the Google Sheet.

You’ve completed Part One! Now that Zapier is logging the created date in the Google Sheet, you will set-up separate Zaps to timestamp each stage.  

Here’s an example of how to generate this type of Zap. You’ll want to start by logging the second stage, then create separate Zaps for all other stages including the Close Date.  

End Goal - these timestamps will provide the lifecycle of opportunities from creation to close!

Zapier Part II:  

    1. Create a new Zap - Trigger: Update an Opportunity Stage
    2. Connect to your Copper account
    3. Test this step and click ‘Continue’
    4. Add a filter (similar to 1st Zap)
    5. Click 'Filter' under 'Add a Filter or an Action.'
  • Select: Only continue if…

Screen_Shot_2018-02-20_at_2.17.38_PM.png

 

  • Note: Select the Pipeline Stage Name you will be timestamping and the Pipeline Name

6. Next step is to create a Spreadsheet Row

  • Connect to your Google Sheet (same from Part 1)
  • Link the appropriate Spreadsheet and Worksheet
  • In the screenshot below, click ‘Add a Search Step’ - this will lookup the correct field, i.e: Copper ID. Fill out Lookup Column as Copper ID. Lookup Value as ID (screenshoot)  

Screen_Shot_2018-02-20_at_2.21.41_PM.png

 7. Update Spreadsheet Row as your Zapier Action

    • Connect 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 the “Stage” you are timestamping (example Quote Stage)

Screen_Shot_2018-02-20_at_2.24.03_PM.png

    • Click ‘Continue’ and save your Zap. Make sure the Zap is turned on. To test it, access your Copper account, create an opportunity, and check your spreadsheet. Your opportunity information should populate, including the timestamp of the stage.

Part 3: Reporting Insights

To identify the average days in stage, we can add in a formula that takes the Entered Quote Stage minus the Created Date. In a new column (G for our example), type: =D2-C2 in cell G2 and click ‘Enter’ to get the days in stage for each opportunity. Click in the corner of cell G2 and drag it down for the formula to populate the cells below. 

Screen_Shot_2018-02-20_at_2.25.30_PM.png

To get additional metrics, (i.e. average number of days to move all opportunities from Created to Quote), you can type the following formula in a new column:

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

Screen_Shot_2018-02-20_at_2.27.38_PM.png

To capture the number of days for opportunities in each stages, you will want to run the same formulas for the remaining stages. For the last stage you will subtract the Close Date minus the last stage (in this example Contract Pending)

Screen_Shot_2018-02-20_at_2.28.33_PM.png

To view this spreadsheet: https://docs.google.com/spreadsheets/d/1ccFgehcfbSAbKqu9XMrqF9fNve4OjqgsZbHkmk9mPx8/edit?usp=sharing

To create a visual chart, select All Data and create a pivot table. Data → Pivot Table

Under Rows: Add all Average Days, click ‘Insert Chart’ and share insights! Filter by Created Date to capture a period (example January).

Screen_Shot_2018-02-20_at_2.29.43_PM.png

Potential Reporting Enhancement:

  • Breakdown the average days in stages by month and dive into individual performance, identifying the average stage by sales rep

 

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