kpi_key: kpi_activation_rate
kpi_name: Activation Rate
status: locked          # draft | active | locked | deprecated
owner: data_team
purpose: "Ngukur efektivitas onboarding dan kualitas adopsi awal."

definition_one_liner: >
  % signup baru yang selesai onboarding dan memicu >=1 key_action
  dalam 7 hari sejak signup_time.

unit: user
window_days: 7
filters:
  exclude_internal: true
  exclude_test: true
  exclude_bots: true

source_of_truth:
  primary: warehouse.events
  secondary: ga4_directional
precedence_rules: [warehouse_events, product_db_billing, ga4]

dependencies:
  events_required: [signup, onboarding_complete, key_action]
  required_fields: [user_id, event_time, source]

edge_cases:
  - "clock_skew: key_action sebelum signup -> valid jika <=24h"
  - "onboarding_missing -> do not count"
  - "late_events -> backfill max 7 hari lalu freeze"

qa:
  daily: [completeness_by_platform, duplicate_spike, missing_required_fields]
  weekly: [drift_device_mix, late_event_rate]

version: 2
effective_date: "2025-12-26"

create table if not exists kpi_registry (
  kpi_key text primary key,
  kpi_name text not null,
  status text not null check (status in ('draft','active','locked','deprecated')),
  owner text not null,
  version int not null,
  effective_date date not null,
  definition_one_liner text not null,
  source_primary text not null,
  source_secondary text,
  doc_path text,
  updated_at timestamptz not null default now()
);

create table if not exists kpi_daily (
  day date not null,
  kpi_key text not null,
  version int not null,
  value numeric not null,
  computed_at timestamptz not null default now(),
  primary key (day, kpi_key, version)
);

with signups as (
  select
    user_id,
    min(event_time) as signup_time,
    date_trunc('day', min(event_time))::date as signup_day
  from events
  where event_name = 'signup'
    and coalesce(is_internal,false) = false
    and coalesce(is_test,false) = false
    and coalesce(is_bot,false) = false
  group by 1
),
onboarding as (
  select user_id, min(event_time) as onboarding_time
  from events
  where event_name = 'onboarding_complete'
    and coalesce(is_internal,false) = false
    and coalesce(is_test,false) = false
    and coalesce(is_bot,false) = false
  group by 1
),
key_actions as (
  select user_id, min(event_time) as first_key_action_time
  from events
  where event_name = 'key_action'
    and coalesce(is_internal,false) = false
    and coalesce(is_test,false) = false
    and coalesce(is_bot,false) = false
  group by 1
),
activated as (
  select s.user_id, s.signup_day
  from signups s
  join onboarding o on o.user_id = s.user_id
  join key_actions k on k.user_id = s.user_id
  where k.first_key_action_time <= s.signup_time + interval '7 days'
    and k.first_key_action_time >= s.signup_time - interval '24 hours'
)
select
  s.signup_day,
  count(distinct a.user_id)::numeric / nullif(count(distinct s.user_id), 0) as activation_rate,
  count(distinct a.user_id) as activated_users,
  count(distinct s.user_id) as signups
from signups s
left join activated a on a.user_id = s.user_id and a.signup_day = s.signup_day
group by 1
order by 1 desc;

insert into kpi_daily (day, kpi_key, version, value)
select
  signup_day,
  'kpi_activation_rate',
  2,
  count(distinct a.user_id)::numeric / nullif(count(distinct s.user_id), 0)
from signups s
left join activated a on a.user_id = s.user_id and a.signup_day = s.signup_day
group by 1;

select
  date_trunc('day', event_time)::date as day,
  source,
  event_name,
  count(*) as cnt
from events
where event_name in ('signup','onboarding_complete','key_action')
  and event_time >= now() - interval '14 days'
group by 1,2,3
order by 1 desc, 2, 3;

select
  date_trunc('day', event_time)::date as day,
  count(*) as duplicate_groups
from (
  select
    event_name,
    user_id,
    event_time,
    coalesce(object_id,'-') as object_id,
    count(*) as c
  from events
  where event_time >= now() - interval '7 days'
    and user_id is not null
  group by 1,2,3,4
  having count(*) > 1
) d
group by 1
order by 1 desc;

from collections import Counter
import math

def kl(p, q, eps=1e-9):
    keys = set(p) | set(q)
    tp, tq = sum(p.values()) + eps, sum(q.values()) + eps
    return sum(((p.get(k,0)+eps)/tp) * math.log(((p.get(k,0)+eps)/tp)/((q.get(k,0)+eps)/tq)) for k in keys)

yesterday = Counter({"ios": 1200, "android": 800, "web": 400})
baseline7 = Counter({"ios": 6500, "android": 5200, "web": 3100})

score = kl(yesterday, baseline7)
if score > 0.08:
    print("ALERT drift:", round(score, 4))
else:
    print("OK drift:", round(score, 4))

select *
from kpi_daily
where kpi_key = 'kpi_activation_rate'
  and day >= current_date - 7;