Try out all OWOX BI features with a trial period Start for free

Data needed to work with OWOX BI Attribution

Note:OWOX BI Attribution is available only for the Reports & Attribution and Enterprise subscription plans.

User behavior data

As a source for your ML Funnel Based Attribution model, you can use user behavior data collected to Google BigQuery with OWOX BI Pipeline or with the Google BigQuery Export feature for Google Analytics.

Below, is a list of fields that are used to calculate ML Funnel Based Attribution model for your project.

Also, note that the e-commerce action data is available only if you have the Google Analytics Enhanced Ecommerce module.

Parameter name Field in OWOX BI Pipeline schema Field in Google BigQuery Export for Google Analytics schema
Session ID sessionId fullVisitorId and visitId
Client ID clientId fullVisitorId
User ID customDimensions.index and customDimensions.value
E-commerce actions hits.eCommerceActionType hits.eCommerceAction.action_type
Product ID hits.product.productSku hits.product.productSKU
Product category ID hits.product.productCategory hits.product.v2ProductCategory
Geo region geoNetwork.region geoNetwork.region
Bounce rate totals.pageviews totals.bounces
Event Category hits.eventInfo.eventCategory hits.eventInfo.eventCategory
Event Action hits.eventInfo.eventAction hits.eventInfo.eventAction
Event Label hits.eventInfo.eventLabel hits.eventInfo.eventLabel
Campaign Source trafficSource.source trafficSource.source
Campaign Medium trafficSource.medium trafficSource.medium
Campaign Name trafficSource.campaign trafficSource.campaign
Campaign Term trafficSource.keyword trafficSource.keyword
Campaign Content trafficSource.adContent trafficSource.adContent
Transaction ID hits.transaction.transactionId hits.transaction.transactionId
Session end time hits.time visitStartTime and hits.time
Session date in the format “YYYYMMDD” date date

Data about advertising costs and transactions revenue

Advertising costs and transaction revenue data is required to calculate ROI and ROAS after you get the results of your model calculation.

To get advertising costs data in Google BigQuery, firstly import it into Google Analytics.
Here are the steps to do that:

  1. Connect your Google Analytics with Google Ads to import costs for non-Google campaigns.
  2. Set up automatic cost data import from Facebook, Bing and other sources.
  3. Create OWOX BI Pipeline that exports cost data from Google Analytics to Google BigQuery.

Transactions revenue data is available in both data sources, no additional setup required.

Data type Field in OWOX BI Pipeline schema Field in Google BigQuery Export for Google Analytics schema
Costs data Field trafficSource.attributedAdCost Table of Google Analytics costs data export to Google BigQuery
Transactions revenue data Fields hits.product.productPrice and hits.product.productQuantity Fields hits.product.productPrice and hits.product.productQuantity

Additional transactions revenue data stored in ERP, CRM, call-centre and other sources

With ML Funnel Based Attribution, you can attribute value not only for Google Analytics transactions, but also for transactions from other sources like CRM system, offline store, or call-center.

For that, you need transaction data stored in a Google BigQuery view or table in the structure as shown below.

Fields highlighted in bold are required to be filled in. All other fields must be present in the table, but you can leave them empty.

Field Data type Description
user_id STRING User ID. The value of the field must be the same as in Google Analytics in order to link online visitors with offline transactions.
client_id STRING Online visitor ID. The value of the field must be the same as in Google Analytics in order to link online visitors with offline transactions.
user_phone STRING User phone number
user_email STRING User email address
transaction_id STRING Transaction ID
transaction_status STRING

Transaction status: completed, refunded, cancelled, pending, in_process, awaiting_fulfillment, awaiting_shipment or awaiting_pickup

*Only the transactions with the completed status will count.


transaction_responsible STRING ID of a manager or operator who processes the transaction
transaction_coupon  STRING Promo code
transaction_discount FLOAT Discount in currency 
transaction_revenue FLOAT Total transaction revenue including product price and additional services, excluding discounts
transaction_currency STRING Currency
transaction_payment_type STRING Payment method
transaction_delivery_type STRING Delivery method
transaction_delivery_service STRING Delivery service used
transaction_delivery_time  INTEGER Delivery time in days
transaction_touchpoint STRING Transaction source. For example, a website, a call center, or an offline store
transaction_store_id STRING Store ID
transaction_city STRING City
transaction_region STRING Region
transaction_country STRING Country
transaction_created TIMESTAMP The time when the transaction was created
transaction_changed TIMESTAMP The time of last transaction update
product_id STRING Product ID
product_name STRING Product name
product_category STRING Product category
product_groupcategory1 STRING Level 1 category
product_groupcategory2 STRING Level 2 category
product_groupcategory3 STRING Level 3 category
product_brand STRING Product brand
product_cogs FLOAT Product COGS in the % of its price. For example, 0.4 or 0.15
product_quantity INTEGER The quantity of products in the transaction
product_price FLOAT Product price
promo_name STRING Promotion name
promo_start TIMESTAMP Promotion start time
promo_end  TIMESTAMP Promotion end time

How to load transactions data into Google BigQuery?

Was this article helpful?
4 out of 4 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.