Menu
Amazon Mobile Analytics
User Guide

Example Amazon Redshift Queries

Note

After April 30, 2018, Amazon Mobile Analytics features will be provided only by Amazon Pinpoint. If you're new to Mobile Analytics, use Amazon Pinpoint instead. If you're currently using Mobile Analytics, see Migrating from Amazon Mobile Analytics to Amazon Pinpoint.

The “users” columns in the following queries count distinct client_cognito_id values only when users are authenticated with Amazon Cognito. For unauthenticated use cases, use the client_id for app-device counts.

Top item purchases, by store (last 30 days):

SELECT application_app_id AS"app id", monetization_transaction_item_id AS"item id", monetization_transaction_store AS"store", COUNT(DISTINCTclient_id) AS"devices", COUNT(DISTINCTclient_cognito_id) AS"users", SUM(monetization_transaction_quantity) AS"quantity", SUM(monetization_transaction_price_amount) "amount (Apple only)", monetization_transaction_price_currency_code AS"currency (Apple only)" FROM AWSMA.v_event WHERE event_type = '_monetization.purchase'AND event_timestamp BETWEENgetdate() - 30 ANDgetdate() + 1 GROUPBY "app id", "item id", "currency (Apple only)", "store" ORDERBY "app id"ASC, "item id"ASC, "quantity"DESC, "store", "devices"DESC ;

30 day active users and devices, by device language code:

SELECT application_app_id AS"app id", COUNT(DISTINCTclient_id) AS"devices", COUNT(DISTINCTclient_cognito_id) AS"users", device_locale_language AS"language" FROM AWSMA.v_event WHERE event_type = '_session.start'AND event_timestamp BETWEENgetdate() - 30 ANDgetdate() + 1 GROUPBY "app id", "language" ORDERBY "app id"ASC, "devices"DESC, "language" ;

30 day active users and devices, by app version:

SELECT application_app_id AS"app id", device_platform_name AS"platform", application_version_name AS"version name", application_version_code AS"version code", COUNT(DISTINCTclient_id) AS"devices", COUNT(DISTINCTclient_cognito_id) AS"users" FROM AWSMA.v_event WHERE event_type = '_session.start'AND event_timestamp BETWEENgetdate() - 30 ANDgetdate() + 1 GROUPBY "app id", "platform", "version name", "version code" ORDERBY "app id"ASC, "platform"ASC, "devices"DESC, "version name"DESC, "version code"DESC ;