Integration with Google BigQuery

Practical use

BigQuery integration may be really useful in such situations:

  • you have CRM where you store a lot of contact data
  • you meet problems when creating multiple additional fields in eSputnik system or supporting continuous updates of contact data
  • you want your data to be stored in one place instead of several databases to get rid of constant need to store the same info in different places
  • you want to build custom reports

At the moment, the following BigQuery integration features are available:

  • exporting data from eSputnik system to the BigQuery table
  • exporting data from eSputnik system to the BigQuery table to form external groups

To configure the BigQuery integration you need:

  • Google Cloud Platform account (https://cloud.google.com/)
  • create a project key
  • create tables in Google BigQuery
  • configure the mutual BigQuery / eSputnik data transfer

 

Configuring BigQuery / eSputnik integration

Creating a Project Key

If you already have a project key and configured the BigQuery tables you can use this key to go directly to the following items:


What have you do to get the project key:

1. In the project, open Account data tab and click Add account data


2. In the list, select Service Account Key


3. Select the account you need

4. New service account


5. Specify account name and ID

6. In the Roles drop-down list, ...

7. ...choose Billing / BigQuery Admin

8. Then, browser will propose you to download the key

We will upload this key to eSputnik settings a bit later.

 


Exporting data from eSputnik system to the BigQuery table

It works as follows:

  • you store the certain contact database in eSputnik system
  • you configure database export to BigQuery
  • then, you choose what you want to export
  • once a day, system will transfer data to BigQuery.

Since you configured BigQuery / eSputnik integration, all the data is being transferred to BigQuery once a day (with regular update of tables).

Today, the following data sets are available to export:

  • contact activities
  • contacts
  • orderItems
  • orders

Each of these sets contains data that may be exported.

An important note! Using this method you don’t need to pre-create BigQuery tables. They are generated automatically at the first export and since that, all the incoming data will be updated. All the table names will correspond to the data sets (contact activities, contacts, order items, orders)

 

Configuring the export to BigQuery


Step 1. Data sets

Choose the fields you want to export


For a moment, not all types of data are allowed to export.


Step 2. Authentication

You have to download the key file. This is a .json file. It is generated when you create your Google BigQuery account. After file is downloaded, system will adjust the Project ID and Dataset ID list automatically.

Choose Dataset ID destination to download data and click Save.

Now, export configuration is completed.

 

Exporting data from eSputnik system to the BigQuery table to form external groups

It’s suitable when you need to select a segment of users and send them a newsletter.

I.e., you store your contacts database in BigQuery and transmit info there, for example, from CRM. You want to select a certain customers segment and send them a mailing. In this case you can form an external group in eSputnik and make a mailing campaign.

It works as follows:

  • you have a table in BigQuery where you store the contacts info that is transmitted there, for example, BigQuery / CRM transfer
  • you want to select a specific segment of customers and send them a newsletter
  • in eSputnik system, choose the fields you want to segment. For example, you want to add the campaign for those men who have bought laptops $500 and above during the last 6 months. In this case, you make segmentation on Gender, Product Category, Price, and Order Date table fields when configuring the new campaign.
  • eSputnik system will form an external group
  • editing the new segment you specify it’s conditions just like for a regular conditional group (gender - male, category of products - laptops, etc.)
  • now you can start mailing for this group


Configuring the use of external database


Step 1.  Authentication


You have to download the key file. This is a .json file. It is generated when you create your Google BigQuery account. After file is downloaded, system will adjust the Project ID and Dataset ID list automatically.


Step 2.  Fields

At this stage we choose:

  • name for a new source. Сome up with any source name
  • select a table (choose the table that contains Dataset selected at the previous step)


Then, a table that contain all the fields (columns) and a content preview (the first several values) will appear.

It’s obligatory to select the following items of the table:

  • field that determines the contact uniqueness. It can be either email or phone number.
  • fields to built the segment. For example, Gender, Age, City.

There is Contacts identification block with two drop-down lists below the table.

The first list unites all the fields selected in the table. Here you have to select the field with email address or contact phone numbers.

In the second drop-down list, you must specify exactly is this email or sms.


Step 3.

After clicking the Finish button, we are redirected to the main integration page. In the Configure external data use section, a table that contains the added source will appear.

There are 3 actions available: Form External Group, Edit, Delete

 

Creating the External Group

It allows you to work with contacts and data stored in some external source. For example, in Google BigQuery tables. You must have at least one external database source to create a group.

You can create an External Group in several ways:

1. On the Groups List page, select New Group / External


2. From the groups description page

 

3. On the Integration with External Sources page, in the "Configuring use of external data from BigQuery" section.

Each external data source you created includes an option to create an External Group.

 

Creation of group is done in several stages.

1. Selecting the data source

If group was created from the Integration with external sources page, the source is already selected.


If no external source was created you will have to add it first and only then continue creating the External Group.

 

Now the group will include only the contacts that are already exist in eSputnik database.

For example:
if there are 10 000 contacts in an external source but only 1,000 of these contacts are added to eSputnik database then our service will use only these 1,000 contacts. In order for the service to use all 10,000 contacts, they must be added to eSputnik database through the List group firstly. We are going to add the option to use all contacts from an external source a bit later, as well as feature allowing to save them in eSputnik database automatically and update the existing contacts database.


2. Group info

You must specify the group name here.


The purpose of creation is important to understand what is the current group for and how to use it.

Tags are used to quickly find a group. It's enough to add one tag to find this group among the list of thousands others. For example, if you need a group to select a particular segment for studying or testing some contacts parameters you can add the Experiment or Test tag. You can add an already existing tag from the list or come with a new one.

3. Conditions of contacts selection

The conditions for those fields you chose during the External Groups configuration will be adjusted to this point. Here you can already set the specific criteria for segment building.

By default, the service will use contacts from the External Group that are already stored in eSputnik database (see the note on the algorithm of using contacts from external sources at the first stage of group creating) if conditions are not set. Adding the conditions you will create a funnel that will allow you to select a specific segment from the whole group contacts.


4. Completed
A new External group will be added to the list of groups when completed.