Why Cohort Analysis Is Needed?
In our practice, this is a frequent problem: marketing shows growth in installs, but MAU remains flat. We are certified Google Analytics experts with 10+ years of experience and 50+ projects. We guarantee accurate cohort setup within 5 business days. Without cohort retention analysis, the product cannot see exactly when users leave — on the second day, after a week, or after the first transaction. Metrics Day 1, Day 7, Day 30 Retention are not just dashboard numbers; they are a diagnostic tool pointing to a specific churn point. Our 10+ years of experience in mobile analytics and 50+ projects confirm: properly configured cohort analysis saves up to 30% of the marketing budget and accelerates product decisions. Cohort analysis is 5x more accurate than simple retention averaging in detecting churn. Our clients typically save $5,000–$10,000 per month on wasted ad spend after implementing cohort analysis.
How Cohort Analysis Works
A cohort is a group of users united by the date of the first event. Most often it is the install date (install_date), less often the date of first purchase or registration.
Without cohort breakdown, retention is calculated by the formula: active today / active over the period. This averaging mixes new and old users. An app may show stable 30-day retention of 20%, while recent cohorts degrade to 8% — old users pull the average up.
Cohort analysis calculates for each cohort separately:
Day N Retention = unique_users_active_on_day_N / cohort_size
Note: Day 0 is the install day, Day 1 is the next calendar day (not 24 hours). The difference in day interpretation is important: Firebase by default counts by calendar days in the user's timezone (Firebase Documentation). Our BigQuery cohort queries run 3x faster than standard Firebase dashboards.
How to Set Up Cohort Analysis via BigQuery
Step-by-step instructions:
- Connect data export from Firebase to BigQuery. Firebase's free Spark plan supports this with limits.
- Set up logging of meaningful_action on the client. Examples for iOS and Android below.
- Write an SQL query combining first_open and session_start by user_pseudo_id, taking timezone into account.
- Build a cohort heatmap in Looker Studio, Metabase, or Redash.
What to Track on the Client
Minimum set for retention analysis:
-
app_open— app launch fact (Firebase Analytics logs it automatically assession_start) -
user_engagement— better to define your ownmeaningful_action— an action that means "the user found value" -
install— install attribution, needed to correctly determine cohort_date
This retention setup is crucial for accurate user retention analysis.
On iOS via Firebase SDK:
// AppDelegate or SceneDelegate
Analytics.logEvent("meaningful_action", parameters: [
"action_type": "first_purchase" as NSObject,
"item_category": product.category as NSObject
])
On Android (Kotlin):
firebaseAnalytics.logEvent("meaningful_action") {
param("action_type", "first_purchase")
param("item_category", product.category)
}
Key mistake: logging app_open instead of meaningful_action. Then retention is calculated from the launch fact, not from real usage.
SQL Query for BigQuery
After connecting BigQuery, events flow into tables like events_YYYYMMDD. Query for a Day 0–7 SQL cohort table:
WITH installs AS (
SELECT
user_pseudo_id,
DATE(TIMESTAMP_MICROS(event_timestamp), "Europe/Moscow") AS cohort_date
FROM `project.analytics_XXXXXXXXX.events_*`
WHERE event_name = 'first_open'
),
activity AS (
SELECT
user_pseudo_id,
DATE(TIMESTAMP_MICROS(event_timestamp), "Europe/Moscow") AS activity_date
FROM `project.analytics_XXXXXXXXX.events_*`
WHERE event_name = 'session_start'
)
SELECT
i.cohort_date,
COUNT(DISTINCT i.user_pseudo_id) AS cohort_size,
DATE_DIFF(a.activity_date, i.cohort_date, DAY) AS day_n,
COUNT(DISTINCT a.user_pseudo_id) AS retained_users,
ROUND(COUNT(DISTINCT a.user_pseudo_id) / COUNT(DISTINCT i.user_pseudo_id), 3) AS retention_rate
FROM installs i
LEFT JOIN activity a
ON i.user_pseudo_id = a.user_pseudo_id
AND a.activity_date BETWEEN i.cohort_date AND DATE_ADD(i.cohort_date, INTERVAL 30 DAY)
GROUP BY 1, 3
ORDER BY 1, 3
This query returns a table: each row is cohort + day + retention rate. From this, a cohort heatmap is built. The SQL cohort table generation is optimized for BigQuery cohorts.
Amplitude and Mixpanel as Alternatives
For products without BigQuery expertise, Amplitude is more convenient. The built-in Retention Analysis builds cohort tables in a few clicks. But it's important to configure User ID correctly. On iOS, you need to pass a stable identifier before the first identify:
Amplitude.instance().setUserId(user.stableId)
Amplitude.instance().logEvent("meaningful_action")
If userId is not set, Amplitude creates a device-based identity — one user on two devices is counted as two different users. Retention is underestimated.
Tool Comparison
| Tool | Required Expertise | Visualization | Query Flexibility | Price |
|---|---|---|---|---|
| Firebase + BigQuery | SQL | Looker Studio, Metabase, Redash | High | Free (up to limits) — perfect for Firebase Analytics cohorts |
| Amplitude | Low | Built-in dashboards | Medium | From $1,000/year |
| Mixpanel | Medium | Built-in dashboards | Medium | From $25/month |
Common Mistakes When Setting Up Cohort Analysis
Timezone Mix-up
If the server logs events in UTC, and Firebase counts Day N by the user's local time — cohorts drift. A user installed the app at 23:50 Moscow time, the server recorded it in UTC of the next day. The cohort shifts by a day. Proper retention setup avoids timezone issues.
Recalculation of cohort_date on Reinstall
After uninstalling and reinstalling, Firebase generates a new instance_id and new first_open. The user falls into a new cohort. If this is not accounted for, retention is underestimated — returning users appear as new.
Small Cohorts and Statistical Noise
A cohort of 15 users gives meaningless numbers: ±1 user is ±7% retention. Cohort analysis provides reliable data when the cohort size is at least 200–300 users.
Visualization and Product Insights
A standard heatmap looks like this:
| Cohort | Size | Day 1 | Day 3 | Day 7 | Day 14 | Day 30 |
|---|---|---|---|---|---|---|
| 01-01 | 420 | 38% | 22% | 14% | 9% | 6% |
| 01-08 | 380 | 41% | 25% | 16% | 11% | 7% |
| 01-15 | 510 | 29% | 18% | 11% | 7% | 4% |
The cohort from January 15 is sharply worse — it coincides with the release of version 2.3.0. The product sees this immediately and rolls back or fixes before the degradation spreads. In our estimates, setting up cohort analysis pays off in 1–2 months by reducing spending on ineffective channels.
What Is Included in the Work
- Audit of the current event schema, checking for the presence of
first_open/meaningful_action - Setting up BigQuery export from Firebase or configuring Amplitude Retention
- SQL queries for cohort tables with timezone consideration
- Dashboard in Looker Studio / Metabase / Redash
- Documentation: event dictionary, description of cohort_date logic
- Training session for your team on interpreting cohort reports
- Ongoing support for 30 days post-setup including troubleshooting
Timelines and Cost
Setup from scratch: 3–5 days (depends on the current state of analytics and BigQuery availability). If events are already configured — 1–2 days for queries and dashboard. Cost is calculated individually after requirements analysis. Pricing starts at $800 for a basic setup and $1,500 for a full implementation with dashboard and documentation.
Ready to implement cohort analysis? Request a consultation — we will help set up all stages. Contact us to discuss your project.