Trigger Mails from Google BigQuery
- Sylvain Paillard
Overview
In this document we describe how to configure Trigger Mail in Boxalino BigQuery environment and how the result can be integrated in e-mail system like MailChimp for the sending of the e-mail and with Boxalino Real-Time strategies and composite images / redirect links for the dynamic content of the e-mail.
As indicated in the following diagram, the steps are as follow:
Have you core data well integrated in the Boxalino Data Warehouse in BigQuery
Configure the calculation of Triggers and products according to your needs (see section “Configure Triggers” for details)
The system will generate the triggers and delay or cancel planned trigger mails depending on the marketing pressure configuration (e.g.: maximum number of e-mail per day or week, see section “Configure Marketing Pressure” for details).
Then, the process splits in two parts: Re-Target (bottom part)
Boxalino Mailchimp connector will automatically update the generated fields in Mailchimp (see section “Mailchimp connector” for more details). If you don’t have Mailchimp, you will need to pull the data from the lab table “subscriber_properties” and synchronize them with your e-mail system.
Configure the name of your Mailchimp fields to match the names defined in the “Configure Triggers” step and the connector will automatically update all the audiences (i.e.: subscriber lists) defined for each trigger with Mailchimp.
You can set many fields in Mailchimp through this mechanisms, but the ones we focus on here are the triggers with Marketing pressure. These fields must be of date format and then should be set in Mailchimp for the e-mail to be sent on that specific date.
Individualize (top-part)
Products (as well as content or banners) related to the triggers (and of course also not related to the triggers) are loaded in Boxalino Real-Time Cloud (RTC)
You can configure your widget strategies to return the right product/content/banner according to different parameters, including the specific products related to the triggers you defined (this part is not described in this document and fall into the general documentation of your widget strategies)
After configuring the templates of your different type of content (Products, Blog Articles, Banner) and configuring their automated generation in your Media Server, you can configure them in your e-mail content as dynamic link (this part is not described in this document and fall into the documentation of how to configure the dynamic links and image sources in your e-mail templates)
Configure Triggers
In the coming sections, we will list the supported trigger configurations.
The configuration is always done in the Configuration view “..._views.subscriber_properties”.
The results are always generated in the calculated Results table “..._lab.subscriber_properties”:
#1 - Date based on prior orders (nth_order_ts)
This trigger defines an date based on the prior orders of the customers. Either from the first orders (e.g.: 6 months after first order) or from the last orders (e.g.: no orders for the last 6 months).
Identify products for dynamic content?
repurchase : yes the product which should be repurchased
otherwise : no
Example cases:
Anniversary e-mail from first purchase
No purchase for the last 6 months (but a recent visit to the web-site)
Products you ordered are now in discount (use filter_product_property and filter_product_property_value to set products which are currently in discount)
Re-fill your stock (based on typical time between a repurchase of the same product)
Parameter Name | Typical Value | Comments |
|---|---|---|
nth | 1 / -1 | Which order should be considered (1 = first, 2 = second, -1 = last, -2 = second to last, …). If all purchases should be considered (e.g.: for repurchase case) can be left unset or set with an empty string. |
datetime_add_day | 180 | How many days should be added to the nth purchase (180 would make it 6 months later) |
filter_product_property filter_product_property_value | brand Apple | should only purchase about specific product be considered? (if not, no need to define or define with empty string) |
filter_customer_property filter_customer_property_value | gender Female | should only purchase about specific customers be considered? (if not, no need to define or define with empty string) |
filter_order_property filter_order_property_value | source MobileApp | should only purchase of specific types of orders be considered? (if not, no need to define or define with empty string) |
from to | ‘2000-01-01’ ‘2050-31-12’ | should the cases considered only be limited to a specific date range |
set_today | 0/1 | should the date considered (before the application of datetime_add_day) be replace with today if a match is found |
max_order_ts_days | 30 | should only the orders of the last X days be considered |
visit_max_day | 10 | should only customers who recently visited the web-site during the last X days be considered |
repurchase_type | avg_days_between | if defined, will considered the average days between two purchases of the same product (from all customer) only set if needed (if not, no need to define or define with empty string) |
repurchase_min_cnt | 10 | what is the minimum number of type customers have re-bought this product so it is considered (to filter out products which are not really repurchase-able) |
#2 - Count based on prior orders (order_count)
This case defines an count based on the prior orders of the customers. Either a count of orders matching the conditions or a count of products matching the conditions.
This case is not a trigger as it exports a number which can be used for segmentation.
Identify products for dynamic content?
no
Example cases:
Newsletter to customers who bought brand Apple more than 3 times
Newsletter to customers who have over 3 products they bought currently in discount
Parameter Name | Typical Value | Comments |
|---|---|---|
distincts | order_id / product_id | what should we count (distinct orders or distinct products in these orders) |
filter_product_property filter_product_property_value | brand Apple | should only purchase about specific product be considered? (if not, no need to define or define with empty strings) |
filter_customer_property filter_customer_property_value | gender Female | should only purchase about specific customers be considered? (if not, no need to define or define with empty string) |
filter_order_property filter_order_property_value | source MobileApp | should only purchase of specific types of orders be considered? (if not, no need to define or define with empty strings) |
from to | ‘2000-01-01’ ‘2050-31-12’ | should the cases considered only be limited to a specific date range |
#3 - Customer Property (customer_property)
This case defines an count based on the prior orders of the customers. Either a count of orders matching the conditions or a count of products matching the conditions.
This case can be used both for trigger or not as customer properties might be dates (like date of birth) or not.
Example cases:
Birthday
Any customer property to be used for segmentation
Parameter Name | Typical Value | Comments |
|---|---|---|
property | id / any property name special names: dob / gender / zip / country | what customer property should be exported (typically id = e-mail address) |
format | ts / string | is the format a date (ts = timestamp) or something else (string is sufficient as non time-stamp cases are stored as string) |
birthday | true/false | if set to true, will replace the date (e.g.: date of birth) with the same date of the year but of the current year |
datetime_add_day | 180 | How many days should be added to the date (only for ts format) |
#4 - Ordered products (ordered_products)
This case defines an concatenated list of product property values based on the prior orders of the customers. Either a count of orders matching the conditions or a count of products matching the conditions. The values will appear all next to each other separated by a ','. You can then do segmentation by using a logic “if this field contains XXX”.
This case is not a trigger as it exports a number which can be used for segmentation.
Identify products for dynamic content?
no
Example cases:
Brands the customer already bought
Specific products the customer already bought
Parameter Name | Typical Value | Comments |
|---|---|---|
property | id / any property name | what product property should be concatenated |
filter_product_property filter_product_property_value | brand Apple | should only purchase about specific product be considered? (if not, no need to define or define with empty strings) |
filter_customer_property filter_customer_property_value | gender Female | should only purchase about specific customers be considered? (if not, no need to define or define with empty string) |
filter_order_property filter_order_property_value | source MobileApp | should only purchase of specific types of orders be considered? (if not, no need to define or define with empty strings) |
from to | ‘2000-01-01’ ‘2050-31-12’ | should the cases considered only be limited to a specific date range |
#5 - Abandoned carts (abandoned_cart)
This trigger defines a date based on a recently abandoned basket.
Identify products for dynamic content?
yes, the products in the abandoned cart
Example cases:
Abandoned-cart follow the next day
Abandoned-cart follow up when a product of the basket is in discount
Parameter Name | Typical Value | Comments |
|---|---|---|
datetime_add_day | 1 | How many days should be added to the nth purchase (1 would make it the next day) |
filter_product_property filter_product_property_value | brand Apple | should only purchase about specific product be considered? (if not, no need to define or define with empty strings) |
filter_customer_property filter_customer_property_value | gender Female | should only purchase about specific customers be considered? (if not, no need to define or define with empty string) |
from to | ‘2000-01-01’ ‘2050-31-12’ | should the cases considered only be limited to a specific date range |
#6 - Recent Product Changes (version_change)
This trigger defines a date based on a recently change on a product (new product, newly in discount, or other versioned properties).
Identify products for dynamic content?
yes, the products identified with recent change
Example cases:
New products of same brand you already bought
Parameter Name | Typical Value | Comments |
|---|---|---|
type | new | what type of recent change (new = new product) |
datetime_add_day | 1 | How many days should be added from the change (1 would make it the next day) |
filter_product_property filter_product_property_value | brand Apple | should only specific product be considered? (if not, no need to define or define with empty strings) |
filter_customer_property filter_customer_property_value | gender Female | should only purchase about specific customers be considered? (if not, no need to define or define with empty string) |
link_type | bought | what type of connection between the customer and the product (bought means to compare with his purchase history) |
link_product_property | id or any property | what product property should be considered for the link (e.g.: brand, means it will match if any linked products is of the same brand) |
filter_link_product_property filter_link_product_property_value | brand Apple | should only specific linked products be considered? (if not, no need to define or define with empty strings) |
filter_link_order_property filter_link_order_property_value | source MobileApp | should only linked purchase of specific types of orders be considered? (if not, no need to define or define with empty strings) - only used if link_type is “bought” |
from to | ‘2000-01-01’ ‘2050-31-12’ | should the cases considered only be limited to a specific date range |
Configure Marketing Pressure
You can configure your marketing pressure directly in the configuration of your triggers.
All the trigger configurations support the following parameters which, if defined, will automatically apply the desired marketing pressure logic.
Parameter Name | Typical Value | Comments |
|---|---|---|
trigger_active | 1 | if not set to 1, the trigger is not included in the marketing pressure limitation logic |
priority | 1 | highest priority will stay and lower priority will be delayed / cancel (1 is the highest priority), in case of similar priority, the selection is arbitrary |
maximum_delay_day | 14 | what is the maximum number of day the e-mail can be delayed (if would need to be delayed more, then will be canceled) |
maximum_frequency_day | 60 | what is the minimum time between two sending of the same trigger to the same customer |
In addition, there is a general configuration your marketing pressure in the view “subscriber_config”
SELECT
1 as maximum_daily_messages,
2 as maximum_weekly_messages,
6 as maximum_monthly_messages