Data needed to work with OWOX BI Attribution

User behavior data

As a source for your Funnel Based attribution model, you can use Google Analytics data stored in Google BigQuery in the format of OWOX BI Pipeline or Google BigQuery Export for Google Analytics.

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

Please note, that not all the data listed below is available in Google Analytics by default. For example, to collect session, client and user IDs, you need to set up custom dimensions. And e-commerce actions data is available only if Google Analytics Enhanced Ecommerce module is implemented.

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 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
Pagepath hits.page.pagePath hits.page.pagePath
Event Category hits.eventInfo.eventCategory hits.eventInfo.eventCategory
Event Action hits.eventInfo.eventAction hits.eventInfo.eventAction
Event Label hits.eventInfo.eventLabel hits.eventInfo.eventLabel
Hostname hits.page.hostname hits.page.hostname
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 transactions revenue data is required to calculate ROI and ROAS after you get 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 AdWords 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 OWOX BI 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 the transactions 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
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?
1 out of 1 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.