Integrating with Google BigQuery
Step-by-step guide how to export data from eSputnik, your CRM, different databases to BigQuery. It allows you to manage and store all data, create multiple additional fields and custom reports, etc. in one place.
Important
Please note that this integration is paid. Contact our sales team to activate the functionality
eSputnik provides two options for BigQuery integration:
- Exporting data from eSputnik to BigQuery
- Using data from BigQuery for segmentation and substitution of external data in messages.
To configure the BigQuery integration:
- Register at Google Cloud Platform
- Get a project key
- Create tables in Google BigQuery
- Configure two-sided BigQuery - eSputnik data transfer
If you already have a project key and have configured the BigQuery tables, you can go directly to the connector settings.
Creating a Project Key
-
Create a Google Cloud Platform account.
-
Select a project → APIs & Services → Credentials.
- Click Create credentials → Service account.
- Enter a service account name and click the Create and continue button.
- Select BigQuery Admin role, click Continue → Done.
- Click the link Manage service accounts on the main page of the Credentials section.
- Click on the three dots opposite created account and select the option Manage keys.
- Click on the Add key button and select Create new key option in Keys section.
- Select JSON key type and click Create.
- The browser will automatically download the key to the download folder.
Setting up BigQuery Connector
- Go to your profile > Settings → Connectors → and select Connect BigQuerry.
- Create connector:
A. Fill in the Name field.
B. Upload the key file.
C. Click Validate connection to test whether the authorization data is correct. You will see the notification if the connection is successful or not. If the connection fails, check the entered data and contact your system administrator if needed.
D. Set unique contact field: select a table column that contains a unique contact key and eSputnik's unique contact key. They should match.
E. Click Save.
You will see created connector in Settings → Connectors. Click the tab with its name to edit the connector’s parameters.
Exporting Data to the BigQuery Table
To regularly update information about the audience, contacts' activity in widgets, orders and campaign results in BigQuery, set up data export to tables. For example, you can export responses to an NPS survey, purchase history, date of the last click in a message, etc.
Available datasets for export:
- broadcasts;
- contactActivities;
- contacts;
- devices;
- events;
- messages;
- orderItems;
- orders;
- revenue.
This information will be transferred to BigQuery and will update the data in the tables once a day after setting up.
Important
You don’t need to pre-create BigQuery tables. They will be generated automatically at the first export, and since that all the incoming data will be updated. All the table names will correspond to the data sets (contact activities, contacts, order items, orders)
List of Data Export Parameters
Broadcasts
Parameter | Type | Description |
---|---|---|
createdDate | timestamp | Day and time of message creation (format: '2021-10-08 11:11:02') |
groupId | int | Segment IDs participating in the broadcast |
id | int | Broadcast ID |
mediaType | string | Media type (SMS, Email, WebPush, Viber, MobilePush, AppInbox, Widget) |
messageId | int | Message ID |
name | string | Broadcast name |
startedDate | timestamp | Day and time of broadcast sending (format: '2021-10-08 11:11:02') |
status | string | Broadcast statuses: • IDLE - completed; • RUNNING - started; • PAUSED - paused (if the mailing was not stopped manually by you, contact support for more details); • SCHEDULED - planned; • UNCONFIRMED - in queue for moderation; • CONSIDERATION - under moderation; • BLOCKED - blocked by moderator. |
updatedDate | timestamp | Day and time of broadcast updating (format: '2021-10-08 11:11:02') |
ContactActivities
Parameter | Type | Description |
---|---|---|
activity (activityStatus) * | string | Activity status: • DELIVERED – the message was delivered. • UNDELIVERED – the message was undelivered (statusDescription contains the reason). • RECEIVED – the message was opened. • UNSUBSCRIBED – a contact unsubscribed from the broadcasting list. • CLICKED – a contact clicked links in the message. • SPAM – a contact reported spam. • SUBSCRIPTION_CHANGED – a contact changed the subscription category. • PUSH_SUBSCRIBED — a contact subscribed to push notifications. |
broadcastId | int | Broadcast ID |
campaignType | string | Campaign type: • IM — triggered message, • Group — bulk campaign. |
clickEventLink | string | Contains the link clicked by a contact (when the status is CLICKED) |
contactId | int | Contact ID in eSputnik (Internal) |
errorCode (statusDescription) * | string | Delivery error SMTP and description |
eventKey | string | Event key |
eventTypeKey | string | Event type key |
eventId | string | ID of an event that had started a workflow |
externalCustomerId | string | Contact ID in your system (External) |
mediaType | string | Media type (SMS, Email, WebPush, Viber, MobilePush, AppInbox, Widget, In-App, Telegram) |
messageInstanceId | int | Service field |
messageLanguageCode | string | Message language code |
messageName | string | Message name in eSputnik account |
messageId | int | Message ID |
messageTags | string | Message tags |
messageURL | string | Contains the link to the email web-version |
senderName | string | Viber sender name |
started (activityDateTime) * | timestamp | Day and time of message sending (format: '2021-10-08 11:11:02') |
utmCampaign | string | Campaign UTM |
workflowId | string | Workflow ID |
workflowInstanceId | string | The identifier of a particular workflow launch. Use it to group mailings within the launch of a workflow. |
workflowBlockId | string | Workflow block ID |
*The field will be removed as overdated; use the field indicated in brackets
Contacts
Parameter | Type | Description |
---|---|---|
contactId | int | Contact ID in eSputnik (Internal) |
contactSource string | string | Contact source: • SITE_AUTOMATED - binding an email to a push subscriber (webPush collection script), • I_MESSAGE - sending a single message, • CAMPAIGN - workflow (block Create contact or Add to segment), • IMPORT - file import or Add contacts method, • MANUAL - manually created, • SUBSCRIPTION - subscription form (Subscribe a contact API method), • Add contact API method, • ORDER - order on the site (Add orders API method). |
createdDate | timestamp | Contact creation date and time (format: '2021-10-08 11:11:02') |
string | Contact email | |
emailDomain | string | Email domain |
emailStatus | string | Email status |
externalCustomerId | string | Contact ID in your system (External) |
firstName | string | Contact first name |
languageCode | string | Contact language code |
lastClickedDate | timestamp | Day and time of the last click (format: '2021-10-08 11:11:02') |
lastName | string | Contact last name |
lastReceivedDate | timestamp | Day and time of the last delivery (format: '2021-10-08 11:11:02') |
lastSentDate | timestamp | Day and time of the last sending (format: '2021-10-08 11:11:02') |
lastViewedDate | timestamp | Day and time of the last opening (format: '2021-10-08 11:11:02') |
sms | string | Phone number |
totalClicked | int | Total clicks amount |
totalReceived | int | Total received messages amount |
totalSent | int | Total sent messages amount |
totalViewed | int | Total viewed messages amount |
Devices
Parameter | Type | Description |
---|---|---|
appVersion | string | App version |
applicationId | int | App ID |
category | string | Category |
contactId | int | Contact ID |
deviceId | string | Device ID |
deviceModel | string | Device model |
externalCustomerId | string | External customer ID |
languageCode | string | Language code |
osType | string | OS type |
osVersion | string | OS version |
pushToken | string | Push token |
timeZone | string | Time zone |
Events
Parameter | Type | Description |
---|---|---|
contactId | int | Contact ID |
eventId | int | Event ID |
eventTypeKey | string | Event type |
keyValue | string | Event key |
occured | timestamp | Date and time of occurrence (format: '2021-10-08 11:11:02') |
paramsJson | json | 50 first event parameters and values (name/value); up to 300 characters of values are transferred. |
Messages
Parameter | Type | Description |
---|---|---|
annoyanceLevel | int | Message annoyance level |
language | string | Language of the main version of the message |
mediaType | string | Media type (SMS, Email, WebPush, Viber, MobilePush, AppInbox, Widget, In-App) |
messageId | int | Message ID |
name | string | Message name |
replyTo | string | Reply-to address |
sender | string | Sender |
subject | string | Subject |
tags | string | Tags |
translations | string | Language versions of the message |
updateDate | timestamp | Day and time of message updating (format: '2021-10-08 11:11:02') |
OrderItems
Parameter | Type | Description |
---|---|---|
cost float | float | Product price |
description | string | Product description |
externalProductId | string | External product ID |
imageUrl | string | Product image link |
name | string | Product name |
orderDate | timestamp | Order creation date (format: '2021-10-08 11:11:02') |
orderId | int | Order ID in eSputnik (Internal) |
quantity | int | Number of products |
url | string | Product URL |
Orders
Parameter | Type | Description |
---|---|---|
contactId | int | Contact ID in eSputnik (Internal) |
deliveryAddress | string | Delivery address |
deliveryMethod | string | Delivery method |
discount | float | Discount |
string | ||
externalOrderId | string | External order ID |
firstName | string | Contact first name |
lastName | string | Contact last name |
orderCreatedDate | timestamp | Date of order creation in eSputnik (format: '2021-10-08 11:11:02') |
orderDate | timestamp | Order creation date (format: '2021-10-08 11:11:02') |
orderId | int | Order ID in eSputnik (Internal) |
paymentMethod | string | Payment method |
Phone | string | Phone number |
Status | string | Order status: • INITIALIZED, • IN PROGRESS, • DELIVERED, • CANCELED. |
totalCost | float | Total order cost |
Revenue
Parameter | Type | Description |
---|---|---|
activity (activityStatus)* | string | Activity status: • DELIVERED – the message was delivered. • UNDELIVERED – the message was undelivered (statusDescription contains the reason). • RECEIVED – the message was opened. • UNSUBSCRIBED – a contact unsubscribed from the broadcasting list. • CLICKED – a contact clicked links in the message. • SPAM – a contact reported spam. • SUBSCRIPTION_CHANGED – a contact changed the subscription category. • PUSH_SUBSCRIBED — a contact subscribed to push notifications. |
campaignType | string | Campaign type: • IM — triggered message, • Group — bulk campaign. |
clickEventLink | string | Contains the link clicked by a contact (when the status is CLICKED) |
contactId | int | Contact ID in eSputnik (Internal) |
currency | string | Currency |
externalCustomerId | string | Contact ID in your system (External) |
externalOrderId | string | External order ID |
mediaType | string | Media type (SMS, Email, WebPush, Viber, MobilePush, Widget) |
messageInstanceId | int | Service field |
messageName | string | Message name in eSputnik account |
messageTags | string | Message tags |
messageUrl | string | Contains the link to the email web-version |
orderDate | timestamp | Order creation date (format: '2021-10-08 11:11:02') |
senderName | string | Viber sender name |
started (activityDateTime)* | timestamp | Day and time of message sending (format: '2021-10-08 11:11:02') |
totalCost | float | Total order cost |
utmCampaign | string | Campaign UTM |
eventKey | string | Event key |
eventTypeKey | string | Event type key |
messageLanguageCode | string | Message language code |
orderId | int | Order ID in eSputnik (Internal) |
workflowId | int | Workflow ID |
broadcastId | int | Broadcast ID |
*The field will be removed as overdated; use the field indicated in brackets
Note
Existing entries in the Revenue table (for orders placed after 03.08.2024) can be updated along with the upload of new orders. Updating may be necessary, for example, if the total cost of the order has changed.
Setting up BigQuery Connector for Data Export
- Go to Settings → _Connectors and click _Add data source.
- Upload the key file and check the boxes for the data sets you want to upload. Click Save.
The connection will be displayed in Connectors → Export to BigQuery, here you can edit the settings.
Configuring a Data Source
- Go to your profile → Settings → Data sources, click New data source and select External data source.
- Select the created connector.
- Select dataset and table and enter a source name. Click Save.
- Import contacts to eSputnik and fill the external source with data. Now you can build segments based on the imported contact fields.
Important
Connection to the external database doesn’t presuppose contact import. Segmentation is only available for contacts that exist both in your eSputnik account and in the external database. Synchronize and update contacts before creating campaigns.
Adding data to a message is similar to adding data from PostgreSQL.
Updated 9 days ago