Integration Guide

How to Sync Referral Data to Your Data Warehouse with Segment + GrowSurf

Name: GrowSurf
Rating: 4.74 (47 reviews)
Author: GrowSurf

Load GrowSurf referral event data into your data warehouse via Segment for advanced analytics and reporting.

Your data warehouse is where the deepest referral program insights live. While real-time dashboards show you what's happening now, warehouse-powered analytics reveal long-term patterns: which referral channels produce the highest-LTV customers, how referral quality changes over time, and what your true referral program ROI looks like when you account for all costs.

This guide covers configuring Segment to load GrowSurf referral data into your data warehouse (BigQuery, Snowflake, Redshift, or Databricks), structuring referral tables for efficient querying, and building SQL-based analytics that answer the strategic questions your referral program team needs answered.

Integration Steps

Step 1: Enable Your Warehouse Destination in Segment

Connect your data warehouse to Segment so referral events are automatically loaded.

Go to Segment > Connections > Destinations > Add Destination
Select your warehouse: BigQuery, Snowflake, Redshift, or Databricks
Provide connection credentials (project ID, dataset, credentials for BigQuery; account, warehouse, database for Snowflake)
Configure sync schedule (default: every 1 hour for most warehouses)
Enable the destination and verify the initial sync

Step 2: Understand the Warehouse Schema

Segment creates tables in your warehouse automatically based on your events and identifies.

Tables created by Segment:
- tracks — all Track events (includes referral events)
- identifies — all Identify calls (includes referral user traits)
- referral_program_joined — auto-created table for this specific event
- referral_made — auto-created table for referral events
- referral_converted — auto-created table for conversion events
Each event table contains: user_id, timestamp, and all event properties as columns

Step 3: Build Core Referral Analytics Queries

Write SQL queries against your warehouse tables to answer key referral program questions.

Query referral conversion funnel: participants → active referrers → conversions
Calculate referral program ROI: total revenue from referred customers vs. total rewards paid
Identify top referrer profiles: demographics, usage patterns, and referral success rates
Track referral program metrics over time: monthly referral volume, conversion rate trends

Step 4: Create Referral Cohort Analysis

Build cohort queries that track referred customer behavior over time compared to non-referred customers.

Define cohorts by referral month (when the referral was made)
Track retention: what percentage of referred customers are still active after 3, 6, 12 months?
Compare referred vs. organic customer LTV at each month mark
Identify which referral sources produce the highest-quality customers

Step 5: Build a Referral Attribution Model

Create a data model that attributes downstream revenue to the original referral for complete ROI tracking.

Join referral events with purchase/subscription events on user_id
Calculate total revenue generated by each referred customer over their lifetime
Attribute that revenue back to the original referrer
Factor in reward costs to calculate net referral value

Step 6: Connect Your BI Tool for Visualization

Connect a BI tool to your warehouse to create referral program dashboards and scheduled reports.

Popular BI tools: Looker, Metabase, Tableau, Mode, Apache Superset
Create dashboards with: referral funnel visualization, LTV comparison charts, top referrer leaderboards, monthly trend lines
Schedule automated reports to stakeholders: weekly referral performance, monthly ROI analysis
Set up alerts for anomalies: sudden drop in referral volume or conversion rate

Code Snippets

-- Core Referral Analytics SQL Queries

-- 1. Referral Funnel by Month
SELECT
  DATE_TRUNC('month', rpj.timestamp) AS month,
  COUNT(DISTINCT rpj.user_id) AS participants_joined,
  COUNT(DISTINCT rm.user_id) AS made_referral,
  COUNT(DISTINCT rc.user_id) AS referral_converted,
  ROUND(COUNT(DISTINCT rm.user_id)::decimal /
    NULLIF(COUNT(DISTINCT rpj.user_id), 0) * 100, 1) AS referral_rate_pct,
  ROUND(COUNT(DISTINCT rc.user_id)::decimal /
    NULLIF(COUNT(DISTINCT rm.user_id), 0) * 100, 1) AS conversion_rate_pct
FROM referral_program_joined rpj
LEFT JOIN referral_made rm ON rpj.user_id = rm.user_id
LEFT JOIN referral_converted rc ON rm.user_id = rc.referrer_email
GROUP BY 1
ORDER BY 1 DESC;

-- 2. Top Referrers by Revenue Impact
SELECT
  rm.referrer_email,
  COUNT(DISTINCT rm.user_id) AS total_referrals,
  COUNT(DISTINCT rc.user_id) AS converted_referrals,
  SUM(p.amount) AS total_referred_revenue,
  SUM(p.amount) / NULLIF(COUNT(DISTINCT rc.user_id), 0) AS avg_revenue_per_referral
FROM referral_made rm
LEFT JOIN referral_converted rc ON rm.user_id = rc.user_id
LEFT JOIN purchases p ON rc.user_id = p.user_id
GROUP BY 1
ORDER BY total_referred_revenue DESC
LIMIT 20;

-- 3. Referred vs Organic Customer LTV Comparison
SELECT
  CASE WHEN i.referred_by IS NOT NULL THEN 'Referred' ELSE 'Organic' END AS acquisition_type,
  COUNT(DISTINCT i.user_id) AS customers,
  AVG(customer_ltv.total_spend) AS avg_ltv,
  AVG(customer_ltv.months_active) AS avg_months_active
FROM identifies i
LEFT JOIN (
  SELECT user_id, SUM(amount) AS total_spend,
    COUNT(DISTINCT DATE_TRUNC('month', timestamp)) AS months_active
  FROM purchases GROUP BY 1
) customer_ltv ON i.user_id = customer_ltv.user_id
GROUP BY 1;

Tips

Optimize Table Partitioning for Referral Queries

Partition your referral event tables by date (month or day) in your warehouse. Referral analytics queries almost always filter by time period, and partitioned tables dramatically reduce query costs and execution time — especially as your data grows.

Create a Denormalized Referral Fact Table

Join your referral events into a single denormalized table that combines the referrer, referred person, conversion status, and revenue. This makes ad-hoc querying much faster and simpler than joining multiple tables every time you run an analysis.

Set Up Incremental Models with dbt

If you use dbt for data transformation, create incremental models for your referral tables. This processes only new events since the last run, keeping transformation costs low even as your referral program scales to millions of events.

FAQ

How much data warehouse storage do referral events consume?

Referral events are relatively low-volume compared to product usage events. A program generating 1,000 referrals/month would add roughly 10-50 MB of warehouse data per month. Even at scale, referral data is a tiny fraction of your overall warehouse storage.

How often does Segment sync data to the warehouse?

Segment syncs to most warehouses every 1-2 hours by default. For BigQuery and Snowflake, you can configure more frequent syncing. This means warehouse-based referral dashboards have a 1-2 hour lag — use Segment's real-time destinations (like Mixpanel or Amplitude) for up-to-the-minute metrics.

Can I backfill historical GrowSurf referral data into my warehouse?

Yes. Use GrowSurf's API to export historical participant and referral data, then use Segment's HTTP Source API or direct warehouse loading to backfill historical events. Structure the backfilled data to match your Segment event schema so it integrates seamlessly with ongoing event data.

GrowSurf for Startups Program

Startups get a 50% discount

GrowSurf Deals

Get $234k+ In Software Savings

LoyaltySurf

Reward Your Customers For Their Loyalty

Startup Growth Tactics Quiz

How to Sync Referral Data to Your Data Warehouse with Segment + GrowSurf

Integration Steps

Step 1: Enable Your Warehouse Destination in Segment

Step 2: Understand the Warehouse Schema

Step 3: Build Core Referral Analytics Queries

Step 4: Create Referral Cohort Analysis

Step 5: Build a Referral Attribution Model

Step 6: Connect Your BI Tool for Visualization

Code Snippets

Tips

Optimize Table Partitioning for Referral Queries

Create a Denormalized Referral Fact Table

Set Up Incremental Models with dbt

FAQ

How much data warehouse storage do referral events consume?

How often does Segment sync data to the warehouse?

Can I backfill historical GrowSurf referral data into my warehouse?

Unlock the power of word-of-mouth marketing anddeepen your bond with customers

Set up your refer a friend program with customer referral and affiliate program software that lowers your acquisition costs, increases customer loyalty, and saves you gobs of time.

Built for startups,scaled for unicorns

View GrowSurfCustomer Examples

Unlock the power of word-of-mouth marketing and
deepen your bond with customers

Built for startups,
scaled for unicorns

View GrowSurf
Customer Examples