SQL:

messenger sender receiver 2018(10-12月) 2 times

ROI ads 2018(10-12月) 2018(7-9月) 2018(1-3月) 2018(4-6月) 2018(1-3月)

spotify听歌问题 2018(10-12月)

fraud ad_account 2018(7-9月)

Survey log 2018(7-9月)

phone number confirmation 2018(7-9月)

message 2018(1-3月)

membership status change 2017(10-12月)

unique pair counting problem 2018(1-3月)

Q3 composer/post

Post table, user_id | date | event

event 可以是 'enter','post','cancel‘。每一次enter就有一个record，但是enter的内容可能被post或者被cancel。

User
user_id | date | country | au_flag
dau_flag：active user ，可以是0或者1

1) what is the average rate of succesufully post on last week?

Select sum(case when event=’post’ then 1 else 0 end)/cast(sum(case when event=’enter’ then 1 else 0 end) as float) avg_post_rate

from post

where date >=subdate(curdate(),dayofweek(curdate())+5) and date<= subdate(curdate(), dayofweek(curdate())-1);

1) what is the average rate of post for daily active user by country on today
注：一个user没有任何post也可能是active user

select country,

sum(case when p.user_id is not null then 1 else 0 end)/cast(count(u.user_id) as float)

from user u

left join post

on p.user_id = u.user_id and event=’post’

where date = curdate() and au_flag = 1

group by country;

如果construct confidence interval for proportion.结果sample proportion为0，怎么办？ http://www.pmean.com/01/zeroevents.html

So zero to 3/n(the natural log of.05 is roughly -3) is an approximate 95% confidence interval for a data set where we observed 0 events in n trial. As the sample size increases, this approximation gets better and better.

1) How do you evaluate which question is best answered?

就是求回答率最高的那道题吧，写完code, 我就主动提出concern，如果某些问题impression次数太少，对rate计算可能有影响，你想设置什么threshold for impression frequency吗？他说good question, 然后给我两个case, 数字是我刚编的，具体不记得了，大概就是answer rate一样，但是分母很不一样，你怎么判断这两种rate相同还是不同。。。

Confidence interval will be different, we are more confident about the rate with large impression.

1) 有n个meeting room，要随机assign k个meeting 到这几个meeting room。每个room被分到meeting数量的期望。
We can think of this as a binomial trial:
Success = Meeting is scheduled in Room1
P(success) = 1/N

Let's check all the requirements of binomial distribution are valid:
1. Trials are independent (because we can schedule a meeting in any room irrespective of whether they have meetings scheduled or not)
2. Fixed number of trials (k)
3. P(success) is the same across trials (yes, this is 1/N for every meeting assignment we have to do)

That's it! The expected value of a binomial distribution:
E(X) = number of trials * P(success) = k * 1/N = K/N

2) 变形：一共有N个conference room,然后有K个meeting独立随机分配到这N个conference room, 问题是你现在走进了一个会议室，发现是有会议的，求问这个会议室平均被分到了多少个会议。

a. If there is at least one meeting scheduled in room of interest:

已知1号房存在一个meeting，也就是1号房不为空。在这个条件下求1号房总的meeting数的期望
把meeting的集合写成 M1,M2,...,Mk
对任意的i, 利用Bayes公式
条件概率 P(Mi in room 1 | room 1 not empty) = P(Mi in room 1) / P(room 1 nonempty)

P(Mi in room 1) = 1/N
P(room 1 nonempty) = 1-(1-1/N)^k
以下是期望的可加性，加起来就是了，答案略去。一个例子是，N=k=2的时候，期望是4/3。

b. If we are sure there is only one meeting scheduled in room of interest:

We can think of this as a binomial trial:
Success = Meetingi is scheduled in Room1
P(success) = 1/N

Let's check all the requirements of binomial distribution are valid:
1. Trials are independent (because we can schedule a meeting in any room irrespective of whether they have meetings scheduled or not)
2. Fixed number of trials (k)
3. P(success) is the same across trials (yes, this is 1/N for every meeting assignment we have to do)

That's it! The expected value of a binomial distribution:
E(X) = number of trials * P(success) = k * 1/N
But hold on!
We know Room1 has already a meeting scheduled.
So now, our number of trials = k-1
Thus, E(X) = ((k-1) /N ) + 1 (we add 1 here because of the meeting that's already scheduled, but this might overestimate the number of rooms because expected value is just the mean)

Measure Story https://medium.com/stellarpeers/how-would-you-measure-the-success-of-facebook-stories-d4a520327119

Evaluate before launch

Objective & key results

The story aims to increase engagement and retention. We hope that people will find the new format of sharing more fun and engaging, so they will create and consume more contents and open the app frequently.

Describe what this feature does:

Determine if it align with ORKs
Evaluate the feature based on benefit and cost

Measure success after launch

1. goal of the feature

2. how this feature works

Users can share the story publicly or send directly to specific users. The stories disappears after 24 hours. FB customized the display of stories, the stories from friends that users interacts with more frequently are prioritized.

The camera is one component of the story feature. The camera has features like filters, sticker, color brush and more that people can use to enhance their photo and video.

An added bonus for Facebook Stories would be to attract Snapchat’s segment of 30-year-olds and younger and the 40+ segment that Snapchat does not focus on.

3. if feature have multiple components, define the metrics to measure the effectiveness of each component in achieving the goal

Metrics for stories feature:

User base: # of users who create stories, segment users based on historical data into users that never post, users that post seldom and those that post frequently, % of users that never post convert to active user;

Engagement by age group: # of stories consumed/# of total content consumed, # of stories created/# of total content created, # of shares by channel(Story, Direct Message and Post)

Retention: number of times a user shares a Story per day, per week, and per month

Customized display: CRT by rank - If people click on Stories randomly without prioritizing stories from close friends then a more appropriate ordering of Stories should be thought out.

Metrics for camera feature:

The fraction of the stories that user use the widgets - evaluate if the widgets induce the users to share video/photos with friends by enabling users make their videos/photos more fun;

filters used rate by rank - determine most frequently used filters so we can prioritize the display;

# of steps the users to create and share the story - One reason Snapchat doesn’t have more users ages 40 and older is because the Snapchat UI is not intuitive to use, with its hidden gestures. The Facebook Stories feature should be easier to use than Snapchat if Facebook wants to win the 40+ segment, therefore I would check if users are having difficulty using the Stories feature.

4. summarize key insights

whether Facebook Stories is encouraging users that don’t post regularly to start posting more and whether the younger and older users are equally fond of creating Facebook Stories.

5. how to set up test? a test user publish story, but a control (follower) may not see it. so the experience is not fully showed.

Cluster sampling. Assign treatment randomly based on clusters of users instead of individual user.

6. if originally got 100 post, now 80 post, 20 story. good or bad? why?

When does this happen? if it happened within a short time period after the launch, it may be due to novelty effect, where the existing users see the new feature and test everything. To validate that, we can segement the users by new users and existing user.

IG story vs. post 2018(10-12月)

给了FB的两个明星产品让我做比较、分析以及建议

User type: story for teenager and young people

Post for all age group

User base: DAU, # content creator, # content consumer
Engagement by age group: # of stories consumed(created)/# of total content consumed, # of post consumed(created)/# of total content consumed, # of shares/comments/likes per user
Retention: number of times a user shares/likes/comments/reaction a Story/post per day, per week, and per month

Measure Marketplace

1. Acquisition: # of suppliers, # of buyers, # of items listed by category, # of searches by category, items listed growth rate

2. Engagement: # conversations (Frequent communication also creates a platform lock-in for both sides of the marketplace), message respond rate (if the platform becomes a user’s central hub to manage communication with potential suppliers, it has a higher chance of retaining and monetizing that user later on)

3. Retention: retention rate/cohorts across both buyers and suppliers

4. success rate: % of items or services that get sold, and within what period of time. The higher the % and shorter period of time, the more sellers are making money and buyers are becoming loyal customers.

5. revenue: ad clicks/impressions

6. additional metrics can be defined to evaluate different component, like abandonment rate/CTR for search feature.

% of items or services that get sold/booked, and within what period of time. The higher the % and shorter period of time, the more sellers are making money and buyers are becoming loyal customers.

fb_product_zhengli