CRM data – OWOX Help Center

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_priceproduct_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

0 Comments

Related articles