Sales Forecasting Dashboard Using Looker, Big Query And Sql

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