整合站内与搜索引擎流量，为“主页浏览排名”功能提供数据建议

ScholarLink is a social network for academics. Its members include full professors, assistant professors, postdoctoral researchers, graduate students and others. To increase engagement among members, the product manager wants to launch a feature that ranks members by how many times other people have viewed their profiles. Each member would see their own version, along these lines:

Your rank for profile views

1. Jane Doe
2. John Doe
3. You
4. Jon Doe
5. …

You are the data team’s point person on the project, responsible for pulling together the relevant data and advising the product manager on how it should shape the feature.

The data

• users — one row per member: id, age, region and title_id. Names were removed for privacy.
• titles — one row per job title; titles.id matches users.title_id.
• user_profile_urls — one row per member: the internal profile_url for user_id.
• internal_profile_views — one row per profile view inside the network, over two days in January 2014: user_id is the member who viewed, viewed_user_id the member whose profile was viewed, created_at when.
• external_profile_views — search-engine traffic to members’ public profiles: public_profile_url and unique_PageViews, the number of unique page views from search-engine searches. The table does not say what period it covers.

Profile URLs look like /pub/n6ef3b792-nf7e05fff-ms or /cv/…. The words of each member’s name have been replaced by codes (the same word always gets the same code); degree suffixes such as -phd or -ms and trailing /1, /2 are kept. Public URLs in external_profile_views don’t always match internal URLs exactly, so linking the two sources is part of the work.

Your task

1. Identify one or two trends in the data that matter for this feature — for example, how concentrated views are, how they vary by title, region or age, who views whom, or anything odd in the view logs. How does each one change the way you would build the ranking? In a sentence or two each, how would you explain them to the product manager, who isn’t technical?
2. Link the external traffic to members. Describe how you matched public URLs to users, what share of external page views you could attribute, what you could not match and why, and how matching errors could distort the ranking.
3. How much traffic are members’ profiles receiving from internal and external sources combined? Is it reasonable to include both sources in the feature? Consider what each source measures, the periods they cover, and how each could be inflated or gamed.
4. How would you present the profile-view data in the “Rank for profile views” feature? Decide who each member is ranked against (everyone, or a peer group such as title or region), over what time window, and how to handle ties, members with no views, and very low counts. Provide code that produces the ranked list a given member would see.
5. After launch, how would you tell whether the feature increases engagement? Name the metrics you would track, including any guardrails, and how you would test it.

What to deliver

A written report for the product manager, with one short section per question and charts where they help. Add an appendix with the queries and code behind each number, including the code that produces the ranking.

All company names in this question are fictional.