This article helps you:
Use field shortcuts to query your Snowflake database in Amplitude SQL
Understand the schema of the $events table
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 using 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. This must be used in conjunction with $events.$events: The shorthand used to refer to the table in your current project. When using this table, merged users are automatically handled. Note: This must be used in conjunction with $date.$amplitude_id: The original Amplitude ID for the user. Use this field to automatically handle merged users.Amplitude SQL uses a one-table schema. The table, $events, handles the merged user mappings automatically. But, the merged users table will also be available to view. This will allow you to quickly see the number of users that have been merged into one.
The following tables show the schema of $events and merged users.
| Column | Data type | Description |
|---|---|---|
$amplitude_id |
NUMBER(38,0) | The original Amplitude ID for the user. Use this field to automatically handle merged users. |
adid |
VARCHAR(16777216) | (Android) Google Play Services advertising ID (AdID). This usually is wiped after ingestion and therefore will be blank. |
amplitude_attribution_ids |
Anonymized hash of the advertising IDs that we store for internal purposes; not useful for the customer by any means. But this will appear if advertising IDs were sent which proves that adid/idfv existed even though currently wiped. | |
amplitude_event_type |
VARCHAR(16777216) | Amplitude specific identifiers based on events Amplitude generates. This is a legacy field so event_type should suffice for all queries. |
amplitude_id |
NUMBER(38,0) | An internal ID used to count unique users. |
app |
NUMBER(38,0) | Project ID found in your project's Settings page. |
city |
VARCHAR | City. |
client_event_time |
TIMESTAMP | Local timestamp (UTC) of when the device logged the event. |
client_upload_time |
TIMESTAMP | The local timestamp (UTC) of when the device uploaded the event. |
country |
VARCHAR | Country. |
data |
VARIANT | Dictionary where certain fields such as first_event and merged_amplitude_id are stored. |
device_brand |
VARCHAR(16777216) | Device brand. |
device_carrier |
VARCHAR(16777216) | Device carrier. |
device_family |
VARCHAR(16777216) | Device family. |
device_id |
VARCHAR(16777216) | The device specific identifier. |
device_manufacturer |
VARCHAR(16777216) | Device manufacturer. |
device_model |
VARCHAR(16777216) | The device model. |
device_type |
VARCHAR(16777216) | Device type. |
dma |
VARCHAR(16777216) | Designated marketing area (DMA). |
event_id |
NUMBER(38,0) | A counter that distinguishes events. |
event_time |
TIMESTAMP | Amplitude timestamp (UTC) which is the client_event_time adjusted by the difference between server_received_time and client_upload_time, specifically: event_time = client_event_time + (server_received_time - client_upload_time) We use this timestamp to organize events on Amplitude charts. If the difference between server_received_time and client_upload_time is less than 60 seconds, the event_time will not be adjusted and will equal the client_event_time. |
event_type |
VARCHAR(16777216) | The assigned type of event. |
followed_an_identify |
BOOLEAN | True if there was an identify event between this current SDK event and the last SDK event seen. |
groups |
VARIANT | Group types. See theAccounts documentation for more information. |
idfa |
VARCHAR(16777216) | (iOS) Identifier for Advertiser. This usually is wiped after ingestion and therefore will be blank. |
ip_address |
VARCHAR(16777216) | IP address. |
location_lat |
FLOAT | Latitude. |
location_lng |
FLOAT | Longitude. |
os_name |
VARCHAR(16777216) | OS name. |
os_version |
VARCHAR(16777216) | OS version. |
paying |
VARCHAR | True if the user has ever logged any revenue, otherwise '(none)'. The property value can be modified via the Identify API. |
region |
VARCHAR | Region. |
server_upload_time |
TIMESTAMP | Amplitude timestamp (UTC) of when our servers received the event. |
session_id |
NUMBER(38,0) | The session start time in milliseconds since epoch. |
start_version |
VARCHAR | App version the user was first tracked on. |
user_creation_timeTIMESTAMP |
TIMESTAMP | Event_time (UTC) of the user's first event. |
user_id |
VARCHAR(16777216) | A readable ID specified by you. |
uuid |
VARCHAR(16777216) | A unique identifier per row (event sent). |
version_name |
VARCHAR(16777216) | The app version. |
See this article to learn more about how Amplitude tracks unique users.
| Column | Data type | Description |
|---|---|---|
amplitude_id |
NUMBER(38,0) | The Amplitude ID that is being merged into a user's original Amplitude ID. |
merge_event_time |
TIMESTAMP | The time of the event a user's new Amplitude ID was associated with their original Amplitude ID. |
merge_server_time |
TIMESTAMP | The server time of the event when a user's new Amplitude ID was associated with their original Amplitude ID. |
merged_amplitude_id |
NUMBER(38,0) | The originally assigned Amplitude ID when the user is first created. |
Thanks for your feedback!
May 30th, 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.