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
You can use the “Scorecard” widget since we’re looking for the total number.
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.
Change the Copper ID metric to be “count” and adjust the name so you know what you’re looking at
Leads per Source
Click the “Table” icon & “Pie Chart” icon
For Dimension, choose Source
For Metric, switch the Copper ID to “Count” and rename
You can customize to add a border shadow and take out the rows & page numbers
Add a filter to exclude Source Name = Null
This will give you the number of leads created per source and the percentage
Customize - Add Bar Chart, Filter & Date Picker
If you’d like to see another visual, you can set up a bar chart to reflect the number of leads per source
You may also want to add a filter so you can segment by user (Owned by)
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.
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
Click “Add field”
Name the field - i.e. “time to close” and in the text box section, start typing “date”
Select Date_Diff - this will be the type of formula to use
The format of this equation will be “(End Time, Start Time)”
In this case, type: (Close Date, Created Date)
Save the field
We’ll also want to include another formula field to determine how long it takes to close a Lead
Click “Add field’
Name the field - i.e. “time to close - lead conversion”
Type: (Close Date, Lead Created Date)
Save the field
Click Done and go back to your blank 2nd page
Create a Bar Chart
Click the ‘123’ icon next to each of these and be sure to have “Average” selected
For Data Source, be sure you have the correct source connected (should be Opportunities, not Leads)
For Date Range & Dimension, you can use Close Date
For metric, select “Time to Close” and “Time to Close - Lead Conversion”
Format your chart so that your data labels show and the X axis & Y axis are labeled.
Create a “Scorecard” widget
For your metric, choose “Lead Created Date” and change the format to “Count”
This will give you the number of Opportunities, converted from Leads
Create another Bar Chart
For Dimension, choose Close Date
For Metric, choose Status
Add a filter to get rid of null close date
Create a Pie Chart
For metric, choose “Source”
Add a Filter for “Lead Created Date”
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.