Build a target oriented sales dashboard
Last updated
Last updated
Objectives:
As a sales manager, having a target oriented reporting strategy for your team is a driver of success.
Objective tracking is a must-have tool that will animate your team and enable your teammate to deliver. It's a good start for starting sales gamification!
On a higher level, it will give you a good tool to drive your 1-to-1 meetings with your Sales rep and give you an easy way to identify which teammate requires coaching and on which topic.
If you follow this article, you will be able to build the following dashboard:
🚛 Importing data into Whaly
Importing CRM data
Importing target data
🔗 Creating the relationships between the datasets
📆 Creating the exploration from Days
📈 Creating the charts
✨ Creating the dashboard
On this article we'll use data from Airtable but the same pattern can be applied to any CRM such as Hubspot, Salesforce and Pipedrive 🤗
To get started, the first thing we are going to do is import our CRM data into Whaly. Our CRM data is composed of two tables.
One "deal" table containing information about our deals:
One "deal stage history" table containing information about our deals movements throughout our sales pipelines:
The second type of data we are going to import is the targets. We will create monthly targets for our salespersons and structure our data with the following columns:
owner_id: the owner to whom the target applies
type: the current objective (revenue, number of call done, ...)
date: the current objective date
target: the current target amount
Targets should be written in a system on which you can easily input new data. We recommend to use either Google Sheets or Airtable for this task.
Let's take have a look at our example target database:
Before creating our relationships, we need to ensure that our data will match. In our days table, the date is a timestamp at the beginning of the day (ex: June 27 2022, 00:00:00). In our deal stage history table, our timestamps have hours, minutes and seconds (ex: February 17 2020, 17:39:02). We will need to round our dates in our deal stage history table in order to have matching data for the relationship. Let's do it:
Open the workbench, go the your deal stage history view
Click on add a column, select formula, and use the cohort formula day as the type in order to round the date to the current day:
As our targets and deals are going to be linked using our Days table, we will need to create relationships between:
Days has many Targets
Days has many Deal Stage History
Deal has many Deal Stage History
Let's get into the workbench, on the day table and create the following relationships:
We will also need to create the relationship between our deals and deals stage history (this is optional is you are using Whaly native CRM connectors that auto create such relationships):
It's possible that your deal stage history table, that we will use our exploration, is missing some columns, such as our deal amount and our owner id. Fortunately we have this information in our deal table, so it's easy to bring them in the deal stage history table.
Let's do it :
Open the workbench, go the your deal stage history view
Click on add a column, select lookup, and fill the information as required:
Now let's do the same thing for our amount column:
Now that we have all our data, we can start building our exploration.
Let's get to our workspace and create a new exploration, starting from our Days table. We do this in order to be able to add our "Deal Stage history" and "Targets" tables as related data.
Let's do it:
Create a new exploration from Days
On the Days table:
add Date as a dimension
add Deal stage history as a related data
add Targets as a related data
Remove all the metrics created automatically
You should now have an exploration that looks like the following:
Now we will create our metrics for targets and current progress.
Let's get started with the bookings target:
Click on Add a metric under the targets table:
Create a sum of the column target
Filter on all rows matching your desired owner_id and target type revenue
Give your custom metric a name, for example: Target bookings (owner #0)
Add a currency suffix to ensure our charts will look good
Create the metric
Now let's do the same for the current bookings of our Owner #0:
Click on Add a metric under the deal stage history table:
Create a sum of the column amount
Filter on all rows matching your desired owner_id and deal stage closedwon
Give your custom metric a name, for example: Bookings (owner #0)
Add a currency suffix to ensure our charts will look good
Create the metric
If the columns amount and owner_id are missing from the deal stage history table you can easily add them using a lookup column in the workbench. See our guide here: https://docs.whaly.io/data-management/workbench#2.-lookup
We just have to repeat these steps for each of our owners and metrics that we want to follow. For example, with 2 owners and with revenue, Nb call done, Nb demo done, Nb trials done, we should build the following exploration:
In order to create the charts, we will:
select the Metric chart type
add both our current KPI and target to our query builder, for example Bookings (owner #0) and Target bookings (owner #0)
add our Date dimension as the time field,
select a relevant time range
set Gauge as metric type
run the query
We can repeat the previous step as many times as necessary in order to build our dashboard. For our example, with 4 KPIs and two different salespersons we can build the following report:
And voilà, that's it 🎉
