All Collections
Reporting and data management
Copper reports
Looker Studio: Report on Leads created, converted to Opportunities and avg close time
Looker Studio: Report on Leads created, converted to Opportunities and avg close time

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

Success at Copper avatar
Written by Success at Copper
Updated over a week ago

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 Looker Studio

Note: Before building this report, you will need to Import & Connect your Copper Data to Looker 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 Looker 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)


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

  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


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

  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

  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.

  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

  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

  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


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.

Did this answer your question?