CUSTOMER DATA INFRASTRUCTURE
The missing link between BigQuery and Klaviyo
Klaviyo's power is in its ecommerce data model — Placed Order events with $value and Items arrays, RFM profile properties, list-based winback flows. But Klaviyo's primary key is email, your BigQuery has `customer_id`s, and your order line items live in nested ARRAY fields that Klaviyo can't consume directly. Meiro Pipes resolves the identity gap, flattens nested BigQuery fields in the transform layer rather than via expensive SQL, translates your RFM scores and purchase history into Klaviyo's exact schema, and keeps your warehouse and email platform in sync — without custom ETL.
Free trial · No credit card · Live in minutes
Every ecommerce team with BigQuery hits the same wall with Klaviyo.
Identity is the first problem. Klaviyo's primary key is email. BigQuery order tables are keyed on customer_id from your OMS or ecommerce platform, and records may also carry Firebase IDs or Salesforce account IDs that are even further removed from a Klaviyo email. Resolving those identifiers requires a maintained identity step that generic connectors don't provide. When it fails, profile updates land on the wrong person or silently create duplicates.
Klaviyo's Placed Order Metric requires $value as a number, an Items array with specific keys (ProductName, SKU, Quantity, ItemPrice), and order-level metadata in a fixed structure. BigQuery adds a structural problem on top: line items are often stored as ARRAY fields that must be flattened before reaching Klaviyo's schema. BigQuery also bills per byte scanned, making full-table-scan change detection both an engineering liability and a direct GCP cost.
Klaviyo prices by active profile count: syncing properties to suppressed profiles can reactivate them in Klaviyo's billing model — teams find out on the invoice. Building winback campaigns from RFM scores requires two coordinated operations: updating the rfm_segment profile property and subscribing customers to the right Klaviyo list. Standard connectors don't handle both together.
Five ways the BigQuery → Klaviyo pipeline breaks
Identity mismatch
Problem
Klaviyo uses email as its primary profile key. BigQuery has customer_id from your OMS or ecommerce platform. Profile updates either land on the wrong person, create duplicate profiles, or fail silently when the email resolution fails.
Meiro solves it
Pipes resolves identity across email, customer_id, account_id, Shopify customer ID, and any other identifier — using deterministic matching with configurable merge limits. RFM scores and purchase history reach the correct Klaviyo profile every time.
Nested ARRAY order data
Problem
BigQuery order line items live in ARRAY fields. Klaviyo's Placed Order event requires a flat Items array with specific key names and $value as a number. Unnesting in BigQuery SQL (UNNEST, ARRAY_AGG) scans more bytes — directly increasing your BigQuery bill.
Meiro solves it
Pipes transform functions handle ARRAY flattening and property key remapping in the JavaScript sandbox. Your BigQuery query stays simple and cheap. The transform layer constructs Klaviyo's Items array, coerces string prices to numbers, and calculates $value — without expensive SQL.
Per-byte billing on sync queries
Problem
BigQuery charges per byte scanned. Full-table or wide date-range scans — how most reverse ETL tools detect changes — cost money on every sync run. On large order tables, this billing impact compounds over time.
Meiro solves it
Pipes uses partition-aware and watermark-based queries designed for BigQuery's billing model. DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) scoped queries. Only changed records are processed. Your sync runs lean.
Profile property volume and cost
Problem
Klaviyo charges by active profile count. Syncing profile properties to suppressed or inactive profiles can reactivate them in Klaviyo's billing model, causing unexpected cost increases. Teams discover this after the invoice arrives.
Meiro solves it
Pipes lets you model which profile properties to sync and to which audience segments before the data reaches Klaviyo. Update active customers' RFM scores without reactivating suppressed profiles you don't intend to mail.
RFM campaign architecture
Problem
Building winback campaigns from BigQuery RFM scores requires both a profile property sync (to set rfm_segment on the Klaviyo profile) and a list membership sync (to add qualifying customers to the Klaviyo list that triggers the flow). Neither step works without the other.
Meiro solves it
Pipes handles profile property sync and list membership sync as a unified operation. Model RFM segments in BigQuery. Pipes sets the rfm_segment profile property and subscribes qualifying customers to the correct Klaviyo list — in the right order, with the correct API calls.
One pipeline. Identity-resolved. Schema-aware.
Collect from Klaviyo
Klaviyo engagement data — email opens, clicks, conversions, Placed Order confirmations — flows into Pipes via webhook or export. Events land without replacing your existing Klaviyo setup.
Load & Model in BigQuery
Events land in BigQuery automatically. Pipes connects directly — browse order tables, customer records, BQML or SQL-derived RFM score outputs. Join with product catalog data or ERP records. BigQuery stays your source of truth for purchase intelligence.
Resolve Identity
Pipes stitches profiles across Klaviyo email, BigQuery customer_id, Shopify customer ID, and any other order-system identifier. Deterministic matching with configurable limits. Purchase history and RFM scores reach the right Klaviyo profile.
Activate Back to Klaviyo
Enriched profiles push back to Klaviyo with correctly formatted Placed Order events, typed profile properties, and list membership changes — via the correct Klaviyo API endpoints. Nested ARRAY fields flattened in the transform layer. Scheduled or real time.
Use case: RFM-powered winback campaign from BigQuery purchase history
Your ecommerce brand processes all orders through BigQuery. Your data team has built an RFM scoring model — either using BigQuery ML or pure SQL — that scores every customer on recency, frequency, and monetary value, updated weekly. Customers with high monetary value but low recency are your highest-priority winback targets.
You want Klaviyo to trigger a winback flow when a customer's RFM segment changes from active to at-risk. Order line items in your BigQuery table live in a nested ARRAY field.
Without Meiro: You'd write a BigQuery job that uses UNNEST to flatten the line items ARRAY (scanning additional bytes), resolves Klaviyo email from customer_id via a join, constructs a Placed Order event with $value as a float and an Items array with the correct Klaviyo key names, calls the Klaviyo profile update API to set rfm_segment, and separately subscribes them to the winback list. Every sync scans the full table. Any schema change breaks the job.
With Meiro Pipes: RFM scores are modeled as Klaviyo profile properties in Meiro. A partition-aware BigQuery query using DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) fetches only changed records efficiently. The Pipes transform flattens the line items ARRAY and constructs Klaviyo's Items format in the JavaScript sandbox. Pipes resolves customer_id to Klaviyo email using the identity graph. The rfm_segment property updates on the Klaviyo profile. Customers whose segment changed to at-risk are subscribed to the winback list. The Klaviyo flow triggers automatically.
Time from updated RFM model to live Klaviyo winback flow: hours, not sprints.
Pipes speaks Klaviyo's schema so your BigQuery doesn't have to
Your BigQuery table
SELECT
o.customer_id,
c.email,
o.order_id,
CAST(o.order_total AS FLOAT64) AS order_total,
o.ordered_at,
c.rfm_segment,
CAST(c.lifetime_value AS FLOAT64) AS lifetime_value,
DATE_DIFF(CURRENT_DATE(), DATE(o.ordered_at), DAY)
AS days_since_purchase
FROM `project.orders` o
JOIN `project.customers` c
ON c.customer_id = o.customer_id
WHERE o.ordered_at > DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)Pipes transform
// Pipes send function (Event Destination)
async function send(payload, headers) {
return payload.events.map(row => ({
data: {
type: 'profile',
attributes: {
email: row.email,
properties: {
rfm_segment: row.rfm_segment,
lifetime_value: parseFloat(row.lifetime_value),
days_since_purchase: row.days_since_purchase
}
}
}
}));
}What Klaviyo receives
{
"data": {
"type": "profile",
"attributes": {
"email": "[email protected]",
"properties": {
"rfm_segment": "champions",
"lifetime_value": 2840.50,
"days_since_purchase": 12
}
}
}
}No custom API client. No ARRAY unnesting in BigQuery SQL. Pipes handles nested field flattening, type coercion, revenue calculation, identity resolution, and delivery — and adapts when your BigQuery order table schema changes.
The cost of bolting it together
The standard stack
- Custom ETL job — query BigQuery with backtick table refs, resolve Klaviyo email from `customer_id`, batch API calls
- `UNNEST` and ARRAY_AGG to flatten line items in BigQuery SQL — scans extra bytes on every sync
- Full-table change-detection scans — every sync run bills against BigQuery quota
- Manual Placed Order schema construction: $value type, Items array, correct property key names
- No identity resolution — `customer_id` to email fails silently, wrong profile updated
- Risk of reactivating suppressed profiles during sync — unexpected billing increase
- Breaks on every order table schema change
Meiro Pipes
- Native connectors for Klaviyo and BigQuery
- Partition-aware queries — change detection without full table scans
- Nested ARRAY flattening in the transform layer — no expensive BigQuery SQL unnesting
- Deterministic identity matching across email, `customer_id`, Shopify ID, CRM ID
- Correct Placed Order schema — $value as number, Items array with correct keys
- RFM profile property sync + Klaviyo list membership in one unified operation
- Sync-scope control: update active profiles without reactivating suppressed ones
A reverse ETL tool syncs rows. It doesn't resolve `customer_id` to Klaviyo email, flatten nested BigQuery ARRAY fields without driving up your bytes-scanned bill, or manage list membership alongside profile properties. Meiro Pipes does all of that.
One platform. Two problems solved.
For the Ecommerce Marketer
You want to build Klaviyo winback flows, loyalty campaigns, and retention sequences using the full purchase intelligence your data team has built in BigQuery — RFM scores, BQML-derived LTV predictions, product affinity — not just the events Klaviyo has collected.
- Describe the audience you need — Piper builds it
- RFM segment, predicted LTV, purchase frequency — all available as Klaviyo profile properties
- Winback list sync happens automatically as RFM segments change
- Build flows on complete purchase intelligence, not just recent Klaviyo events
- Control which profiles receive property updates — no unexpected billing increases
For the Data Engineer
You're tired of maintaining the BigQuery → Klaviyo pipeline. The `customer_id`-to-email resolution. The ARRAY unnesting that costs bytes. The Placed Order schema construction that breaks when the order table changes.
- Connect BigQuery and Klaviyo once — Pipes handles schema translation
- Nested ARRAY flattening in the JavaScript sandbox, not in expensive BigQuery SQL
- Partition-aware change detection — only sync what changed
- Identity resolution across email, `customer_id`, Shopify customer ID, CRM ID
- CI/CD-native config management via mpcli — version-control your pipeline
Under the hood
Klaviyo Event Destination
Native connector. Pushes profile properties and Metric events (Placed Order, Started Checkout, Viewed Product, custom events) to Klaviyo in the exact API format. Handles $value type enforcement, Items array serialization, and ISO 8601 timestamp formatting.
BigQuery Connector
Direct warehouse connection supporting backtick-quoted table references, DATE_SUB, DATE_DIFF, CAST, and FLOAT64 type handling. Browse order tables, customer records, BQML model outputs. Map identifier columns to Meiro identity types.
Identity Resolution
Deterministic stitching across Klaviyo email, BigQuery customer_id, Shopify customer ID, account_id, and any other order-system identifier. Configurable merge limits. Purchase history and RFM scores reach the correct Klaviyo profile every time.
Transform Sandbox
Sandboxed JavaScript functions for schema translation. Flatten BigQuery ARRAY line items into Klaviyo's Items format. Construct Placed Order events with correct property keys and types. Coerce string prices to numbers. 47 allowlisted packages available.
Reverse ETL / Profile Sync (Customer Studio)
Scheduled or real-time Live Profile Sync. Partition-aware change detection to minimize BigQuery bytes scanned. Push RFM scores, predicted LTV, and purchase history to Klaviyo profile properties. Sync-scope control to avoid reactivating suppressed profiles.
List Membership Sync
Model BigQuery-derived RFM segments as Klaviyo list memberships. Pipes computes membership deltas and issues the correct list subscribe/unsubscribe calls — coordinated with profile property updates so winback flows trigger correctly.
Why connecting BigQuery and Klaviyo requires more than a connector
Identity is the first. Klaviyo's primary key is email. BigQuery order tables are keyed on customer_id from your OMS, ERP, or ecommerce platform. Resolving customer_id to a Klaviyo email for every record in every sync requires building and maintaining an identity resolution step. When this resolution fails, profile updates land on the wrong customer or create duplicate profiles.
BigQuery's nested schema model is the second problem. Order line items in BigQuery typically live in ARRAY fields — repeated records with nested properties. Klaviyo's Placed Order Metric requires a flat Items array with specific property key names (ProductName, SKU, Quantity, ItemPrice) and $value as a numeric type. Flattening BigQuery ARRAY fields using UNNEST and ARRAY_AGG in SQL means scanning more bytes, which directly increases per-query costs. The transform layer is the right place to handle this.
Per-byte billing is the third problem. BigQuery charges per byte scanned. Full-table or wide date-range scans — how most reverse ETL connectors detect changes — cost money on every sync run. For high-frequency syncs on large order tables, this billing impact is significant.
Profile property volume and billing is the fourth problem. Klaviyo's pricing is based on active profiles. Syncing profile properties to suppressed or inactive profiles can reactivate them in Klaviyo's billing model, causing unexpected cost increases.
The RFM campaign architecture is the fifth dimension. The highest-value use case — building winback campaigns from warehouse-derived RFM scores — requires two synchronized operations: updating the rfm_segment profile property, and subscribing the customer to the Klaviyo list that triggers the campaign flow. Both steps need the correctly resolved Klaviyo email. Neither can run independently.
Stop debugging the pipeline. Start activating the data.
Connect BigQuery and Klaviyo through Meiro Pipes. Identity-resolved. Ecommerce-aware. Bidirectional. Start free.