Menu
This Diagnostic Sales Report template is the third level of a set of three, each with different amounts of required data. We have developed this method so you can easily perform the right analysis that fits the amount of data you have from your sales process. Please refer to the main article for a clear overview of all three levels and choose which one suits your organisation best.
What makes this dashboard great, in comparison to the first level: ‘the easiest sales breakdown’, is that we now measure a number of fixed reference points in the sales cycle. Without getting completely lost in all individual touchpoints, we look at just the most important milestones: first touch, lead, opportunity, customer, and ex-customer. By only measuring these finish lines, a very clear picture of the sales process emerges. While many companies tend to start by collecting far too much information on all the individual touchpoints, they actually find it hard to make that information actionable. This structure of multiple finish lines makes the data much more actionable, allowing useful insights to emerge sooner. By combining this knowledge with other data points, such as lead source channel and use case, we can then compare the different initiatives in your go-to-market: for example, which acquisition channel has the shortest sales cycle, and which subscription level generates the most successful or profitable customers?
By reducing the analysis to only this amount of data, we make it:
This report is similar in content to the second level: ‘The manual sales diagnosis’, but includes a strong improvement in the automated synchronisation of data from the CRM to the dashboard. This means you don’t have to handle your data manually to make it ready for Data Studio and you also always have direct and real-time insight into the current state of affairs in your sales organisation.
In contrast to the other two levels, this variant does not only make use of free tools, but we also use Supermetrics to synchronise data from the CRM to the dashboard. By using Supermetrics you can link your CRM directly to Data Studio (or any other data visualisation tool), or automatically export to Google Sheets so that you also have visibility of the data being extracted from your CRM. With automatically scheduled queries your data is continuously synchronised and your dashboard is always automatically up to date.
This analysis is perfect for you if your sales process is performed with enough consistency and data is recorded with enough consistency that you can be confident that the data you synchronise from your CRM directly to the dashboard does not need manual intervention to be cleaned or completed.
The video below shows briefly how we synchronised data directly from HubSpot to Google Sheets. In this setup, the Data Studio dashboard was connected to this Google Sheets template, so you can benefit from both the automatic synchronisation, but also have a visual view of the data being exported from your CRM. This offers just that little bit more opportunity to get to know your data than a direct connection to Data Studio.
The queries we used to retrieve company and contact data for this analysis:
Metrics company_total_count, c_custom_company_customer_value_score, deal_total_count, average_deal_size, deal_amount_closed_won, deal_amount_closed_lost, deal_amount, deal_recurring_revenue_amount
Dimensions company_id, company_name, company_industry, company_annual_revenue, company_employees, company_country, company_create_date, company_lifecycle_stage, original_source
Metrics contact_total_count
Dimensions company_id, contact_company, contact_first_name, contact_last_name, contact_lead_date, contact_opportunity_date, contact_customer_date, contact_persona, contact_job_title
In this template, the first page contains the headers of the queries we made to export our HubSpot data via Supermetrics. On the second page you can see the queries. In your CRM, whether it’s HubSpot or another one, the labels of the properties are probably different: you’ll have to change the headers of the columns to the names you find in the Google Sheets template of level two.
If you want, you can also export additional data from your CRM to this Google Sheets template, after which you can add new data visualisations to the Data Studio template to accommodate the specific data requirements you have.
This third level of diagnostic sales analysis consists of three parts: a comprehensive overview of the sales process, sales insights on who and how you sell to, and an extensive section in which the ideal customer profile can be examined by cross-referencing MRR and the Customer Value Score against different characteristics of your customers. It connects your CRM to Data Studio, allowing for automated data synchronisation to ensure the dashboard is always up to date.
