The project usually starts with a screenshot. Someone in support pastes three CRM records into a channel, all carrying the same person’s name, and asks which one is real. One has the order history. One has the subscription. The third has four tickets attached and no orders at all.<\/p>\n\n\n\n
Nobody broke anything. Shopify, Stripe, the CRM and the helpdesk each created a customer record using whatever identifier they had at the moment they needed one, and none of those identifiers agreed. The systems are all correct in isolation. The person is the thing that got lost.<\/p>\n\n\n\n
This post covers what actually breaks when you unify Shopify, Stripe, CRM and support data, and how to build the join so it survives guest checkouts, duplicate webhooks, refunds, currency conversion and deletion requests. It’s organized by failure family rather than by tool, because the tools get acquired and renamed every couple of years and the failures never change.<\/p>\n\n\n\n
Every one of these systems will happily give you an email address, which is exactly why so many pipelines join on it and quietly produce garbage.<\/p>\n\n\n\n
Start with where identity actually lives in each system:<\/p>\n\n\n\n
Here’s the part that stays invisible until someone finally checks: nothing errors. Every dashboard still returns a number. Lifetime value gets silently split across three profiles, so your LTV looks lower and your repeat-purchase rate looks worse than it is. Support metrics look better<\/em> than they are, because a customer coming back with the same unresolved problem shows up as a brand new contact. You can run like that for a year without a single failed job.<\/p>\n\n\n\n The fix is boring and it works: a surrogate person key, and a separate table of every identifier ever observed for that person, with the source that reported it.<\/p>\n\n\n\n Normalization happens before the row is written, not in the join. Lowercase and trim emails, store phone numbers in E.164, strip whitespace from external IDs. Resist the urge to get clever with Gmail’s dot and plus handling. Stripping Deterministic matching first, always. Exact matches on shared identifiers, transitively closed. Probabilistic matching on name plus address plus device is worth reaching for only when you have exhausted the deterministic keys, and it has an asymmetric failure mode: a household sharing one card gets collapsed into one person, and unmerging afterwards is expensive because every downstream system has already copied the merged key into its own records. Merges are cheap to make and painful to undo. Bias toward leaving two records separate.<\/p>\n\n\n\n One thing worth doing early on the Stripe side: when you create a Customer, write your own identifier into Both platforms are explicit about this, and both get treated as if they weren’t.<\/p>\n\n\n\n Stripe delivers at least once, does not guarantee ordering, and retries failed deliveries with exponential backoff for up to three days. The event For a customer-data pipeline specifically, out-of-order delivery is nastier than duplication. Consider a Verify the signature, claim the event ID, write the raw body to durable storage, return 2xx. That’s it. Identity resolution, CRM lookups and enrichment calls all belong in a worker reading from that store. A handler that tries to resolve identity inline will eventually be slow enough to time out, which triggers a retry, which arrives while the first attempt is still running.<\/p>\n\n\n\n Make the claim atomic rather than check-then-act, because two retries genuinely can land concurrently:<\/p>\n\n\n\n Return a 2xx for a duplicate. A duplicate is a success from the sender’s point of view, and returning an error just schedules more of them.<\/p>\n\n\n\n Signature verification has one recurring trap on the Shopify side: it runs against the raw<\/em> body. Any JSON body parser that runs first will change the bytes and every signature will fail. Capture the raw body before parsing, and compare in constant time.<\/p>\n\n\n\n Worth knowing before you spend an afternoon on it: after rotating an app’s client secret, Shopify notes it can take up to an hour before signatures are generated with the new one. If verification starts failing right after a rotation, that’s usually why.<\/p>\n\n\n\n And because delivery isn’t guaranteed, webhooks alone are never enough. Run a reconciliation job that pulls orders and customers modified since the last watermark and fills the gaps. This is the job that saves you during an outage on your side, and it’s the one people skip. If you’d rather not build the buffering and replay layer, managed webhook infrastructure like Hookdeck exists for exactly this, and self-hosting a small queue and worker on a modest VPS from somewhere like Contabo or InterServer is perfectly reasonable too. The point is that something has to catch what the webhook missed.<\/p>\n\n\n\n This is the one that turns into a meeting. Finance says the number is wrong. Analytics says the number is right. Both are correct, because they’re measuring different things and nobody wrote down which.<\/p>\n\n\n\n There’s a structural trap here too. If the store runs Shopify Payments, there is no Stripe account for you to query. Shopify Payments is built on Stripe’s processing infrastructure, but it’s Shopify’s gateway, and payouts, fees and disputes live in the Shopify admin rather than behind a Stripe API key. Plenty of businesses end up with Shopify Payments handling storefront orders and a separate Stripe account handling subscriptions or invoicing, which means two payment systems, two payout schedules and one customer who has no idea any of this exists.<\/p>\n\n\n\n Do not build a single Store every timestamp in UTC and keep the original timezone as a separate column. Store every amount in minor units as an integer alongside its currency code, and store the FX rate that was actually applied rather than recalculating it later from a rates table. Recalculated FX is the single most common source of “the number moved and nobody changed anything.”<\/p>\n\n\n\n On ingestion, Stripe Data Pipeline can push Stripe data directly into Snowflake, Redshift or Databricks as a data share, which removes a connector from the picture if you’re already on one of those. Otherwise a managed ELT tool like Fivetran or a self-hosted Airbyte will handle Shopify, Stripe, the CRM and the helpdesk with off-the-shelf connectors. Both routes are fine. The connector is genuinely the easy part.<\/p>\n\n\n\n If you’re building this as a Shopify app, three compliance webhooks are mandatory: The interesting problem isn’t receiving the webhook. It’s that by the time it arrives, that person’s email exists in your raw landing zone, your staging tables, your identity table, your customer view, a BI extract, the CRM, the helpdesk, and a Slack message from a debugging session six months ago. A delete that only touches the golden record is not a delete.<\/p>\n\n\n\n The design decision that makes this tractable is to hold personal data in exactly one place. Every downstream table carries Practical pieces that make it real:<\/p>\n\n\n\n A customer view nobody can act on is a report. The value shows up when a support agent opens a ticket and can see subscription status and lifetime order count without switching tabs.<\/p>\n\n\n\n That’s reverse ETL, and the market is well served: Hightouch, Census (now part of Fivetran), Polytomic, RudderStack, and iPaaS-style tools like Workato if you need branching logic rather than field syncs. The warehouse-native argument is straightforward and mostly correct: your models already live there, and you avoid maintaining a second copy of the customer profile inside a vendor’s rigid schema. The counter-argument is also real. Reverse ETL is batch by nature, so if you need sub-minute reaction to an event, a packaged CDP or a direct event pipeline will serve you better, and you’ll be running several vendors plus warehouse compute plus the people to maintain the models.<\/p>\n\n\n\n Three things go wrong here more or less every time.<\/p>\n\n\n\n The write loop.<\/strong> You compute a churn-risk field and sync it into the CRM. Your ELT connector reads the CRM back into the warehouse. Now the computed field is an input to the model that computes it. This is genuinely hard to spot because nothing errors, the value just drifts. Namespace every synced field with a consistent prefix and explicitly exclude that namespace from ingestion.<\/p>\n\n\n\n The resync stampede.<\/strong> Incremental syncs push diffs and stay small. Change a model definition and the next run wants to update every record, which will meet a helpdesk API rate limit at speed. Cap the batch size, and treat a full resync as a planned operation rather than something a merge to main can trigger.<\/p>\n\n\n\n Syncing logic instead of keys.<\/strong> Push Put together, the order of operations matters more than the tool choice:<\/p>\n\n\n\n These are the ones that come up repeatedly, and the check that usually settles them.<\/p>\n\n\n\n Not necessarily. If you already run a warehouse and have someone comfortable with dbt, ELT plus modelling plus reverse ETL covers the same ground with more control over the data model. A packaged CDP earns its cost when you need real-time behavioural segmentation, when nobody on the team can maintain models, or when marketing needs to ship changes without waiting on data engineering.<\/p>\n\n\n\n Create a person from the order’s email anyway, and mark the record as identity-weak. When that email later appears attached to a real Shopify customer or Stripe Customer object, the deterministic match links them and the order history follows. The mistake is discarding guest orders because they have no customer ID, which is exactly the revenue you most want attributed.<\/p>\n\n\n\n Keep the full picture in the warehouse and sync a deliberately small set of fields outward. The test is whether an agent or a rep will act on the field during a conversation. Lifetime value, subscription status, open ticket count and churn risk pass. A hundred-column profile does not, and it turns every model change into a rate-limit incident.<\/p>\n\n\n\n Because they’re measuring different things at different times. Shopify shows gross sales at order time; Stripe pays out batched amounts net of fees, refunds and disputes, and the timing differs by days. Add currency conversion and a timezone mismatch and the numbers will never tie without an explicit bridging table linking payouts to the transactions inside them.<\/p>\n\n\n\n You can get away with it longer, but it’s still a bad primary key. People change email addresses, use one address for orders and another for support, and share addresses within a household. A surrogate key with an identifier table costs very little up front and means an email change is a new row rather than a broken join and a duplicate customer.<\/p>\n\n\n\n Run it in shadow mode. Compute person keys without activating them, then have support pick twenty customers they know are duplicates and twenty they know are distinct, and check what the resolver did. That exercise finds normalization bugs and over-eager merge rules faster than any aggregate metric, because the people who answer the tickets already know where the duplicates are.<\/p>\n\n\n\n Raw landing with deduplication, an identifier table with deterministic matching, one fact table per source, and a single synced field into the helpdesk. That’s a couple of weeks of work and it removes most of the pain. Everything above it is refinement.<\/p>\n\n\n\n If you take one thing from this: the hard part of unifying Shopify, Stripe, CRM and support data is not moving the data. Connectors are commodity. The hard part is deciding what a customer is<\/em>, writing that decision down as a key you own, and then refusing to let any downstream system quietly invent its own version of it.<\/p>\n\n\n\n Do the identity work first, land everything raw so you can reprocess when you get it wrong, and keep personal data in one place so deletion stays a single operation. The dashboards will follow. Skip that order and you get exactly the screenshot this post opened with, eighteen months later and with a lot more rows behind it.<\/p>\n\n\n\n I work on exactly this kind of plumbing: the unglamorous layer between an ecommerce platform, a payment processor and the tools your team actually opens all day. Things I can help with here:<\/p>\n\n\n\n If you’re stuck on a specific piece, send me the concrete thing: a webhook payload, a schema dump, the query that disagrees with your Stripe payout report. It’s much easier to say something useful about a real artifact than about a diagram.<\/p>\n\n\n\nBuild an identifier table, not a fuzzy match<\/h3>\n\n\n\n
-- One row per real human, generated by you, owned by you.\nCREATE TABLE person (\n person_key uuid PRIMARY KEY,\n created_at timestamptz NOT NULL DEFAULT now(),\n merged_into uuid NULL REFERENCES person(person_key)\n);\n\n-- One row per identifier per source. This is the whole trick.\nCREATE TABLE person_identifier (\n person_key uuid NOT NULL REFERENCES person(person_key),\n source text NOT NULL, -- shopify | stripe | crm | helpdesk\n id_type text NOT NULL, -- email | phone | shopify_customer_id | stripe_customer_id\n id_value text NOT NULL, -- already normalized\n first_seen_at timestamptz NOT NULL,\n last_seen_at timestamptz NOT NULL,\n PRIMARY KEY (source, id_type, id_value)\n);<\/code><\/pre>\n\n\n\n+tags<\/code> is a business policy decision with real consequences, not a technical detail, and the same rule applied to a domain that treats plus addresses as distinct mailboxes will merge two genuinely different people.<\/p>\n\n\n\nmetadata<\/code>. Stripe allows fifty key-value pairs per object, with keys up to forty characters and values up to five hundred, and it only returns metadata for requests made with a secret key. That’s enough room for a person key and an order reference, which turns a future fuzzy join into an exact one. Never put anything sensitive there.<\/p>\n\n\n\nFailure family two: webhooks arrive twice, out of order, or not at all<\/h2>\n\n\n\n
id<\/code> (the one starting evt_<\/code>) stays identical across every retry, which makes it your deduplication key. Shopify sends X-Shopify-Webhook-Id<\/code> for the same purpose, signs the raw body into X-Shopify-Hmac-Sha256<\/code>, and states plainly that ordering isn’t guaranteed within a topic or across topics for the same resource. Shopify’s own documentation goes further and tells you not to rely on webhooks as your only source of data.<\/p>\n\n\n\ncustomers\/update<\/code> that changes someone’s email arriving before the orders\/create<\/code> that was placed under the old address. Process those in arrival order and you attach the order to a person who doesn’t exist yet, or worse, to the wrong one. Order your writes by the event’s own timestamp, using X-Shopify-Triggered-At<\/code> or the updated_at<\/code> in the payload, and make profile updates last-writer-wins on that timestamp rather than on insertion time.<\/p>\n\n\n\nDo almost nothing in the handler<\/h3>\n\n\n\n
-- Atomic claim. If this inserts zero rows, someone already has it.\nINSERT INTO webhook_event (source, event_id, topic, received_at, payload)\nVALUES ($1, $2, $3, now(), $4)\nON CONFLICT (source, event_id) DO NOTHING\nRETURNING event_id;<\/code><\/pre>\n\n\n\nimport base64, hashlib, hmac\n\ndef verify_shopify(raw_body: bytes, header_value: str, client_secret: str) -> bool:\n digest = hmac.new(\n client_secret.encode(\"utf-8\"),\n raw_body,\n hashlib.sha256,\n ).digest()\n expected = base64.b64encode(digest).decode(\"utf-8\")\n return hmac.compare_digest(expected, header_value)<\/code><\/pre>\n\n\n\nFailure family three: “revenue” means four different things<\/h2>\n\n\n\n
\n
revenue<\/code> column. Build separate fact tables at their natural grain (orders, order line items, payments, refunds, disputes, payouts) plus a bridging table that links a payout back to the transactions inside it. Then let each team define its own metric on top. Finance gets net settled cash. Marketing gets gross booked revenue. Neither has to be wrong for the other to be right.<\/p>\n\n\n\nFailure family four: deletion is the requirement nobody models<\/h2>\n\n\n\n
customers\/data_request<\/code>, customers\/redact<\/code> and shop\/redact<\/code>. You acknowledge with a 200 and complete the action within thirty days. shop\/redact<\/code> fires forty-eight hours after uninstall and covers the shop’s data. These are enforced at app review, so you’ll find out about them either way.<\/p>\n\n\n\nperson_key<\/code> and nothing else. Facts, aggregates, models, extracts and syncs all reference the key. When a redaction request lands, you redact one row set in the identifier table and the rest of the warehouse is already anonymous. Getting this right at schema-design time costs an afternoon; retrofitting it costs a quarter.<\/p>\n\n\n\n\n
Failure family five: pushing the unified view back out<\/h2>\n\n\n\n
person_key<\/code> into a custom field on the CRM contact and the helpdesk user. It makes the key searchable by agents, it gives the next pipeline a deterministic join, and it stops three teams from each rebuilding their own version of your matching rules.<\/p>\n\n\n\nA pipeline shape that holds up<\/h2>\n\n\n\n
\n
person_key<\/code>, as a scheduled job, never inside a request handler.<\/li>\n\nperson_key<\/code>, with no raw PII.<\/li>\n\ndbt_customer360<\/code> package is a reasonable starting point if you want to see how someone else structured the identity step.<\/li>\n\nTroubleshooting: symptom to root cause<\/h2>\n\n\n\n
\n
person<\/code> rows have exactly one identifier and one order.<\/li>\n\nCommon mistakes<\/h2>\n\n\n\n
\n
revenue<\/code> column that finance and marketing are both told to use.<\/li>\n\nBest practices worth the effort<\/h2>\n\n\n\n
\n
person_key<\/code> into Stripe metadata<\/code> and Shopify customer metafields at creation time, so future joins are exact rather than inferred.<\/li>\n\nFAQ<\/h2>\n\n\n\n
Do I need a CDP to unify Shopify, Stripe, CRM and support data?<\/h3>\n\n\n\n
How do I handle guest checkout customers?<\/h3>\n\n\n\n
Should I sync everything into the CRM or keep it in the warehouse?<\/h3>\n\n\n\n
Why don’t my Shopify sales match my Stripe payouts?<\/h3>\n\n\n\n
Can I use email as the primary key if my store requires account creation?<\/h3>\n\n\n\n
How do I test the identity resolution before trusting it?<\/h3>\n\n\n\n
What’s the minimum viable version of this?<\/h3>\n\n\n\n
\n\n\n\nConclusion: unify Shopify, Stripe, CRM and support data around a key you own<\/h2>\n\n\n\n
Need help wiring this together?<\/h2>\n\n\n\n
\n