CRM data

Before importing CRM data to Google BigQuery, you need to create an empty table with a schema definition. More details on creating a table for CRM data storing can be found here.

From this article, you’ll learn about a schema definition, including the required fields, their types, and descriptions. While working with the CRM data schema below, please consider the following:

  • Fields marked with an asterisk mark (*) are required to be filled in.
  • If you have no data for some schema fields, add them to the table with empty values.
  • Custom parameters of User or Transaction data can be added to the table via the additional fields. OWOX BI doesn’t process custom parameters when calculating an attribution model.
  • CRM data structure can be defined via notional nested levels. According to them, the same transaction_id can be used for several product_ids, while transaction_revenue is always unique per product_id.
  • Providing a timezone is required for the TIMESTAMP-type fields (i.e. transaction_created, transaction_changed, promo_start, promo_end fields).

Follow the references to see the examples of a table and view in Google BigQuery.

NoteIf at least one schema field is missing or has a type different from the required one, the calculation of an attribution model might return an error.

To use CRM data in a structure that differs from the standard one, use the custom events mechanism. Read more

Field Data type Description
user_id* STRING

User ID

Note: By default, OWOX BI uses the user_id field to merge transaction data from CRM with data from other sources.

If the User ID value is unknown for a certain transaction, leave NULL in the user_id field for this transaction. In this case, OWOX BI will merge data using the client_id field.

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.
*In an attribution model, only the transactions with the completed status are taken into account.
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

The total revenue from all the individual products purchased in this transaction, excluding discounts.

Note: If your data is detailed to the "product" level, then transaction_revenue must be unique per product_id. To obtain the revenue for an individual product, use 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

Providing a timezone is required for this field

transaction_changed TIMESTAMP

The time of the last transaction update

Providing a timezone is required for this field

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

Providing a timezone is required for this field

promo_end TIMESTAMP

Promotion end time

Providing a timezone is required for this field


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


Please sign in to leave a comment.