Get data insights from a contribution analysis model using a summable ratio metric

In this tutorial, you use a contribution analysis model to analyze the contribution of the cost of sales ratio in the Iowa liquor sales dataset. This tutorial guides you through performing the following tasks:

• Create an input table based on publicly available Iowa liquor data.
• Create a contribution analysis model that uses a summable ratio metric. This type of model summarizes the values of two numeric columns and determines the ratio differences across the control and test dataset for each segment of the data.
• Get the metric insights from the model by using the ML.GET_INSIGHTS function.

Before starting this tutorial, you should be familiar with the contribution analysis use case.

Required permissions

• To create the dataset, you need the bigquery.datasets.create Identity and Access Management (IAM) permission.

• To create the model, you need the following permissions:

  • bigquery.jobs.create
  • bigquery.models.create
  • bigquery.models.getData
  • bigquery.models.updateData

• To run inference, you need the following permissions:

  • bigquery.models.getData
  • bigquery.jobs.create

Costs

In this document, you use the following billable components of Google Cloud:

• BigQuery ML: You incur costs for the data that you process in BigQuery.

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

For more information about BigQuery pricing, see BigQuery pricing in the BigQuery documentation.

Before you begin

1. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Go to project selector

2. Make sure that billing is enabled for your Google Cloud project.

3. Enable the BigQuery API.

   Enable the API

Create a dataset

Create a BigQuery dataset to store your ML model.

{
  "datasetReference": {
     "datasetId": "bqml_tutorial"
  }
}

Create a table of input data

Create a table that contains test and control data to analyze. The following query creates two intermediate tables, a test table for liquor data from 2021 and a control table with liquor data from 2020, and then performs a union of the intermediate tables to create a table with both test and control rows and the same set of columns.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, run the following statement:

   CREATE OR REPLACE TABLE bqml_tutorial.iowa_liquor_sales_data AS
   (SELECT
     store_name,
     city,
     vendor_name,
     category_name,
     item_description,
     SUM(sale_dollars) AS total_sales,
     SUM(state_bottle_cost) AS total_bottle_cost,
     FALSE AS is_test
   FROM `bigquery-public-data.iowa_liquor_sales.sales`
   WHERE EXTRACT(YEAR FROM date) = 2020
   GROUP BY store_name, city, vendor_name, category_name, item_description, is_test)
   UNION ALL
   (SELECT
     store_name,
     city,
     vendor_name,
     category_name,
     item_description,
     SUM(sale_dollars) AS total_sales,
     SUM(state_bottle_cost) AS total_bottle_cost,
     TRUE AS is_test
   FROM `bigquery-public-data.iowa_liquor_sales.sales`
   WHERE EXTRACT(YEAR FROM date) = 2021
   GROUP BY store_name, city, vendor_name, category_name, item_description, is_test);

Create the model

Create a contribution analysis model:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, run the following statement:

   CREATE OR REPLACE MODEL bqml_tutorial.liquor_sales_model
   OPTIONS(
     model_type = 'CONTRIBUTION_ANALYSIS',
     contribution_metric = 'sum(total_bottle_cost)/sum(total_sales)',
     dimension_id_cols = ['store_name', 'city', 'vendor_name', 'category_name', 'item_description'],
     is_test_col = 'is_test',
     min_apriori_support = 0.05
   ) AS
   SELECT * FROM bqml_tutorial.iowa_liquor_sales_data;

The query takes approximately 35 seconds to complete, after which the model liquor_sales_model appears in the bqml_tutorial dataset in the Explorer pane. Because the query uses a CREATE MODEL statement to create a model, there are no query results.

Get insights from the model

Get insights generated by the contribution analysis model by using the ML.GET_INSIGHTS function.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, run the following statement to select columns from the output for a summable ratio metric contribution analysis model:

   SELECT
   contributors,
   metric_test,
   metric_control,
   metric_test_over_metric_control,
   metric_test_over_complement,
   metric_control_over_complement,
   aumann_shapley_attribution,
   apriori_support
   contribution
   FROM
     ML.GET_INSIGHTS(
       MODEL `bqml_tutorial.liquor_sales_model`)
   ORDER BY aumann_shapley_attribution DESC;

The first several rows of the output should look similar to the following. The values are truncated to improve readability.

In the output, you can see that the data segment city=DES MOINES has the highest contribution of change in the sales ratio. You can also see this difference in the metric_test and metric_control columns, which show that the ratio decreased in the test data compared to the control data. Other metrics, such as metric_test_over_metric_control, metric_test_over_complement, and metric_control_over_complement, compute additional statistics that describe the relationship between the control and test ratios and how they relate to the overall population. For more information, see Output for summable ratio metric contribution analysis models.

Clean up

1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.