Learn more about 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.

  • A MailPublisher Smart Account with API access enabled (done via MailPublisher Smart Console).

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: 利用種別:外部システム連携).


Obtain a File ID

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

Obtain a Draft ID

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


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 TD Console.

  2. Navigate to Integrations Hub >  Catalog

  3. Search for and select MailPublisher.
    The following dialog opens.


  4. 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 a service-specific Login ID.

    • MailPublisher Smart password (required): This is the password for the 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 retry attempts.

    • Initial intervals in milliseconds between retries (optional, default 15000): Interval to retry if a recoverable error happens (in milliseconds).

      • 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.

Connection

  1. Type a name for your Source in the Data Transfer field.


  2. 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

You can see a preview of your data before running the import by selecting Generate Preview.

Data shown in the data preview is approximated from your source. It is not the actual data that is imported.

  1. Select Next.
    Data preview is optional and you can safely skip to the next page of the dialog if you want.

  2. To preview your data, select Generate Preview. Optionally, select Next

  3. Verify that the data looks approximately like you expect it to.


  4. 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 the query:

      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


Result Schema

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 addresses (ja: キャリア別件数: 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:使用しない))


Result Schema

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 addresses as mobile are included (ja: 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:使用しない))


Result Schema

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)




  • No labels