Week 4 - Advanced GA4 - Audiences, Predictive Analytics & BigQuery Mastery

Name: Cart Abandoners - 7D
Membership: Last 7 days

Conditions:
- Event: add_to_cart (at least once)
- Event: purchase (NOT happened)
- Within: Last 7 days

Use case: High-intent remarketing
Expected size: 2-5% of traffic
Conversion rate: 15-25%

Name: Product Viewers - No Purchase - 14D
Membership: Last 14 days

Conditions:
- Event: view_item (at least once)
- Event: add_to_cart (NOT happened)
- Event: purchase (NOT happened)
- Within: Last 14 days

Use case: Move from consideration to cart
Expected size: 10-20% of traffic

Name: Purchasers - Last 30D
Membership: Last 30 days

Conditions:
- Event: purchase (at least once)
- Within: Last 30 days

Use case:
- Upsell/cross-sell
- Exclude from acquisition campaigns
- VIP treatment

Name: High Intent Visitors - 7D
Membership: Last 7 days

Conditions:
Include users who meet ANY:
- Event: view_item (3+ times)
- Event: add_to_cart (1+ times)
- Event: begin_checkout (1+ times)
- Event: page_view (5+ pages)
- Session duration: >180 seconds

Use case: Aggressive remarketing
Expected conversion rate: 8-12%

Name: Newsletter Subscribers - Engaged
Membership: Last 90 days

Conditions:
- Event: sign_up (method = 'newsletter')
- Event: page_view (3+ times since signup)

Use case:
- Content promotion
- Product launches
- Loyalty campaigns

Name: Recent Converters - 30D
Membership: Last 30 days

Conditions:
- Event: purchase (1+ times)
- Within: Last 30 days

Use case: EXCLUDE from acquisition campaigns
Ad spend saved: 10-20%

Name: Bounced Visitors - 7D
Membership: Last 7 days

Conditions:
- Session engaged: = 0
- Pages per session: = 1
- Session duration: <10 seconds

Use case: Exclude from remarketing
Low intent, high cost

Name: VIP Customers - High LTV
Membership: Last 540 days (18 months)

Conditions:
Include users who meet ANY:
- Purchase count: ≥ 3
- Total revenue: ≥ $500
- Event: purchase (last 60 days)

Use case:
- Exclusive offers
- Early access
- Premium support
- Loyalty rewards

Name: Feature Adopters - [Feature Name]
Membership: Last 30 days

Conditions:
- Event: feature_used (parameter: feature_name = 'advanced_reporting')
- Event count: ≥ 5 times

Use case:
- Upsell to higher tier
- Case study candidates
- Beta testers
- Product feedback

Name: Content Enthusiasts - 30D
Membership: Last 30 days

Conditions:
- Event: page_view (parameter: page_type = 'blog')
- Event count: ≥ 3
- Session duration: ≥ 120 seconds

Use case:
- Newsletter promotion
- Webinar invitations
- Lead magnets
- Content upgrades

1. Admin → Product Links → Google Ads
2. Enable "Personalized advertising"
3. Select audiences to share
4. Wait 24-48 hours for population
5. Create remarketing campaigns

Campaign structure:
- Campaign 1: Cart Abandoners (high bid, aggressive)
- Campaign 2: Product Viewers (medium bid)
- Campaign 3: High Intent (medium bid)
- Campaign 4: Content Engaged (low bid, awareness)

Cart Abandoners: 7 days (urgency)
Product Viewers: 14 days (consideration window)
Past Purchasers: 30-180 days (depends on purchase cycle)
High Intent: 7 days (strike while hot)
VIP Customers: 540 days (max allowed)

Metric: Purchase probability
Meaning: Likelihood user will purchase in next 7 days
Range: 0-100%

Create audience:
Name: Likely Purchasers - 7D
Condition: Purchase probability ≥ 50%

Use case:
- Proactive outreach
- Personalized offers
- Aggressive remarketing
- Premium ad placement

Metric: Churn probability
Meaning: Likelihood user will NOT purchase again in next 7 days
Range: 0-100%

Create audience:
Name: Likely Churners - 7D
Condition: Churn probability ≥ 50%

Use case:
- Win-back campaigns
- Special offers
- Customer success outreach
- Survey for feedback

Metric: Predicted revenue
Meaning: Expected revenue from user in next 28 days
Range: $0 - $X

Create audience:
Name: High Value Potential - 28D
Condition: Predicted 28-day revenue ≥ $100

Use case:
- VIP treatment
- Premium customer service
- Upsell opportunities
- Account-based marketing

Name: Hot Prospects - Ready to Buy
Membership: Last 7 days

Conditions:
- Purchase probability: ≥ 60%
- Event: purchase (NOT happened in last 30 days)

Result: Users highly likely to buy for first time
Campaign: Aggressive acquisition, slight discount

Name: At-Risk VIPs
Membership: Last 7 days

Conditions:
- Churn probability: ≥ 50%
- Lifetime revenue: ≥ $500

Result: Valuable customers about to leave
Campaign: Urgent win-back, personal outreach

Name: Expansion Opportunities
Membership: Last 30 days

Conditions:
- Predicted 28-day revenue: ≥ $200
- Event: feature_used (advanced features)
- Subscription tier: = 'pro'

Result: Users ready for enterprise upgrade
Campaign: Sales outreach, enterprise demo

Explore → Funnel Exploration

Steps:
1. view_item (Baseline: 100%)
2. add_to_cart (Typical: 40%)
3. begin_checkout (Typical: 60% of cart)
4. purchase (Typical: 70% of checkout)

Add comparison:
- New vs Returning users
- Mobile vs Desktop
- Paid vs Organic traffic

Insights:
- Which segment converts best?
- Where's the biggest drop-off?
- Device-specific issues?

Explore → Path Exploration

Starting point: add_to_cart
Ending point: purchase

Shows:
- Most common path
- Drop-off points
- Alternative journeys
- Time to conversion

Use case:
- Identify friction
- Optimize checkout flow
- Understand user behavior

Explore → Cohort Exploration

Cohort by: Week (first visit)
Return: Week 1, 2, 3, 4 after first visit
Metric: Active users

Shows:
- How many users return?
- Which cohorts are stickiest?
- Product-market fit signal

By traffic source:
- Which channels have best retention?
- Paid vs organic retention

Explore → Cohort Exploration

Cohort by: Month (first purchase)
Metric: Total revenue
Time range: 12 months

Shows:
- LTV by acquisition month
- Seasonal patterns
- Campaign effectiveness over time

Example insight:
"Users acquired in Q4 have 30% higher LTV than Q2"

Explore → Segment Overlap

Segments:
- Mobile users
- Purchasers
- Newsletter subscribers
- High engagement (5+ pages)

Shows:
- Overlap between segments
- Unique to each segment
- Combination opportunities

Example insight:
"Mobile users who subscribe convert 2x"

1. Create Google Cloud Project
   - Go to console.cloud.google.com
   - Create new project

2. Enable BigQuery API
   - APIs & Services → Enable APIs
   - Search "BigQuery API"
   - Enable

3. Link GA4 to BigQuery
   - GA4 Admin → Product Links → BigQuery
   - Link → Choose project
   - Select:
     ☑ Daily export (free up to 1M events/day)
     ☐ Streaming export (costs money)

4. Wait 24 hours
   - First export happens next day
   - Dataset: analytics_<property_id>
   - Tables: events_YYYYMMDD

SELECT  PARSE_DATE('%Y%m%d', event_date) AS date,
  COUNT(DISTINCT user_pseudo_id) AS daily_active_users
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20251231'GROUP BY dateORDER BY date DESC

SELECT  traffic_source.source,
  traffic_source.medium,
  traffic_source.name AS campaign,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END) AS purchasers,
  SUM(CASE WHEN event_name = 'purchase' THEN ecommerce.purchase_revenue END) AS revenue,
  ROUND(SUM(CASE WHEN event_name = 'purchase' THEN ecommerce.purchase_revenue END) /
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id END), 2) AS avg_order_value
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  AND event_name = 'purchase'GROUP BY source, medium, campaign
HAVING revenue > 0ORDER BY revenue DESC

WITH funnel AS (
  SELECT    user_pseudo_id,
    MIN(CASE WHEN event_name = 'page_view' THEN event_timestamp END) AS step1_time,
    MIN(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) AS step2_time,
    MIN(CASE WHEN event_name = 'begin_checkout' THEN event_timestamp END) AS step3_time,
    MIN(CASE WHEN event_name = 'purchase' THEN event_timestamp END) AS step4_time
  FROM `project.dataset.events_*`
  WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY user_pseudo_id
)
SELECT  COUNT(DISTINCT user_pseudo_id) AS total_users,
  COUNT(DISTINCT CASE WHEN step1_time IS NOT NULL THEN user_pseudo_id END) AS step1_users,
  COUNT(DISTINCT CASE WHEN step2_time IS NOT NULL THEN user_pseudo_id END) AS step2_users,
  COUNT(DISTINCT CASE WHEN step3_time IS NOT NULL THEN user_pseudo_id END) AS step3_users,
  COUNT(DISTINCT CASE WHEN step4_time IS NOT NULL THEN user_pseudo_id END) AS step4_users,
  -- Conversion rates  ROUND(COUNT(DISTINCT CASE WHEN step2_time IS NOT NULL THEN user_pseudo_id END) * 100.0 /
    COUNT(DISTINCT CASE WHEN step1_time IS NOT NULL THEN user_pseudo_id END), 2) AS step1_to_2_rate,
  -- Time to convert (seconds)  ROUND(AVG(CASE WHEN step4_time IS NOT NULL AND step1_time IS NOT NULL
    THEN (step4_time - step1_time) / 1000000 END), 0) AS avg_seconds_to_purchase
FROM funnel

SELECT  item.item_id,
  item.item_name,
  item.item_category,
  item.item_brand,
  SUM(item.quantity) AS total_quantity_sold,
  ROUND(SUM(item.item_revenue), 2) AS total_revenue,
  ROUND(AVG(item.price), 2) AS avg_price,
  COUNT(DISTINCT user_pseudo_id) AS unique_purchasers
FROM `project.dataset.events_*`,
  UNNEST(items) AS item
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  AND event_name = 'purchase'GROUP BY item.item_id, item.item_name, item.item_category, item.item_brand
ORDER BY total_revenue DESCLIMIT 50

WITH first_touch AS (
  SELECT    user_pseudo_id,
    FIRST_VALUE(traffic_source.source) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp ASC    ) AS first_source,
    FIRST_VALUE(traffic_source.medium) OVER (
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp ASC    ) AS first_medium
  FROM `project.dataset.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
),
purchases AS (
  SELECT    user_pseudo_id,
    SUM(ecommerce.purchase_revenue) AS total_revenue
  FROM `project.dataset.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND event_name = 'purchase'  GROUP BY user_pseudo_id
)
SELECT  ft.first_source,
  ft.first_medium,
  COUNT(DISTINCT ft.user_pseudo_id) AS total_users,
  COUNT(DISTINCT p.user_pseudo_id) AS purchasers,
  ROUND(SUM(p.total_revenue), 2) AS total_revenue,
  ROUND(SUM(p.total_revenue) / COUNT(DISTINCT ft.user_pseudo_id), 2) AS revenue_per_user
FROM first_touch ft
LEFT JOIN purchases p ON ft.user_pseudo_id = p.user_pseudo_id
GROUP BY ft.first_source, ft.first_medium
ORDER BY total_revenue DESC NULLS LAST

Trigger: Schedule (every morning)
↓
BigQuery: Query yesterday's revenue
↓
Compare: Revenue vs 7-day average
↓
Condition: If >15% difference
↓
Slack: Send alert to #marketing channel

Benefit: Catch issues immediately

Trigger: Webhook from GA4 (cart_abandoned event)
↓
Delay: 1 hour
↓
Check: Did user purchase? (GA4 Measurement Protocol)
↓
If NO → Send email 1: "You left something in your cart"
↓
Delay: 24 hours
↓
If still NO → Send email 2: "10% off your cart"
↓
Delay: 48 hours
↓
If still NO → Send email 3: "Last chance - expires today"

Benefit: Recover 10-15% of abandoned carts

Trigger: Schedule (Monday morning)
↓
BigQuery: Run 5 key queries
  - Revenue by source
  - Top products
  - Conversion rate
  - New vs returning
  - AOV trend
↓
Google Sheets: Update dashboard
↓
Looker Studio: Refresh report
↓
Email: Send PDF to stakeholders

Benefit: Save 2-3 hours/week

Trigger: Schedule (daily)
↓
GA4 Reporting API: Export audience members
  - VIP Customers
  - High Intent Visitors
  - At-Risk Churners
↓
Match: Email/User ID
↓
HubSpot/Salesforce: Update contact properties
  - GA4_Segment: "VIP"
  - Last_Engagement: Date
  - Purchase_Probability: 75%
↓
CRM: Trigger automations based on segments

Benefit: Sales team knows who to prioritize