User Journey Data Architecture

This document describes how user data flows through Eli Health's systems, from website visit and email engagement through purchase to biometric measurements.

High-Level Data Flow

Marketing Funnel Metrics (December 2025)

Stage	Count	Conversion
Klaviyo Profiles	36,832	100%
Linked to LoopWork	34,579	94%
Downloaded App	2,238	6.1%
Completed Test	1,636	4.4%

Top Performing Flows (by Test Completion Rate)

Flow	Recipients	Completed Test	Rate
BETA // Welcome Eli Beta	1,135	657	57.9%
DA | Post-Purchase	1,115	614	55.1%
PP V2 | Welcome + App setup	184	104	56.5%
IN | Welcome Flow	12,538	384	3.1%

Email Click to Test Completion Timing

Time Window	Users	Percentage
0-7 days	262	31.8%
8-30 days	242	29.4%
31-90 days	124	15.0%
90+ days	63	7.6%

Key Insight: 61% of email clickers complete a test within 30 days.

Website Visitor Journey (GA4 → Shopify → App)

This is the primary acquisition flow: a website visitor discovers Eli Health, makes a purchase, and becomes an app user.

Key Linking Points

From	To	Join Key	Match Rate
GA4 purchase	Shopify order	transaction_id = order.id	~94%
Shopify customer	Firebase user	email (case-insensitive)	~94%
Firebase user	Biometrics user	uid = accountId	100%

Marketing Attribution

With GA4 synced to the same BigQuery region, we can directly JOIN to answer:

• Which marketing channels drive the most revenue?
• What is the cost per acquisition (CAC) by channel?
• How many sessions does it take before a user purchases?
• What pages do users visit before converting?

See GA4-Shopify Data Linking for full details. The live Marketing Dashboard is available at eli-kpi/marketing?range=30.

Data Systems Overview

System	Purpose	Key Identifier	BigQuery Dataset
GA4	Website analytics	user_pseudo_id, transaction_id	analytics_ga4
Klaviyo	Email marketing	profile.email, profile.$loop_external_customer_id	eli_health_klaviyo
Shopify	E-commerce	customer_id, order.id, email	eli_health_shopify
LoopWork	Subscriptions	customer.shopify_id, subscription_id	eli_health_loopwork
Firebase	App authentication	uid, email	firebase_users
Biometrics	Hormone data	user.accountId (=Firebase uid)	eli_health_biometricspublic

Linking Keys Between Systems

Primary Linkage Path

Key Field Mappings

From	To	Link Field
Klaviyo Profile	Shopify Customer	$loop_external_customer_id = customer_id
Klaviyo Profile	LoopWork Customer	$loop_customer_id = customer.id
LoopWork Customer	Shopify	customer.shopify_id = customer_id
Firebase User	Biometrics User	uid = user.accountId
Klaviyo Profile	Firebase User	email = email (fuzzy)

Known Limitation: Email Mismatch

The Disconnect: Users may purchase on Shopify with one email address but log into the mobile app with a different email address.

Example:
  Shopify purchase:  john.doe@work.com
  App login:         johnd@gmail.com

  Result: Cannot automatically link purchase → app usage

Current Coverage: ~94% of Klaviyo profiles have Shopify IDs, but linking to Firebase requires email matching which may fail for users with different emails.

Future Solution: Shopify integration in mobile app to link accounts.

BigQuery Schema Reference

eli_health_klaviyo (Airbyte Schema)

Airbyte Schema (December 2025)

Klaviyo data is now synced via Airbyte instead of custom eli-kpi sync. The schema changed:

• data column → attributes and relationships columns
• _synced_at → _airbyte_extracted_at
• Segments replaced with campaigns table

Table	Key Fields	Join Keys
profiles	id, attributes.email, attributes.properties.$loop_external_customer_id	Shopify customer_id
events	id, relationships.metric.data.id, relationships.profile.data.id, attributes.event_properties	Campaign Name, Subject
metrics	id, attributes.name	Event type definitions
flows	id, attributes.name, attributes.status, attributes.trigger_type	Automated email sequences
campaigns	id, attributes.name, attributes.status	Email campaigns
lists	id, attributes.name	Subscriber lists

Data Volume (December 2025):

• 36,856 profiles (93.8% linked to Shopify)
• 2.85M events
• 116 campaigns
• 43 flows
• 27 lists
• 115 metrics

eli_health_loopwork

Table	Key Fields	Join Keys
subscription	id, customer_id, status, product	subscription_id
customer	id, shopify_id, email	Shopify customer_id
order	id, subscription_id, order_number	Shopify order
product	id, shopify_product_id, title	Product catalog

eli_health_biometricspublic

Table	Key Fields	Join Keys
user	id, accountId, email, created	Firebase uid
reading	id, userId, status, timeZone, created	User reading attempts
record	id, userId, startDate, result, metadata	Hormone measurements

firebase_users

Table	Key Fields	Join Keys
auth_users	uid, email, created	Firebase uid

Common Query Patterns

Link Klaviyo Email to Biometrics User (Airbyte Schema)

-- Find biometrics user from Klaviyo email
SELECT
  JSON_EXTRACT_SCALAR(k.attributes, '$.email') as klaviyo_email,
  f.uid as firebase_uid,
  u.id as biometrics_user_id,
  COUNT(r.id) as total_readings
FROM `eli-health-prod.eli_health_klaviyo.profiles` k
JOIN `eli-health-prod.firebase_users.auth_users` f
  ON LOWER(JSON_EXTRACT_SCALAR(k.attributes, '$.email')) = LOWER(f.email)
JOIN `eli-health-prod.eli_health_biometricspublic.user` u
  ON f.uid = u.accountId
LEFT JOIN `eli-health-prod.eli_health_biometricspublic.reading` r
  ON u.id = r.userId AND r.status = 'completed'
WHERE u.deleted IS NULL
GROUP BY 1, 2, 3

Flow Performance with Real Conversions

-- Which flows actually drive test completions?
WITH flow_recipients AS (
  SELECT DISTINCT
    JSON_EXTRACT_SCALAR(e.attributes, "$.event_properties['$flow']") as flow_id,
    JSON_EXTRACT_SCALAR(e.relationships, '$.profile.data.id') as profile_id
  FROM `eli-health-prod.eli_health_klaviyo.events` e
  JOIN `eli-health-prod.eli_health_klaviyo.metrics` m
    ON JSON_EXTRACT_SCALAR(e.relationships, '$.metric.data.id') = m.id
  WHERE JSON_EXTRACT_SCALAR(m.attributes, '$.name') = 'Received Email'
    AND JSON_EXTRACT_SCALAR(e.attributes, "$.event_properties['$flow']") IS NOT NULL
),
profile_emails AS (
  SELECT id, LOWER(JSON_EXTRACT_SCALAR(attributes, '$.email')) as email
  FROM `eli-health-prod.eli_health_klaviyo.profiles`
),
test_completers AS (
  SELECT DISTINCT u.accountId
  FROM `eli-health-prod.eli_health_biometricspublic.user` u
  JOIN `eli-health-prod.eli_health_biometricspublic.reading` r ON u.id = r.userId
  WHERE r.status = 'completed' AND u.deleted IS NULL
)
SELECT
  JSON_EXTRACT_SCALAR(f.attributes, '$.name') as flow_name,
  COUNT(DISTINCT fr.profile_id) as emails_sent,
  COUNT(DISTINCT CASE WHEN tc.accountId IS NOT NULL THEN fr.profile_id END) as completed_test,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN tc.accountId IS NOT NULL THEN fr.profile_id END) /
    NULLIF(COUNT(DISTINCT fr.profile_id), 0), 1) as test_conversion_rate
FROM flow_recipients fr
JOIN `eli-health-prod.eli_health_klaviyo.flows` f ON fr.flow_id = f.id
JOIN profile_emails pe ON fr.profile_id = pe.id
LEFT JOIN `eli-health-prod.firebase_users.auth_users` fu ON pe.email = fu.email
LEFT JOIN test_completers tc ON fu.uid = tc.accountId
GROUP BY 1
ORDER BY completed_test DESC

Track Email → Purchase → First Test Journey (Airbyte Schema)

WITH email_received AS (
  -- When did user receive their first email?
  SELECT
    JSON_EXTRACT_SCALAR(p.attributes, '$.email') as email,
    MIN(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%S',
      SUBSTR(JSON_EXTRACT_SCALAR(e.attributes, '$.datetime'), 1, 19))) as first_email
  FROM `eli-health-prod.eli_health_klaviyo.profiles` p
  JOIN `eli-health-prod.eli_health_klaviyo.events` e
    ON JSON_EXTRACT_SCALAR(e.relationships, '$.profile.data.id') = p.id
  GROUP BY 1
),
first_purchase AS (
  -- When did they make first purchase?
  SELECT LOWER(JSON_EXTRACT_SCALAR(data, '$.email')) as email,
         MIN(PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%S',
           SUBSTR(JSON_EXTRACT_SCALAR(data, '$.createdAt'), 1, 19))) as first_purchase
  FROM `eli-health-prod.eli_health_loopwork.customers`
  WHERE JSON_EXTRACT_SCALAR(data, '$.email') IS NOT NULL
  GROUP BY 1
),
first_test AS (
  -- When did they complete first biometric test?
  SELECT
    LOWER(f.email) as email,
    MIN(r.created) as first_test
  FROM `eli-health-prod.firebase_users.auth_users` f
  JOIN `eli-health-prod.eli_health_biometricspublic.user` u ON f.uid = u.accountId
  JOIN `eli-health-prod.eli_health_biometricspublic.reading` r ON u.id = r.userId
  WHERE r.status = 'completed'
  GROUP BY 1
)
SELECT
  er.email,
  er.first_email,
  fp.first_purchase,
  ft.first_test,
  TIMESTAMP_DIFF(fp.first_purchase, er.first_email, DAY) as days_email_to_purchase,
  TIMESTAMP_DIFF(ft.first_test, fp.first_purchase, DAY) as days_purchase_to_test
FROM email_received er
LEFT JOIN first_purchase fp ON LOWER(er.email) = fp.email
LEFT JOIN first_test ft ON LOWER(er.email) = ft.email
WHERE fp.first_purchase IS NOT NULL
ORDER BY er.first_email DESC

Sync Schedule

Data Source	Sync Frequency	Time (UTC)	Method
GA4	Daily	6:00 AM	Cloud Function (cross-region)
LoopWork	Daily	4:00 AM	eli-kpi sync
Klaviyo	Daily	5:00 AM	Airbyte
Shopify	Daily	Configurable	Airbyte
Firebase	Real-time	Continuous	Native replication
Biometrics	Real-time	Continuous	Datastream CDC

Data Freshness

• GA4 Events: Synced daily, typically 1 day behind (GA4 exports previous day's data)
• Klaviyo Events: Last 90 days (configurable)
• LoopWork: Full sync on each run
• Shopify: Incremental sync via Airbyte
• Firebase/Biometrics: Real-time via BigQuery replication

---

Last Updated: January 2026