Login

First steps

Onboarding With Yespo
Account Management
Courses

User Data

Importing Historical Data
User Profile
Customer Identifiers and Matching
Subscriber Data Collection
Uploading File with User Profile Data

Email

Email Setting Up
Launching an Email Campaign
Email Editor
AMP
Product Blocks

Omnichannel

Mobile Push
App Inbox
In-App
Web Push
Viber
SMS
Widgets
Telegram

Automation

Segmentation
Introduction to Workflows
Workflow Examples
Orders
Zapier

Personalization

Personalization and Dynamic Variables
Introduction to Velocity
Promocodes

Analytics

Campaign Reports
Contact Reports

Multilanguage Campaigns

Multilanguage Overview

Events and Behaviour Tracking

External Data Sources
Events and Behaviour Tracking
Web Tracking Overview

Recommendations

Setting Up Product Recommendations
Recommendations for Website
Recommendations in Mobile App
Recommendations in Media Channels

API

API Methods

Move In

Migration

Security and Compliance

GDPR Compliance

Integration

Website Integration

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;
  • events;
  • 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
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 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                                                       

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

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

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

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.
Yespo / Support / Events and Behaviour Tracking / External Data Sources / Integration with Google BigQuery
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