Importing Data from the BigQuery Tables

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

Configuring a Data Source for Importing Data to Yespo

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 Personalized E-commerce Campaigns 

BigQuery data can drive highly targeted campaigns by dynamically referencing customer-specific details such as purchase history, engagement levels, and preferred product categories. Here’s how to incorporate this data into your e-commerce campaigns.

1. Bulk Campaign with BigQuery Data

Suppose you want to run a campaign targeting customers who recently purchased specific product categories, offering a personalized discount code. Here’s an example data setup for a contact, jdoe@example.com using the discount_codes data source:

{
  "data": {
    "discount_codes": [
      {
        "id": "3",
        "email": "jdoe@example.com",
        "name": "John Doe",
        "recent_purchase_category": "Home Appliances",
        "discount_code": "SAVE20-HOME",
        "expiry_date": "2023-12-31T23:59:59Z"
      }
    ]
  }
}

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

• Directly with parameters (if you know the array has a fixed length):

$!data.get('discount_codes').get(0).get('name')
$!data.get('discount_codes').get(0).get('discount_code')

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

#foreach($code in $!data.get('discount_codes'))
  $!code.get('name')
  $!code.get('discount_code')
#end

2. Triggered Campaign Based on Purchase Milestones

To send personalized messages when customers reach purchase milestones, such as their 50th order:

1. Create a dynamic segment that includes customers meeting specific purchase conditions (for example, the 50th purchase was made).

2. 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 Yespo (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\":\"shopper@example.com\",\"name\":\"Jordan Lee\",\"lastPurchaseDate\":\"2023-10-15T10:00:00Z\",\"purchaseFrequency\":50,\"averageBasketValue\":120}]}"
    },
    {
      "name": "emailAddress",
      "value": "shopper@example.com"
    }
  ]
}

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

{
  "data": {
    "1043": [
      {
        "id": "2",
        "email": "shopper@example.com",
        "name": "Jordan Lee",
        "lastPurchaseDate": "2023-10-15T10:00:00Z",
        "purchaseFrequency": 50,
        "averageBasketValue": 120
      }
    ]
  }
}

If you know that there’s a single entry in the 1043 array, you can access fields directly:

$!data.get('1043').get(0).get('name')
$!data.get('1043').get(0).get('lastPurchaseDate')
$!data.get('1043').get(0).get('purchaseFrequency')
$!data.get('1043').get(0).get('averageBasketValue')

If the 1043 array could contain multiple entries, use a loop to extract all values dynamically:

#foreach($entry in $!data.get('1043'))
  $!entry.get('name')
  $!entry.get('lastPurchaseDate')
  $!entry.get('purchaseFrequency')
  $!entry.get('averageBasketValue')
#end

Creating a Dynamic Segment Using BigQuery Parameters

1. In Contacts → Segments, create a new dynamic segment.

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,  purchaseFrequency → equals → 50.