LoopWork Data Integration

Overview

Loop Subscriptions (LoopWork) is Eli Health's subscription management platform that runs on top of Shopify. This document describes how we synchronize subscription data from Loop to BigQuery for analytics and reporting.

Why LoopWork Data Sync?

Unlike Shopify (which uses Airbyte), Loop Subscriptions requires a custom synchronization solution because:

• Airbyte doesn't have a native Loop Subscriptions connector
• Loop provides rich subscription lifecycle data not available in Shopify
• We need incremental sync to minimize API calls and respect rate limits
• Subscription analytics require joining Loop data with Shopify and Firebase data

What Data is Synced?

Resource	Description	Data Points
Subscriptions	All subscription contracts	Status, billing/delivery policies, line items, discounts, customer references
Customers	Subscription customers	Contact info, subscription counts by status
Orders	Subscription-related orders	Order details, pricing, fulfillment status
Products	Subscription products	Product variants, pricing, SKUs

Architecture

API Configuration

Base URL & Authentication

Setting	Value
Base URL	https://api.loopsubscriptions.com/admin/2023-10/
Authentication Header	x-loop-token
Token Source	Loop Admin Portal → Settings → Manage API tokens

Available Endpoints

Endpoint	HTTP Method	Rate Limit	Description
/subscription	GET	2 req/3s	List all subscriptions
/customer	GET	1 req/s	List all customers
/order	GET	2 req/3s	List all orders
/product	GET	6 req/s	List products with variants

Rate Limits

Loop uses fixed-window rate limiting with endpoint-specific pools:

• Global Pool: 10 requests per second (applies to endpoints without specific limits)
• Endpoint-Specific Limits: See table above (isolated from global pool)

Best Practices:

• Implement separate rate limiters for each endpoint
• Add jitter to requests to avoid synchronized bursts
• Handle HTTP 429 responses with exponential backoff

Pagination

All list endpoints use page-based pagination:

{
  "success": true,
  "message": "Subscriptions fetched successfully",
  "data": [...],
  "code": "SUCCESS",
  "pageInfo": {
    "hasNextPage": true,
    "hasPreviousPage": false
  }
}

Parameter	Type	Description
pageNo	int32	Page number (starts from 1)
pageSize	int32	Items per page (max 50)

BigQuery Dataset

Dataset Configuration

Setting	Value
Project	eli-health-prod
Dataset ID	eli_health_loopwork
Location	northamerica-northeast1 (Montreal)

Important: The dataset location matches eli_health_shopify to enable cross-dataset joins.

Table Schemas

subscriptions

Column	Type	Description
id	INT64	Loop subscription ID
shopify_id	INT64	Shopify subscription contract ID
origin_order_shopify_id	INT64	Original order ID
status	STRING	ACTIVE, PAUSED, CANCELLED, EXPIRED
created_at	TIMESTAMP	Subscription creation time
updated_at	TIMESTAMP	Last update time
cancelled_at	TIMESTAMP	Cancellation time (if cancelled)
cancellation_reason	STRING	Reason for cancellation
currency_code	STRING	Currency (CAD, USD, etc.)
total_line_item_price	FLOAT64	Total price before discounts
delivery_price	FLOAT64	Shipping cost
completed_orders_count	INT64	Number of fulfilled orders
is_prepaid	BOOL	Whether subscription is prepaid
last_payment_status	STRING	SUCCESS, FAILED, etc.
next_billing_date_epoch	INT64	Next billing timestamp
billing_policy	JSON	Interval, count, anchor settings
delivery_policy	JSON	Delivery interval settings
shipping_address	JSON	Customer shipping address
lines	JSON	Line items with products
discounts	JSON	Applied discounts
customer_id	INT64	Loop customer ID
customer_shopify_id	INT64	Shopify customer ID
_synced_at	TIMESTAMP	Sync timestamp

customers

Column	Type	Description
id	INT64	Loop customer ID
shopify_id	INT64	Shopify customer ID
first_name	STRING	First name
last_name	STRING	Last name
email	STRING	Email address
phone	STRING	Phone number
active_subscriptions_count	INT64	Active subscription count
paused_subscriptions_count	INT64	Paused subscription count
cancelled_subscriptions_count	INT64	Cancelled subscription count
all_subscriptions_count	INT64	Total subscription count
_synced_at	TIMESTAMP	Sync timestamp

orders

Column	Type	Description
id	INT64	Loop order ID
shopify_id	INT64	Shopify order ID
shopify_order_number	INT64	Human-readable order number
status	STRING	PROCESSED, PENDING, etc.
financial_status	STRING	paid, refunded, etc.
fulfillment_status	STRING	Fulfillment state
billing_date_epoch	INT64	Billing timestamp
currency_code	STRING	Currency code
total_price	FLOAT64	Total order price
total_price_usd	FLOAT64	Price in USD
total_discount	FLOAT64	Total discounts
total_shipping_price	FLOAT64	Shipping cost
total_tax	FLOAT64	Tax amount
shopify_created_at	TIMESTAMP	Shopify creation time
shopify_updated_at	TIMESTAMP	Shopify update time
shipping_address	JSON	Shipping address
discount_codes	JSON	Applied discount codes
lines	JSON	Order line items
subscription_id	INT64	Related subscription ID
customer_id	INT64	Loop customer ID
_synced_at	TIMESTAMP	Sync timestamp

products

Column	Type	Description
shopify_id	INT64	Shopify product ID
title	STRING	Product title
variants	JSON	Product variants with SKUs, prices
_synced_at	TIMESTAMP	Sync timestamp

Sync Implementation

Sync Strategy: Incremental with Full Refresh

The sync uses an incremental strategy with periodic full refresh:

1. Hourly Incremental Sync: Fetch records updated since last sync using updated_at timestamp
2. Daily Full Refresh: Complete sync of all records to catch any missed updates
3. Upsert Logic: Records are upserted based on primary key (id)

Sync Endpoint in eli-kpi

The sync job is implemented as a Cloud Run endpoint in the eli-kpi service:

POST /sync/loopwork

Trigger: Cloud Scheduler job running every hour

Implementation Steps

Phase 1: BigQuery Dataset Setup

-- Create dataset in Montreal region (same as Shopify data)
CREATE SCHEMA IF NOT EXISTS `eli-health-prod.eli_health_loopwork`
OPTIONS (
  location = 'northamerica-northeast1',
  description = 'Loop Subscriptions data synchronized from LoopWork API'
);

Phase 2: Secret Management

1. Add token to Google Secret Manager:

gcloud secrets create loopwork-api-token \
  --project=eli-health-prod \
  --replication-policy="automatic"

echo -n "YOUR_LOOPWORK_TOKEN" | gcloud secrets versions add loopwork-api-token --data-file=-

2. Grant access to KPI service account:

gcloud secrets add-iam-policy-binding loopwork-api-token \
  --project=eli-health-prod \
  --member="serviceAccount:kpi-service-us@eli-health-prod.iam.gserviceaccount.com" \
  --role="roles/secretmanager.secretAccessor"

3. Add to GitHub Secrets for deploy.yml:
   • Add LOOPWORK_API_TOKEN to eli-kpi repository secrets
   • Update deploy.yml to include: --set-env-vars="LOOPWORK_API_TOKEN=${{ secrets.LOOPWORK_API_TOKEN }}"

Phase 3: Sync Service Implementation

Create /src/loopwork-sync.ts in eli-kpi with:

• API client with rate limiting
• Pagination handling
• BigQuery upsert logic
• Error handling and retries

Phase 4: Cloud Scheduler Setup

Note: The Cloud Scheduler job is now managed by Terraform in eli-devops/tf/modules/regional/kpi-compute/main.tf. Do not create it manually with gcloud.

Terraform Resource:

# eli-devops/tf/modules/regional/kpi-compute/main.tf
resource "google_cloud_scheduler_job" "loopwork_sync" {
  name             = "loopwork-hourly-sync"
  description      = "Hourly sync of Loop Subscriptions data to BigQuery"
  schedule         = "0 * * * *"  # Every hour at minute 0
  time_zone        = "America/Toronto"
  attempt_deadline = "600s"
  region           = var.gcp.location  # us-east1

  retry_config {
    retry_count          = 3
    min_backoff_duration = "30s"
    max_backoff_duration = "300s"
    max_doublings        = 3
  }

  http_target {
    http_method = "POST"
    uri         = "${google_cloud_run_v2_service.kpi.uri}/sync/loopwork"

    oidc_token {
      service_account_email = google_service_account.kpi.email
      audience              = google_cloud_run_v2_service.kpi.uri
    }
  }
}

To apply: Run terraform apply in eli-devops/tf with the appropriate environment.

Legacy gcloud command (for reference only)

gcloud scheduler jobs create http loopwork-hourly-sync \
  --project=eli-health-prod \
  --location=us-east1 \
  --schedule="0 * * * *" \
  --uri="https://kpi.app.eli.health/sync/loopwork" \
  --http-method=POST \
  --oidc-service-account-email=kpi-service-us@eli-health-prod.iam.gserviceaccount.com \
  --oidc-token-audience="https://kpi.app.eli.health"

Current Data Volume

As of November 2025:

Resource	Record Count	Estimated Sync Time
Subscriptions	~2,200	~70 seconds
Customers	~2,000+	~40 seconds
Orders	~1,500+	~50 seconds
Products	~20	~1 second

Total estimated sync time: ~3 minutes for full refresh

Monitoring & Maintenance

Monitoring

1. Cloud Run Logs: Check sync job logs for errors
2. Cloud Scheduler Console: Verify job execution history
3. BigQuery: Monitor _synced_at timestamps for freshness

Key Metrics to Track

• Sync job success rate
• Sync duration
• Record count changes
• API error rates

Common Issues

Rate Limit Errors (HTTP 429)

Symptoms: Sync fails with 429 status codes

Resolution:

1. Check rate limiter implementation
2. Add more delay between requests
3. Implement exponential backoff

Stale Data

Symptoms: BigQuery data older than expected

Resolution:

1. Check Cloud Scheduler job status
2. Verify Cloud Run service is healthy
3. Check API token validity

Schema Drift

Symptoms: New fields in API response not in BigQuery

Resolution:

1. Update BigQuery table schema
2. Modify sync code to handle new fields
3. Re-run full sync

Querying LoopWork Data

Example: Subscription Status Distribution

SELECT
  status,
  COUNT(*) as count,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER(), 2) as percentage
FROM `eli-health-prod.eli_health_loopwork.subscriptions`
GROUP BY status
ORDER BY count DESC;

Example: Join with Shopify Orders

-- Link Loop subscriptions to Shopify order details
SELECT
  l.id as loop_subscription_id,
  l.status as subscription_status,
  s.id as shopify_order_id,
  s.total_price as shopify_total,
  s.created_at as order_date
FROM `eli-health-prod.eli_health_loopwork.subscriptions` l
JOIN `eli-health-prod.eli_health_shopify.orders` s
  ON l.origin_order_shopify_id = s.id
WHERE l.status = 'ACTIVE'
ORDER BY s.created_at DESC
LIMIT 100;

Example: Customer Subscription Analytics

-- Active customers with their subscription value
SELECT
  c.email,
  c.first_name,
  c.last_name,
  c.active_subscriptions_count,
  SUM(s.total_line_item_price) as total_subscription_value
FROM `eli-health-prod.eli_health_loopwork.customers` c
JOIN `eli-health-prod.eli_health_loopwork.subscriptions` s
  ON c.id = s.customer_id
WHERE c.active_subscriptions_count > 0
GROUP BY 1, 2, 3, 4
ORDER BY total_subscription_value DESC
LIMIT 50;

Security Considerations

Token Management

• Never commit tokens: Store in Secret Manager or GitHub Secrets
• Rotate regularly: Update token quarterly
• Minimum scope: Only request necessary API permissions

Data Protection

• Encryption at rest: BigQuery encrypts all data by default
• Access control: Use BigQuery IAM to restrict access
• PII handling: Customer data should be accessed only by authorized personnel

Related Documentation

• Airbyte Data Integration - Shopify to BigQuery sync
• BigQuery Access - Data warehouse permissions
• DevOps Overview - Infrastructure overview

Support & Contact

For issues with LoopWork integration:

1. Check Logs: Cloud Run logs in GCP Console
2. API Issues: Contact Loop support at support@loopwork.co
3. Internal Contact: DevOps team at Eli Health

---

Last Updated: November 2025 Maintained By: Chip (chip@eli.health) API Version: 2023-10