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

  1. Create a Google Cloud Platform account.

  2. Select a project → APIs & ServicesCredentials.

Credentials
  1. Click Create credentialsService account.
Service account
  1. Enter a service account name and click the Create and continue button.
Create and continue
  1. Select BigQuery Admin role, click ContinueDone.
BigQuery Admin
  1. Click the link Manage service accounts on the main page of the Credentials section.
Manage service accounts
  1. Click on the three dots opposite created account and select the option Manage keys.
Manage keys
  1. Click on the Add key button and select Create new key option in Keys section.
Create new key
  1. Select JSON key type and click Create.
JSON
  1. The browser will automatically download the key to the download folder.

Setting up BigQuery Connector

  1. Go to your profile > SettingsConnectors → and select Connect BigQuerry.
Connect BigQuerry
  1. 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.
Create connector

You will see created connector in SettingsConnectors. Click the tab with its name to edit the connector’s parameters.

Settings → Connectors

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.
BigQuery Table

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

ParameterTypeDescription
createdDatetimestampDay and time of message creation (format: '2021-10-08 11:11:02')
groupIdintSegment IDs participating in the broadcast
idintBroadcast ID
mediaTypestringMedia type (SMS, Email, WebPush, Viber, MobilePush, AppInbox, Widget)
messageIdintMessage ID
namestringBroadcast name
startedDatetimestampDay and time of broadcast sending (format: '2021-10-08 11:11:02')
statusstringBroadcast 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.
updatedDatetimestampDay and time of broadcast updating (format: '2021-10-08 11:11:02')

ContactActivities

ParameterTypeDescription
activity (activityStatus) *stringActivity 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.
broadcastIdintBroadcast ID
campaignTypestringCampaign type:
• IM — triggered message,
• Group — bulk campaign.
clickEventLink stringContains the link clicked by a contact (when the status is CLICKED)
contactIdintContact ID in eSputnik (Internal)
errorCode (statusDescription) *stringDelivery error SMTP and description
eventKeystringEvent key
eventTypeKeystringEvent type key
eventIdstringID of an event that had started a workflow
externalCustomerIdstringContact ID in your system (External)
mediaTypestringMedia type (SMS, Email, WebPush, Viber, MobilePush, AppInbox, Widget, In-App, Telegram)
messageInstanceIdintService field
messageLanguageCodestringMessage language code
messageNamestringMessage name in eSputnik account
messageIdintMessage ID
messageTagsstringMessage tags
messageURLstringContains the link to the email web-version
senderNamestringViber sender name
started (activityDateTime) *timestampDay and time of message sending (format: '2021-10-08 11:11:02')
utmCampaign stringCampaign UTM
workflowIdstringWorkflow ID
workflowInstanceIdstringThe identifier of a particular workflow launch. Use it to group mailings within the launch of a workflow.
workflowBlockIdstringWorkflow block ID

*The field will be removed as overdated; use the field indicated in brackets

Contacts

ParameterTypeDescription
contactIdintContact ID in eSputnik (Internal)
contactSource stringstringContact 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).
createdDatetimestampContact creation date and time (format: '2021-10-08 11:11:02')
email stringContact email
emailDomainstringEmail domain
emailStatusstringEmail status
externalCustomerId stringContact ID in your system (External)
firstName stringContact first name
languageCode stringContact language code
lastClickedDate timestampDay and time of the last click (format: '2021-10-08 11:11:02')
lastNamestringContact last name
lastReceivedDate timestampDay and time of the last delivery (format: '2021-10-08 11:11:02')
lastSentDate timestampDay and time of the last sending (format: '2021-10-08 11:11:02')
lastViewedDatetimestampDay and time of the last opening (format: '2021-10-08 11:11:02')
smsstringPhone number
totalClickedintTotal clicks amount
totalReceivedintTotal received messages amount
totalSentintTotal sent messages amount
totalViewedintTotal viewed messages amount

Devices

ParameterTypeDescription
appVersionstringApp version
applicationIdintApp ID
categorystringCategory
contactIdintContact ID
deviceIdstringDevice ID
deviceModelstringDevice model
externalCustomerIdstringExternal customer ID
languageCodestringLanguage code
osTypestringOS type
osVersionstringOS version
pushTokenstringPush token
timeZonestringTime zone

Events

ParameterTypeDescription
contactIdintContact ID
eventIdintEvent ID
eventTypeKeystringEvent type
keyValuestringEvent key
occuredtimestampDate and time of occurrence (format: '2021-10-08 11:11:02')
paramsJsonjson50 first event parameters and values ​​(name/value); up to 300 characters of values ​​are transferred.

Messages

ParameterTypeDescription
annoyanceLevelintMessage annoyance level
languagestringLanguage of the main version of the message
mediaTypestringMedia type (SMS, Email, WebPush, Viber, MobilePush, AppInbox, Widget, In-App)
messageIdintMessage ID
namestringMessage name
replyTostringReply-to address
senderstringSender
subjectstringSubject
tagsstringTags
translationsstringLanguage versions of the message
updateDatetimestampDay and time of message updating (format: '2021-10-08 11:11:02')

OrderItems

ParameterTypeDescription
cost floatfloatProduct price
descriptionstringProduct description
externalProductIdstringExternal product ID
imageUrlstringProduct image link
namestringProduct name
orderDatetimestampOrder creation date (format: '2021-10-08 11:11:02')
orderIdintOrder ID in eSputnik (Internal)
quantityintNumber of products
urlstringProduct URL

Orders

ParameterTypeDescription
contactIdintContact ID in eSputnik (Internal)
deliveryAddress stringDelivery address
deliveryMethodstringDelivery method
discountfloatDiscount
emailstringEmail
externalOrderIdstringExternal order ID
firstName stringContact first name
lastNamestringContact last name
orderCreatedDatetimestampDate of order creation in eSputnik (format: '2021-10-08 11:11:02')
orderDate timestampOrder creation date (format: '2021-10-08 11:11:02')
orderId intOrder ID in eSputnik (Internal)
paymentMethodstringPayment method
PhonestringPhone number
StatusstringOrder status:
• INITIALIZED,
• IN PROGRESS,
• DELIVERED,
• CANCELED.
totalCostfloatTotal order cost

Revenue

ParameterTypeDescription
activity (activityStatus)*stringActivity 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 stringCampaign type:
• IM — triggered message,
• Group — bulk campaign.
clickEventLink stringContains the link clicked by a contact (when the status is CLICKED)
contactIdintContact ID in eSputnik (Internal)
currency stringCurrency
externalCustomerId stringContact ID in your system (External)
externalOrderId stringExternal order ID
mediaType stringMedia type (SMS, Email, WebPush, Viber, MobilePush, Widget)
messageInstanceIdintService field
messageName stringMessage name in eSputnik account
messageTags stringMessage tags
messageUrl stringContains the link to the email web-version
orderDate timestampOrder creation date (format: '2021-10-08 11:11:02')
senderName stringViber sender name
started (activityDateTime)*timestampDay and time of message sending (format: '2021-10-08 11:11:02')
totalCostfloatTotal order cost
utmCampaign stringCampaign UTM
eventKeystringEvent key
eventTypeKeystringEvent type key
messageLanguageCodestringMessage language code
orderIdintOrder ID in eSputnik (Internal)
workflowIdintWorkflow ID
broadcastIdintBroadcast 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

  1. Go to Settings → _Connectors and click _Add data source.
Add data source
  1. Upload the key file and check the boxes for the data sets you want to upload. Click Save.
1521

The connection will be displayed in ConnectorsExport to BigQuery, here you can edit the settings.

Export to BigQuery

Configuring a Data Source

  1. Go to your profile → SettingsData sources, click New data source and select External data source.
Data sources
  1. Select the created connector.
Where is your data?
  1. Select dataset and table and enter a source name. Click Save.
  1. 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.