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

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

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

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

Required permissions

• To create the dataset, you need thebigquery.datasets.createIdentity 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 thepricing calculator.

For more information about BigQuery pricing, seeBigQuery pricing inthe BigQuery documentation.

Before you begin

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

   Roles required to select or create a project

   • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
   • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.
   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

2. Verify that billing is enabled for your Google Cloud project.

3. Enable the BigQuery API.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

   Enable the API

Create a dataset

Create a BigQuery dataset to store your ML model.

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

importgoogle.cloud.bigquerybqclient=google.cloud.bigquery.Client()bqclient.create_dataset("bqml_tutorial",exists_ok=True)

Create a table of input data

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

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

   Go to BigQuery

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

   CREATEORREPLACETABLEbqml_tutorial.iowa_liquor_sales_dataAS(SELECTstore_name,city,vendor_name,category_name,item_description,SUM(sale_dollars)AStotal_sales,SUM(state_bottle_cost)AStotal_bottle_cost,FALSEASis_testFROM`bigquery-public-data.iowa_liquor_sales.sales`WHEREEXTRACT(YEARFROMdate)=2020GROUPBYstore_name,city,vendor_name,category_name,item_description,is_test)UNIONALL(SELECTstore_name,city,vendor_name,category_name,item_description,SUM(sale_dollars)AStotal_sales,SUM(state_bottle_cost)AStotal_bottle_cost,TRUEASis_testFROM`bigquery-public-data.iowa_liquor_sales.sales`WHEREEXTRACT(YEARFROMdate)=2021GROUPBYstore_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 theBigQuery page.

   Go to BigQuery

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

   CREATEORREPLACEMODELbqml_tutorial.liquor_sales_modelOPTIONS(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)ASSELECT*FROMbqml_tutorial.iowa_liquor_sales_data;

The query takes approximately 35 seconds to complete, after which the modelliquor_sales_model appears in thebqml_tutorial dataset. Because thequery uses aCREATE MODEL statement to create a model, there are no queryresults.

Get insights from the model

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

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

   Go to BigQuery

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

   SELECTcontributors,metric_test,metric_control,metric_test_over_metric_control,metric_test_over_complement,metric_control_over_complement,aumann_shapley_attribution,apriori_supportcontributionFROMML.GET_INSIGHTS(MODEL`bqml_tutorial.liquor_sales_model`)ORDERBYaumann_shapley_attributionDESC;

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 segmentcity=DES MOINEShas the highestcontribution of change in the sales ratio. You can also see this differencein themetric_test andmetric_control columns, which show that the ratiodecreased in the test data compared to the control data. Other metrics,such asmetric_test_over_metric_control,metric_test_over_complement,andmetric_control_over_complement, compute additional statistics thatdescribe the relationship between the control and test ratios and how theyrelate to the overall population. For more information, seeOutput for summable ratio metric contribution analysis models.

Clean up

Caution: Deleting a project has the following effects:
• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as anappspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

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

   Go to Manage resources

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