Deep-Dive User Retention Analysis: Mastering Growth with Adjust & GA4

In the world of mobile growth, there is only one metric that truly determines your app’s survival: Retention. However, not all “active users” are created equal. Counting a user as “retained” just because they accidentally opened your app is a vanity metric that can lead to expensive mistakes.

Today, we’re going to build a high-precision retention model by merging the attribution power of Adjust with the behavioral depth of Google Analytics 4 (GA4).

Why Settle for Less? The Power of “Engagement-Based” Retention

Standard retention reports often look at “App Opens.” We’re raising the bar. By combining these two giants, we achieve two things:

1. Adjust Attribution: We identify exactly which marketing channel (Google Ads, Facebook, Organic, etc.) brought the user in.
2. GA4 Engagement: We use the engagement_time_msec parameter to ensure the user actually interacted with the app, filtering out “ghost users” and accidental clicks.

The Blueprint: 4 Steps to Data Mastery

Our approach follows a logical flow:

• Define the Cohort: Identifying the “birth date” and acquisition source of each user via Adjust.
• Filter Quality Activity: Extracting only the days where users had an engagement time $> 0$ in GA4.
• Calculate Day N: Determining the gap between the install date and the activity date.
• Normalize for Visualization: Calculating the “Day 0” baseline to allow for percentage-based analysis in dashboarding tools.

The SQL Engine: Merging Adjust & GA4

Here is the “kitchen” where the magic happens. You can copy-paste this BigQuery SQL directly into your project:

WITH user_installs AS (
  -- STEP 1: Define cohorts and sources from Adjust
  SELECT 
    _idfa__gps_adid__fire_adid_,
    _network_name_,
    DATE(TIMESTAMP_SECONDS(CAST(_created_at_ AS INT64))) AS first_install_date
  FROM 
    `your-project.adjust.raw_data`
  WHERE 
    _idfa__gps_adid__fire_adid_ IS NOT NULL
    AND _activity_kind_ = "install"
    AND _idfa__gps_adid__fire_adid_ != '00000000-0000-0000-0000-000000000000'
),

activity_events AS (
  -- STEP 2: Capture active/engaged days from GA4
  SELECT
    device.advertising_id AS advertising_id,
    PARSE_DATE('%Y%m%d', event_date) AS event_day,
    SUM((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) AS daily_engagement
  FROM
    `your-project.analytics_123456.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 8 WEEK)) 
                      AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND device.advertising_id IS NOT NULL
  GROUP BY 
    1, 2
),

joined_data AS (
  -- STEP 3: Merge the two worlds
  SELECT
    i.network_name,
    i.first_install_date AS cohort_day,
    DATE_DIFF(a.event_day, i.first_install_date, DAY) AS retention_day,
    COUNT(DISTINCT i.advertising_id) AS active_users
  FROM
    user_installs i
  INNER JOIN 
    activity_events a ON i._idfa__gps_adid__fire_adid_ = a.advertising_id
  WHERE
    a.event_day >= i.first_install_date
    AND a.daily_engagement > 0 -- The "Quality" filter
  GROUP BY
    1, 2, 3
)

-- STEP 4: Final output with Day 0 baseline for Looker Studio
SELECT 
  *,
  FIRST_VALUE(active_users) OVER(PARTITION BY network_name, cohort_day ORDER BY retention_day) AS day_0_users
FROM 
  joined_data
WHERE 
  retention_day >= 0
ORDER BY 
  1, 2, 3

From Data to Insight: Visualizing in Looker Studio

Data in a table is just numbers; data on a dashboard is a strategy. To turn this SQL output into a professional Cohort Heatmap in Looker Studio, follow these steps:

1. Chart Type: Select “Pivot Table with Heatmap.”
2. Rows: cohort_day | Columns: retention_day.
3. The Metric: Create a Calculated Field called Retention % using: SUM(active_users) / SUM(day_0_users). Set the type to Percent.
4. The Filter: Add a “Drop-down List” for network_name.

Now, you can instantly compare if users from Google Ads are sticking around longer than those from Influencer Campaigns.

Actionable Takeaways

• Low Day 1 Retention? Check your onboarding flow or app load times.
• Sharp drop after Day 7? Your content might be getting stale, or you need a better push notification strategy.
• Organic vs. Paid? If Organic retention is significantly higher, focus on ASO (App Store Optimization). If Paid is higher, scale your budget!

Conclusion

By bridging the gap between Adjust and GA4, you stop guessing and start growing. You aren’t just looking at who downloaded your app; you’re looking at who values it.