project 02 Social Product Analytics
Understand social engagement. Analyze event quality, conversion funnels, sessions and experiment results.
business question Clean event retries, reconstruct ordered funnels, sessionize activity, and compare experiment conversions. you deliver Reliable events, ordered funnels, user sessions and conversion metrics. Exported as a report with your checked SQL, result evidence and written findings. skills Deduplication · Funnels · Sessions scope 4 milestones · 3 tables · advanced Start the first milestone milestones
01 Deduplicate event retriesIdentify repeated events and keep a consistent record. 02 Ordered conversion funnelsCount users who complete events in the required order. 03 Sessionize an event streamGroup consecutive events into user sessions. 04 Experiment conversion denominatorsChoose the correct population for experiment conversion rates. data SocialLoop · 3 tables
Member events, referral links, and experiment assignments. Event retries create duplicate event_key values.
members 5 columns · 8 rows id integer · key email text country text joined_at text referrer_id integer 1 ALICE@Example.com US 2025-01-01 NULL 2 ben@example.com US 2025-01-02 1 3 Cara@example.com GB 2025-01-03 1 4 dev@example.com IN 2025-01-04 2 5 NULL CA 2025-01-05 NULL
social_events 6 columns · 22 rows id integer · key event_key text member_id integer event_type text event_at text ingested_at text 1 a 1 view 2025-01-01 10:00:00 2025-01-01 10:00:01 2 b 1 signup 2025-01-01 10:03:00 2025-01-01 10:03:01 3 c 1 purchase 2025-01-01 10:05:00 2025-01-01 10:05:01 4 a 1 view 2025-01-01 10:00:00 2025-01-01 10:10:00 5 d 2 view 2025-01-02 09:00:00 2025-01-02 09:00:01
experiment_assignments 3 columns · 8 rows member_id integer · key variant text assigned_at text 1 control 2025-01-01 09:00:00 2 control 2025-01-02 08:00:00 3 treatment 2025-01-03 08:30:00 4 treatment 2025-01-04 09:00:00 5 control 2025-01-05 10:00:00