SQL
Query and analyze your data with SQL.
Amply gives you advanced search capabilities with full SQL access. You can get info from your data and answer any questions you have at high efficiency. SQL is a very powerful tool for asking specific questions over large data sets.
Any questions you have about specific delivery or engagement events can be answered directly with a SQL query. You can write queries to generate customized reports without needing to connect to any outside tools.
Amply is the only transactional email service provider that supports data warehousing for email analytics. With SQL access, you can query your data as far back as 2 years.
Write a SQL Query
To write a SQL query, go to the SQL tab under Analytics. Start typing your query into the text box and then click "Run Query". Your results will show up below.
All of your query results can be exported as reports in CSV format.
Types of Data
Amply collects data around email activity. This data is easily queryable via SQL or from our API.
email_activities
| Field | Details |
|---|---|
| activity_type_id | The type of activity. Can be sends, opens, clicks, bounces, spam_reports, unsubscribes, inbounds. |
| browser | A string containing the browser. |
| categories | A comma separated string containing tagged email categories. |
| city | A string containing the city. We ignore this field if it has been proxied. |
| country | A string containing the country. We ignore this field if it has been proxied. |
| created_at | An iso8601 formatted string containing the time of the event. |
| email_client | A string containing the email client. |
| error | A string containing the error (if any). |
| from | A string containing the header from. |
| href | A string containing the link (if a click event). |
| ip_address | A string containing the IP address of the recipient. |
| latitude | A string containing the latitude that has been derived from the ip_address. We ignore this field if it has been proxied. |
| longitude | A string containing the longitude that has been derived from the ip_address. We ignore this field if it has been proxied. |
| message_id | A string containing the message id from the email content. |
| outbound_ip_address | A string containing the IP address that sent the email. |
| recipient | A string containing the email address of the recipient. |
| state | A string containing the city. We ignore this field if it has been proxied. |
| subject | A string containing the subject from the email content. |
Basic SQL Syntax
Use the word SELECT to select what you want from the database.
SQL SELECT Statement
| SQL Syntax | What it Does | Example |
|---|---|---|
| SELECT * | Selects all; the * sign means all in SQL | SELECT * FROM email_activities; |
| SELECT * FROM table | Selects everything from a specific table | SELECT * FROM recipients |
| SELECT column FROM table; | Selects a specific column from a table | SELECT firstname FROM users; |
| SELECT column1, column2 FROM table; | Selects 2 columns from the table | SELECT firstname, age FROM users; |
Example - "I want to select all opens from the email activities table"
SELECT opens
FROM email_activities;
Example - "I want to select bounces and spam reports from the email activities table"
SELECT bounces, spam_reports
FROM email_activities;
Commas are used to separate 2 different columns you want to select from the table.
Conditionals
Apply conditions to filter your results:
Example - "I want to see how many clicks come from Los Angeles"
SELECT clicks
FROM email_activities;
WHERE city = 'Los Angeles';
You can also add another conditional to filter your results further:
Example - "I want to see how many clicks from a specific browser come from Los Angeles"
SELECT clicks
FROM email_activities;
WHERE city = 'Los Angeles'
AND Browser = 'Chrome 62';
Updated 8 months ago