Lookup Table API

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://amplitude.com/api/2/lookup_table
EU residency server https://analytics.eu.amplitude.com/api/2/lookup_table

This version of the Lookup Tables API is deprecated

This API is no longer supported. Use the new API.

Considerations

The CSV file must meet the following 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.
  • Columns must be separated by commas.
  • Rows must be separated by line breaks.
  • If a field value contains commas or quotes, it should be wrapped within 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 are replaced by 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
Description
name Required. String. Name of the table.
file Required. File. A CSV representation of the mappings.

Request

1curl -L -X POST 'https://amplitude.com/api/2/lookup_table/:name' \
2 -u API_KEY:SECRET_KEY \
3 -F 'file=@"/path/to/file.csv"' \

1POST '/api/2/lookup_table/:name' HTTP/1.1
2Host: api2.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

Response

1{
2 "name": "skuToMetadata",
3 "column_headers": [
4 "Product Category",
5 "Product Name"
6 ],
7 "created_at": "2021-07-15T21:04:23.000593",
8 "created_by": "rest",
9 "last_modified_at": "2021-07-16T19:14:11.627477",
10 "last_modified_by": "rest"
11}

1HTTP 400: Bad Request
  • Invalid file
  • File type is invalid. Accepted file types are text/csv, text/plain, and text/tab-separated-values.
  • File is empty
  • Found duplicate column header. There's a duplicate column, remove the column so the file can be processed.
1HTTP 409: Conflict (Conflict, name already exists)

The table already exists

1HTTP 413: Payload Too Large

The file exceeds the max size.

Retrieve a Lookup Table

Retrieve a Lookup Table by its name.

Parameters

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

Request

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

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

Response

1{
2 "name": "skuToMetadata",
3 "column_headers": [
4 "Product Category",
5 "Product Name"
6 ],
7 "created_at": "2021-07-15T21:04:23.000593",
8 "created_by": "rest",
9 "last_modified_at": "2021-07-16T19:14:11.627477",
10 "last_modified_by": "rest"
11}

1HTTP 404: Not found

The table wasn't found because it wasn't created

Update a Lookup Table

Update a Lookup Table's columns and data.

Parameters

Name
Description
name Required. String. Name of the table.
file Required. File. A CSV representation of the mappings.

Request

1curl -L -X PATCH 'https://amplitude.com/api/2/lookup_table/:name' \
2 -u API_KEY:SECRET_KEY
3 -F 'file=@"/path/to/file.csv"' \

1PATCH /api/2/lookup_table/:name HTTP/1.1
2Host: 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

Response

1{
2 "name": "skuToMetadata",
3 "column_headers": [
4 "Product Category",
5 "Product Name"
6 ],
7 "created_at": "2021-07-15T21:04:23.000593",
8 "created_by": "rest",
9 "last_modified_at": "2021-07-16T19:14:11.627477",
10 "last_modified_by": "rest"
11}

1HTTP 400: Bad Request
  • Requires at least one modification. There should be a file attached.
  • File type is invalid. Accepted file types are text/csv, text/plain, and text/tab-separated-values.
  • File is empty.
  • Found duplicate column header. There's a duplicate column, please remove the column so the file can be processed.
1HTTP 404: Not found

The table wasn't found because it wasn't created

1 
2HTTP 413: Payload Too Large

The file exceeds the max size.

Delete a Lookup Table

Delete a Lookup Table.

Parameters

Name
Description
name Required. String. Name of the table.
force Optional. Boolean. Delete the associated properties. Defaults to false.

Request

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

1DELETE /api/2/lookup_table/:lookup_table_name?force=True HTTP/1.1
2Host: amplitude.com
3Authorization: Basic {api-key}:{secret-key}

Response

1{
2 "name": "skuToMetadata",
3 "success": true
4}

1 
2HTTP 404: Not found

The table wasn't found.

List all lookup tables

List all the Lookup Tables for the project.

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

1GET /api/2/lookup_table HTTP/1.1
2Host: amplitude.com
3Authorization: Basic :

Response

1{
2 "data": [
3 {
4 "name": "isbnToMetadata",
5 "column_headers": [
6 "Genres",
7 "Authors"
8 ],
9 "created_at": "2021-07-15T21:04:23.000593",
10 "created_by": "rest",
11 "last_modified_at": "2021-07-16T19:14:11.627477",
12 "last_modified_by": "rest"
13 },
14 {
15 "name": "skuToMetadata",
16 "column_headers": [
17 "Product Category",
18 "Product Name"
19 ],
20 "created_at": "2021-07-16T19:28:18.070073",
21 "created_by": "rest",
22 "last_modified_at": "2021-07-16T19:28:18.070073",
23 "last_modified_by": "rest"
24 }
25 ]
26}
Was this page helpful?

Thanks for your feedback!

May 21st, 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.