What is this measuring?
This article covers how to report on the average deal size by building out a custom report via Google Data Studio.
Why is this important?
If you're a sales manager, there are a variety of metrics you will likely be looking at. One important metric is the deal size. One way to show growth is to look at average deal size. If you find that it increases over time, this indicates that your sales reps are either a) growing accounts successfully or b) customers are increasing the amount of money they're spending with your company.
If the deal size is decreasing, this could be an area of improvement (i.e. perhaps reps are giving out too many discounts, maybe pricing needs to be reassessed, etc).
Create the Average Deal Size Report Using Google Data Studio
Note: Before building this report, you will need to Import & Connect your ProsperWorks Data to Google Data Studio. Follow this guide prior to going through this article.
Part 1 - Build your Google Data Studio report
- Once connected, you’ll see a list of your fields (default & custom) listed from the import. In this section, you can customize the name of the fields and the format. For example, instead of “deal_date”, I’m going to rename this “Close Date”
- For this example, we’re going to put together a report of our average deal size, broken down by status & stage. Because I’m going to be using an average, Google Data Studio allows you to select the type of aggregation. I have a “monetary_value” field that represents the value of opportunities, but I’m going to duplicate this field and change the aggregation from “Sum” to “Average”
- Now we’re ready to start building our report
- Once you hit Done & “Add to Report”, you’ll be taken a blank canvas
- You’ll notice a row of icons at the top of the page - these are the chart options you have for building your report. In this example, we’ll be using a bar chart
- The data that populates will need to be adjusted. To edit the dimensions and metrics of the chart, use the right hand panel (Data first, then Style)
- Let’s start with Data - for the dimension, let’s select “Stage” since that’s how we’re going to break down our average deal sizes. To update the dimension, choose “Stage” from available fields and drag it over to the replace the existing dimension
- Once the dimension is updated, update the value. To report on average deal size, drag over the custom “Average Value” field that was created earlier
- Under Sort, click the “Average Value” button and then select “Stage”. This will sort your chart by Stage, rather than sorting by Average Value.
- The chart is almost finished, but you’ll notice that “Stage Name” is showing up as a stage. To filter out this value, click “+ Add a Filter” in the bottom right
- Now that the chart is complete, we can customize the style to make it a little more user friendly. Click into the Style tab
- In the Style tab, you can choose to customize the colors, add data labels, add a shadow, etc.
- The chart that we’re left with is now showing the average deal size of all Opportunities, broken down by stage.
Potential Reporting Enhancements
- To enhance this report, we can add a few other touches, like a filter and a date range. This will allow other report viewers to slice and dice the metrics.
- To add a date filter, click the calendar icon & draw the calendar box
- To add a filter, click the filter icon (tornado icon) and draw the filter box. Once you add in your filters, under “Data”, choose the value you want to filter by and drag it over. In this example, we’ll use Assignee & Status.
What we just built out is a report on the average deal size, broken down per stage. The date filter allows the viewer to adjust the date range to compare periods of time (i.e. this week, this month, a specific quarter, etc). Since we included filters for Assignee and Status, we can look at this report, segmented by a specific user and also by status (open, won, closed, abandoned).
Once you’re finished, you can share the report with your other team members.
Refreshing the report
To update the report, go into the data you imported in a Google Sheet at the very beginning. I would recommend naming your Sheets Import something you’ll always be able to find - i.e. “Opportunities Report Data- ProsperWorks”.
- Go to Add-ons
- Choose ProsperWorks CRM Report Builder
- Select “Import Data”
- If you see your most recent import, click the blue refresh icon (see below)
- If the screen you see says “Let’s load some data”, click the cancel button. Then you’ll see your most recent import and can click the refresh icon