Social Product Analytics

# Social Product Analytics

_Empty report_

Clean event retries, reconstruct ordered funnels, sessionize activity, and compare experiment conversions.

- Sample database: SocialLoop (members, social_events, experiment_assignments)
- Milestones completed (SQL + concept check): 0 of 4
- Findings written: 0 of 4
- Verified checks (Check answer passed): 0
- Exported: 2026-10-14T18:03:39.393Z

Findings are the learner’s own interpretation. Passing a SQL check does not verify the written finding.

## Milestone 1: Deduplicate event retries

Status: Not started

_No finding written yet._

### 1.1 Return id, event_key and member_id for the latest ingested row of each event_key. Break ingestion ties by highest id.

_not attempted_

### 1.2 After keeping the latest ingested row per event_key (tie: highest id), return event_type and event_count by event_type.

_not attempted_

## Milestone 2: Ordered conversion funnels

Status: Not started

_No finding written yet._

### 2.1 Return one row: viewed, signed_up, purchased as member counts for the ordered view → signup → purchase funnel. Equal times are allowed; use each member's earliest valid next step.

_not attempted_

### 2.2 Return member_id, viewed_at, signed_at and purchased_at for members who complete that ordered funnel.

_not attempted_

## Milestone 3: Sessionize an event stream

Status: Not started

_No finding written yet._

### 3.1 Using raw social_events, return member_id and session_count. Start a new session on the first event or a gap of at least 30 minutes; order by event_at then id per member.

_not attempted_

### 3.2 Using the same rule, return id, member_id and session_number for every raw social event.

_not attempted_

## Milestone 4: Experiment conversion denominators

Status: Not started

_No finding written yet._

### 4.1 Return variant, assigned, converted and conversion_pct (0–100, 2 decimals). A member converts with any purchase on/after assigned_at and before Feb 1 2025. Include all assigned members.

_not attempted_

### 4.2 Return member_id, variant and converted (1 or 0) for every assignment using the same post-assignment purchase definition and cutoff.

_not attempted_