Lookup Table API 2

Lookup tables let you augment user and event properties. Instead of using formulas, you can upload a CSV file that contains property mappings to derive new properties.

To create a lookup property, create a lookup table to reference. You can retrieve and update each of the tables using the API. Lookup Tables are identified by the name and are scoped per project.

Authentication

This API uses basic authentication, using the API key and secret key for your project. Pass base64-encoded credentials in the request header like {api-key}:{secret-key}. api-key replaces username, and secret-key replaces the password.

Your authorization header should look something like this:

--header 'Authorization: Basic YWhhbWwsdG9uQGFwaWdlZS5jb206bClwYXNzdzByZAo'`

For more information, see Find your API Credentials

Endpoints

Region Endpoint
Standard server https://data-api.amplitude.com/api/3/lookup_table
EU residency server https://data-api.eu.amplitude.com/api/3/lookup_table

Considerations

The CSV file must follow these requirements:

  • The max file size is 100 MB and the file can't have more than 1,000,000 rows.
  • The first row must contain column names/headers.
  • The first column must correspond to the mapping property value and must contain unique values. Lookup Tables search for exact matches, and are case-sensitive.
  • Separate columns with commas.
  • Separate rows with line breaks.
  • If a field value contains commas or quotes, wrap it in double quotation marks. The first double quote signifies the beginning of the column data, and the last double quote marks the end. If the value contains a string with double quotes, these Amplitude replaces them with two double quotes "".

Create a Lookup Table

Create a Lookup Table object by uploading a CSV that maps an existing property to the new properties to create. Send the request with the type multipart/form-data type.

Parameters

Name
Type Description
name String Required. Name of the table.
file File Required. A CSV representation of the mappings.
key String Required. Column in CSV to use as key of lookup table.
property JSON Required. Property in Amplitude to map to the key column in CSV.
property.value String Required. Name of property in Amplitude.
property.type String Required. Type of property in Amplitude.
property.groupType String Required only if property is a group property.

Example request

1curl -L -X POST 'https://data-api.amplitude.com/api/3/lookup_table' \
2 -u API_KEY:SECRET_KEY \
3 -F 'file=@"/path/to/file.csv"' \
4 -F 'name=":name"' \
5 -F 'key=":key"' \
6 -F 'property="{\"value\": \":propertyName\", \"type\": \":propertyType\", \"groupType\": \":propertyGroupType\"}";type=application/json'

1POST '/api/3/lookup_table' HTTP/1.1
2Host: data-api.amplitude.com
3Authorization: Basic {api-key}:{secret-key}
4Content-Type: multipart/form-data; boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW
5
6------WebKitFormBoundary7MA4YWxkTrZu0gW
7Content-Disposition: form-data; name="key"
8
9:key
10------WebKitFormBoundary7MA4YWxkTrZu0gW
11Content-Disposition: form-data; name="name"
12
13:name
14----WebKitFormBoundary7MA4YWxkTrZu0gW
15Content-Disposition: form-data; name=":name"; filename="file.csv"
16Content-Type: text/csv
17
18(data)
19----WebKitFormBoundary7MA4YWxkTrZu0gW
20Content-Disposition: form-data; name="property"
21Content-Type: application/json
22
23{"value": ":propertyName", "type": ":propertyType", "groupType": ":propertyGroupType"}
24------WebKitFormBoundary7MA4YWxkTrZu0gW--

Response

1{
2"appId": "<projectId>",
3"name": "example-lookup",
4"columnHeaders": [
5 "Language"
6],
7"createdAt": 1715912516,
8"createdBy": "api",
9"lastModifiedAt": 1715912516,
10"lastModifiedBy": "api",
11"isDeleted": false,
12"isConfigured": true,
13"keyColumnHeader": "SKU",
14"keyProperty": {
15 "type": "event",
16 "value": "example",
17 "groupType": "User"
18},
19"fileName": "lookup-table-example.csv",
20"rowCount": 3,
21"sizeBytes": 0,
22}

Retrieve a Lookup Table

Retrieve a Lookup Table by its name.

Parameters

Name
Type Description
name String Required. Name of the table.

Example request

1curl -L -X GET 'https://data-api.amplitude.com/api/3/lookup_table/:name' \
2 -u API_KEY:SECRET_KEY

1GET /api/3/lookup_table/:name HTTP/1.1
2Host: data-api.amplitude.com
3Authorization: Basic {api-key}:{secret-key}

Response

1{
2 "appId": "<projectId>",
3 "name": "example-lookup",
4 "columnHeaders": [
5 "Language"
6 ],
7 "createdAt": 1715912516,
8 "createdBy": "api",
9 "lastModifiedAt": 1715912516,
10 "lastModifiedBy": "api",
11 "isDeleted": false,
12 "isConfigured": true,
13 "keyColumnHeader": "SKU",
14 "keyProperty": {
15 "type": "event",
16 "value": "example",
17 "groupType": "User"
18 },
19 "fileName": "lookup-table-example.csv",
20 "rowCount": 3,
21 "sizeBytes": 5,
22}

Download a CSV

Download the lookup table object as a CSV. Any incremental changes are applied in the downloaded file.

Parameters

Name
Type Description
name String Required. Name of the table.

Example request

1curl -L -X GET 'https://data-api.amplitude.com/api/3/lookup_table/:name/csv' \
2 -u API_KEY:SECRET_KEY

1GET /api/3/lookup_table/:name/csv HTTP/1.1
2Host: data-api.amplitude.com
3Authorization: Basic {api-key}:{secret-key}

Override a Lookup Table

Override a Lookup Table object by uploading a CSV that replaces the CSV already uploaded to Amplitude. Send the request with the type multipart/form-data type.

Parameters

Name
Type Description
name String Required. Name of the table.
file File A CSV representation of the mappings.
property JSON Property in Amplitude to map to the key column in CSV.
property.value String Name of property in Amplitude.
property.type String Type of property in Amplitude.
property.groupType String Required only if property is a group property.

Example request

1curl -L -X PUT 'https://data-api.amplitude.com/api/3/lookup_table/:name' \
2 -u API_KEY:SECRET_KEY \
3 -F 'file=@"/path/to/file.csv"' \
4 -F 'property="{\"value\": \":propertyName\", \"type\": \":propertyType\", \"groupType\": \":propertyGroupType\"}";type=application/json'

1PUT '/api/3/lookup_table/:name' HTTP/1.1
2Host: data-api.amplitude.com
3Authorization: Basic {api-key}:{secret-key}
4Content-Type: multipart/form-data; boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW
5 
6----WebKitFormBoundary7MA4YWxkTrZu0gW
7Content-Disposition: form-data; name=":name"; filename="file.csv"
8Content-Type: text/csv
9 
10(data)
11----WebKitFormBoundary7MA4YWxkTrZu0gW
12Content-Disposition: form-data; name="property"
13Content-Type: application/json
14 
15"value": ":propertyName", "type": ":propertyType", "groupType": ":propertyGroupType"
16------WebKitFormBoundary7MA4YWxkTrZu0gW--

Response

1{
2 "appId": "<projectId>",
3 "name": "example-lookup",
4 "columnHeaders": [
5 "Language"
6 ],
7 "createdAt": 1715912516,
8 "createdBy": "api",
9 "lastModifiedAt": 1715912516,
10 "lastModifiedBy": "api",
11 "isDeleted": false,
12 "isConfigured": true,
13 "keyColumnHeader": "SKU",
14 "keyProperty": {
15 "type": "event",
16 "value": "example",
17 "groupType": "User"
18 },
19 "fileName": "lookup-table-example.csv",
20 "rowCount": 3,
21 "sizeBytes": 0,
22}

Update a Lookup Table

Update a Lookup Table's columns and data. If you provide a CSV file, the file is merged with the existing CSV within Amplitude. This allows for incremental updates of the CSV, instead of a complete replacement.

Parameters

Name
Type Description
name String Required. Name of the table.
file File A CSV representation of the mappings.
property JSON Property in Amplitude to map to the key column in CSV.
property.value String Name of property in Amplitude.
property.type String Type of property in Amplitude.
property.groupType String Required only if property is a group property.

Example request

1curl -L -X PATCH 'https://data-api.amplitude.com/api/3/lookup_table/:name' \
2 -u API_KEY:SECRET_KEY
3 -F 'file=@"/path/to/file.csv"' \
4 -F 'property="{\"value\": \":propertyName\", \"type\": \":propertyType\", \"groupType\": \":propertyGroupType\"}";type=application/json'

1PATCH '/api/3/lookup_table/:name' HTTP/1.1
2Host: data-api.amplitude.com
3Authorization: Basic {api-key}:{secret-key}
4Content-Type: multipart/form-data; boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW
5 
6----WebKitFormBoundary7MA4YWxkTrZu0gW
7Content-Disposition: form-data; name=":name"; filename="file.csv"
8Content-Type: text/csv
9 
10(data)
11----WebKitFormBoundary7MA4YWxkTrZu0gW
12Content-Disposition: form-data; name="property"
13Content-Type: application/json
14 
15"value": ":propertyName", "type": ":propertyType", "groupType": ":propertyGroupType"
16------WebKitFormBoundary7MA4YWxkTrZu0gW--

Response

1{
2 "appId": "<projectId>",
3 "name": "example-lookup",
4 "columnHeaders": [
5 "Language"
6 ],
7 "createdAt": 1715912516,
8 "createdBy": "api",
9 "lastModifiedAt": 1715912516,
10 "lastModifiedBy": "api",
11 "isDeleted": false,
12 "isConfigured": true,
13 "keyColumnHeader": "SKU",
14 "keyProperty": {
15 "type": "event",
16 "value": "example",
17 "groupType": "User"
18 },
19 "fileName": "lookup-table-example.csv",
20 "rowCount": 3,
21 "sizeBytes": 0,
22}

Delete a Lookup Table

Delete a Lookup Table.

Parameters

Name
Type Description
name String Required. Name of the table.

Example request

1curl -L -X DELETE 'https://data-api.amplitude.com/api/3/lookup_table/:name' \
2 -u API_KEY:SECRET_KEY

1DELETE /api/3/lookup_table/:name HTTP/1.1
2Host: data-api.amplitude.com
3Authorization: Basic {api-key}:{secret-key}

Response

1{
2 "message": "Lookup table <:name> deleted successfully",
3 "success": true
4}

List all Lookup Tables

List all the Lookup Tables for the project.

Example request

1curl -L -X GET 'https://data-api.amplitude.com/api/3/lookup_table' \
2 -u API_KEY:SECRET_KEY

1GET /api/3/lookup_table HTTP/1.1
2Host: data-api.amplitude.com
3Authorization: Basic {api-key}:{secret:key}

Response

1[
2 {
3 "appId": "<projectId>",
4 "name": "example-lookup",
5 "columnHeaders": [
6 "Language"
7 ],
8 "createdAt": 1715912516,
9 "createdBy": "api",
10 "lastModifiedAt": 1715912516,
11 "lastModifiedBy": "api",
12 "isDeleted": false,
13 "isConfigured": true,
14 "keyColumnHeader": "SKU",
15 "keyProperty": {
16 "type": "event",
17 "value": "example",
18 "groupType": "User"
19 },
20 "fileName": "lookup-table-example.csv",
21 "rowCount": 3,
22 "sizeBytes": 5,
23 },
24 {
25 "appId": "<projectId>",
26 "name": "example-lookup-2",
27 "columnHeaders": [
28 "Language"
29 ],
30 "createdAt": 1715912516,
31 "createdBy": "api",
32 "lastModifiedAt": 1715912516,
33 "lastModifiedBy": "api",
34 "isDeleted": false,
35 "isConfigured": true,
36 "keyColumnHeader": "SKU",
37 "keyProperty": {
38 "type": "event",
39 "value": "example",
40 "groupType": "User"
41 },
42 "fileName": "lookup-table-example.csv",
43 "rowCount": 50,
44 "sizeBytes": 10,
45 }
46]

Error Codes

All the above lookup table APIs share common error codes as described below.

Structure

Name
Description
statusCode Http status code of error. 400, 409, 413
message Human readable message describing the error
errorCode Static error code string
extraParams Each error might have extra parameters in the response to help better point to the exact reason for the error

Types

Code
Description
LOOKUP_TABLE_INVALID_FILE_COUNT Attempted to upload more than 1 file for a single lookup table.
LOOKUP_TABLE_INVALID_FILE_SIZE Created/Edited a lookup table using a file bigger than 100mb.
LOOKUP_TABLE_INVALID_FILE_TYPE Created/Edited a lookup table using a file that wasn't a CSV.
LOOKUP_TABLE_INVALID_KEY_COLUMN Created a lookup table with a "key" input not present in the headers of the table.
LOOKUP_TABLE_INVALID_VALUE_COLUMN Cell in uploaded file exceeds 1,024 characters.
LOOKUP_TABLE_INVALID_KEY_PROPERTY Provided key property doesn't exist in Amplitude.
LOOKUP_TABLE_INVALID_NUMBER_OF_ROWS Created/Edited a lookup table using a file with more than 1mil rows.
LOOKUP_TABLE_KEY_COLUMN_DUPLICATE_VALUES Specified key column has duplicate values.
LOOKUP_TABLE_INVALID_TABLE_NAME Provided name is invalid.
LOOKUP_TABLE_MALFORMED_CSV Provided CSV not processed correctly. See error message for more details.
LOOKUP_TABLE_INVALID_INPUT Input for field doesn't match expectation. See error message for more details.
LOOKUP_TABLE_ALREADY_EXISTS Created a table that already exists in the provided project.
LOOKUP_TABLE_DOES_NOT_EXIST Attempted to load or edit table that doesn't exist.
LOOKUP_TABLE_INVALID_COLUMN_HEADERS Column headers in file not processed correctly.
Was this page helpful?

Thanks for your feedback!

June 17th, 2024

Need help? Contact Support

Visit Amplitude.com

Have a look at the Amplitude Blog

Learn more at Amplitude Academy

© 2024 Amplitude, Inc. All rights reserved. Amplitude is a registered trademark of Amplitude, Inc.