Build Google ECommerce Dimensions

Now that you are finished connecting yourGoogle ECommerceaccount, what can you do with that data in MBI? In this article, we walk you through building dimensions that will link your eCommerce data with your orders and customer data.

The dimensions we cover will 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 article, you need a Google ECommerce table, an orders table, and a customers table. Those tables have to 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, we create the first three dimensions in the list below. Next, we use those dimensions to create three user/customer dimensions in the customers table. To finish up, we join those columns to the orders table.

Here are the dimensions we cover:

• 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

In this example, we build the Order’s Google Analytics Source dimension.

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

2. Click Create a Column.

3. Name the column.

4. Select Joined Column from the definition dropdown. In this example, we are working with a one-to-one relationship, matching the eCommerce.transactionID column to exactly one row of the orders table.

5. Next, we 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 we need is not available, so we 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 will change 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

In this example, we build the Customer’s first order’s Google Analytics source dimension.

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

2. Click Create a Column.

3. Name the column.

4. For this example, we 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 we 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. We only want orders included in the orders we count filter set to be included, so it is important that this filter set is selected.

9. Click Add Filter. We want to find the customer’s first order’s Google Analytics source, so we 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 will change 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 we 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.

In this example, we join 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 our 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 will join the customer dimensions 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. Give it a try, and as we mentioned before, check out the end of the article if you need help.

Wrapping Up

We finished creating the dimensions, which means we can now create powerful analyses that track the performance of our various channels and campaigns. We know you are eager to get started, but remember the new columns will not be available until after the next update completes.

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

I am stuck! what is different?

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

Customers table: When creating the Customer's first order's Google Analytics medium and campaign dimensions, the difference will be the columns selected in step 5. In our 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 will be the columns selected in step 5. In our example, the column was Customer's first order's Google Analytics source.

Business.Adobe.com resources