Overview
MoEngage allows customers to import users and events through tables stored inside your Snowflake databases.
Types of Imports
MoEngage can import the following from your Snowflake data warehouse:
- Registered Users: These are users who are already registered on MoEngage.
- Anonymous Users: These are users who are not yet registered on MoEngage.
- Events (Standard and User Defined): MoEngage can import standard events like Campaign Interaction Events as well as your own user-defined events.
Preparing the Data
MoEngage does not need any particular table schema to be prepared for imports. All the columns in your table can be either skipped or mapped separately on the MoEngage Dashboard. However, there are a few things to be taken care of before setting up the imports.
When you set up periodic User Imports on MoEngage, we will only sync the data that has changed since the last sync was run. MoEngage uses a timestamp (date + time) column called updated_at
to identify which row has changed since the previous sync. You are free to name this column as you like provided it contains the timestamp of the instant when the row's data was last changed. If you're calling this column by a different name in your table, you can always configure this mapping separately on the MoEngage Dashboard.
When you set up Event Imports on MoEngage, you will need to mandatorily map a column that contains the event's timestamp (date + time). This column will be used to only sync the new events since the previous sync. If you have a different name for this column, you can configure this mapping as well on the MoEngage Dashboard.
Additionally, if you want to import standard MoEngage events, make sure that the event names in your table match the ones with MoEngage standard event names.
Importing Datetype attributes
Importing Datetype attributes requires additional steps. You learn more about this in our dedicated guide.
Required Access Permissions
MoEngage will require READ
access to your database so that we can pull data into MoEngage. You can either grant these permissions to an existing database user or choose to create a new dedicated database user for MoEngage:
-- Create a role for the MoEngage user
CREATE ROLE MOENGAGE_ROLE;
-- Allow the user to run queries in the warehouse
GRANT USAGE ON WAREHOUSE <YOUR_WAREHOUSE_NAME> TO ROLE MOENGAGE_ROLE;
-- Let the user see this database
GRANT USAGE ON DATABASE "<YOUR_DATABASE_NAME>" TO ROLE MOENGAGE_ROLE;
-- Let the user see all schemas in this database
GRANT USAGE ON ALL SCHEMAS IN DATABASE "<YOUR_DATABASE_NAME>" TO ROLE MOENGAGE_ROLE;
-- Let the user read all existing tables in this schema
GRANT SELECT ON ALL TABLES IN DATABASE "<YOUR_DATABASE_NAME>" TO ROLE MOENGAGE_ROLE;
-- Let the user read all future tables in this schema
GRANT SELECT ON FUTURE TABLES IN DATABASE "<YOUR_DATABASE_NAME>" TO ROLE MOENGAGE_ROLE;
-- Create the MoEngage user (MOENGAGE_USER is username here)
CREATE USER MOENGAGE_USER WITH DEFAULT_ROLE = MOENGAGE_ROLE DEFAULT_WAREHOUSE = <YOUR_WAREHOUSE_NAME> PASSWORD = '<YOUR_PASSWORD>';
-- Grant the role to the user
GRANT ROLE MOENGAGE_ROLE TO USER MOENGAGE_USER;
Remember to change the following values:
-
<YOUR_WAREHOUSE_NAME>
- The name of the warehouse you want MoEngage to use while executing queries. -
<YOUR_DATABASE_NAME>
- The name of the database you want to import data from. -
<YOUR_PASSWORD>
- Give a strong password to the newly created database user.
Set Up Imports from Snowflake
info |
Prerequisites
|
You can set up Snowflake Imports from the MoEngage Dashboard >> Segment >> Imports page.
Clicking on "+Import" button will allow you to select your source. Click on Snowflake to create a new import for either Users (Registered/Anonymous can be picked in the next step) or Events.
Step 1: Select Your Snowflake Connection and Table Source
Import Name
Enter a name for this import to easily identify it on the Imports Dashboard. Based on the type of import selected, your next steps might vary:
You will now have the option to select whether you want to import Registered users or Anonymous users. You can also choose to import both together:
For Event imports, you will now have the option to select which event it is that you want to import:
You can either select an existing event or create a new one. If you have multiple events stored in a single table, MoEngage will look at the "Event Name" column to determine matching rows to import. The value of the "Event Name" column will be determined by the event you pick. Some of these events might have different Display Names and Event Names. You can always go to the Data Management page and view the Event Information. For example, the event "App/Site Opened" should have the value of the "Event Name" column as MOE_APP_OPENED
:
You can also create a new event by clicking on the "+ Create event" option at the end of the dropdown:
Give your event a name that is unique. 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 also your other MoEngage events. Do note that any new events you create now will only be available in your app after the first successful import.
Import Source
In this first step, "Source and format", you will need to tell MoEngage which Snowflake connection to use and the table to import from.
To get started, select a Snowflake connection to use for this Import. If you have not already created a Snowflake connection, you can click on the "+ Add connection" option at the end of the dropdown and you will be taken to the App Marketplace to set it up. You can learn more about how to connect your Snowflake warehouse to MoEngage here.
Once you have selected your Snowflake connection, you will be required to select the schema and table to import from. If there is an error loading your schemas, ensure you have given MoEngage sufficient permissions as mentioned above.
Event Imports
In addition to the above steps, MoEngage provides additional support for tables containing multiple events. If your table contains multiple events, you need to first "Preview" the table and then select the "Table contains multiple events" option:
MoEngage will use the values of the "Event name" column to filter out rows that need to be imported. We will import only those rows that match the selected event name. You can mark an existing column of your table as the column containing the event name. When you select this column and Preview again, we will show you the filtered rows for you to confirm before proceeding:
After you preview your table, you can move on to the next step.
Step 2: Map Your Columns to MoEngage Attributes
In this step, you need to map the columns of your table to the attributes present in MoEngage. All your columns will be shown one below the other:
- Column name - This is the column name to be mapped. Below the column name, we also show a sample value (picked from the first row of the fetched table in the previous step) for your reference.
- Map attribute - Here you need to pick which MoEngage attribute you want to map the table 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.
- More actions - 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:
Mapping | Description |
---|---|
User ID | Your table should mandatorily have a column that contains a unique user identifier (that identifies a user’s account in your system). |
Updated at |
MoEngage will use this column to determine which rows have been added/updated since the last sync. You need to ensure that this timestamp (date + time) is in UTC Timezone. The column type for this should be
For the full list of supported datetime formats, refer to this section. |
Mapping | Description |
---|---|
Anonymous ID | You need to mark a column from your table (email, mobile number, etc.) as an identifier of these users. |
Updated at |
MoEngage will use this column to determine which rows have been added/updated since the last sync. You need to ensure that this timestamp (date + time) is in UTC Timezone. The column type for this should be
For the full list of supported datetime formats, refer to this section. |
Mapping | Description |
---|---|
User ID | Your table should mandatorily have a column that contains a unique user identifier (that identifies a user’s account in your system). Any user that has an empty user ID will automatically be imported as an Anonymous user. |
Anonymous ID | You need to mark a column from your table (email, mobile number, etc.) as an identifier of these users. |
Updated at |
MoEngage will use this column to determine which rows have been added/updated since the last sync. You need to ensure that this timestamp (date + time) is in UTC Timezone. The column type for this should be
For the full list of supported datetime formats, refer to this section. |
Mapping | Description |
---|---|
User ID | This column will be used to match the users in MoEngage to your events. |
Event time |
You need 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
For the full list of supported datetime formats, refer to this section. |
Once a mandatory mapping is marked, it will reflect against the column name in the mapping table and you will no longer be able to mark the column as skippable.
Just like new events, you can also create a new attribute from the dropdown by clicking on "+ Create attribute":
Give your attribute a name and select its Data type. You can edit this as well as existing attributes from the Data Management page. Do note that any new attributes you create now will only be available in your app after the first successful import.
Manifest Files
Optionally, you can choose to auto-map these columns by uploading a manifest file.
To upload a manifest file, click the "Upload mapping file" option on the top-right of the mapping table:
Upload your manifest file and click Done. Your mappings will be auto-configured accordingly. Any columns with non-MoEngage attributes will be left blank and can either manually map the column or create a new attribute for it. Make sure your manifest file follows the expected conventions as mentioned.
Any additional columns that are present in your Manifest File but not there 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 for you to manually configure it.
Do note that if a column in the manifest file is mapped to a non-existent MoEngage Attribute, then the mapping will be blank and you will need to first manually create a new attribute from the UI and then map it.
Support for Object Data Type
info |
Prerequisites Support for Object Data Type needs to be enabled for your account. |
The Object data type is supported in Snowflake as well.
Store Compatible JSON Data in Snowflake
To store valid JSON inside Snowflake, you must change the data type of the column to VARIANT type. For more information, refer here. The JSON stored inside Snowflake should be a valid JSON; otherwise, the values will not be written as JSON. Here is an example JSON column:
{ "Designation": "SSE", "Palace": "Banglore", "age": 30, "name": "Shasha" }
Import JSON Data via Snowflake
Existing attributes in MoEngage that have been marked as an Object type and are available to map to columns in Snowflake:
You can always create new Object attributes from the mapping step as well, as shown below:
info |
Information MoEngage does not support mapping with nested attributes. Only top-level attributes are available to map. |
Save as a Segment
If you're importing users, you can also add them to a custom segment inside MoEngage. Each time the sync is run, the imported users will continue to be added to this custom segment. We will not delete any users from this segment. You can then send targeted campaigns to this segment. To save your imported users in a custom segment, enable the "Save as a custom segment" option:
Give your Segment a name and 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, check the "Update existing users only" option under Import Behaviour:
Sending Import Notifications
You can, optionally, choose to get notified about your imports' statuses via email. Select up to 10 emails to send the status emails to. You will receive an email about the following events:
- An import was created
- An import was successful
- An import failed
When you are satisfied with all mappings, click Next. If there are any errors, correct them to move forward.
Step 3: Select the Import Frequency
In this step, you need to define when to sync with your tables. We support the following types of imports:
- One-Time imports: You can either choose to run the import as soon as possible or at a later date and time (scheduled). All existing rows that match the import criteria will be imported.
- Periodic: You can choose to run your imports every few minutes, hourly, daily, weekly, or monthly, or with intervals and advanced configurations.
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 Please note that the first time the import runs, we will import all the matching rows from your table. On every subsequent import, we will pull in only the changed rows. |
Duplicate Imports
You can only configure one unique import at a time. An import is considered a duplicated import when all of these are the same:
- Import Type - Users / Events
- Import Sub-Type - Event Name / Registered / Anonymous / All users - If the event name is the same, or there already exists a registered/anonymous/all users import with the same Snowflake connection
- Snowflake connection
- Import schema & table
As long as any one of the above is different, the import is considered unique.
Frequently Asked Questions
No. Currently, writing manual queries (to perform joins, etc) is not supported. Creating a dedicated table with all the columns you want to import in MoEngage is recommended.
We currently only support importing data from your Snowflake Tables. Support for Views will be added in the future.
Yes. Creating a one-time import is essentially the same as importing historical data since we pull in all the rows on the first sync.