Looker and BigQuery ML: Two Approaches for KPI Monitoring

Introduction

Working with cross-organizational business teams on strategic measures development, we noticed one common analytical requirement in all areas. And this is: monitoring and reporting the KPI targets.

If we focus on monitoring, the main task is tracking the targets against the actual values. With this said, the targets should first be defined.

However, defining the targets on a multi-series dataset is not always easy for business users. In other words, the complexity rises with each new series in the dataset.

We will share one example.

Let’s say we want to track the revenue KPI for 40+ shops. To do so, we need to define the upper and lower limits of the expected revenue per shop level, i.e. 80+ targets. And if we want to track the revenue per shop and country level, we can calculate the number of needed targets with the formula: number_of_shops x number_of_countries x 2 (upper and lower target).

To overcome the complexity of defining the KPI targets on the business side, our Data and Analytics team has developed two analytical approaches:

BigQuery ML approach on shop level: used for setting the targets of continuous measures like revenue or marketing costs.
The hard-coded approach in Looker on shop-group level: used for setting the targets of the ratio measures, like [cost revenue ratio](https://www.indeed.com/career-advice/career-development/cost-revenue-ratio#:~:text=What%20is%20a%20cost%20revenue,including%20marketing%20and%20shipping%20costs.) or [return on ad spend](https://www.klipfolio.com/metrics/marketing/return-on-ad-spend-roas).

Let’s start with an explanation of the development stages. 🙂

Methodological framework

Three main stages define the development framework:

Stage 1: Defining the KPI targets
Stage 2: Data modelling
Stage 3: Insights presentation

In addition, each stage is divided into two steps, as presented in the image below.

Break down of the development methodology [Image by author]

In the development part, we will use two different datasets to show the implementation of the above-presented steps:

(1) the cost marketing dataset, and
(2) the shop order dataset.

From these two datasets, we will calculate the following KPIs:

marketing costs – the costs of all traffic channels (e.g. Google, Facebook, etc.) per shop,
cost revenue ratio (CRR) – the ratio of marketing costs and revenue per shop,
return on ad spend (ROAS) – inverse CRR, i.e. the ratio of revenue and marketing costs per shop.

As mentioned in the introduction, the aim is to show how to monitor and report the strategic KPIs with two distinct approaches.

So, let’s start with the hands-on part in Looker. 🙂

Stage 1/3: Defining the KPI targets with the BigQuery ML approach and the hard-coded approach in Looker

Step 1/2: Bigquery ML approach: setting targets for the marketing costs

We will use the BigQuery ML (BQML) time series model ARIMA_PLUS to find the marketing costs targets.

Generally speaking, to train the BQML multiple time-series model, we need to define the following [1]:

model_type = type of the time-series model, i.e. ARIMA_PLUS
time_series_data_col= the target (prediction) variable = marketing costs
time_series_timestamp_col = the timestamp variable = log date of the cost
time_series_id_col = the series variable = shop
horizon = prediction horizon, i.e. how far in the future we want to predict
data_frequency= timeframe frequency, i.e. daily, monthly, weekly, etc.

After explaining the model architecture, we can place the BQML model in one LookML view:

The outcomes of the model are saved to your Looker project’s predefined database (check Admin section → Database → Connections).

— – – – – – – – – – – – – – – – – – – – – – – – – – – –

Step 2/2: The hard-coded approach in Looker: setting the targets for the cost revenue ratio (CRR) and return on ad spend (ROAS)

This step is straightforward as the task is to:

group the shops into shop groups, i.e. Category/Brand/Performance shop group,
set the hard-coded targets for CRR and ROAS KPIs per each shop group.

For this, we will use the [manifest](https://docs.looker.com/fr/reference/manifest-reference) file in the LookML layer and create constants for each shop group and constants for CRRand ROAS targeted values per shop group.

An example for creating the constants is shown in the image below, and the complete manifest code can be found HERE.

Amazing. Now we can switch to Stage 2 and create a data model to merge the outcomes from the steps developed in this stage.

Stage 2/3: Data modelling: assembling actual and target KPI values

Now comes the neat part, i.e. data modelling part.

Step 1/2: LookML modelling: Combining the results in one view

This step includes the development of one derived table that is a product of the UNION of the three queries:

a query ** that retrieves the target value**s of marketing costs per shop (BQML model outcomes),
a query that retrieves the actual values of marketing costs per shop (calculated from the cost marketing dataset), and
a query that retrieves the actual values of revenue per shop (calculated from the shop order dataset).

As you can see from the above view, by calculating the marketing costs and revenue per shop, we were able to get the actual CRR and ROAS values too.

In addition, we compared the actual values to the target values for these two KPIs.

The last part left in this stage is creating a data model on top of this view.

— – – – – – – – – – – – – – – – – – – – – – – – – – – –

Step 2/2: LookML modelling: Creating a data model on top of the view created in __ Step 1

Voila! We successfully concluded the data modelling part and can now move to the last stage.

Stage 3/3: Insights presentation: create control charts and control tables

We can finally present the data insights, i.e. our three KPIs, in visual form.

Step 1/2: Looker: Create a control chart to track marketing costs

Tutorial:

Select filters: Marketing Shop Types, Order Date, Shop Brand.
Select dimensions and measures: Order date, PM Costs Predicted [€], PM Costs Lower Bound[€], PM Costs Upper Bound [€], Above/Below Predicted cost value [%].
Select the Line chart type to visualize the outcomes.

Looker: Control chart for tracking marketing costs targets [Image by author]

Functionality:

with the above-described setup, we can track marketing costs daily for each shop in the dataset.

— – – – – – – – – – – – – – – – – – – – – – – – – – – –

Step 2/2: Looker: Create a control table to track CRR and ROAS KPIs

Tutorial:

Select filters: Marketing Shop Types, Order Date, Shop Brand.
Select dimensions and measures: Order date, Marketing Shop Types OR Shop Brand, CRR Actual [%], CRR Target [%], ROAS Actual, ROAS Target, Above/Below predicted CRR value [%], Above/Below predicted ROAS value [%].
Select the Table (Report) visualization type to present the outcomes.

Looker: Control table for tracking CRR and ROAS targets [Image by author]

Functionality:

with the control table, we can track the CRR and ROAS KPIs daily per defined shop group.

Summary

A short recap of the implementation stages presented in this article is as follows:

First, we presented two distinct approaches for defining the KPI targets for multi-series datasets; BigQuery ML and the hard-coded approach in Looker.
Second, we showed how to combine the actual and target KPI values in the data modelling stage.
Finally, we created a Looker tutorial for making a control chart/control table and presenting the data insights visually.

With these summary notes, we are completing our implementation journey for monitoring and reporting the KPI targets. Happy coding! 🙂

References:

[1] BigQuery ML ARIMA PLUS documentation, accessed: May 1st 2022, https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-syntax-create-time-series