Report on Leads created, converted to Opportunities and avg close time

 What is this measuring?

This article covers how to report on:

  • The number of Leads Created
  • The number of Leads Converted into an Opportunity
  • The average time it takes to close a Lead

Why is this important?

Let’s say you’re a sales manager and you obtain leads in a variety of ways - i.e via website, cold calling, purchased lists, and events you attend. You may want to understand how many Leads are being created, where they’re coming from, how many of those are being converted, and how long it takes to close a Lead. This will ultimately allow you to figure out which source has the biggest ROI and where there’s room for improvement (i.e. with the time to close metric).

Create the Leads & Opportunity Breakdown Report Using Google Data Studio

Note: Before building this report, you will need to Import & Connect your Copper Data to Google Data Studio. Follow this guide prior to going through this article.

For this report, you’ll need to import your Opportunities & your Leads.

Part 1 - Open a new Google Data Studio Report

  1. Click “Connect Data Source”
  2. Select Google Sheets
  3. Choose your spreadsheet with your imported data
  4. Choose one tab (i.e. All Opportunities) first; for the range, type in A2:AM (or the last column in your spreadsheet)
  5. Connect the data source
  6. Once your blank report loads, go to Resource
  7. Select “Manage Added Data Sources”
  8. Add your other tab from the same spreadsheet (i.e. All Leads)

 Connecting_Tabs_DataStudio.gif

Part 2 - Building your Leads Report

  1. Number of Leads Created
    1. You can use the “Scorecard” widget since we’re looking for the total number.
    2. In this case, I’m going to have our date range metric be “Lead Created Date” and use the Copper ID as the metric. Since every Lead has a Copper ID, this is appropriate to use for the total number created.
    3. Change the Copper ID metric to be “count” and adjust the name so you know what you’re looking at
  2. Leads per Source
    1. Click the “Table” icon & “Pie Chart” icon
    2. For Dimension, choose Source
    3. For Metric, switch the Copper ID to “Count” and rename
    4. You can customize to add a border shadow and take out the rows & page numbers
    5. Add a filter to exclude Source Name = Null
    6. This will give you the number of leads created per source and the percentage

Creating_Lead_Source.gif

  1. Customize - Add Bar Chart, Filter & Date Picker
    1. If you’d like to see another visual, you can set up a bar chart to reflect the number of leads per source
    2. You may also want to add a filter so you can segment by user (Owned by)
    3. Now, add a date picker so you can narrow the report down to a specific time frame

Adding_Chart_Customization.gif

Adding_Filter_Date.gif

 Part 3 - Building your Opportunities Report

  1. Once you’re finished creating your Leads report, click “Add New Page” in the top left
  2. Go to Resource → Manage data sources
  3. Click edit on the All Opportunities source
  4. Verify that your data fields (i.e. close date, created date, etc) are coming through in a date format, not text.
    1. If your date fields are interpreted as text fields, change the format
  5. Now, we’re going to create a calculated field to get the “Time to Close” metric to use in our report
    1. Click “Add field”
    2. Name the field - i.e. “time to close” and in the text box section, start typing “date”
    3. Select Date_Diff - this will be the type of formula to use
    4. The format of this equation will be “(End Time, Start Time)”
    5. In this case, type: (Close Date, Created Date)
    6. Save the field

Adding_Page_Calculated_Field.gif

  1. We’ll also want to include another formula field to determine how long it takes to close a Lead
    1. Click “Add field’
    2. Name the field - i.e. “time to close - lead conversion”
    3. Select Date_Diff
    4. Type: (Close Date, Lead Created Date)
    5. Save the field
  2. Click Done and go back to your blank 2nd page

Creating_2nd_Calculated_Field.gif

  1. Create a Bar Chart
    1. Click the ‘123’ icon next to each of these and be sure to have “Average” selected
    1. For Data Source, be sure you have the correct source connected (should be Opportunities, not Leads)
    2. For Date Range & Dimension, you can use Close Date
    3. For metric, select “Time to Close” and “Time to Close - Lead Conversion”
    4. Format your chart so that your data labels show and the X axis & Y axis are labeled.

Time_to_Close_Chart.gif

  1. Create a “Scorecard” widget
    1. For your metric, choose “Lead Created Date” and change the format to “Count”
    2. This will give you the number of Opportunities, converted from Leads

Lead_Conversions_Number.gif

  1. Create another Bar Chart
    1. For Dimension, choose Close Date
    2. For Metric, choose Status
    3. Add a filter to get rid of null close date

Adding_Opp_Status_Breakdown.gif

  1. Create a Pie Chart
    1. For metric, choose “Source”
  2. Add a Filter for “Lead Created Date”
    1. Unselect “Null” - this will ensure that the data is reflecting only Opportunities that have been converted from Leads
  3. Add a Date Picker and Text Box if necessary

Adding_Source_Filter_Date_Opps.gif

 

Here is a sample report built out in this example.

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 1 found this helpful