Magento Business Intelligence Help Center

Building Google ECommerce dimensions with order and customer data

BROUGHT TO YOU BY

Avatar

Hey! You need Admin permissions to do this.

Now that you’re finished connecting your Google ECommerce account, what can you do with that data in Magento BI? In this article, we’ll walk you through building dimensions that’ll link your eCommerce data with your orders and customer data.

The dimensions we'll 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?

Let’s get started!

Prerequisites and overview

To create the dimensions in this article, you'll 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's 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’ll create the first three dimensions in the list below. Next, we’ll use those dimensions to create three user/customer dimensions in the customers table. To finish up, we’ll join those columns to the orders table.

Here are the dimensions we’ll cover:

  • Orders table
    • Order's GA source
    • Order's GA medium
    • Order's GA campaign
    • Customer's first order's GA source
    • Customer's first order's GA medium
    • Customer's first order's GA campaign
  • Customers table
    • Customer's first order's GA source
    • Customer's first order's GA medium
    • Customer's first order's GA 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’ll build the Order’s GA 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 the Create a Column button.
  3. Name the column.
  4. Select Joined Column from the definition dropdown. In this example, we’re 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 isn’t 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’re back in the table schema, click Save again to create the dimension.

Here’s a look at the whole process:

That’s it! Next, try creating Order’s GA 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’s different.

Customers table

In this example, we’ll build the Customer’s first order’s GA 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 the Create a Column button.
  3. Name the column.
  4. For this example, we’ll 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’ll do later.
  5. Click the Select a table and column dropdown and select the orders table, then the Order’s GA source column.
  6. Click Save.
  7. Once you’re 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’s important that this filter set is selected.
  9. Click Add Filter. We want to find the customer’s first order’s GA source, so we need to add a filter:

    orders.Customer’s order number = 1

  10. Click Save to create the dimension.

Here’s a look at the whole process:

 

That’s it! Next, try creating Customer’s first order’s GA 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’s 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 GA 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 GA attributes of a customer’s first order.

In this example, we’ll join the Customer’s first order’s GA 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 the Create a Column button.
  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 GA source column.
  6. If a path doesn’t automatically populate, select the path that best connects the customers and orders tables.
  7. Click Save to create the dimension.

Here’s a look at the whole process:



That’s it! 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’re eager to get started, but remember the new columns won’t be available until after the next update completes.

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

 

 

I’m stuck! What’s different?

Orders table #1: When creating the Order’s GA 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 GA medium and campaign dimensions, the difference will be the columns selected in step 5. In our example, the column was Order’s GA source.

Orders table #2: When joining the Customer’s first order’s GA 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 GA source.

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk