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.
Note, if at least one schema field is missing or has a type different from the required one, calculation of attribution model might return an error.
| 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. *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. To obtain the revenue for an individual product, use the formula: |
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