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

FieldDetails
activity_type_idThe type of activity. Can be sends, opens, clicks, bounces, spam_reports, unsubscribes, inbounds.
browserA string containing the browser.
categoriesA comma separated string containing tagged email categories.
cityA string containing the city. We ignore this field if it has been proxied.
countryA string containing the country. We ignore this field if it has been proxied.
created_atAn iso8601 formatted string containing the time of the event.
email_clientA string containing the email client.
errorA string containing the error (if any).
fromA string containing the header from.
hrefA string containing the link (if a click event).
ip_addressA string containing the IP address of the recipient.
latitudeA string containing the latitude that has been derived from the ip_address. We ignore this field if it has been proxied.
longitudeA string containing the longitude that has been derived from the ip_address. We ignore this field if it has been proxied.
message_idA string containing the message id from the email content.
outbound_ip_addressA string containing the IP address that sent the email.
recipientA string containing the email address of the recipient.
stateA string containing the city. We ignore this field if it has been proxied.
subjectA 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 SyntaxWhat it DoesExample
SELECT *Selects all; the * sign means all in SQLSELECT * FROM email_activities;
SELECT * FROM tableSelects everything from a specific tableSELECT * FROM recipients
SELECT column FROM table;Selects a specific column from a tableSELECT firstname FROM users;
SELECT column1, column2 FROM table;Selects 2 columns from the tableSELECT 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';


What’s Next