BigQuery Imports

Overview

MoEngage enables you to import both user profiles and event data directly from tables within your BigQuery databases. This integration streamlines the process of bringing your valuable customer data into MoEngage for enhanced segmentation and engagement.

Types of Imports

MoEngage supports the following types of data imports from your BigQuery data warehouse:

Registered Users: Users who are already registered on MoEngage.
Anonymous Users: Users who are not yet registered on MoEngage.
Events (standard and user-defined): MoEngage can import standard events like campaign interaction events and your user-defined or custom events.

Required Access Permissions

For more information, refer to Grant Permissions to MoEngage

Import Datetype Attributes

Importing datetype attributes requires additional steps. For more information, refer here.

Set Up Imports from BigQuery

library_add_check

Prerequisites

Ensure you have an existing BigQuery connection set up in the MoEngage App Marketplace with relevant permissions.
If your security policies require you to whitelist our IPs, you can refer to here.
Support for Object Data Type needs to be enabled for your account (optional).

To set up BigQuery Imports, perform the following steps:

On the left navigation menu in the MoEngage dashboard, click Data > Data imports.
Click + Import in the upper-right corner and select Users or Events to create a new import.
Click the Google BigQuery tile.
Click Continue.

Step 1: Source and Format

In this step, configure the BigQuery connection and specify the file format. In the Import name box, enter a name for this import to easily identify it on the Imports Dashboard.

Import Name

Based on the type of import selected, your next steps might vary:

You can select whether to import registered users, anonymous users, or both. In the Select user type section, select Registered users or Anonymous users. Both user types require different file names. For more information, refer to Naming Conventions.

For Event imports, you can select the event you want to import from the Select event list.
Based on the event you pick, your file names will be different. Your file names must begin with the event name of your chosen event. In the Select event list, MoEngage displays the names of all the available events for your app. When you hover over an event, MoEngage will show you the file name it expects for that particular event:

You can either select an existing event or create a new one. If you have multiple events stored in a single table, MoEngage determines matching rows to import based on the Event Name column. The value in the Event name column depends on the event you select. Some of these events might have different display names and event names. You can always view the event information on the Data Management page. For example, the event App/Site Opened must have the value of the Event name column as MOE_APP_OPENED:

To create a new event, perform the following steps:

Click the + Create event at the end of the Select event list. The Create new event dialog box is displayed.
In the Event name box, type a unique name for your event. By default, your display name will be the same as the event name.

You can go to the Data Management page to view or edit this event and your other MoEngage events.

Import Source

In this first step, Source and format, you must specify MoEngage, which BigQuery connection to use, and the table from which to import. To get started, perform the following steps:

In the BigQuery connection list, select a connection to use for this import.
If you have not already created a BigQuery connection, click + Add connection at the end of the BigQuery connection list, and you will be redirected to the App Marketplace to set it up. You can learn more about connecting your BigQuery warehouse to MoEngage here.
After you have selected your BigQuery connection, the Schema/Dataset and Table/View lists are displayed.
In the Schema/Dataset list, select the schema/dataset.
Note: If your schemas are loading incorrectly, ensure that you have granted MoEngage the necessary permissions detailed in the Prerequisites.
In the Table/View list, select the table/view to import data from.

Event Imports

In addition to the above steps, MoEngage provides additional support for tables containing multiple events. If your table contains multiple events, you must first preview it and then select the Table contains multiple events check box.

MoEngage uses the values of the Event_ Name column to filter out rows that need to be imported. It imports only those rows that match the selected event name. You can designate an existing column in your table as the event name column. After selecting this column and previewing the data again, filtered rows are displayed for your review before proceeding with the import:

After you preview your table, you will move to the second step, Import configuration and action.

Step 2: Import Configuration and Action

In this step, after configuring the BigQuery connection and file format, you must map your data columns to MoEngage attributes. All your columns are shown one below the other:

In the Map Columns section, you have the following:

Column name: This is the column name (picked from the first row of the fetched file in the previous step) to be mapped. Below the column name, MoEngage also shows a sample value (picked from the second row of the fetched file in the previous step) for your reference.
Map column to attribute: You must select which MoEngage attribute you want to map the column to. You can also choose to create a new attribute. Some attributes support ingestion from multiple data types, so you need to pick the data type of the column as well. For the "DateTime" columns, you also need to pick the format. For the "DateTime" columns, you also need to pick the format.
Action: You can optionally choose to skip the column. The skipped column will not be imported.

Depending on the type of import, there are a few mandatory mappings required:

arrow_drop_down User Imports

Mapping Description

User ID In your table, include a column with a unique user identifier, which is essential for identifying user accounts within your system.

Updated at

MoEngage uses this column to determine which rows have been added/updated since the last sync. You must ensure that this timestamp (date+time) is in UTC Timezone. The column type for this should be TIMESTAMP.

For the complete list of supported datetime formats, refer to this section.

arrow_drop_down Event Imports

Mapping Description

User ID This column matches the user IDs in MoEngage to your events.

Event time

You must ensure to map the column that contains the timestamp (date+time) of when the event occurred. You need to ensure that this timestamp (date+time) is in UTC Timezone. The column type for this should be TIMESTAMP. The Event Time of the imported event will be converted to the timezone chosen in your MoEngage dashboard settings.

For the complete list of supported datetime formats, refer to this section.

After a mandatory mapping is marked, it will reflect against the column name in the mapping table, and you can no longer mark the column as skippable.

Just like new events, you can also create a new user attribute. To do so:

Click + Create attribute available in the Select attribute list. The Create new attribute dialog box is displayed.
In the Attribute name box, type a name for your attribute.
In the Data type list, select a data type. You can edit these and existing attributes from the Data Management page.

info
Information

The newly created user attributes will not appear on the Data Management page until the initial import is successful.

Manifest Files

Optionally, you can auto-map these columns by uploading a Manifest file. To upload a manifest file:

Click the Upload mapping file in the upper-right of the mapping table.
On the Upload mapping dialog box, upload your manifest file.
Click Done.

Your mappings are auto-configured accordingly. Any columns with non-MoEngage attributes are left blank, and you can either manually map the column or create a new attribute for it. Make sure your manifest file follows the expected conventions.

Any additional columns in your Manifest File that are not in your table will be ignored. Also, if the mapping for an existing table column is not present in the manifest file, MoEngage will keep the mapping blank so that you can manually configure it.

info	Information If a column in the manifest file is mapped to a non-existent MoEngage attribute, the mapping will be blank, and you will need to create a new attribute from the UI manually and then map it.

Support for Object Data Type

The Object data type is supported in BigQuery as well.

Store Compatible JSON Data in BigQuery

To store JSON data inside BigQuery, you must change the data type of the column to VARIANT type. For more information, refer here. The JSON stored inside BigQuery should be a valid JSON; otherwise, the values will not be written as JSON. Here is an example JSON column:

JavaScript

{ "Designation": "SSE", "Palace": "Banglore", "age": 30, "name": "Shasha" }

Import JSON Data via BigQuery

You can import the JSON data into BigQuery by associating existing attributes in the MoEngage platform that have been designated as Object type with columns in BigQuery.

You can also create new Object attributes by clicking Create new attribute in the Select attribute list under the Map attribute column.

info	Information MoEngage does not support mapping with nested attributes. Only top-level attributes are available to map.

Save Users as a Segment

When importing users, you can include them in a custom segment in MoEngage. The imported users are consistently added to this segment with each sync, and no users will be removed. To save imported users as a custom segment, perform the following steps:

Turn the Save as a custom segment toggle on to save your imported users in a custom segment and send tailored campaigns to the same.
In the Segment name box, type a name for your segment.
In the Column having user ID list, select the identifier column in your table.

Import Behaviour

In the case of User Imports, you can also choose to update existing users only. This is helpful when you want to bulk update users' attributes in MoEngage without creating any new users. To enable this, select the Update existing users only check box under Import Behaviour:

Send Import Notifications

You can choose to be notified about the status of your imports via email. To do so:

Select the Send import status to check box.
In the Select email id list, select the email ID. You can select up to 10 emails to send the status emails to.

The import status email contains information about the following events:
- An import was created
- An import was successful
- An import failed
After completing all mappings, click Next.

Step 3: Scheduling

In this step, you must define when to import files from your BigQuery server.

In this step, you must define when to sync with your tables. MoEngage supports the following types of imports:

One-time imports: You can run the import as soon as possible or at a later date and time (scheduled).
Periodic: You can run your imports hourly, daily, weekly, or monthly, or with intervals and advanced configurations.

For each run, MoEngage will try to fetch a file with the configured DateTime format, so ensure your file names are configured properly. Optionally, you can specify whether the import should end after a specified set of occurrences or at a particular date. Click Done when ready.

warning

Warning

Upon initial import, all matching rows from your table are imported. Subsequent imports only include changed rows.

Duplicate Imports

A duplicate import is considered when the:

Users/ Events import types are the same.
Event name/ Registered/Anonymous/All users import subtypes are the same.
Event name/ Registered/Anonymous/All users import types have the same BigQuery connection.
Event name/ Registered/Anonymous/All users import types have the same Schema/Dataset and Table/View.

FAQ

arrow_drop_down What should the BigQuery column data type be for JSON data to import successfully as an Object Data Type into MoEngage?

BigQuery requires the column type to be JSON during table creation.

arrow_drop_down How does MoEngage import STRUCT columns in BigQuery?

You can import STRUCT columns as Object Data Types in MoEngage. We will pull in the schema of your STRUCT and create a corresponding set of fields in MoEngage. It is not possible to cherry-pick or rename the individual fields within your struct columns when importing them into MoEngage.

arrow_drop_down How does MoEngage handle BigQuery TIMESTAMP columns containing local time zones instead of UTC during import?

MoEngage interprets stored time as UTC. BigQuery advises storing timestamps in UTC.

arrow_drop_down Can I import data across tables?

No. Currently, writing manual queries (to perform joins, and so on) is not supported. It is recommended that you create a dedicated table or view with all the columns you want to import to MoEngage.

arrow_drop_down Do you support importing Views from BigQuery?

Yes. We also support importing data from your BigQuery Views. If you grant MoEngage access to read Views, they will be automatically listed under the Table/View selection drop-down list.

arrow_drop_down Can I do a historical import?

Yes. Creating a one-time import is essentially the same as importing historical data because we pull in all the rows on the first sync.