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 Source for Data Export
- Go to Settings → Connectors and click Add data source.
- Upload the key file and check the boxes for the data sets you want to upload. Click Save.
The connection will be displayed in Connectors → Export to BigQuery, here you can edit the 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, 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 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) |
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 Reteno 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 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') |
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 | 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 |
string | ||
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, Viber, 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') |
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 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.
Updated 28 days ago