Amplitude Export Integration

Copy

• Copy for LLM

  Copy page as Markdown for LLMs
• View as Markdown

  Open this page as Markdown
• Open in ChatGPT

  Get insights from ChatGPT
• Open in Claude

  Get insights from Claude
• Connect to Cursor

  Install MCP server on Cursor
• Connect to VS Code

  Install MCP server on VS Code

Learn more about Amplitude Import Integration.

Use queries to create Amplitude Events from the data you store in Treasure Data.

Prerequisites

• Basic knowledge of Treasure Data, including the TD Toolbelt.
• An Amplitude account that can grant permissions to Treasure Data.

Create an Amplitude Account

If you do not have an Amplitude account, go to https://amplitude.com and register. Then, create an organization and a project. When you create a project, you receive an API Key for the project.

Amplitude User Activity

In the Amplitude console, you can view User Activity to explore Amplitude events:

Usage

Go to the TD Console, and Queries and select New Query to access the query editor. You can pick the query language and write your query. Select Output results.

Create or Select an Existing Amplitude Connection

Creating a new connection:

Selecting an existing connection:

Optional: Complete Additional Configuration

After creating a new amplitude connection or selecting an existing one, you see the Additional configuration popup, for example:

Write a Quick Query to Populate Amplitude Events

In the following example, the Amplitude project has no events before the output of a query result:

In Treasure Data, run the following query with Amplitude as Result Export:

SELECT
  user_id,
  device_id,
  event_type,
  event_properties,
  country,
  time
FROM (
  VALUES(
    'a@gmail.com',
    'f925065b-4d56-486f-a2be-e97952e62925',
    'game_open',
    '{"load_time":0.0619166532,"dates":["sunday","monday"],"source":"notification"}',
    'United States',
    1515083919310
  ),
  (
    'b@gmail.com',
    'b36c8c80-71a0-4de4-8bcb-c801f5c1c6c3',
    'select_avatar',
    '{"load_time":0.0517593568,"dates":["friday","sunday"],"source":"notification"}]',
    'United States',
    1515083943092
  ),
  (
    'c@gmail.com',
    'e44a1024-c4bb-4eef-a02a-f2a7b3bf68b3',
    'watch_tutorial',
    '{"load_time":0.0689998562,"dates":["sunday","monday"],"source":"notification"}',
    'United States',
    1515083970894
  ),
  (
    'd@gmail.com',
    '8274fee1-7222-4e41-83ed-fb5521d5f4b8',
    'game_start',
    '{"load_time":0.0976384392,"dates":["friday","monday"],"source":"notification"}',
    'United States',
    1515084000948
  ),
  (
    'e@gmail.com',
    '29ba193d-0955-4084-9640-bb4d8ba9a23f',
    'ad_clicked',
    '{"load_time":0.0708434955,"dates":["monday","saturday"],"source":"notification"}',
    'United States',
    1515084024759
  )
  ) tbl(
    user_id,
    device_id,
    event_type,
    event_properties,
    country,
    time
  )

The preceding query requires no source table, which makes testing easy, but you still must choose a database. Pick "sample_datasets" or any other arbitrary table. Also, make sure that Presto is chosen as the SQL dialect.

The query completes in a few seconds. Log into your Amplitude account and check User Activity. It should look similar to this:

Combine Multiple Values into a Single JSON Column Value

If you have multiple values, you can combine them into a single JSON column value as follows:

SELECT CAST(MAP(ARRAY['source', 'status', 'action'], ARRAY['notification', 'pending', 'add']) AS JSON) AS event_properties

You create a simple JSON object for event_properties: '{"action":"add","source":"notification","status":"pending"}'.

You can create a more complex column, as follows:

SELECT CAST(MAP(ARRAY['load_time', 'source', 'dates'], ARRAY[CAST (0.8371 AS JSON), CAST ('notification' AS JSON), CAST(ARRAY['monday', 'tuesday'] AS JSON)]) AS JSON) AS event_properties

You create the following JSON object as the event_properties value: '{"dates":["monday","tuesday"],"load_time":0.8371,"source":"notification"}'.

Write a Query to a TD Table to Populate Amplitude Events

An example of a TD database table that can be used to create records for Amplitude is as follows:

In the Treasure Data console, go to query editor and enter a query, for example:

SELECT user_id, device_id, event_type, event_properties, country, event_time AS time FROM events

Select Output results to send the data to Amplitude. Next, you select an existing connector, or create a new connection using your api_key.

When the connector has been selected or created, in the query editor, select Run to begin the data transfer.

Event Deduplication

It is highly recommended that an insert_id be sent with each event to prevent duplicate events from being saved by Amplitude. The insert_id is a unique identifier an event. Amplitude drops events that have the same insert_id if the IDs are sent within 7 days of each other.

Disable Auto-Generation of Insert IDs

If you do not select to disable auto-generation of insert IDs, then TD automatically generates a SHA256 insert_id for each record.

If you select to disable auto-generation of insert IDs, then insert_id’s must be provided by the user.

Amplitude Columns

The following table lists event columns and data types supported by Amplitude.

Event Standard Columns

View footnotes for 1,2,3,4,5

Event Optional Columns

Identify Schema and data type

User Properties Fields (Identify Target Only)

Or any field that not in Identify Schema list column is the user_properties columns.

(Optional) Schedule Query Export Jobs

You can use Scheduled Jobs with Result Export to periodically write the output result to a target destination that you specify.

Treasure Data's scheduler feature supports periodic query execution to achieve high availability.

When two specifications provide conflicting schedule specifications, the specification requesting to execute more often is followed while the other schedule specification is ignored.

For example, if the cron schedule is '0 0 1 * 1', then the 'day of month' specification and 'day of week' are discordant because the former specification requires it to run every first day of each month at midnight (00:00), while the latter specification requires it to run every Monday at midnight (00:00). The latter specification is followed.

Scheduling your Job Using TD Console

1. Navigate to Data Workbench > Queries

2. Create a new query or select an existing query.

3. Next to Schedule, select None.

4. In the drop-down, select one of the following schedule options:

   Drop-down Value	Description
   Custom cron...	Review Custom cron... details.
   @daily (midnight)	Run once a day at midnight (00:00 am) in the specified time zone.
   @hourly (:00)	Run every hour at 00 minutes.
   None	No schedule.

Custom cron... Details

 *    *    *    *    *
 -    -    -    -    -
 |    |    |    |    |
 |    |    |    |    +----- day of week (0 - 6) (Sunday=0)
 |    |    |    +---------- month (1 - 12)
 |    |    +--------------- day of month (1 - 31)
 |    +-------------------- hour (0 - 23)
 +------------------------- min (0 - 59)

The following named entries can be used:

• Day of Week: sun, mon, tue, wed, thu, fri, sat.
• Month: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec.

A single space is required between each field. The values for each field can be composed of:

1. (Optional) You can delay the start time of a query by enabling the Delay execution.

(Optional) Export Integration Using the CLI

You can also use CLI (Toolbelt) to export results to Shopify by specifying the information you want to export to your Shopify server using the --result option of the td query command. For more information about the td query command, refer to TD Toolbelt: Query Commands.

Options for the yml configuration files used for TD Toolbelt are formatted in JSON, and the general structure is as follows.

• For Event Target

out:
  type: amplitude
  api_key: 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
  target: 'event'
  disable_auto_generation_insert_ids: false
  region: 'US'
  events_per_batch: 10

• For Identify Target

out:
  type: amplitude
  target: identify
  api_key: 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
  region: EU
  operation: set
  skip_invalid_records: false

Parameters

CLI Usage Example

Upload Event

td query \
--result '{"type":"amplitude","api_key":"api_key","target":"event","region":"US","disable_auto_generation_insert_ids":"false","events_per_batch":10}'  \
-d sample_database \
"select user_id,device_id,event_type,value from tbl_shop" \
-T presto

Upload Identify

td query --result \
'{"type":"amplitude","api_key":"api_key","target":"identify","region":"US","operation":"add","skip_invalid_records":true}'\
-d sample_database \
"select user_id,device_id,user_properties,value from tbl_shop" \
-T presto

See Also

• The Result Export can be scheduled to periodically upload data to a target destination. See Scheduling Jobs Using TD Console.
• All import and export integrations can be added to a TD Workflow. The td>: data operator can export a query result to a specified integration. For more information, see Reference for Treasure Workflow Operators.