Apple (App Store Connect)
Let AI connect your sources for you
Skip the manual setup — run this in your project and the wizard auto-detects your databases and APIs and connects them to PostHog.
Connect Apple (App Store Connect) to PostHog to sync your data into the PostHog data warehouse for analysis and modeling.
Pull your App Store apps, versions, builds, reviews and sales reports into the PostHog Data warehouse.
An Account Holder or Admin creates an API key under Users and Access → Integrations → App Store Connect API in App Store Connect. Copy the issuer ID and key ID from that page, then paste the contents of the .p8 private key file you download. Apple only lets you download that file once, so keep a copy.
Sales and subscription reports also need your vendor number (App Store Connect → Payments and Financial Reports) and a key with the Finance, Sales, or Admin role. Leave it blank if you only want app, review and build data.
The analytics tables need a key with the Admin role. Apple lets only an Admin key start an analytics report.
Analytics report tables keep every restatement Apple publishes for a report date (about six days of revisions), so a raw SUM grouped by date overcounts. The raw rows preserve the restatement history; to aggregate, keep only the latest restatement per report date and dimension combination, for example:
SELECT
raw.date,
raw.app_id,
raw.app_name,
raw.app_apple_identifier,
raw.app_version,
raw.device,
raw.platform_version,
raw.source_type,
raw.page_type,
raw.app_download_date,
raw.territory,
argMax(raw.sessions, raw.processing_date) AS sessions,
argMax(raw.total_session_duration, raw.processing_date) AS total_session_duration,
argMax(raw.unique_devices, raw.processing_date) AS unique_devices
FROM appstoreconnect_analytics_app_sessions AS raw
INNER JOIN (
SELECT app_id, date, max(processing_date) AS processing_date
FROM appstoreconnect_analytics_app_sessions
GROUP BY app_id, date
) AS latest
ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date
GROUP BY
raw.date,
raw.app_id,
raw.app_name,
raw.app_apple_identifier,
raw.app_version,
raw.device,
raw.platform_version,
raw.source_type,
raw.page_type,
raw.app_download_date,
raw.territory
This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. Each analytics table's description carries the exact query for that table.
Configuration
| Option | Type | Required |
|---|---|---|
Issuer ID | text | Yes |
Key ID | text | Yes |
Private key (.p8 contents) | textarea | Yes |
Vendor number (optional) | text | No |
Linking Apple (App Store Connect) to PostHog
- Go to the Data pipeline page in PostHog
- Click New source and select Apple (App Store Connect)
- Fill in the required configuration fields
- Click Next, select the tables you want to sync, and then press Import
Supported tables
| Table | Description | Sync method | Incremental field | Primary key |
|---|---|---|---|---|
apps | An app on the App Store, and the parent of the per-app version, review, purchase and subscription tables. | Full refresh | — | id |
app_store_versions | A version of an app submitted to the App Store, with its review and release state. | Full refresh | — | app_id, id |
builds | A build uploaded to App Store Connect for TestFlight or App Store release. | Full refresh | — | id |
beta_groups | A TestFlight group of beta testers, internal or external, with its public link settings. | Full refresh | — | id |
customer_reviews | A customer review left on the App Store, including its star rating and territory. | Full refresh | — | app_id, id |
review_responses | A developer response published to a customer review, one row per responded review. | Full refresh | — | app_id, id |
in_app_purchases | An in-app purchase product configured for an app. | Full refresh | — | app_id, id |
subscription_groups | A group of auto-renewable subscriptions within an app that customers can move between. | Full refresh | — | app_id, id |
sales_reports | One row of Apple's daily Sales and Trends summary report: units and developer proceeds per SKU, territory and product type. | Incremental, Full refresh | report_date | report_date, _line |
subscription_reports | One row of Apple's daily Subscription summary report: active, paid and trial subscription counts by state and territory. | Incremental, Full refresh | report_date | report_date, _line |
subscription_event_reports | One row of Apple's daily Subscription Event report: counts of subscription lifecycle events such as renewals, cancellations and plan changes. | Incremental, Full refresh | report_date | report_date, _line |
analytics_app_sessions | How often people open the app and for how long, from Apple's App Sessions analytics report. One row per instance line; an instance's rows can restate earlier data dates. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.app_download_date, raw.territory, argMax(raw.sessions, raw.processing_date) AS sessions, argMax(raw.total_session_duration, raw.processing_date) AS total_session_duration, argMax(raw.unique_devices, raw.processing_date) AS unique_devices FROM appstoreconnect_analytics_app_sessions AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_app_sessions GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.app_download_date, raw.territory This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |
analytics_app_store_downloads | How many times people download the app on the App Store, including redownloads and updates, from Apple's App Store Downloads analytics report. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.download_type, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.pre_order, raw.territory, argMax(raw.counts, raw.processing_date) AS counts FROM appstoreconnect_analytics_app_store_downloads AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_app_store_downloads GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.download_type, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.pre_order, raw.territory This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |
analytics_installations_deletions | How many times users install and delete the app, from Apple's App Store Installations and Deletions analytics report. Covers users who opted in to sharing data. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.event, raw.download_type, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.app_download_date, raw.territory, argMax(raw.counts, raw.processing_date) AS counts, argMax(raw.unique_devices, raw.processing_date) AS unique_devices FROM appstoreconnect_analytics_installations_deletions AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_installations_deletions GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.event, raw.download_type, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.app_download_date, raw.territory This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |
analytics_discovery_engagement | How users find and interact with the app on the App Store (impressions, page views, taps), from Apple's App Store Discovery and Engagement analytics report. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.event, raw.page_type, raw.source_type, raw.engagement_type, raw.device, raw.platform_version, raw.territory, argMax(raw.counts, raw.processing_date) AS counts, argMax(raw.unique_counts, raw.processing_date) AS unique_counts FROM appstoreconnect_analytics_discovery_engagement AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_discovery_engagement GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.event, raw.page_type, raw.source_type, raw.engagement_type, raw.device, raw.platform_version, raw.territory This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |
analytics_app_crashes | Crash counts by app version and device, from Apple's App Crashes analytics report. Covers users who opted in to sharing data, with low-volume rows suppressed. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.app_version, raw.device, raw.platform_version, argMax(raw.crashes, raw.processing_date) AS crashes, argMax(raw.unique_devices, raw.processing_date) AS unique_devices FROM appstoreconnect_analytics_app_crashes AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_app_crashes GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.app_version, raw.device, raw.platform_version This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |
analytics_app_store_preorders | Pre-orders placed and canceled for the app, from Apple's App Store Pre-orders analytics report. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.territory, raw.pre_order_start_date, raw.pre_order_end_date, argMax(raw.pre_orders_placed, raw.processing_date) AS pre_orders_placed, argMax(raw.pre_orders_canceled, raw.processing_date) AS pre_orders_canceled FROM appstoreconnect_analytics_app_store_preorders AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_app_store_preorders GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.territory, raw.pre_order_start_date, raw.pre_order_end_date This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |
analytics_app_clip_usage | How users engage with the app's App Clips, from Apple's App Clip Usage analytics report. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.app_clip_event_type, raw.source_type, raw.app_version, raw.device, raw.platform_version, raw.territory, argMax(raw.counts, raw.processing_date) AS counts, argMax(raw.total_session_duration, raw.processing_date) AS total_session_duration, argMax(raw.unique_devices, raw.processing_date) AS unique_devices FROM appstoreconnect_analytics_app_clip_usage AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_app_clip_usage GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.app_clip_event_type, raw.source_type, raw.app_version, raw.device, raw.platform_version, raw.territory This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |
analytics_app_sessions_detailed | How often people open the app and for how long, with the acquisition attribution columns, from Apple's App Sessions Detailed analytics report. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.app_download_date, raw.territory, raw.campaign, raw.page_title, raw.source_info, argMax(raw.sessions, raw.processing_date) AS sessions, argMax(raw.total_session_duration, raw.processing_date) AS total_session_duration, argMax(raw.unique_devices, raw.processing_date) AS unique_devices FROM appstoreconnect_analytics_app_sessions_detailed AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_app_sessions_detailed GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.app_download_date, raw.territory, raw.campaign, raw.page_title, raw.source_info This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |
analytics_app_store_downloads_detailed | How many times people download the app on the App Store, with the acquisition attribution columns, from Apple's App Downloads Detailed analytics report. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.download_type, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.pre_order, raw.territory, raw.campaign, raw.page_title, raw.source_info, argMax(raw.counts, raw.processing_date) AS counts FROM appstoreconnect_analytics_app_store_downloads_detailed AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_app_store_downloads_detailed GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.download_type, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.pre_order, raw.territory, raw.campaign, raw.page_title, raw.source_info This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |
analytics_installations_deletions_detailed | How many times users install and delete the app, with the acquisition attribution columns, from Apple's App Store Installation and Deletion Detailed analytics report. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.event, raw.download_type, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.app_download_date, raw.territory, raw.campaign, raw.page_title, raw.source_info, argMax(raw.counts, raw.processing_date) AS counts, argMax(raw.unique_devices, raw.processing_date) AS unique_devices FROM appstoreconnect_analytics_installations_deletions_detailed AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_installations_deletions_detailed GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.event, raw.download_type, raw.app_version, raw.device, raw.platform_version, raw.source_type, raw.page_type, raw.app_download_date, raw.territory, raw.campaign, raw.page_title, raw.source_info This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |
analytics_discovery_engagement_detailed | How users find and interact with the app on the App Store, with the acquisition attribution columns, from Apple's App Store Discovery and Engagement Detailed analytics report. Apple restates each report date for about six days, and every restatement is kept as new rows with a later processing_date, so summing raw rows overcounts. Get one row per report date and dimension combination with: SELECT raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.event, raw.page_type, raw.source_type, raw.engagement_type, raw.device, raw.platform_version, raw.territory, raw.campaign, raw.page_title, raw.source_info, argMax(raw.counts, raw.processing_date) AS counts, argMax(raw.unique_counts, raw.processing_date) AS unique_counts FROM appstoreconnect_analytics_discovery_engagement_detailed AS raw INNER JOIN ( SELECT app_id, date, max(processing_date) AS processing_date FROM appstoreconnect_analytics_discovery_engagement_detailed GROUP BY app_id, date ) AS latest ON raw.app_id = latest.app_id AND raw.date = latest.date AND raw.processing_date = latest.processing_date GROUP BY raw.date, raw.app_id, raw.app_name, raw.app_apple_identifier, raw.event, raw.page_type, raw.source_type, raw.engagement_type, raw.device, raw.platform_version, raw.territory, raw.campaign, raw.page_title, raw.source_info This query names the table as connected without a table name prefix; if you set one, add it wherever the table name appears. | Incremental, Full refresh | processing_date | app_id, processing_date, _line |