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. Yet, you can use this data to create custom reports in Smart Data.
  • 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.