IMPORTANT NOTE: All Enterprise customers now have access to a 45-day free trial of our new product add-on, Amplitude SQL. The free trial will be available from October 9th, 2017 to November 22nd, 2017. Customers can query data that has been collected as of September 25, 2017. No data has been backfilled for this free trial.
Amplitude SQL allows you to write custom SQL queries against Amplitude data without the need for a managed Redshift cluster. Analyses can be saved, shared, and dashboarded just like all other chart content within Amplitude.
Table of Contents
Amplitude SQL is integrated into the native Amplitude experience and can be found in the "Create Chart" dropdown.
By default, a simple SQL query is shown returning the date, unique users, and total events performed in the past 30 days. SQL syntax will be highlighted to help you distinguish SQL commands from the rest of your query.
In addition, Amplitude SQL supports autocomplete of columns in the table. As you type, the query editor will recommend columns to help expedite your typing.
Because Amplitude SQL is built directly into the Amplitude chart experience, you can leverage much of the same familiar Amplitude user interface such as the datepicker chart saving experience. You can do this by utilizing Amplitude SQL's special fields for powerful shortcuts:
$date: When using this shortcut, the time range chosen by the datepicker is automatically applied and will update the query over time. It refers to the event time on the event and will respect the timezone the project has been set to. Otherwise, Amplitude SQL will return data in UTC.
$events: The shorthand used to refer to the table in your current project. When using this table, merged users are automatically handled.
$amplitude_id: The canonical Amplitude ID for the user. Use this field to automatically handle merged users.
Amplitude SQL uses a single table schema that can be referenced using the shorthand
$events for a particular project. If you want to access other tables, then you can use the full name which can be found by clicking "Show Schema".
$events table handles the merged user mappings automatically, the schema for the merged users table is also made available below the schema for
$events. This is helpful if you want to view the number of users that have been merged into one.
To execute your query, click "Compute" to the far right. When the query has finished executing, the results will be displayed below in a data table. The results of the data table and the CSV export are capped at 1,000 rows. In addition, a visualization of the data returned will be presented in a chart below the table. At this time, only a time series can be displayed.
To customize the visualization, there is a set of controls that you can use below the query editor. The options available in the visualization controls are the fields that you return in your SQL
SELECT statement. For example, in the query above, the 3 fields we can visualize are "DATE", "UNIQUES", and "TOTALS".
- X-axis column: Select what is plotted on the X-axis. Currently, only time series are supported.
- Metric column: Select which field is returned in your SQL
SELECTstatement is plotted on the Y-axis.
Sharing and Saving Queries
Like other Amplitude charts, you are able to export the results as a PNG, PDF, or CSV file using the "EXPORT" icon in the top right. Furthermore, you can save your analyses and share it with your team or even add the visualizations you create to a dashboard in Amplitude.