Retail is being renamed to Vertex AI Search for retail. We are in the process of updating content to reflect the new branding.

Get dashboards that show key performance indicators

This page explains the process for getting Looker dashboards that can display key performance indicators for your retail business.

Before you begin

Before you can get Looker dashboards for your retail business, you must:

Have your retail data uploaded using Vertex AI Search for retail. For more information, see:
Export your retail data from Vertex AI Search for retail into BigQuery. For more information, see Export your data into BigQuery .

Alternatively, if your product and user event data is already in BigQuery, in the Vertex AI Search for retail format, you can use that data to get Looker dashboards. In this case you don't need to upload your retail data and export it to BigQuery. For more information about the BigQuery format, see Product schema and About user events .

Set up Looker

Get a Looker instance from your Looker Sales Engineer or Professional Services consultant.
Set up a connection in Looker to BigQuery by following the instructions in Google BigQuery in the Looker documentation. Your Looker Sales Engineer or Professional Services consultant will assist you in this task if needed. Save the name of the connection that you set up to use in Install the Looker Block .

Convert your user event BigQuery view to a table

Looker Dashboards require your user event data to be in a BiqQuery table, not a view.

To convert your user event BigQuery view to a table, do the following:

Replace the variables in the following SQL example code as follows:
- rdm_user_event_view. The project, dataset, and table IDs of the user event view that you exported to BigQuery. The format is project_id.dataset_id.table_id .
- rdm_user_event_table. The project, dataset, and table IDs in BigQuery for the new BigQuery table. Use the same project ID and dataset ID that you used for the user event view that you exported to BigQuery. For the table ID, use tbl_events . The format is project_id.dataset_id.tbl_events .
```
 CREATE 
  
 OR 
  
 REPLACE 
  
 TABLE 
  
 ` RDM_USER_EVENT_TABLE 
` 
 AS 
  
 SELECT 
  
 * 
  
 FROM 
  
 ` RDM_USER_EVENT_VIEW 
` 
```
Copy the SQL code sample from the previous step.
Open the BigQuery page in the Google Cloud console.

Go to the BigQuery page
If it's not already selected, select the project that contains your user event table.
In the Editorpane, paste the SQL code sample.
Click Runand wait for the query to finish running.

Your new table is written to the location in BigQuery that you set using the rdm_user_event_tablevariable. Save this location to use in Install the Looker Block .

Create a materialized view for sales

Use the following SQL example code to create a materialized view that your Looker dashboards can query. For more information, see Introduction to materialized views in the BigQuery documentation.

To create a materialized view for sales, do the following:

Replace the variables in the following SQL example code as follows:

mv_sales. The project, dataset, and table IDs in BigQuery for the new materialized view for sales. Use the same project ID and dataset ID that you used for the user event table that you exported to BigQuery. For the table ID, use mv_sales . The format is project_id.dataset_id.mv_sales .
rdm_user_event_table. The project, dataset, and table IDs of the user event table that you exported to BigQuery. The format is project_id.dataset_id.table_id .

 CREATE 
  
 MATERIALIZED 
  
 VIEW 
  
 ` MV_SALES 
` 
 OPTIONS 
 ( 
  
 friendly_name 
 = 
 "Sales View" 
 , 
  
 description 
 = 
 "View of Sales Data" 
 , 
  
 labels 
 = 
 [ 
 ( 
 "team" 
 , 
  
 "cloud_retail_solutions" 
 ), 
  
 ( 
 "environment" 
 , 
  
 "development" 
 ) 
 ] 
 ) 
 AS 
 SELECT 
  
 EXTRACT 
 ( 
 DATE 
  
 FROM 
  
 event_time 
 ) 
  
 as 
  
 day 
 , 
  
 session_id 
  
 as 
  
 session 
 , 
  
 ANY_VALUE 
 ( 
 TRIM 
 ( 
 UPPER 
 ( 
 visitor_id 
 ))) 
  
 as 
  
 visitor 
 , 
  
 ANY_VALUE 
 ( 
 TRIM 
 ( 
 UPPER 
 ( 
 user_info 
 . 
 user_id 
 ))) 
  
 as 
  
 user 
 , 
  
 ANY_VALUE 
 ( 
 TRIM 
 ( 
 UPPER 
 ( 
 purchase_transaction 
 . 
 id 
 ))) 
  
 as 
  
 tx_id 
 , 
  
 MAX 
 ( 
 purchase_transaction 
 . 
 revenue 
 ) 
  
 as 
  
 tx_total 
 , 
  
 MAX 
 ( 
 purchase_transaction 
 . 
 tax 
 ) 
  
 as 
  
 tx_tax 
 , 
  
 MAX 
 ( 
 purchase_transaction 
 . 
 cost 
 ) 
  
 as 
  
 tx_cost 
 , 
  
 MAX 
 ( 
 purchase_transaction 
 . 
 currency_code 
 ) 
  
 as 
  
 tx_cur 
 , 
  
 SUM 
 ( 
 d 
 . 
 quantity 
 * 
 d 
 . 
 product 
 . 
 price_info 
 . 
 price 
 ) 
  
 as 
  
 product_total 
 , 
  
 COUNT 
 ( 
 d 
 ) 
  
 AS 
  
 basket_size 
 FROM 
  
 ` RDM_USER_EVENT_TABLE 
` 
 , 
  
 UNNEST 
 ( 
 product_details 
 ) 
  
 d 
 WHERE 
  
 event_type 
  
 = 
  
 'purchase-complete' 
 GROUP 
  
 BY 
  
 EXTRACT 
 ( 
 DATE 
  
 FROM 
  
 event_time 
 ), 
  
 session_id 
 ;

Copy the SQL code sample from the previous step.
Open the BigQuery page in the Google Cloud console.

Go to the BigQuery page
If it's not already selected, select the project that contains your user event table.
In the Editorpane, paste the SQL code sample.
Click Runand wait for the query to finish running.

Your new materialized view for sales is written to the location in BigQuery that you set using the mv_salesvariable. Save this location to use in Install the Looker Block .

Install the Looker Block

In this procedure, you download a Looker Block using the Looker Marketplace website that is connected to your Looker instance. We also have a public Looker Marketplace , where you can view the various Looker Blocks that are available, but you cannot download Looker Blocks from that website.

Go to the Looker instance that your Looker Sales Engineer helped you set up in Set up Looker .
Click , and then click Discover.

The Looker Marketplace page appears.
In the search box, enter discovery .

The Modelspane appears, displaying Discovery: E-Commerce Insights.
Click Discovery: E-Commerce Insights.
Click Install > Install.
If you accept the license agreement, click Accept.
If you agree to allow Looker to do the actions shown in the dialog, click Agree and Continue.

The Configurationdialog appears.
Set the Configurationdialog:
1. In the Events Tablebox, enter the project, dataset, and table IDs of the user event table that you exported to BigQuery. The format is project_id.dataset_id.table_id.
2. In the Products Tablebox, enter the project, dataset, and table IDs of the retail product table that you exported to BigQuery. The format is project_id.dataset_id.table_id.
3. In the Sales Materialized Viewbox, enter the project, dataset, and table IDs of the materialized view for sales that you created in Create a materialized view for sales . The format is project_id.dataset_id.table_id.
4. In the Connectionbox, select the name of the connection that you created in Set up Looker .
5. Click Installto complete the configuration and install the Looker Block.
  
  The Looker Marketplacepage appears.
On the Looker Marketplacepage, in the Discovery: E-Commerce Insightsrow, click Open.

The Discovery: E-Commerce Insightspage appears.
Click a pane under the Dashboardsheading to view a dashboard.

Customize the Looker Block

This Looker Block uses refinements for customization. For more information about using refinements to customize Looker Blocks installed from the Looker Marketplace, see Customizing Looker Marketplace Blocks .