User retention from Bigquery

Question

I have an android app for which I need the user retention data over 6 months period. I want to write a sql query for bigquery but having trouble with the syntax. I need to get a date series for any events from each month against a user_id. Then I can process it in Excel and make month cohorts.

From Google analytics, I can get retention for past 2 months only.

Answer 1

No sample data is given so Im acting here hypothetically:

Have a CTE at top where you have exploded the dates with unnest, then join this to your retention users and compare the exploded dates to signup dates in your table with 6 month interval inbetween. I imagined that you have separate events and users tables.

SELECT
  signupDate,
  eventDate,
  COUNT(DISTINCT userId) AS retentionCount
FROM (
  SELECT
  userId,
  MIN(eventDate) AS signupDate /* again not sure if minimum eventDate here is to be your signupDate or not, do change accordingly */
  FROM eventsTable
  GROUP BY userId
) signupDates
JOIN explodedCTE
ON explodedCTE.userId = signupDates.userId
AND explodedCTE.date >= signupDates.signupDate
AND explodedCTE.date <= signupDates.signupDate + INTERVAL 6 MONTH
LEFT JOIN userEvents 
ON eventsTable.userId = signupDates.userId
AND eventsTable.event_date = explodedCTE.date
GROUP BY signupDate, eventDate

You can remove the group by if count of retention users is not what you are looking for (if instead you just want the Distinct userIds without aggregating or summing the rows.)

Documentation on interval functions

Interval type