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 Data Export

Go to Settings → Data Export → BigQuery.

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

The connection will be displayed in the Data export section, here you can edit its settings.

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, 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 Reteno (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)
iid	string	Sender name for Email and SMS
mediaType	string	Media type (Sms, Email, WebPush, MobilePush, AppInbox, Widget, In-App)
messageInstanceId	int	Service field
messageLanguageCode	string	Message language code
messageName	string	Message name in Reteno account
messageId	int	Message ID
messageTags	string	Message tags
messageURL	string	Contains the link to the email web-version
osName	string	Device operating system
osType	string	Device type
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 Reteno (Internal)
contactSource string	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, 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	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 Reteno (Internal)
quantity	int	Number of products
url	string	Product URL

Orders

Parameter	Type	Description
contactId	int	Contact ID in Reteno (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 Reteno (format: '2021-10-08 11:11:02')
orderDate	timestamp	Order creation date (format: '2021-10-08 11:11:02')
orderId	int	Order ID in Reteno (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 Reteno (Internal)
currency	string	Currency
externalCustomerId	string	Contact ID in your system (External)
externalOrderId	string	External order ID
mediaType	string	Media type (Sms, Email, WebPush, MobilePush, AppInbox, Widget)
messageInstanceId	int	Service field
messageName	string	Message name in Reteno 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')
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 Reteno (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 Reteno to BigQuery:

1. Advanced Campaign Performance Analysis

Dataset Used: broadcasts, contactActivities, messages
Description: BigQuery allows teams to perform complex analyses on broadcast performance, message open rates, and click-through rates, helping refine campaign strategies. Exporting broadcasts data with metrics like media type, message status, and segmentation criteria enables a deep dive into which campaigns drive the best user engagement.
Use Case Benefit: By identifying high-performing campaigns, marketers can optimize message content, timing, and target audiences, improving future campaign effectiveness and ROI.

2. Customer Segmentation and Behavioral Analytics

Dataset Used: contacts, contactActivities, events
Description: BigQuery can process large volumes of customer activity and segmentation data to identify patterns in user behavior, such as engagement frequency or preferred communication channels. This enables mobile apps to identify highly engaged users and customize interactions accordingly.
Use Case Benefit: With detailed segmentation analysis, apps can create highly personalized, behavior-driven campaigns that lead to better user retention and increased lifetime value (LTV).

3. Personalization and Retargeting

Dataset Used: devices, contacts, contactActivities, events
Description: Exported data on user devices, activities, and engagement history allows for personalized retargeting efforts based on past behavior. For example, data on contacts’ last activity and device types enables personalized messaging for re-engagement.
Use Case Benefit: Personalizing messages based on user activity enhances user experience and increases the likelihood of conversion, creating a more dynamic engagement strategy.

4. Predictive Modeling for Churn and Retention

Dataset Used: contacts, orders, revenue, contactActivities
Description: With datasets that contain historical revenue, engagement, and order data, BigQuery can be used to create predictive models for user churn and retention. These models help identify at-risk users based on engagement history, purchasing patterns, and campaign response rates.
Use Case Benefit: By identifying users likely to churn, apps can trigger targeted campaigns or loyalty offers to retain them, ultimately reducing churn and increasing LTV.

5. Order and Revenue Analysis for Strategic Insights

Dataset Used: orders, orderItems, revenue
Description: Detailed order and revenue data exported to BigQuery allows teams to monitor average order values, purchasing frequency, and revenue trends. Analyzing these metrics helps identify opportunities for upselling, product bundling, and other revenue-boosting strategies.
Use Case Benefit: By understanding revenue patterns, mobile apps can make data-driven decisions on pricing, promotions, and customer incentives to maximize revenue and profitability.

6. Monitoring and Alerting on Key Metrics

Dataset Used: broadcasts, contactActivities, revenue
Description: With data exported to BigQuery, teams can set up dashboards to monitor key performance metrics like message delivery rates, engagement levels, and revenue. When abnormal trends are detected (e.g., a drop in delivery rates), teams can receive alerts.
Use Case Benefit: Monitoring allows for rapid response to performance issues, ensuring high campaign effectiveness and consistent user engagement.

7. Campaign ROI and Budget Allocation Analysis

Dataset Used: broadcasts, orders, revenue, contactActivities
Description: Exporting detailed broadcast and revenue data enables marketers to measure ROI by matching campaign efforts to revenue generated. This insight is valuable for calculating the cost-effectiveness of various campaigns and making informed budget allocation decisions.
Use Case Benefit: By analyzing campaign ROI, marketers can focus on high-performing campaigns and optimize spending, ultimately maximizing marketing effectiveness.

8. Customer Journey Mapping Across Channels

Dataset Used: contacts, contactActivities, events, orders
Description: BigQuery’s data processing allows the consolidation of user journeys, tracking users as they interact with different channels and messages throughout their lifecycle. This provides a cohesive view of how users progress from onboarding to conversion.
Use Case Benefit: Mapping the user journey offers insights into potential drop-off points and highlights areas for improvement, allowing apps to enhance the user experience and improve conversion rates.