Connecting Yotpo to Snowflake

Architecture

How Yotpo and Snowflake actually connect

Two systems that know nothing about each other. Everything below exists to give them one shared reference.

1. Step 1

   Yotpo holds the truth

   The flag is what matters. A redeemed order is revenue that a retention program produced, and crediting it to the paid click that acquired the customer eighteen months ago overstates that campaign badly.

2. Step 2

   Snowflake needs a click to match

   A schema both sides agree on, consistent types, and identifiers normalised identically before hashing. The failure mode is not rejection but silent divergence — a timestamp in the wrong zone or a trimmed-versus-untrimmed email produces answers that look plausible.

3. Step 3

   The identifier is the bridge

   Whatever join key both parties agree on, usually a hashed identifier, captured at the visit and carried into the Yotpo loyalty redemption. Without it there is nothing to join on and no tool fixes that.

4. Step 4

   The event source decides reliability

   Send purchases with a channel attribute distinguishing first orders, loyalty-driven repeats and referral-driven orders. Three different economics, three different owners.

5. Step 5

   Deduplication at the destination

   Nothing deduplicates for you. Loads are at-least-once, so a retried pipeline creates duplicate rows and the only protection is a deduplication step you write and actually schedule.

Diagnose it honestly

Working vs. broken, side by side

Both states look identical from the Snowflake dashboard, which is why this runs for months before anyone notices.

Diagnostic sequence

Diagnostic order

Each step rules out a class of cause. Run them in order and the last one usually has a single explanation standing.

1. 1

   Count redemptions carrying whatever join key both parties agree on, usually a hashed identifier

   The ceiling on everything else. Do this before debugging anything downstream.

2. 2

   Follow one real conversion end to end

   From the ad click through Yotpo and into Snowflake.

3. 3

   Count orders carrying a reward or referral code

   Then check whether any of them appear in your paid conversion figures. If they do, the acquisition numbers are inflated by exactly that amount.

4. 4

   Write down both parties' conversion definitions before writing any query

   Definition mismatch, not technology, is what makes most partner measurement projects produce numbers neither side trusts.

5. 5

   Read the match rate, not the success message

   Snowflake will not tell you anything is wrong. Verify by querying against each party's own totals and checking for duplicate rows on the join key before trusting any aggregate.

Source-of-truth validation

How it gets proven

Against Yotpo's own records, not against either platform's dashboard.

• Whatever join key both parties agree on, usually a hashed identifier confirmed present on a live redemption.
• A real conversion followed from click through Yotpo to Snowflake.
• Row counts and revenue reconciled against each source system for a closed window, with duplicates checked on the join key and both definitions documented.
• First-time-buyer orders reconciled against imported conversions, with loyalty and referral orders excluded and reported separately.
• Reconciled over a fully closed window, matched per record rather than on totals.
• Re-checked 30 days later.

Straight talk

When you don't need this

Worth reading before paying anyone, including us.

• If Snowflake already reports conversions that reconcile against Yotpo's records within a few percent, this join is working and there is nothing to buy.
• Measure identifier coverage yourself first — pull your last hundred redemptions and count how many carry whatever join key both parties agree on, usually a hashed identifier. Ten minutes, free, and it tells you whether any of this is worth doing.
• If you run Yotpo for reviews only and have no loyalty or referral program, this reduces to the Trustpilot case: keep reviews out of your conversion list and stop there.
• If you have no partner to share data with and one cloud, a warehouse you already run does the same job. The sharing capability is what justifies this one.