Login

Security and Compliance

Integration with Google BigQuery

Step-by-step guide how to export data from Yespo, 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

Yespo provides two options for BigQuery integration:

  • Exporting data from Yespo 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 - Yespo 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 & Services → Credentials.

Credentials

3. Click Create credentials → Service account.

Service account

4. Enter a service account name and click the Create and continue button.

Create and continue

5. Select BigQuery Admin role, click Continue Done.

BigQuery Admin

6. Click the link Manage service accounts on the main page of the Credentials section.

Manage service accounts

7. Click on the three dots opposite created account and select the option Manage keys.

Manage keys

8. Click on the Add key button and select Create new key option in Keys section.

Create key

9. Select JSON key type and click Create.

Select JSON key type

10. The browser will automatically download the key to the download folder.

Setting up BigQuery Connector

1. Go to your profile → Settings Connectors → and select Connect BigQuerry.

Connect BigQuerry

2. 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 Yespo's unique contact key. They should match.
E. Click Save.

Setting up BigQuery Connector

You will see created connector in Settings Connectors. 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;
  • messages;
  • orderItems;
  • orders
  • revenue.

Export tables

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 Yespo (Internal)

errorCode (statusDescription)* string

Delivery error SMTP and description

eventKey string

Event key

eventTypeKey string

Event type key

externalCustomerId string

Contact ID in your system (External)

mediaType string

Media type (Sms, Email, WebPush, Viber, MobilePush, AppInbox, Widget)

messageInstanceId int

Service field

messageLanguageCode string

Message language code

messageName string

Message name in Yespo 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 Yespo (Internal)

contactSource 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')

email 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                                                       

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)

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

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 Yespo (Internal)

quantity int

Number of products

url string

Product URL

Orders

Parameter Type

Description

contactId int

Contact ID in Yespo (Internal)

deliveryAddress string

Delivery address

deliveryMethod string

Delivery method

discount float

Discount

email string

Email

externalOrderId string

External order ID

firstName string

Contact first name

lastName string

Contact last name

orderCreatedDate timestamp

Date of order creation in Yespo (format: '2021-10-08 11:11:02')

orderDate

timestamp Order creation date (format: '2021-10-08 11:11:02')

orderId

int Order ID in Yespo (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 Yespo (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, AppInbox, Widget)

messageInstanceId

int Service field

messageName

string Message name in Yespo 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 Yespo (Internal)
workflowId int Workflow ID
broadcastId int Broadcast ID

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

Setting up BigQuery Connector for Data Export

1. Go to Settings → Connectors and click Add data source.

Add data source

2. Upload the key file and check the boxes for the data sets you want to upload. Click Save.

Export settings

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

Export to BigQuery

Configuring a Data Source

1. Go to your profile → Settings Data sources, click New data source and select External data source.

External data source

2. Select the created connector.

Select the created connector

3. Select dataset and table and enter a source name. Click Save.

Select dataset

4. Import contacts to Yespo and fill the external source with data. Now you can build segments based on the imported contact fields.

Build segments

Important

Connection to the external database doesn’t presuppose contact import. Segmentation is only available for contacts that exist both in your Yespo 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.

Any Questions?
We’re always happy to help!
Discount services
Schedule a Call
Fill in the form, and our specialists will call you back as soon as possible.
Discount services
Chat Support
We’re waiting for your questions!
Discount services
Email
Contact the support team