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
- 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”
- Select Date_Diff
- 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 link the sample report built out in this example: https://datastudio.google.com/open/1FVadoZ1XQM-OYii0iA0ELopGbhzoN8h-