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

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

Part 2 - Building your Leads Report

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

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

Once you’re finished creating your Leads report, click “Add New Page” in the top left
Go to Resource → Manage data sources
Click edit on the All Opportunities source
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
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

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
Click Done and go back to your blank 2nd page

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.

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

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

Create a Pie Chart
1. For metric, choose “Source”
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
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.