How to Build a Sales Funnel Dashboard for a Sales Team Google Forms & Google Sheets QUERY COURSE

The video begins with an introduction to building a sales funnel dashboard using Google Sheets and Google Forms. The speaker, Michael, explains the importance of understanding the performance of a sales funnel and how it can help identify where customers are dropping off. He emphasizes the significance of monitoring individual salesperson performance for accountability and improvement. Michael outlines the steps to create a dashboard that automatically updates with data collected from sales reps through Google Forms. The video aims to teach viewers how to use the query formula in Google Sheets effectively and provide insights into data structure.

This paragraph delves into analyzing sales funnel conversion metrics. Michael discusses how to identify the percentage of customers progressing from one stage of the sales funnel to the next. He uses an example of a solar and roofing company to illustrate how sales reps go through various stages, from door knocks to closed contracts. The video explains how to use data from a dashboard to make strategic decisions, such as increasing the number of leads by focusing on improving pitches. The speaker also introduces the concept of a contracts leaderboard and a table format for viewing raw numbers and conversion rates.

Michael provides a step-by-step guide on creating a Google Form to collect data from sales reps. He explains the importance of structuring the form to track significant stages in the sales funnel and the need to include location, department, and sales rep information. The video covers how to use drop-down menus for easy data selection and how to connect the form to a Google Sheet. Michael also offers tips on maintaining an organized list of sales reps and handling data from new or departing team members.

In this section, Michael addresses the challenges of working with Google Forms data in Google Sheets. He explains how to handle the automatic timestamp column added by Google Forms and how to ensure formulas remain intact when new form responses are submitted. The video presents two solutions: using an array formula or projecting the data onto a new tab. Michael demonstrates both methods and discusses their pros and cons, providing practical advice on which approach to use based on the user's needs.

Michael moves on to building interactive filters and scorecards in the dashboard. He shows how to set up date range filters and drop-down menus for location, department, and sales rep. The video emphasizes the importance of creating a user-friendly dashboard that updates automatically when filters are changed. Michael then uses the query formula to create a scorecard that displays the number of敲门 (knocks) within a selected date range, explaining how to format the formula for clarity and ease of use.

This paragraph focuses on creating visual representations of data through charts and tables. Michael explains how to create a table for sales funnel stages and convert it into a bar chart for better visualization. He also demonstrates how to calculate and display conversion rates at each stage of the sales funnel. The video covers the use of conditional formatting to highlight positive and negative changes and introduces the concept of a contracts leaderboard to display top-performing sales reps. Michael also mentions the importance of adding context to numbers through wins and challenges, which can provide deeper insights into the sales team's performance.

In the concluding part of the video, Michael discusses additional features that can enhance the sales team KPI dashboard. He talks about setting up default date range options for quick selection and the potential of adding trend lines for data analysis. The video also touches on the creation of a wins and losses tab for more context on sales performance. Michael encourages viewers to download the provided sheet for practice and to look out for advanced tutorials for further learning. He concludes by inviting viewer questions and feedback.