# Easy RFP — UTM Scheme & Attribution

**Goal:** every social click → tracked → attributed back to the specific post → linked to signup → linked to paid customer.

This is the layer that makes social ROI measurable. Without it, you're guessing.

---

## 1. Parameter convention

Every link in a social post MUST carry these 5 UTM params, no exceptions.

| Param | Allowed values | Example |
|---|---|---|
| `utm_source` | `instagram` / `facebook` / `linkedin` / `tiktok` | `instagram` |
| `utm_medium` | `organic_post` / `bio_link` / `story` / `reel` / `carousel` / `video` / `dm` | `organic_post` |
| `utm_campaign` | snake_case theme tag (matches `social_posts.campaign_tag`) | `hotel_intake_template_2026q2` |
| `utm_content` | unique post slug (matches `social_posts.post_slug`) | `post_017_de_organizer_hours_not_weeks` |
| `utm_term` | language code (lowercase, ISO 639-1) | `en` / `es` / `de` |

**Order matters for readability** but not function — pick one order and stick to it.

**Canonical example (one post, one link):**
```
https://easyhotelrfp.com/?utm_source=linkedin&utm_medium=organic_post&utm_campaign=hotel_intake_template_2026q2&utm_content=post_017_de_organizer_hours_not_weeks&utm_term=de
```

---

## 2. Why these specific 5?

- **`utm_source`** answers "which channel?" — used for top-line channel ROI.
- **`utm_medium`** answers "what kind of unit?" — bio link converts very differently from a feed post.
- **`utm_campaign`** answers "which theme?" — lets you A/B "hotel-intake-template" vs "free-forever-narrative" without renaming individual posts.
- **`utm_content`** answers "which exact post?" — this is the killer field. Every post has a unique slug, so attribution can prove "post 017 generated 12 signups, post 018 generated 0".
- **`utm_term`** answers "which language?" — separate report for EN vs ES vs DE so you don't average them and lose signal.

---

## 3. Snake_case slug recipe

`post_{seq:03d}_{lang}_{audience}_{theme}`

Examples:
- `post_001_en_organizer_2_min_demo`
- `post_023_es_organizer_pdf_chaos`
- `post_044_de_hotel_free_forever`

**Rules:**
- Always 3-digit padded seq for sortability.
- 2-char lang code right after seq.
- Audience: `organizer` | `hotel` | `mixed`.
- Theme is freeform but stable across languages (so post_001 EN and post_001 DE share the theme suffix).

---

## 4. Bio link UTMs

Bio links are static (same URL every visitor uses) so they can't carry per-post `utm_content`. Use this fixed pattern:

| Network | Bio link |
|---|---|
| Instagram | `https://easyhotelrfp.com/?utm_source=instagram&utm_medium=bio_link&utm_campaign=profile&utm_term={detected}` |
| Facebook | `https://easyhotelrfp.com/?utm_source=facebook&utm_medium=bio_link&utm_campaign=profile&utm_term={detected}` |
| LinkedIn | `https://easyhotelrfp.com/?utm_source=linkedin&utm_medium=bio_link&utm_campaign=profile&utm_term={detected}` |
| TikTok | `https://easyhotelrfp.com/?utm_source=tiktok&utm_medium=bio_link&utm_campaign=profile&utm_term={detected}` |

`{detected}` is filled by client-side JS reading `navigator.language` so EN/ES/DE bio-link clicks are still split.

---

## 5. The attribution ladder (3 layers)

### Layer 1 — Network-side metrics (vanity)
Pulled from each platform's API every 24h via `social-metrics-pull`:
- Impressions
- Reach
- Engagement (likes + comments + shares + saves)
- Video views (if applicable)
- Profile visits (IG/TT only)
- Outbound clicks (where API exposes it: FB and LinkedIn yes, IG limited, TT limited)

These are vanity but useful for content optimization (which post FORMAT works).

### Layer 2 — Site-side click tracking
On every page load:
```js
const u = new URL(window.location.href);
const utm = {
  source:   u.searchParams.get('utm_source'),
  medium:   u.searchParams.get('utm_medium'),
  campaign: u.searchParams.get('utm_campaign'),
  content:  u.searchParams.get('utm_content'),
  term:     u.searchParams.get('utm_term'),
};
if (utm.source) {
  // Stamp a first-touch cookie (180 days) and last-touch cookie (30 days)
  document.cookie = `er_first_touch=${btoa(JSON.stringify({...utm, t: Date.now()}))}; max-age=15552000; path=/; samesite=lax`;
  document.cookie = `er_last_touch=${btoa(JSON.stringify({...utm, t: Date.now()}))}; max-age=2592000; path=/; samesite=lax`;
}
```

This script ships in the public `<head>` (file: `/site/assets/utm-track.js`, 480 bytes minified).

### Layer 3 — Signup attribution (the gold)
On `auth.signUp` (Supabase client side), read both cookies and pass them as user metadata:
```js
const firstTouch = readCookie('er_first_touch');
const lastTouch  = readCookie('er_last_touch');
await supabase.auth.signUp({
  email, password,
  options: {
    data: {
      first_touch_utm: firstTouch ? JSON.parse(atob(firstTouch)) : null,
      last_touch_utm:  lastTouch  ? JSON.parse(atob(lastTouch))  : null,
    }
  }
});
```

Server-side, the `social-attribute-signup` Supabase webhook (fires on `auth.users` INSERT) inserts a row into `social_attribution`:
```sql
INSERT INTO social_attribution (
  user_id, post_slug, source, medium, campaign, language,
  first_touch_at, signup_at, attribution_kind
) VALUES (...);
```

### Layer 4 — Paid attribution
Stripe webhook for `customer.subscription.created` joins `auth.users.id → social_attribution.user_id` and stamps `paid_at` + `mrr_eur` on the matching row. Now you can answer:

- "How much paid MRR did post 017 generate?"
- "What's the CAC payback for LinkedIn organic vs Instagram organic?"
- "Which language has the highest paid conversion rate?"

This is exactly what Buffer/Metricool can't give you, because they don't see your Stripe.

---

## 6. Attribution rules (decision time)

When a user signs up, 3 outcomes are possible:

1. **First-touch only** (cookie still alive, came back via direct/email): credit the *first* social touch. Standard for organic content because the journey is long.
2. **Last-touch wins** (different social touched them later): credit the *last* social touch. Used when comparing across paid campaigns.
3. **Both stamped, default report uses first-touch.** Last-touch is available as a column for cross-checks.

We store BOTH and let dashboard filter. Don't pick one and lose the other.

**Direct/no-UTM signups** = `attribution_kind = 'direct'`. Don't fight it; ~30-50% of conversions will be direct because users research, then sign up later from a bookmark.

---

## 7. Anti-patterns to avoid

- ❌ **Don't rename UTM values across posts.** `instagram` and `Instagram` and `IG` will fragment your reports forever.
- ❌ **Don't put commercial info in `utm_content`.** Use the slug. Pricing changes; the slug is a stable foreign key.
- ❌ **Don't auto-strip UTMs in your Cloudflare rules.** Some teams do this for "clean URLs" — it kills attribution.
- ❌ **Don't shorten links via bit.ly.** TikTok and LinkedIn already shorten. Bit.ly adds redirect latency, drops UTMs in some cases, and looks spammy.
- ❌ **Don't use the same `utm_campaign` across two A/B variants.** That's exactly what `utm_campaign` is for — splitting them.

---

## 8. Reporting queries (live in Supabase, free)

These views power the analytics dashboard at `/app/admin/social/analytics/`.

```sql
-- ROI per post (last 90 days)
SELECT
  sp.post_slug,
  sp.language,
  sp.campaign_tag,
  COUNT(DISTINCT sa.user_id) FILTER (WHERE sa.attribution_kind != 'direct') AS attributed_signups,
  COUNT(DISTINCT sa.user_id) FILTER (WHERE sa.paid_at IS NOT NULL)         AS paid_customers,
  COALESCE(SUM(sa.mrr_eur) FILTER (WHERE sa.paid_at IS NOT NULL), 0)        AS mrr_generated_eur,
  -- Network metrics
  COALESCE(MAX(sm.impressions),  0) AS impressions,
  COALESCE(MAX(sm.engagement),   0) AS engagement,
  COALESCE(MAX(sm.click_through),0) AS clicks
FROM social_posts sp
LEFT JOIN social_attribution sa ON sa.post_slug = sp.post_slug
LEFT JOIN social_metrics sm     ON sm.post_id   = sp.id
WHERE sp.published_at > NOW() - INTERVAL '90 days'
GROUP BY sp.post_slug, sp.language, sp.campaign_tag
ORDER BY mrr_generated_eur DESC, attributed_signups DESC;
```

```sql
-- Channel ROI
SELECT
  sa.source,
  COUNT(DISTINCT sa.user_id)                                    AS signups,
  COUNT(DISTINCT sa.user_id) FILTER (WHERE sa.paid_at IS NOT NULL) AS paid,
  COALESCE(SUM(sa.mrr_eur), 0)                                   AS mrr_eur,
  ROUND(
    100.0 * COUNT(DISTINCT sa.user_id) FILTER (WHERE sa.paid_at IS NOT NULL)
        / NULLIF(COUNT(DISTINCT sa.user_id), 0),
    2
  ) AS conversion_rate_pct
FROM social_attribution sa
WHERE sa.signup_at > NOW() - INTERVAL '90 days'
GROUP BY sa.source
ORDER BY mrr_eur DESC;
```

```sql
-- Language × Channel matrix
SELECT
  sa.source,
  sa.language,
  COUNT(*) AS signups,
  COUNT(*) FILTER (WHERE sa.paid_at IS NOT NULL) AS paid
FROM social_attribution sa
WHERE sa.signup_at > NOW() - INTERVAL '90 days'
GROUP BY sa.source, sa.language
ORDER BY sa.source, paid DESC;
```

These queries run free on your existing Supabase. Pin them to the analytics dashboard and they auto-refresh.