BigQuery connection

Data that Boldem collects and processes can be exported to the external service Google BigQuery.

The following data can be transferred:

• custom events
• orders
• automation data

How do I connect to Google BigQuery?

1. Log in to your account in the Boldem app.
2. In the left menu, select Settings and click on Integration.

3. Click the + New integration button.
4. From the integration type dropdown, select BigQuery.

5. You will then be prompted to enter the Project ID, dataset ID and login credentials in JSON format to connect to your Google BigQuery account. You can obtain these credentials after login to the Google Cloud Console. In the left menu, select BigQuery.

6. Select or create a new project.

If you don’t have a Project ID yet, click Enable and your first BigQuery project will be created automatically:

7. In your project, click the three dots next to the Project ID and create a new dataset using the Create dataset button. Name it, select the region where the data will be stored, and confirm by clicking the Create dataset button in the bottom right.

8. Enter the Project ID and Dataset ID in the form in Boldem:

9. Then, in the Google Cloud Console, go to the IAM & Admin section and select the Service Accounts tab, where you can obtain the credentials in JSON format.

10. If you don’t have an account yet, you need to create one. Click Create service account:

11. Enter a name and description. Click Create and continue. Choose an appropriate role (e.g., Editor, Storage Admin, BigQuery Admin, or a more specific role). You can also skip this step if you want to assign roles later. Click Continue, then Done.

12. In the list of service accounts, find the one you just created. Click the three dots on the right in the Actions custom field and go to Manage Keys.

13. Click the Add Key button and then Create new key, select Key type JSON and click Create. The key will be downloaded automatically by your browser.

14. Open the file and paste its contents into Boldem’s JSON Credentials field.
15. Test the integration using the Test integration button.

16. Enter the name or ID of the specific table, depending on which data you want to transfer.
17. If you want to transfer automation data, fill in the Table ID for the recipient in the automation and Table ID for the recipient log in the automation.
18. Along with recipient data, you can also transfer automation definitions and automation blocks
    – Automation table ID and Automation blocks table ID
19. Table names must not contain diacritics, special characters, spaces, or start with a number.
20. Use e.g. boldem_automations and boldem_automations_logs for recipient logs and boldem_automations_def and boldem_automations_blocks for automation definitions.
21. After clicking the Register integration button, tables with the specified names and required data structure are automatically created in Google BigQuery. If tables already exist in the dataset, this step will be skipped.
22. Enable the integration using the toggle switch Enable integration and save it.

23. Data transfer to Google BigQuery for the past 180 days will begin within 15 minutes. This may take some time depending on the size of your data.

Automation data

After creating the integration, two tables are created in the dataset with the configured names. One contains basic information about recipients who went through the automation, and the other stores a log of their progress and activity in emails, SMS, notifications, etc.

All times are in UTC.

Custom fields for table boldem_automations

• automationStateId – Automation run ID
• automationId – automation ID
• contactId – contact ID
• guid – GUID, unique identifier of an automation run.
• blockId – ID of the automation block the recipient passes through
• nextBlockId – the ID of the next automation block the recipient will enter
• Email – recipient email
• created – timestamp when the recipient entered the automation.
• updated – timestamp, last update
• finished – timestamp if the automation run is completed.
• unsubscribed – timestamp, if the user is unsubscribed, they are prevented from continuing in the next pass
• nextStart – timestamp when the next start is scheduled, e.g. after the Wait block.

Custom fields for the boldem_automations_logs table

• automationStateLogId – ID of the created log
• automationStateId – Automation run ID
• automationId – automation ID
• parentAutomationStateId – parent
• eventType – event type, data type EventType
• blockId – ID of the block where the event occurred
• entityId – ID of a transactional email, SMS, or Push/In-app message
• created – timestamp when the event was created.
• variant – information about the variant if the contact passed through an A/B split test block

Custom fields of the boldem_automations table.

• automationId – automation ID
• name – name of the automation
• description – automation description
• isActive – information about whether the given automation is started
• created – timestamp when the automation was created.
• updated – timestamp when the automation was modified.
• updated – timestamp when the automation was deleted.

Custom fields for the boldem_automation_blocks table

• automationId – automation ID
• automationBlockId – automation block ID.
• description – automation description
• blockType – automation block type
• blockTypeDesc – text description of the block type
• entityType – the type of entity that the block generates.
• entityTypeDesc – textual description of the block’s entity type
• updated – timestamp when the automation was modified.
• updated – timestamp when the automation was deleted.

Enumeration of the data type EventType.

1 = Start of automation
2 = End of automation
3 = Status change
4 = Email sent
5 = Email opened
6 = Email clicked
7 = Unsubscribed from emails
8 = Undelivered (bounced) email
9 = SMS sent
10 = Counter
11 = Delete recipient status
12 = Push/In-app message opened
13 = Push/In-app message clicked
14 = Push/In-app message sent

Data type enumeration EntityType .

1 = Email
2 = SMS
3 = Push notification
4 = In-app message
5 = Webhook

Mailing list data

After creating the integration, a table named accordingly will be added to the dataset, containing basic information about the recipients from the selected mailing list.

All times are in UTC.

Custom table fields for the selected mailing list

• contactId – recipient ID
• email – recipient email
• mobilePhone – recipient’s mobile phone
• name – recipient’s name
• surname – recipient’s surname
• title – recipient’s title
• street – recipient’s street
• city – recipient’s city
• Recipient ZIP Code
• nameday – recipient’s name day
• birthday – recipient’s birthday
• Sex – recipient’s gender
• company – recipient’s company
• created – date and time of record creation
• updated – date and time of the record’s last update

The table may also contain columns that correspond to configured custom fields. In this case, the column name will match the custom field’s name.