This feature is available only in Business and Enterprise plans.
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 transactions 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 source: 
2. Click CRM transactions: 
3. Select a Google BigQuery view or table containing data on the transactions you need: 
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.
| 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 |
|
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
- There is a transaction in Google Analytics, but the CRM transactions data source doesn't contain one
Value of such transactions isn't attributed. - 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. - 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.
0 Comments