Order attribution interactions
The order attribution interactions table contains data about the marketing campaigns that get customers to visit your store and to create orders. You can examine this data by using an attribution model that makes sense for your products and business. For example, you can create queries that measure the success of your campaigns against both a last click and a first click attribution models to view which activities are effective at getting people to visit your site and create orders.
This table tracks customer visits to your store that lead up to and result in orders. It does not track all visits that are made to your store. That information can be found in the online store sessions table. For an order to be attributed to a marketing campaign, your campaign links need to use Urchin Traffic Module (UTM), parameters.
Each row in this table tracks an order attribution interaction. An interaction is either a customer visit where a customer creates an order or a visit that leads up to the order creation visit. You can use the order_id column to group these interactions by orders.
Data in this table is available from April, 2017 and onwards.
| Column | Data type | Description |
|---|---|---|
| order_attribution_interaction_id | varchar | ID of the order attribution interaction. This column is the primary key for the table. |
| order_id | numeric | ID of the order. This column is a foreign key reference to the order_id column in the orders table. |
| shop_id | numeric | ID of the store. This column is a foreign key reference to the shop_id column in the shops table. |
| interaction_started_at | timestamp | Date (ISO 8601) and time (UTC) when the interaction began. The format is YYYY-MM-DD HH:mm:ss (for example, 2016-02-05 17:04:01). |
| referrer_domain | varchar | Hostname of the website that contained the link to your store. For example, facebook.com. |
| referrer_host | varchar | First portion of the URL of the referring website. For example, www.facebook.com. |
| referrer_path | varchar | Path portion of the URL of the referring website. For example, /pages/about. |
| referrer_subdomain | varchar | First portion of the URL of the referring website. For example, if the host is www.facebook.com, then the value in this column is www.facebook. |
| referrer_tld | varchar | Top-level domain of the referring website. For example, if the hostname is www.facebook.com, then the referrer_tld is com. |
| referrer_url | varchar | Full URL of the referring website. That is, the website that contained the link to your store. For example, facebook.com/pages/about. |
| utm_campaign_content | varchar | Content used in the Urchin Traffic Module parameters (UTM) campaign, such as details about A/B testing and content-targeted ads. For example, 300x300_jeans.jpg. |
| utm_campaign_medium | varchar | UTM campaign medium. For example, Email or CPC. |
| utm_campaign | varchar | Name of the UTM campaign. For example, Spring sale or Jeans. |
| utm_campaign_source | varchar | Referral source of the UTM campaign. This is the name of the website or application where the referral link exists. For example, Facebook. |
| utm_campaign_term | varchar | UTM campaign parameters captured from the URL of the referral link. These parameters can be the keywords used in a paid search ad. For example, Jeans. |
| utm_campaign_content | varchar | UTM campaign content. |
| landing_page_url | varchar | Full URL of the first page visited during the session. For example, https://www.myshop.com/products/cool-tshirt?utm_source=Shop&utm_medium=.... |
| exit_page_path | varchar | Path portion of the URL of the first page that the visitor visited during the interaction. This field is the same as landing_page_url except that it doesn’t contain the name of the store or any parameters. For example, /products/cool-tshirt. |
| is_first | boolean | Whether the customer created an order on the first visit to your store. For each order, there will be one interaction row only that will have its is_first column set to True. |
| is_last | boolean | Whether the customer created the order on the last visit to your store. For each order, there will be one interaction row only that will have its is_last column set to True. If your customer creates an order on their first visit, then the order will have one interaction row only. This row will have both of its is_first and is_last columns set to True. |
Order attribution interaction data is unavailable from before April, 2017. There are other instances when data might be unavailable, such as when apps and private browsers prevent cookie tracking.
The following SQL query uses a first click attribution model to attribute conversions to a marketing campaign. It measures the success of a campaign to get your customers to visit your store and to convert (create an order) during this first visit:
The following SQL query uses a last click attribution model. This model is useful when your customers visit your store more than once before they make a purchase. It measures the overall success of marketing campaign to get a customer to create an order: