Importing Data from the BigQuery Tables

Let’s consider how to create a BigQuery data source and use it for segmentation and personalization in Reteno.

Configuring a Data Source for Importing Data to Reteno

1. Go to your profile → Settings → Data sources, click New data source, and select External data source.

2. Select the created connector.

3. Specify the dataset and table, and enter a source name (also you can specify filters for forming the message content here). Click Save.

Using BigQuery Data for Fitness App Campaigns Based on Engagement and Workout History

You can create personalized campaigns that dynamically reference user data like engagement level or workout history stored in BigQuery. Let’s go through some examples of how to integrate this data into campaign messages.

1. Bulk Campaign with BigQuery Data

Suppose you’re running a bulk campaign aimed at encouraging highly engaged users to try new workout challenges. Here’s an example of how you might structure data for a campaign to a contact with the email [email protected] using the engagement_levels data source:

{  
  "data": {  
    "engagement_levels": [  
      {  
        "id": "2",  
        "email": "[email protected]",  
        "name": "Jordan Lee",  
        "lastWorkoutDate": "2023-10-15T10:00:00Z",  
        "workoutStreak": 15,  
        "averageWorkoutTime": 45  
      }  
    ]  
  }  
}

You can reference each field in the message using a Velocity syntax as follows:

• Directly with parameters (when the number of array items is fixed):

$!data.get('engagement_levels').get(0).get('name')
$!data.get('engagement_levels').get(0).get('workoutStreak')

• Using a loop (ideal for variable-length arrays):

#foreach($el in $!data.get('engagement_levels'))
  $!el.get('name')
  $!el.get('workoutStreak')
#end

2. Triggered Campaign Based on Workout History

Let’s say you want to send a message to users who reach a specific workout milestone, like completing 50 workouts. To do this, create a dynamic segment that includes only users whose workout history meets the milestone condition.

Set up a regular workflow for this segment.

When the workflow triggers for contacts matching specified conditions, the system creates an event. The event name is generated by combining the static prefix regularEventType with the segment ID, resulting in a name like regularEventType-170531841.

This event includes contact information, such as the contact ID in Reteno (contactId), email address (emailAddress), and data pulled from an external table. Each field from the table is converted into a key and placed within an array, labeled with a numeric name that matches the data source ID.

This array is then serialized and stored as a string in the jsonParam field. An example event body might look like this:

{  
  "params": \[  
    {  
      "name": "contactId",  
      "value": "123456789"  
    },  
    {  
      "name": "jsonParam",  
      "value": "{\"1043\":[{\"id\":2,\"email\":\"[email protected]\",\"name\":\"Jordan Lee\",\"lastWorkoutDate\":\"2023-10-15T10:00:00Z\",\"workoutStreak\":50,\"averageWorkoutTime\":45}]}"  
    },  
    {  
      "name": "emailAddress",  
      "value": "[[email protected]](mailto:[email protected])"  
    }  
  ]  
}

When extracting data to a message, the data source number in Reteno is used as the name of the array:

{  
  "data": {  
    "1043": [  
      {  
        "id": "2",  
        "email": "[email protected]",  
        "name": "Jordan Lee",  
        "lastWorkoutDate": "2023-10-15T10:00:00Z",  
        "workoutStreak": 15,  
        "averageWorkoutTime": 45  
      }  
    ]  
  }  
}

You can access the fields of the 1043 array in the message template in two ways:

• With parameters (if you know the number of items in the data array):

$!data.get('1043').get(0).get('name')  
$!data.get('1043').get(0).get('workoutStreak')

• Using a loop (ideal for flexible-length arrays):

#foreach($el in $!data.get('1043'))
  $!el.get('name')
  $!el.get('workoutStreak')
#end

Configuring Dynamic Segment by BigQuery Parameters

1. Create the Dynamic segment (the Contacts → Segments section).
2. Select the conditions for inclusion in the segment. In the condition options, click the source name, then the option name, and set the condition. For example, CA → started → today:
   • CA (Contact Activity) is the name of your data source,
   • started is the fact of contact activity,
   • today is the condition for the contact to join the group based on this parameter.

📘

Note

Connection to the external database doesn’t presuppose contact import. Segmentation is only available for contacts that exist both in your Reteno account and in the external database. Synchronize and update contacts before creating campaigns.