Menu
Amazon Mobile Analytics
User Guide

Example Amazon Redshift Queries

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):

Copy
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:

Copy
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:

Copy
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 ;