Retention Dashboard example

DECLARE date_range STRING DEFAULT format_date('%Y%m%d',date_sub( CURRENT_DATE(), interval 5 day));

DELETE FROM `kyivstar-1267.mk_retention.mk_retention`
WHERE date > PARSE_DATE('%Y%m%d', date_range);

insert `kyivstar-1267.mk_retention.mk_retention` (date, first_tuch, cohort_day, medium, source, campaign, platform, key, users_count)
with all_data as(
  select distinct
    PARSE_DATE('%Y%m%d', event_date) as date,
    platform,
    app_info.version as version,
    if(traffic_source.medium is null, 'none', traffic_source.medium) as medium, 
    if(traffic_source.source is null, 'none', traffic_source.source) as source, 
    IF(traffic_source.name is null, 'none', traffic_source.name) as campaign,
    user_pseudo_id,
    PARSE_DATE('%Y%m%d', FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_MICROS(user_first_touch_timestamp))) as first_tuch
  FROM `kyivstar-1267.analytics_153798249.events_*`
  WHERE
     _TABLE_SUFFIX > date_range
     and stream_id in ('1052100118','1194239115') and event_name in ('login', 'loginsuccess')
     and user_pseudo_id is not null
     and FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_MICROS(user_first_touch_timestamp)) >= '20200101'
)
  select
    date,
    first_tuch,
    date_diff(date, first_tuch, DAY) as cohort_day,
    medium,
    source,
    campaign,
    platform,
    concat(format_date('%Y%m%d', first_tuch),'|', medium,'|', source, '|',campaign, '|',platform) as key,
    count (distinct user_pseudo_id) as users_count,
  from all_data
  where date_diff(date, first_tuch, DAY) between 0 and 89
  group by 1,2,3,4,5,6,7