Login

Security and 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 available only in the Growth and Enterprise plans

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.

Add 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. Fill in the Name field and upload the key file. Set unique contact field: select a table column that contains a unique contact key and Yespo's unique contact key. They should match.

You may 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.

Setting up BigQuery Connector

3. Click Save.

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:

  • contactActivities;
  • contacts;
  • orderItems;
  • orders
  • revenue.

Available datasets

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

ContactActivities

Parameter Type Description
activity 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 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
messageTags string Message tags
messageURL string Contains the link to the email web-version
senderName string Viber sender name
started timestamp Day and time of message sending (format: '2021-10-08 11:11:02') 
utmCampaign string Campaign UTM 
workflowId int Workflow ID
workflowInstanceId int The identifier of a particular workflow launch. Use it to group mailings within the launch of a workflow.

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

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 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 float 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 timestamp Day and time of message sending (format: '2021-10-08 11:11:02') 
totalCost float Total order cost
utmCampaign string Campaign UTM

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