Company

brabrabra is a Ukrainian lingerie brand. Its values include promotion of women's health, self-esteem and development. The communication with customers is based on teaching how to choose the right underwear based on body type, age and personal comfort preferences.

You can buy brabrabra lingerie both on the website and in offline stores. The advantage of buying offline is a personal consultation and individual approach. The advantage of online orders is that the client gets access to personal prices. These are additional discounts that are valid 90 days after the first purchase. You can learn about them by clicking the corresponding link in the site footer.

The company conducted a study aiming to find out if the audience is aware of the loyalty program. Customers were asked to answer the following questions:

• Do you know you have discounts on your account?
• Do you know about the loyalty program?

The result showed that many customers didn’t know they had discounts. The marketing decision was to notify each client as soon as their bonus program is activated.

All data on the customer shopping behavior is collected, processed and segmented in Google BigQuery, but the company lacked a tool for centralized communication with the client.

Thanks to the data processing in BQ, we understood what messages need to be sent to which clients and what time is appropriate. We needed a single platform that would allow us to sync all communication channels and automated workflows, and then deliver the result back to the database. After researching the market, we decided to choose our platform as it enables us to both receive data from Google BigQuery and return it back. This way, we enrich customer profiles and automate further communication processes.”

Andrey, digital marketer at brabrabra

Tasks

1. Integrate BigQuery tables to our platform and transfer customer contacts.
2. Automate notifications on activation of personalized prices.
3. Set data export from our service to Google BigQuery.

Solution

If you store contact data on external platforms and want to transfer it to our platform to use for segmentation and personalized campaigns, you can import it using the following tools: PostgreSQL, Google Sheets and BigQuery. We also set data export from our service to BigQuery to cover the customer's requirements.

Integration with Google BigQuery

We set integration between BigQuery and our platform using the project key which allows us to access the tables.

The key is a JSON file. It contains information about what users get access to the data and what access rights they have.

The key is generated in the console of the project's service accounts in Google Cloud Platform.

Having downloaded the key, we uploaded it to our platform in Settings > Connectors > Connect BigQuery.

Next, we set the connector parameters:

• Uploaded the key file.
• Specified the table column containing the contact uniqueness parameter.
• Mapped it to the our unique contact key.

To set up data export from our platform, we created a new source in Settings > Connectors > Export to BigQuery > Add data source. We uploaded the same project key used to set the connector and selected the dataset where the data will be exported.

We also selected the data we want to export. The data supported for transfer from our platform is as follows:

• contactActivities (status opened/clicked, campaign type bulk/triggered, button click, contact ID, media channel, sent message ID, message name, message tags, message link, campaign start date and time, utm_campaign);
• contacts (ID, source of creation, date of creation, email, domain, contact status in the system, name, language, date of last click, surname, date of last delivery, date of last send, date of last open, phone number, number of clicks, deliveries, sends and opens);
• orderItems (price, description, external product ID, link to the image, product name, order date, internal order ID, number of items in the order, link to the product page);
• orders (contact ID, delivery address, delivery method, promo code, email, external order ID, first name, surname, order date, internal order ID, payment method, phone number, order status, total cost).

The advantage of this method is that you don't need to manually create tables in BigQuery. They are created automatically during the first export and information is further updated. The table names will correspond to the names of the datasets that you specify during the setup (contactActivities, contacts, orderItems, orders).

Creating Personalized SMS

We chose SMS because the client’s base of contacts mainly consists of phone numbers: when the person places an order, they leave their number.

SMS processing

brabrabra had already had a contract with Omnicell. To continue sending SMS through the existing provider, we decided to integrate Omnicell with our platform.

Our technical specialists contacted Omnicell and requested data for integration:

• SMPP host;
• port for integration;
• login;
• password.

After receiving this data, we set up an SMPP connection and tested SMS sending.

Contact transfer to our platform

To send messages from our service, you first need to upload contacts to your account. There are two ways to do this:

• Upload manually with an import file.
• Send using the API Search contacts method.

We chose the second option to ensure complete automation. As a result, every day at 4 a.m. contacts from BigQuery were sent to the same segment. We also created a dynamic segment that included contacts who needed to receive SMS today.

We added this segment to the workflow that sends SMS.

Workflow creation

Using the time block, we limited the send time so that the SMS were always sent at 10 a.m.

The workflow also included the SMS block where we selected the previously created message on price notification.

In launch conditions, we set a special schedule that fit the import time (daily at 4 a.m.).

The workflow started one time a day from 8 a.m. to 9 a.m. The event was processed no more than once a day.

Result

1. Data is exported to BigQuery.

We didn’t contact other contractors due to the lack of functionality for transferring communication results to Google BigQuery. Many can receive data and create triggers based on data from BQ. But nobody can transfer data back to BQ. And for us it was important to understand the result of communication with each of the clients.

Andrey, digital marketer at brabrabra

2. The regular trigger with the bonus program notification generated 10% of the total share of online orders in a month.

The customer often prefers to visit a physical store, try on, touch the product. Our practice shows that online purchases make no more than 10% of the total number of purchases. Conversions of notifications on personalized prices to online orders make about 1%. But these are only online orders. We’re currently working on a tool for tracking offline orders generated by the specific communication. But I am sure that the conversion rate will be much higher when we finish the ROPO report.

Andrey, digital marketer at brabrabra

3. The trigger paid off in two weeks.

We had collected and segmented data in BQ. We needed to set data transfer to our platform, send this data back to BQ and create trigger processing workflows. our specialists did all the settings for the integration between BQ and our platform.

The cost of this integration was paid off in the first two weeks after the trigger on the activation of the bonus program, excluding repeat purchases in offline stores.

Andrey, digital marketer at brabrabra

Summary

If you collect a lot of data, use it to increase sales and client loyalty.

Interview customers and identify their needs. Store your data either in BigQuery or PostgreSQL, and we will help you set up and deliver communication in a channel convenient for your customers.

Get professional expertise