Situation: We need a PDT that precomputes 30‑day retention by signup cohort so dashboards are fast and consistent.Implementation (LookML):- Create a view backed by a persistent derived_table. Use a datagroup that fires daily (or on raw event update) so it rebuilds predictably. Add indexes and partitioning in the SQL if the DB supports it.sql
view: cohort_retention {
derived_table: {
datagroup_trigger: cohort_events_datagroup
sql:
WITH cohorts AS (
SELECT user_id, DATE_TRUNC('day', signup_at) AS signup_day
FROM ${TABLE}.users
WHERE signup_at IS NOT NULL
),
events AS (
SELECT user_id, DATE_TRUNC('day', event_at) AS event_day
FROM ${TABLE}.events
WHERE event_at >= (CURRENT_DATE - INTERVAL '60 day')
)
SELECT
c.signup_day,
datediff('day', c.signup_day, e.event_day) AS days_since_signup,
COUNT(DISTINCT c.user_id) AS cohort_size,
COUNT(DISTINCT CASE WHEN datediff('day', c.signup_day, e.event_day)=30 THEN e.user_id END) AS retained_day_30
FROM cohorts c
LEFT JOIN events e USING (user_id)
WHERE datediff('day', c.signup_day, e.event_day) BETWEEN 0 AND 30
GROUP BY 1,2
;;
}
persist_for: "24 hours" # safety expiry
}
