Sales Forecasting Dashboard Using Looker, Big Query And Sql
Published 1/2024
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 2.20 GB | Duration: 3h 36m
Published 1/2024
MP4 | Video: h264, 1920x1080 | Audio: AAC, 44.1 KHz
Language: English | Size: 2.20 GB | Duration: 3h 36m
Create a real world sales-/ marketing forecast model and a goal tracking report that works with minimum data history.
What you'll learn
How to create a real world sales forecasting dashboard.
Learn how to create a simple forecasting model using rolling averages in Google Big Query SQL.
Get familiar with Google Big Query
Learn advanced techniques in Looker Studio and Power BI to visualize your forecast
Requirements
Beginner level knowledge in looker studio, creating dashboards, excel is recommended.
No SQL coding experience is needed, i will show you how to use the code without prior knowledge.
To create a Google Cloud Project you need A CREDIT- or DEBIT CARD. You will NOT encounter any charges and be in the FREE Tier of Google Cloud.
Description
In this course you will learn how to create a great sales forecast and goal tracking dashboard.We will go through everything step by step: Starting with creating a Google Cloud Project (a credit/ debit card is required to create a Project in Google Cloud, but you will not encounter ANY CHARGES when following this course, you will be in the FREE Tier of Google Cloud), Load the data into Google Big Query, creating the model, connecting the data to looker studio or Power BI and creating the dashboard.The core of this course is the simple forecast model, that we are going to create in Google Big Query using SQL. Don´t worry you don´t have to code SQL to use this model. We will technically write different last xx days rolling averages into the future.The great thing is, this model will only need a minimum of data history, e.g. 1 month of past data.Additionally, it can be easily configured to update everyday with the latest data. This makes it really useful in a real-world business case.Of course, I will show you, how you can set it up using your own data.(Loading your own data into Google Big Query, if its not there already, depends on your specific use case and is not part of this course.)With the model sitting in Google Big Query, the results can be easily exported to Looker Studio, Power BI, Tableau or any other visualization tool. In this course we are focusing on looker studio and show you how to recreate the dashboard in Power BI.
Overview
Section 1: Before we start - Prerequisites
Lecture 1 Why it is so important to start using predictive analytics for your career!
Lecture 2 The Solution - Overview
Lecture 3 Is this course for you?
Lecture 4 Overview Course Content
Lecture 5 Can you do this course without prior knowledge?
Lecture 6 Why in a real world example time series forecasting based on M-Learning fails?
Section 2: Set up Google Big Query
Lecture 7 Google Cloud/ Big Query - Account Setup and why it´s free
Lecture 8 Google Cloud and navigating to Google Big Query
Lecture 9 Overview Google Big Query and creating the dataset
Section 3: Example Dataset and loading the data into Google Big Query
Lecture 10 Overview and access the data
Lecture 11 Overview example data that we want to forecast
Lecture 12 Why a separate dim_date table is needed?
Lecture 13 Loading the data into Google Big Query
Section 4: Basic SQL Concepts
Lecture 14 Introduction
Lecture 15 Basic SQL Query
Lecture 16 Group/ Aggregate costs by date [Important for your own data]
Lecture 17 You don´t want to have the same date in multiple rows
Section 5: The SQL - Forecast Query
Lecture 18 What the Query will do, explained in Google Sheets (I)
Lecture 19 What the Query will do, explained in Google Sheets (II)
Lecture 20 The Forecast Query in Google Big Query
Lecture 21 How to make the Forecast Query work (changes you need to make)
Lecture 22 Running the query and explain the result
Lecture 23 Save the query as a view in Google Big Query
Lecture 24 Connect the forecast view to looker studio and take a first look
Lecture 25 Explore the data and create a calculated field
Lecture 26 Create all forecasts, add a budget line, change designs
Lecture 27 Final thoughts on the forecast model and recommendation on usage
Lecture 28 Create all the other forecasts in SQL (sales, sales_volume,…)
Section 6: Creating the Forecast Dashboard in Looker Studio
Lecture 29 Introduction
Lecture 30 Copy the base report
Lecture 31 Creating the first Page [Manager View] and adding a data source
Lecture 32 Review of the first page and general dashboard design overview
Lecture 33 Second Page (1) - Goal Tracking - Scribble
Lecture 34 Second Page (2) - Goal Tracking - Start
Lecture 35 Second Page (2) - Goal Tracking - Add the data
Lecture 36 Second Page (3) - Goal Tracking - Actual and Goal - Visualization Options
Lecture 37 Second Page (4) - Goal Tracking - Complete the First Forecast Model
Lecture 38 Second Page (5) - Goal Tracking - Complete the second Forecast Model
Lecture 39 Second Page (6) - Goal Tracking - Finalizing
Lecture 40 Dashboard Template - How to copy the report
Section 7: How to create the Dashboard in Microsoft Power BI
Lecture 41 Power BI Settings and connecting the data
Lecture 42 Power BI Explore the data and creating a line chart
Lecture 43 Power BI Creating the running sum measures
Lecture 44 Power BI Finalizing
Section 8: How to create the forecast dashboard with your own data?
Lecture 45 Introduction
Lecture 46 Tips on how to load your own data into Google Big Query
Lecture 47 How to modify your data to make the query work - recap of other lectures.
Beginners, intermediates and experts in the field of business intelligence and analytics.,Sales Controller, Data- and Digital-/Marketing-Analysts