Duplicate URLs in campaign link reports for URLs with ampersands and other HTML-encoded characters

[zackkatz]

Bug Description

Campaign link reports show the same URL multiple times with fragmented click counts instead of a single aggregated entry. This affects any URL containing characters that get HTML-encoded — most commonly & (encoded as &), but also <, >, ", and '.

URLs with query string parameters are especially affected since they use & to separate parameters (e.g., YouTube links with timestamps, UTM-tagged URLs, any multi-parameter URL).

Root Cause

UrlStores::getUrlSlug() searches for existing URLs using the raw input but stores them after running htmlspecialchars_decode(). Because emails are sent in batches across separate PHP processes, this creates a new duplicate fc_url_stores row per batch.

Full trace

1. Helper::urlReplaces() extracts URLs from the raw HTML email body using regex
2. URLs come out with HTML entities intact (e.g., & from href="...&...")
3. For each URL, it calls UrlStores::getUrlSlug($url)
4. getUrlSlug() searches the DB for the raw & version
5. It doesn't find it (previous batches stored it as & via htmlspecialchars_decode)
6. It creates a new row with the decoded & version
7. The static $urls cache prevents duplicates within a single batch, but each batch is a new PHP process with an empty cache
8. Result: one new duplicate fc_url_stores row per batch, per affected URL

// Lookup uses raw input (may contain &)
$isExist = self::where('url', $longUrl)->first();

// Storage decodes HTML entities (stores &)
'url' => htmlspecialchars_decode($longUrl),

Evidence from production data

Same URL stored 5 times across different batch runs:

INSERT INTO `wp_fc_url_stores` (`id`, `url`, `short`, `created_at`, `updated_at`)
VALUES
    (8046, 'https://www.gravitykit.com/products/?utm_source=onboarding&utm_medium=email&utm_campaign=gk_nurture#free-plugins', '2bda', '2026-03-03 12:00:03', '2026-03-03 12:00:03'),
    (8045, 'https://www.gravitykit.com/products/?utm_source=onboarding&utm_medium=email&utm_campaign=gk_nurture#free-plugins', '2bd9', '2026-03-03 11:45:04', '2026-03-03 11:45:04'),
    (8044, 'https://www.gravitykit.com/products/?utm_source=onboarding&utm_medium=email&utm_campaign=gk_nurture#free-plugins', '2bd8', '2026-03-03 09:57:04', '2026-03-03 09:57:04'),
    (8043, 'https://www.gravitykit.com/products/?utm_source=onboarding&utm_medium=email&utm_campaign=gk_nurture#free-plugins', '2bd7', '2026-03-03 07:29:04', '2026-03-03 07:29:04'),
    (8042, 'https://www.gravitykit.com/products/?utm_source=onboarding&utm_medium=email&utm_campaign=gk_nurture#free-plugins', '2bd6', '2026-03-02 17:42:50', '2026-03-02 17:42:50');

All 5 rows have identical decoded URLs — each created by a different email batch.

Impact

• fc_url_stores grows with duplicate rows for every batch run containing affected URLs
• getLinksReport() groups by url_id, so the same URL appears many times in campaign link reports
• Click counts are split across duplicate entries, making analytics inaccurate
• Most commonly affects URLs with & in query strings (YouTube, Google, UTM parameters, etc.)

Steps to Reproduce

1. Create an automation or campaign email containing a URL with query parameters (e.g., https://www.youtube.com/watch?v=abc&t=123s)
2. Trigger the email for enough subscribers that it sends across multiple batches
3. Check fc_url_stores — the same URL will have multiple rows
4. View the campaign link report — the same URL appears multiple times with fragmented click counts

Suggested Fix

1. Normalize the URL with htmlspecialchars_decode() before the lookup in getUrlSlug(), so the search matches what was previously stored
2. Group link reports by normalized URL string instead of url_id to aggregate existing duplicates