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:
- Connect your Google Analytics with Google Ads to import costs for non-Google campaigns.
- Set up automatic cost data import from Facebook, Bing and other sources.
- 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 *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 |
|
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 |
0 Comments