table-query

Posted on | In , |

Metrics

based on postgresQL

DAU/MAU

1
2
3
4
5
6
7
8
9
10
11
with dau_table as (
      select date(ts) AS date, count(distinct userid) as dau
      from dailysessions ds
      group by date(ts)
     )
select date, dau,
       (select count(distinct user_id)
        from dailysessions ds
        where ds.date between date - 29 * interval '1 day' and date
       ) as mau
from dau_table;

(based on https://stackoverflow.com/questions/24494373/querying-dau-mau-over-time-daily)

Click Through Rate (CTR)

1
2
3
4
SELECT
 SUM(CASE WHEN action = "Click" then 1 else 0 end) /
 SUM(CASE WHEN action = "View" then 1 else 0 end) AS CTR
FROM events

Retention

1
2
3
4
5
6
7
8
9
10
select 
  activity.date, 
  count(distinct activity.user_id) as active_users, 
  count(distinct future_activity.user_id) as retained_users,
  count(distinct future_activity.user_id) / count(distinct activity.user_id)::float as retention
from activity
left join activity as future_activity on
  activity.user_id = future_activity.user_id
  and activity.date = future_activity.date - interval '1 day'
group by 1

Bounce

Impression

churn

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with monthly_activity as (
  select distinct
    date_trunc('month', created_at) as month,
    user_id
  from events
)
select
  last_month.month + interval '1 month',
  count(distinct last_month.user_id)
from monthly_activity last_month
left join monthly_activity this_month
  on this_month.user_id = last_month.user_id
  and this_month.month = last_month.month + interval '1 month'
where this_month.user_id is null
group by 1

churn + new user + en-engage + active

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
create table cummulative_client(
  id INT NOT NULL,
  status VARCHAR(255),
  record_date DATE,
);
create table today_client(
  id int NOT NULL,
  record_date DATE
);
insert into cumulative_client Values(1, 'active','2017-3-27');
insert into cumulative_client Values(2, 'active','2017-3-27'); 
insert into cumulative_client Values(5, 'churn','2017-3-27');
insert into cumulative_client Values(6, 'churn','2017-3-27');
insert into cumulative_client Values(7, 're_engage' ,'2017-3-27');
insert into cumulative_client Values(9, 'new','2017-3-27');
insert into today_client Values(1,'2017-3-28');
insert into today_client Values(5,'2017-3-28');
insert into today_client Values(7,'2017-3-28');
insert into today_client Values(9,'2017-3-28');
insert into today_client Values(11,'2017-3-28');
SELECT * FROM today_client;
SELECT  CASE WHEN c.id IS NULL THEN t.id ELSE c.id END AS id,
		CASE WHEN c.status IS NULL THEN 'new'
			WHEN t.id IS NULL THEN 'churn'
			WHEN c.status='churn' AND t.id IS NOT NULL THEN 're_engage'
			ELSE 'active' END AS status
FROM 
	cumulative_client c
		 FULL OUTER JOIN 
	today_client t
		ON c.id = t.id
ORDER BY id
;

ATV (Average Transaction Value)

ARPU (Average Revenue Per User)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select 
  revenue.sum/total_installs.count AS ARPU
from 
  (select sum(price) as sum
  from purchases) as revenue
  ,
  (select count(1) as count
  from installs) as total_installs
-- ARPU by cohorts
select
  date(installs.created_at) as date 
  , sum(purchases.price)/count(distinct installs.user_id) as ARPU
from installs left outer join purchases
  on installs.user_id = purchases.user_id
group by 1

Reference