DocumentationCommerceCommerce Intelligence User Guide

Build Google ECommerce Dimensions

Last update: Mon Jul 24 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • Developer
  • User
NOTE
Requires Admin permissions.

Now that you are finished connecting yourGoogle ECommerce account, what can you do with that data in Commerce Intelligence? This topic walks you through building dimensions that link your eCommerce data with your orders and customer data.

The dimensions covered give you the ability to build analyses that answer vital questions about your marketing channels and campaigns. What percent of revenue comes from each source? How does the lifetime value of Facebook acquired customers compare to those from Google?

Prerequisites and overview

To create the dimensions in this topic, you need a Google ECommerce table, an orders table, and a customers table. Those tables must be synced to your Data Warehouse before dimensions can be built. Tables that are synced display in the Synced Tables section of the Data Warehouse Manager.

Here is a quick look at syncing tables and columns if you need a refresher:

After creating a join from the orders table to the Google eCommerce table, you create the first three dimensions in the list below. Next, you use those dimensions to create three user/customer dimensions in the customers table. To finish up, you join those columns to the orders table.

Here are the dimensions covered:

  • Orders table

  • Order’s Google Analytics source

  • Order’s Google Analytics medium

  • Order’s Google AnalyticsA campaign

  • Customer’s first order’s Google Analytics source

  • Customer’s first order’s Google Analytics medium

  • Customer’s first order’s Google Analytics campaign

  • Customers table

  • Customer’s first order’s Google Analytics source

  • Customer’s first order’s Google Analytics medium

  • Customer’s first order’s Google Analytics campaign

Building the dimensions

To create dimensions, open the Data Warehouse Manager by clicking Data > Data Warehouse.

Orders table, round 1

This example builds the Order’s Google Analytics Source dimension.

  1. From the list of tables in the Data Warehouse, click the table (in this case, orders) that contains your order information.

  2. Click Create a Column.

  3. Name the column.

  4. Select Joined Column from the definition dropdown. This example works with a one-to-one relationship, matching the eCommerce.transactionID column to exactly one row of the orders table.

  5. Next, you need to define the path, or how the table and column being used are connected. Click the Select a table and column dropdown.

  6. The path you need is not available, so you need to create a new one. Click Create new Path.

  7. In the window that displays, set the Many side to orders.order\_id, or the column in the orders table that contains the order ID.

  8. On the One side, find the Google ECommerce table, then set the column to transactionID.

  9. Click Save to create the path.

  10. After the path is added, click the Select table and column dropdown again.

  11. Locate the ECommerce table, and then click the Source column. This ties the orders to the source information.

  12. Once you are back in the table schema, Click Save again to create the dimension.

Here is a look at the whole process:

Next, try creating Order’s Google Analytics medium and campaign. Not much changes for these dimensions, so give it a try. But if you get stuck, you can check out the end of this article to see what is different.

Customers table

This example builds the Customer’s first order’s Google Analytics source dimension.

  1. From the list of tables in the Data Warehouse, click the table (in this case, customers) that contains your customer information.

  2. Click Create a Column.

  3. Name the column.

  4. For this example, select the is MAX definition from the definition dropdown. The is MIN definition could also work if applied to a text column with only one possible value. The important part is ensuring proper filters are set, which you do later.

  5. Click the Select a table and column dropdown and select the orders table, then the Order's Google Analytics source column.

  6. Click Save.

  7. Once you are back in the table schema, click the Options dropdown, then Filters.

  8. Click Add Filter Set and then select the Orders we count set. You only want orders included in the orders that you count filter set to be included, so it is important that this filter set is selected.

  9. Click Add Filter. You want to find the customer’s first order’s Google Analytics source, so you need to add a filter:

    _orders.Customer’s order number = 1

    _

  10. Click Save to create the dimension.

Next, try creating Customer’s first order’s Google Analytics medium and campaign. Not much changes for these dimensions, so give it a try. But if you get stuck, you can check out the end of this article to see what is different.

Bonus: Orders table, round 2

You can stop here if you want, but this section enables further analysis by bringing the Customer’s first order’s Google Analytics dimensions you created in the last section into the orders table. Creating the dimensions in this section lets you analyze all the metrics built on your orders table - Revenue, Number of orders, Distinct buyers, and so on - using the Google Analytics attributes of a customer’s first order.

This example joins the Customer's first order's Google Analytics source dimension to the orders table.

  1. From the list of tables in the Data Warehouse, click the table (in this case, orders) that contains your order information.
  2. Click Create a Column.
  3. Name the column.
  4. Select Joined Column from the definition dropdown. This joins the customer dimensions that you created in the previous section to the orders table.
  5. Click the Select a table and column dropdown, then select the customers table and the Customer's first order's Google Analytics source column.
  6. If a path does not automatically populate, select the path that best connects the customers and orders tables.
  7. Click Save to create the dimension.

Here is a look at the whole process:

Finish up by joining the Customer's first order's medium and campaign dimensions to the orders table. Join the dimensions, and if there are problems, then check out the end of the article if you need help.

Wrapping Up

You finished creating the dimensions, which means you can now create powerful analyses that track the performance of your various channels and campaigns. Remember that the new columns will not be available until after the next update completes.

Some of the more popular dimensions are covered in this topic, but the sky is the limit - try creating your own or feel free to ping us if you want help with exploring other options.

Additional Notes

Orders table #1: When creating the Order's Google Analytics medium and campaign dimensions, the difference is the columns selected in step 12. In this example, the column was Source.

Customers table: When creating the Customer's first order's Google Analytics medium and campaign dimensions, the difference is the columns selected in step 5. In this example, the column was Order's Google Analytics source.

Orders table #2: When joining the Customer's first order's Google Analytics medium and campaign columns to the orders table, the difference is the columns selected in step 5. In this example, the column was Customer's first order's Google Analytics source.

e1f8a7e8-8cc7-4c99-9697-b1daa1d66dbc