Purpose of the BQ Lookup
For each definition type, it is possible to configure a "BigQuery Lookup" attribute. The purpose of this attribute is to reference data from a BigQuery table under a new attribute. This way you can import already existing or calculated data from BQ that is not integrated to adtribute otherwise.
There is a large number of possibilities to use this for, here is just a few examples:
already calculated metrics for specific orders
specific order information like PPS answers
How to create the BQ Lookup in Configuration Type
Click the Create attribute button.
Select the relevant aggregation type.
Select the relevant data type.
Choose "BigQuery Lookup" as the configuration type.
Grant View Access to BQ Project
Before entering your project, dataset, and table details, grant adtribute access to your BigQuery project.
Please make sure to use a resource located in a "multi-region EU" cluster.
Login to your GCP (Google Cloud Plattform) Account
Go to your according GCP Project
Navigate to the "IAM" section
Click "Grant access"
Grant our email access: [email protected]
Provide it with the Role "BigQuery Data Viewer" and save ("Data Editor" access is not necessary in this case):
Important: If your table has dependencies on another BigQuery project (such as nested table references), you will need to grant view access to that project as well.
Enter BQ IDs
Now you can input the project ID, dataset ID, and table ID of the BigQuery table you want to reference the attribute from.
Once you have entered your table details, click the Fetch columns button to load the available columns from your table. During this step adtribute checks if it can access the table. If this does not work, please double check the above mentioned requirements and if there are any typos in your table details.
Configure the BQ Lookup after connecting the table
Choose how adtribute should join the data provided in your table. You can choose multiple join conditions each selecting a BigQuery column, e.g. orderId and an already existing attribute e.g. shopifyOrderId
Select the column which contains the data that you want to reference e.g. PPS answers
Select an attribute or a constant value as a default as a fallback mechanism for missing data
Add a name
Add a description
Click the Save button
When everything is fetched and correctly configuted the lookup attribute will look similar to this:
