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

By default, Google Analytics data stored in Google BigQuery table is the source of both transaction and user behavior data.

If you want to attribute the value of products that were bought not online (e.g. brick-and-mortar, call center, etc.), add a transaction data source on your model's page. This may be data exported from your CRM system.

How to upload transaction data to Google BigQuery

How to add CRM transactions data

1. On the model page, click Add sourceAdd_events_2.png

2. Click CRM transactionsAdd_events_data_type.png

3. Select a Google BigQuery view or table containing data on the transactions you need: Add_events_custom_events_table.png

Note:You can connect only those data tables that are located in the same multi-region location as the rest of the source data for this attribution model. For example, if the data already connected to the attribution model are located in the EU, you won't be able to connect data located in the US, and the other Way around. You also can transfer the BigQuery data between the data set in different locations as discribed in this article.

4. Click Save and calculate your model.

Table data schema

Data schema in a Google BigQuery table or view must exactly match the schema shown below. Fields marked with * are required to be filled in.. All other fields must be present in the table, but you can leave them empty.

Sample table in BigQuery

Sample view in BigQuery

Field Data type Description
user_id* STRING User ID
client_id STRING Online visitor ID
user_phone STRING User phone number
user_email STRING User email address
transaction_id* STRING Transaction ID
transaction_status* STRING Transaction status: completed, refunded, canceled, 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 Transaction revenue for an individual product, excluding discounts.
Calculated by the formula product_price*product_quantity
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 number 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

During the model calculation, data from the source you add replaces data from Google Analytics in the transaction_id, user_id and product_id fields.

* Only transactions with the completed status will count.

Important: It's critical to have the consistent transaction, user, and product data in both sources.

How does the User ID data correlate between the Google Analytics and CRM sources

Note that if User ID information is not available, you can fill in Client IDs into user_id field to match data by Client IDs.

Google Analytics CRM transactions How the data correlate
userId 1 transaction 1 userId 1 transaction 1 Both user and transaction data match. Actions before the purchase will be assigned to this user.
userId 2 transaction 2 userId 3 transaction 2 The transactions match, the users don't. Actions before the purchase will be assigned to the user that was recorded first.
userId 4 transaction 3 null transaction 3 The transactions match, one source has no user data. Actions before the purchase will be assigned to the user that was recorded first. If this will be the 'null' user, only the purchase step will appear in the model's funnel.
null transaction 4 userId 4 transaction 4

The data is taken from the earlier transaction record among the two sources, GA and CRM.

These correlations work only if the conversion step is a CRM transaction. If the conversion step is a custom event, the conversion and event data will be taken from the custom events table.

How does the transactions data correlate between the Google Analytics and CRM sources

  1. There is a transaction in Google Analytics, but the CRM transactions data source doesn't contain one
    Value of such transactions isn't attributed.
  2. There is no transaction in Google Analytics, but the CRM transactions data source contains one
    - For such transactions, purchase sessions are generated. The sessions have the medium set to 'offline'.
    - If a user had online interactions prior to offline purchase, those online interactions get value.
    - Value for all funnel interactions that didn't happen online is attributed to the offline purchase session.
    - If there were no online interactions prior to offline purchase, all value of an offline transaction is attributed to its session.
  3. There is a transaction both in Google Analytics and the CRM transaction data source
    All information about such transactions and sessions that led to them matches Google Analytics data.
Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.