# Emberpoint Mailpublisher Import Integration

[Learn more about EmberPoint MailPublisher Export Integration](/int/emberpoint-mailpublisher-export-integration).

This connector enables you to import statistics from EmberPoint MailPublisher into Treasure Data.

You can use this same connector to send job results to MailPublisher. See EmberPoint MailPublisher Export Integration.

## Prerequisites

- Basic knowledge of Treasure Data, including the [Toolbelt](https://toolbelt.treasuredata.com/).
- A MailPublisher Smart Account with API access enabled (done via MailPublisher Smart Console).


## Limitations

- This connector doesn't support a custom endpoint. The connector supports the default endpoint only.


## MailPublisher Smart Account Information

Access to your API-enabled account is required before you can upload the email delivery list. The Site ID can be retrieved from the MailPublisher Smart console.

Steps to enable and create a new account for API access:

1. Select Account > Create (ja: アカウント > 新規作成) in left menu
2. Create an external system cooperation type User (ja: 利用種別：外部システム連携).
![](/assets/image-20191021-161236.1e135a85e586672b5dc0c8f6b67528d23dc00789c9968695a863bf9ece2c91d9.f209e744.png)


## Obtain a File ID

File IDs can be seen here: (リスト管理 > リスト管理 > チェック・確認)

![](/assets/image-20191021-141153.7887e8103f35037cbaa3875b6f298f9b4978f25079f9f67895a5422b1e71517d.f209e744.png)

## Obtain a Draft ID

1. To use "send_email" option, you need to get draft ID which is seen on here (メール配信・一覧 > 下書きメール)


![](/assets/image-20191021-161221.991c645aeb7126d791f31f4c2b9dc8f0bc7dbc307823e4940d10bd55c75ca044.f209e744.png)

## Use the TD Console to Create Your Connection

### Create a New Connection

When you configure a data connection, you provide authentication to access the integration. In Treasure Data, you configure the authentication and then specify the source information.

1. Open the **TD Console**.
2. Navigate to the **Integrations Hub > Catalog**.
3. Click the search icon on the far-right of the Catalog screen, and enter **Mailpublisher**.
4. Hover over the MailPublisher connector and select **Create Authentication**.
![](/assets/emberpointmailpublisher.e2e930d955b4e27c77cff4cff36def99290719661d5534252fd82eb71b2376e0.f209e744.png)
5. The following dialog opens.
![](/assets/image-20201009-215244.3c7c88619181cba170460d13d723e98deae640f8acff769392974332177b178f.f209e744.png)
6. Complete the new connection information, providing your MailPublisher Smart credential.
  - **MailPublisher Smart Site ID** (required): This is your MailPublisher Smart Site ID.
  - **MailPublisher Smart Login ID** (required): This is service specific Login ID.
  - **MailPublisher Smart password** (required): This is password for above Login ID.
  - **Retry limit** (optional, default `7`): Number of retries before the connector stops trying to connect and send data out
  - **Maximum intervals in milliseconds between retries** (optional, default to `120000`) : Maximum time in milliseconds between retrying attempts.
  - **Initial intervals in milliseconds between retries** (optional, default `15000`): Interval to retry if a recoverable error happens (in millisecond).
    - MailPublisher Smart announces minimal retry interval time should be 15 sec (15000 ms).
  - **Timeout** (optional, default to `100000`) : Timeout for each HTTP request in milliseconds.


### Name Your Connection

1. Type a name for your connection.
2. Select **Done.**


### Transfer Data to Treasure Data

After creating the authenticated connection, you are automatically taken to Authentications.

1. Search for the connection you created.
2. Select **New Source**.
3. Type a name for your **Source** in the Data Transfer field**.**


![](/assets/image-20201009-220746.2b187d64ea7da60af155495ce101918fbe956eeeddacc834e71577abace4dc0f.f209e744.png)
4. Select Next.

### Source Table

The duration value (= Start Date + Duration - 1 day) must not be today or future.

For example, if today is "2018-10-02" in Japan:

- "Start Date = 2018-10-01 and Duration = 1" => acceptable
- "Start Date = 2018-10-01 and Duration = 2" => not acceptable


1. Select **Next**.
The Source Table dialog opens.
2. Edit the following parameters:


| **Parameters** | **Description** |
|  --- | --- |
| **Import Data** | **Email List Statuses:**  Gets the status of distribution lists that were uploaded. Specify the **File ID** for each list.    **Article Statuses:** Get all status data for emails delivered emails within the specified duration. - Start Date: Imports data starting on this date. It must be specified as JST (Japan Standard Time). - Duration: How many days do you want to get the data for, starting from the start date.**Click Count Statuses:** This type gets statistics about the click counts of links in delivered emails.   - Start Date: Imports data starting on this date. It must be specified as JST (Japan Standard Time). - Duration: How many days do you want to get the data for, starting from the start date. |
| **Incremental Loading** | For example, when you specify "2018-10-01" as Start date and "2" as Duration, it loads data between "2018-10-01" and "2018-10-02" in the first loading. Then in the next attempt, it loads data between "2018-10-03" and "2018-10-04". |


### **Data Settings**

1. Select **Next**.
The Data Settings page opens.
2. Select **Next**.


### Data Preview

Data preview is optional, and you can safely click **Next** to go to the next page of the dialog if you would like.

1. Display a preview of your data before running the import by selecting **Generate Preview**.
The data shown in the data preview is approximated from your source. It is not the actual data that is imported.
2. Verify that the data looks approximately like you expect it to.
![](/assets/snippet-data-preview-2024-02-09.27dc5fd8772fca4f7f44ab28c00476ae1894744fe1e75d06932628929cc7bff1.4e139be3.png)
3. Select **Next**.


### Data Placement

For data placement, select the target database and table where you want your data placed and indicate how often the import should run.

1. Select **Next.** Under Storage, you will create a new or select an existing database and create a new or select an existing table for where you want to place the imported data.
2. Select a **Database** > **Select an existing** or **Create New Database**.
3. Optionally, type a database name.
4. Select a **Table**> **Select an existing** or **Create New Table**.
5. Optionally, type a table name.
6. Choose the method for importing the data.
  - **Append** (default)-Data import results are appended to the table.
If the table does not exist, it will be created.
  - **Always Replace**-Replaces the entire content of an existing table with the result output of the query. If the table does not exist, a new table is created.
  - **Replace on New Data**-Only replace the entire content of an existing table with the result output when there is new data.
7. Select the **Timestamp-based Partition Key** column.
If you want to set a different partition key seed than the default key, you can specify the long or timestamp column as the partitioning time. As a default time column, it uses upload_time with the add_time filter.
8. Select the **Timezone** for your data storage.
9. Under **Schedule**, you can choose when and how often you want to run this query.


#### Run once

1. Select **Off**.
2. Select **Scheduling Timezone**.
3. Select **Create & Run Now**.


#### Repeat Regularly

1. Select **On**.
2. Select the **Schedule**. The UI provides these four options: *@hourly*, *@daily* and *@monthly* or custom *cron*.
3. You can also select **Delay Transfer** and add a delay of execution time.
4. Select **Scheduling Timezone**.
5. Select **Create & Run Now**.


After your transfer has run, you can see the results of your transfer in **Data Workbench** > **Databases.**

## Result Schemas

Refer to the API "3-2 リスト状態レポート" in the MailPublisher Smart manual to map data types with external systems. (Alternatively, you can refer to ja: "外部システムとの連携利用マニュアル" to coordinate data types.) The Mail Publisher Connector tries to get as many items as possible by adding request parameters. Refer the API manual for more details.

| **Column name** | **Type** | **Description** |
|  --- | --- | --- |
| file_id | string | File ID (ja: ファイルID) |
| title | string | The Title field in a file (ja: タイトル) |
| filename | string | Name of the file (ja: ファイル名) |
| upload_date | string | Timestamp indicating when a file was uploaded (ja: アップロード日時) |
| status | string | Status of the file (ja: 状態) |
| count_ok | long | Number of valid addresses (ja: リスト件数) |
| count_err | long | Number of invalid addresses (ja: エラー件数) |
| count_dup | long | Number of duplicate addresses (ja: 重複件数) |
| carrier_docomo | long | Number of DoCoMo addresses (ja: キャリア別件数:DoCoMo) |
| carrier_au | long | Number of AU (Ezweb) addresses (ja: キャリア別件数:AU(Ezweb)) |
| carrier_au_noeomoji | long | Number of Ezweb addresses that don't support emoji (ja: キャリア別件数:Ezweb 絵文字非対応) |
| carrier_vodafone | long | Number of SoftBank non-3G addresses (ja: キャリア別件数:SoftBank 非3G) |
| carrier_vodafone_3g | long | Number of SoftBank 3G addresses (ja: キャリア別件数:SoftBank 3G) |
| carrier_mobile | long | Number of other carrier (PHS) addresses (ja: キャリア別件数:その他モバイル(PHS)) |
| carrier_pc | long | Number of PC addresses (ja: キャリア別件数:PC向け) |
| delivery | long | A number assigned to the delivery (ja: 配信回数) |
| start_date | string | Timestamp indicating when list checking started (ja: (リストチェック)開始日時) |
| end_date | string | Timestamp indicating when list checking ended (ja: (リストチェック)終了日時) |
| file_place_holder | boolean | Indicates whether the file is used as a placeholder (ja: ファイル差込(TRUE:差込ファイルあり/FALSE:なし)) |
| progress_conf | string | Progress confirmation. "LONG_EXECUTING" means that the list checking overran the threshold, otherwise the field is blank.  (ja: CSV ファイルのチェック処理の継続時間の閾値を超えている場合はLONG_EXECUTING、それ以外の場合は空文字列) |
| list_use_utf8 | string | Encoding scheme for the list. ("utf-8" if UTF-8, blank if Shift_JIS.)  (ja: リスト文字エンコーディング(utf-8:utf-8 の場合/ブランク:Shift_JIS の場合)) |
| carrier_softbank_i | long | Number of [i.softbank.jp](http://i.softbank.jp) addresses (ja: キャリア別件数: [i.softbank.jp](http://i.softbank.jp)) |
| carrier_docomo_s1 | long | Number of DoCoMo addresses that support loading images (ja: スマートフォン(画像読み込み型)件数:DoCoMo) |
| carrier_docomo_s2 | long | Number of DoCoMo addresses that support attachment of images (ja: スマートフォン(画像添付型)件数:DoCoMo) |
| carrier_au_s1 | long | Number of AU addresses that support loading images (ja: スマートフォン(画像読み込み型)件数:au) |
| carrier_au_s2 | long | Number of AU addresses that support attachment of images (ja: スマートフォン(画像添付型)件数:au) |
| carrier_softbank_s1 | long | Number of SoftBank addresses that support loading images (ja: スマートフォン(画像読み込み型)件数:SoftBank) |
| carrier_softbank_s2 | long | Number of SoftBank addresses that support attachment of images (ja: スマートフォン(画像添付型)件数:SoftBank) |
| carrier_softbank_3g_s1 | long | Number of SoftBank 3G addresses that support loading images (ja: スマートフォン(画像読み込み型)件数:SoftBank 3G) |
| carrier_softbank_3g_s2 | long | Number of SoftBank 3G addresses that support attachment of images (ja: スマートフォン(画像添付型)件数:SoftBank 3G) |
| carrier_pc_s3 | long | Number of smartphone addresses that is PC domain. (ja: PCドメインのスマートフォン端末件数) |
| insertion_clickcount | boolean | Indicates use of URL insertion (ja: 差込URL クリックカウント(TRUE:使用する/FALSE:使用しない)) |


Refer to the API "**3-11 記事状態レポート**" in the MailPublisher Smart manual to map data types with external systems. (Alternatively, you can refer to ja: "**外部システムとの連携利用マニュアル**" to coordinate data types.) The connector tries to get all items as well as the "**Email List Status**" type.

| **Column name** | **Type** | **Description** |
|  --- | --- | --- |
| article_id | string | Article ID (ja: 記事ID) |
| error | string | Error message (ja: エラー) |
| unique_name | string | Comment of the article (ja: 記事コメント) |
| status | string | Status of delivery (ja: 配信ステータス (RESERVED, RUNNING, FINISHED, ERR)) |
| start_date | string | Timestamp indicating when Delivery started (ja: 開始日時 (YYYY/MM/DD hh:mm)) |
| end_date | string | Timestamp indicating when Delivery ended (ja: 終了日時 (YYYY/MM/DD hh:mm)) |
| subject | string | Subject (ja: 件名) |
| count_all | long | Number of addresses (ja: 配信件数) |
| count_err_hard | long | Number of fatal errors (ja: 致命的なエラー件数) |
| count_err_soft | long | Number of non-fatal errors (ja: 致命的でないエラー件数) |
| success_rate | string | Success rate (ja: 配信成功率) |
| open_rate | double | Open rate (ja: 開封率) |
| carrier_type | string | Carrier type (ja: 配信対象キャリア) |
| mail_charset | string | Encoding schema (ja: 文字エンコーディング) |
| deal_mobile | boolean | Indicates whether [i.softbank.jp](http://i.softbank.jp) addresses as mobile are included (ja: [i.softbank.jp](http://i.softbank.jp) を携帯に含めるか否かの表示有無) |
| smart_phone_type_fp | boolean | Indicates whether feature phones are included (ja: フィーチャーフォンを配信対象に含めるか否か) |
| smart_phone_type_s1 | boolean | Indicates whether smart phones that support loading images are included (ja: 画像読み込み型スマートフォンを配信対象に含めるか否か) |
| smart_phone_type_s2 | boolean | Indicates whether smart phones that support attachment of images are included (ja: 画像添付型スマートフォンを配信対象に含めるか否か) |
| open_count | long | Number of opened addresses (ja: 開封件数) |
| csv_file_id | string | File ID specified for the article (ja: 配信時に指定した配信リストID) |
| csv_file_name | string | File name specified for the article (ja: 配信時に指定した配信リストのファイル名) |
| list_unsubscribe | string | URL or address of unsubscribed list (ja: 登録解除先URLまたはメールアドレス) |
| pc_smart_phone_type_pc | boolean | Indicates whether PC devices that have addresses for PC domain are included (ja: PCドメインのPC端末を配信対象に含めるか否か) |
| pc_smart_phone_type_s3 | boolean | Indicates whether smart phone devices that have addresses for PC domain are included (ja: PCドメインのスマートフォン端末を配信対象に含めるか否か) |
| insertion_clickcount | boolean | Indicates whether URL insertion is included (ja: 差込URLクリックカウント (TRUE:使用する/FALSE:使用しない)) |


Refer to the API "**3-21 クリックカウント詳細一覧**" in the MailPublisher Smart manual to map data types with external systems. (Alternatively, you can refer to ja: "**外部システムとの連携利用マニュアル**" to coordinate data types.) One record (row) in Treasure Data provides statistical data for each click count URL.

| **Column name** | **Type** | **Description** |
|  --- | --- | --- |
| article_id | string | Article ID (ja: 記事ID) |
| clickcount_id | string | Click count ID (ja: クリックカウントID) |
| unique_users | long | Unique users (ja: ユニーク人数) |
| total_accesses | long | Total accesses (ja: 延べアクセス回数) |
| url | string | Target URL (ja: リンク先URL) |