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;
• workflows.

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

📘

Note

After you set up the export, the first sync happens on the next scheduled cycle — it isn't instant. If no data has appeared in your BigQuery tables well after that window, double-check that the selected datasets actually have data to export for your account (for example, no data of that type has been generated yet) before assuming the export is stuck. If the issue persists, contact support.

📘

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.

Along with the main tables, temporary patch_<table name>_<UUID> tables appear during a sync. The export updates data through them: changes are first uploaded to a temporary table, then BigQuery merges them into the main table and deletes the temporary one automatically. Table names — both main and temporary — can't be customized. If leftover patch_ tables remain in the dataset after a sync, contact our support team.

📘

Note

Editing an already-exported message (for example, adding or changing a tag) does not retroactively update its existing row in the messages table in BigQuery. The row reflects the message's state as of the export that created it; only later exports pick up subsequent changes going forward.

📘

Note

The connector doesn't backfill historical data — export starts collecting data only from the moment the connector is activated. See Integrating with Google BigQuery for the workaround to get roughly the last 3 months of data via the API.

Creating Data Export

1. Go to Settings → Data Export → BigQuery.

2. 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

ContactActivities

*The field will be removed as overdated; use the field indicated in brackets

Contacts

Devices

Events

📘

Note

• The number of rows in the events table is not the same as the number of contacts — each occurrence of an event is exported as a separate row. A single contact can trigger the same event multiple times (for example, reinstalling the app on a new device, or using multiple devices), and events remain in the table even if the related contact is later deleted. Don't use event row counts as a proxy for unique contact counts.
• paramsJson includes only flat (top-level) event parameters. Nested parameters — where a parameter's value is itself an object (for example, a source field nested inside another parameter) — are not exported to BigQuery.

Messages

OrderItems

Orders

Revenue

*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.

Workflows

Use Cases

Here are some key use cases for exporting data from eSputnik 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.

Message status descriptions

The statusdescription field explains why a message was not sent or delivered:

A null value means that no status description was recorded. Do not treat every non-null value as a delivery failure without also checking the message status.