This page comes from github projects of mine and these code work in Big Query.

🔹 1. Traffic Source Distribution

What it does:

• Counts total events per traffic_source
• Calculates percentage share of each source

Why it matters:

• Shows which channels (Google, Direct, Ads, etc.) bring the most traffic

Key insight:
👉 Helps you understand traffic composition

---

🔹 2. Conversion Rate by Traffic Source

What it does:

• Calculates:
  conversion_rate = total_revenue / total_events

Why it matters:

• Identifies high-quality traffic sources

Key insight:
👉 Not all traffic is equal — some sources convert better

---

🔹 3. New vs Returning Users (Monthly)

What it does:

• Classifies users:
  • First event → New
  • Later events → Returning
• Aggregates by month

Why it matters:

• Tracks user retention & acquisition trends

Key insight:
👉 Balance between acquiring new users vs keeping existing ones

---

🔹 4. Revenue per Session (by Traffic Source)

What it does:

• Calculates average revenue per event/session

Why it matters:

• Measures monetization efficiency

Key insight:
👉 Which channel generates more money per visit

---

🔹 5. Top Event Types

What it does:

• Counts occurrences of each event_type
• Returns top 5

Why it matters:

• Shows user behavior distribution

Key insight:
👉 Understand dominant user actions (view, add_to_cart, etc.)

---

🔹 6. Monthly Active Users

What it does:

• Counts distinct users per month/year

Why it matters:

• Measures growth of user base

Key insight:
👉 Tracks platform popularity over time

---

🔹 7. View → Add to Cart Ratio

What it does:

• Ratio:
  users who added to cart / users who viewed product

Why it matters:

• Measures product attractiveness

Key insight:
👉 Are users interested enough to take action?

---

🔹 8. Activity by Day of Week

What it does:

• Counts events per weekday

Why it matters:

• Finds peak activity days

Key insight:
👉 Optimize campaigns by day

---

🔹 9. Full Funnel Conversion

What it does:
Calculates:

• View → Add to Cart
• Add to Cart → Checkout
• Checkout → Purchase
• View → Purchase

Why it matters:

• Complete conversion funnel analysis

Key insight:
👉 Detect where users drop off

---

🔹 10. Cart Abandonment Rate (by Traffic Source)

What it does:

• Calculates:
  (cart - purchase) / cart

Why it matters:

• Identifies lost revenue opportunities

Key insight:
👉 High abandonment = UX or pricing issues

---

🔹 11. Checkout Completion Rate

What it does:

• Ratio:
  completed checkout / started checkout

Why it matters:

• Evaluates checkout process efficiency

Key insight:
👉 Detect friction in checkout

---

🔹 12. Time to Purchase

What it does:

• Calculates average hours between:
  • product view → purchase

Why it matters:

• Measures decision time

Key insight:
👉 Helps plan remarketing timing

---

🔹 13. Monthly Revenue

What it does:

• Aggregates revenue by month/year

Why it matters:

• Tracks financial performance

Key insight:
👉 Identify growth or seasonality

---

🔹 14. Top Products

What it does:

• Top 5 products by:
  • revenue
  • purchase count

Why it matters:

• Identifies best sellers

Key insight:
👉 Focus on high-performing products

---

🔹 15. AOV (Average Order Value) + Growth

What it does:

• Calculates monthly AOV
• Computes % change vs previous month

Why it matters:

• Measures customer spending trends

Key insight:
👉 Are customers spending more over time?

---

🔹 16. Revenue Contribution by Traffic Source

What it does:

• Calculates cumulative revenue share

Why it matters:

• Identifies top revenue drivers

Key insight:
👉 Pareto analysis (80/20 rule)

---

🔹 17. Repeat Purchase Behavior

What it does:

• Finds users with multiple purchases
• Compares to total purchases

Why it matters:

• Measures customer loyalty

Key insight:
👉 Retention vs one-time buyers

---

🔹 18. Customer Lifetime Value (LTV)

What it does:

• Calculates:
  • AOV
  • lifespan
  • purchase frequency
• Formula:
  LTV = AOV × lifespan × purchase frequency

Why it matters:

• Most important business metric

Key insight:
👉 Who are your most valuable customers?

---

🔹 19. Newsletter Impact

What it does:

• Compares:
  • Avg purchase after newsletter signup
  • Overall avg purchase

Why it matters:

• Measures marketing effectiveness

Key insight:
👉 Does email marketing increase revenue?

---

🔹 20. Session Analysis & Correlation

What it does:

• Builds sessions (30 min inactivity rule)
• Calculates:
  • Avg events per session
  • Correlation between activity & purchase

Why it matters:

• Measures engagement vs conversion

Key insight:
👉 More interaction → higher chance of purchase?

---

🔥 Overall Summary

This SQL file covers:

• Traffic analysis
• Conversion funnel
• User behavior
• Revenue analytics
• Retention & LTV
• Session behavior

👉 Basically: end-to-end e-commerce analytics dashboard in SQL