Login

Omnichannel

Security and Compliance

Exporting Data to the BigQuery Tables

Set up data export to tables to regularly update information about the audience, contacts' activity, and campaign results in BigQuery. Available datasets for export:

  • broadcasts
  • contactActivities
  • contacts
  • devices
  • events
  • messages
  • orderItems
  • orders
  • revenue

This information will update the data in the BigQuery tables once a day after setting up.

Note

You don’t need to pre-create BigQuery tables. They will be generated automatically at the first export, and since then all the incoming data will be updated. All the table names will correspond to the data sets.

Creating a Data Source 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.

Save

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

Export to BigQuery

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.

Use Cases

Here are some key use cases for exporting data from Yespo to BigQuery:

1. Advanced Campaign Performance Analysis

  • Dataset Used: broadcasts, contactActivities, messages
  • Description: BigQuery enables e-commerce teams to analyze campaign performance in-depth, tracking key metrics such as open and click-through rates. Exporting data on broadcasts, including segmentation criteria, delivery status, and message type, allows marketers to assess which campaigns achieve the best engagement.
  • Use Case Benefit: By identifying the most effective campaigns, marketers can optimize message content, timing, and audience targeting, enhancing the impact and ROI of future campaigns.

2. Customer Segmentation and Behavioral Analytics

  • Dataset Used: contacts, contactActivities, events
  • Description: BigQuery can process large volumes of customer activity data, enabling e-commerce brands to spot behavioral patterns such as purchase frequency or preferred interaction channels. This insight allows for deeper segmentation, making it possible to target highly engaged customers more accurately.
  • Use Case Benefit: Tailored, behavior-driven campaigns lead to increased customer retention and higher lifetime value (LTV) by delivering experiences that resonate with each audience segment.

3. Personalization and Retargeting

  • Dataset Used: devices, contacts, contactActivities, events
  • Description: By exporting data on user devices, engagement history, and past interactions, e-commerce teams can implement personalized retargeting. For instance, messaging can be tailored to recent activity or device type to enhance relevance and engagement.
  • Use Case Benefit: Personalizing messages based on user actions and preferences improves user experience, boosts conversions, and builds a more dynamic engagement strategy.

4. Predictive Modeling

  • Dataset Used: contacts, orders, revenue, contactActivities
  • Description: Historical data on revenue, purchases, and engagement in BigQuery allows for the development of predictive models to forecast customer churn. This enables brands to identify at-risk customers and target them with retention strategies.
  • Use Case Benefit: Targeted retention campaigns reduce churn and increase LTV, helping brands retain valuable customers and improve long-term revenue.

5. Order and Revenue Analysis for Strategic Insights

  • Dataset Used: orders, orderItems, revenue
  • Description: By analyzing order and revenue data in BigQuery, e-commerce businesses can track metrics such as average order value and revenue trends. This analysis helps identify upselling and bundling opportunities.
  • Use Case Benefit: Insights into revenue patterns allow for data-driven decisions on pricing, promotions, and product offerings, maximizing revenue potential.

6. Monitoring and Alerting on Key Metrics

  • Dataset Used: broadcasts, contactActivities, revenue
  • Description: BigQuery dashboards can monitor key performance indicators (KPIs) such as engagement levels and revenue trends, with the ability to set up alerts for significant changes (e.g., a sudden drop in delivery rates).
  • Use Case Benefit: Monitoring helps maintain campaign effectiveness by allowing teams to quickly address performance issues, ensuring consistent engagement.

7. Campaign ROI and Budget Allocation Analysis

  • Dataset Used: broadcasts, orders, revenue, contactActivities
  • Description: Exporting campaign and revenue data into BigQuery allows e-commerce marketers to calculate ROI and assess campaign cost-effectiveness, providing guidance on optimal budget allocation.
  • Use Case Benefit: By focusing on high-performing campaigns, marketers can optimize spending to maximize marketing effectiveness and returns.

8. Customer Journey Mapping Across Channels

  • Dataset Used: contacts, contactActivities, events, orders
  • Description: BigQuery’s data processing capabilities enable brands to consolidate user journeys across channels, providing insights into the customer lifecycle from discovery to conversion.
  • Use Case Benefit: Understanding the customer journey reveals drop-off points and highlights opportunities for improvement, supporting a smoother experience and higher conversion rates.
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