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:

Set up Looker

  1. Get a Looker instance from your Looker Sales Engineer or Professional Services consultant.

  2. 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:

  1. 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`

  2. Copy the SQL code sample from the previous step.

  3. Open the BigQuery page in the Google Cloud console.

    Go to the BigQuery page

  4. If it's not already selected, select the project that contains your user event table.

  5. In the Editor pane, paste the SQL code sample.

  6. Click Run and 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_table variable. 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:

  1. 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;

  2. Copy the SQL code sample from the previous step.

  3. Open the BigQuery page in the Google Cloud console.

    Go to the BigQuery page

  4. If it's not already selected, select the project that contains your user event table.

  5. In the Editor pane, paste the SQL code sample.

  6. Click Run and 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_sales variable. 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.

  1. Go to the Looker instance that your Looker Sales Engineer helped you set up in Set up Looker.

  2. Click , and then click Discover.

    The Looker Marketplace page appears.

  3. In the search box, enter discovery.

    The Models pane appears, displaying Discovery: E-Commerce Insights.

  4. Click Discovery: E-Commerce Insights.

  5. Click Install > Install.

  6. If you accept the license agreement, click Accept.

  7. If you agree to allow Looker to do the actions shown in the dialog, click Agree and Continue.

    The Configuration dialog appears.

  8. Set the Configuration dialog:

    1. In the Events Table box, 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 Table box, 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 View box, 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 Connection box, select the name of the connection that you created in Set up Looker.

    5. Click Install to complete the configuration and install the Looker Block.

      The Looker Marketplace page appears.

  9. On the Looker Marketplace page, in the Discovery: E-Commerce Insights row, click Open.

    The Discovery: E-Commerce Insights page appears.

  10. Click a pane under the Dashboards heading 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.