Exchange Rate

Marketing permissions per non-purchaser, across 9 sites.

Average exchange rate · 9 sites

29.80%

Averaged across 9 sites. Roughly 0.30 permissions per non-purchaser on a typical site.

The formula

email_perms + sms_perms

non_purchasers

0.298

What goes into the rate

numerator · denominator · rate

Numerator

Total permissions

Email permissions + SMS permissions. A user with both contributes 2. Each channel is counted independently because each is an independent way to reach the user.

Denominator

Non-purchasers

Users where customer = FALSE OR customer IS NULL. NULL is treated as a non-purchaser to cover anonymous and unconverted visitors.

Result

Exchange rate

SAFE_DIVIDE(numerator, denominator) — returns NULL instead of erroring on a zero denominator (e.g. a brand-new site with no non-purchasers yet).

Permission rules

when does a user count?

📧 Email permission

✓
Has an email on file (otherwise there's nothing to send to).
✓
email_consented = TRUE — opted in.
✓
email_consented = FALSE AND email_consented_updated_at IS NULL — never explicitly opted out (default state, not a real "no").
✗
email_consented = FALSE with a timestamp — active unsubscribe, excluded.

📱 SMS permission

✓
Has a phone on file.
✓
sms_consented = TRUE — explicit opt-in required.
✗
No null-state exception. SMS marketing requires explicit consent (TCPA in the US).

Per-site ranking

9 sites · sorted by rate

Spritz Flower

gdJw2Za

6,529

12,215

53.45%

Danforth Pewter

Dvqyq3v

184,154

234

377,890

48.79%

Isshiki Matcha

jaoQpmL

17,199

38,303

44.91%

Storelli

Jdm7jxv

303,860

192

820,609

37.05%

JP Organic Coffee

jaoQNYL

23,273

819

82,056

29.36%

Final Boss Sour

NaVnjma

410,940

2,198

1,577,293

26.19%

Viajecito

ALOWrbL

17,242

83,067

20.76%

Triumph

oaAz1v9

311,731

161

6,235,478

5.00%

Shiree Odiz

yveyXOv

2,361

88,131

2.68%

Cross-site average

9 sites

—

29.80%

The query

single-site · point lookup on website_id

exchange_rate.sql

-- Single-site exchange rate. website_id is the primary key,
-- so this is a fast point lookup. Cross-site = full scan = expensive.

SELECT
  COUNTIF(
    email IS NOT NULL
    AND (
      email_consented = TRUE
      OR (email_consented = FALSE AND email_consented_updated_at IS NULL)
    )
  ) AS email_permissions,

  COUNTIF(phone IS NOT NULL AND sms_consented = TRUE) AS sms_permissions,

  COUNTIF(/* email rule */) +
  COUNTIF(/* sms rule */) AS total_permissions,

  COUNTIF(customer = TRUE) AS purchasers,
  COUNTIF(customer = FALSE OR customer IS NULL) AS non_purchasers,

  SAFE_DIVIDE(
    COUNTIF(/* email rule */) +
    COUNTIF(/* sms rule */),
    COUNTIF(customer = FALSE OR customer IS NULL)
  ) AS exchange_rate

FROM `user`
WHERE website_id = 'NaVnjma';  -- Final Boss Sour