Ghost icon indicating copy to clipboard operation
Ghost copied to clipboard
verified publisher icon TryGhost

✨ Added email click rate filter for members

Open betschki opened this issue 3 months ago • 2 comments

Allows filtering members by their email click through rate (CTR), calculated as the percentage of tracked emails that resulted in at least one click. Follows the same pattern as email open rate.

Got some code for us? Awesome 🎊!

Please take a minute to explain the change you're making:

Why are you making it? Ghost currently provides an email open rate filter to identify highly engaged members, but lacks a comparable metric for click engagement. Click-through rate (CTR) is a critical engagement metric that shows which members actively interact with email content by clicking links, not just opening emails. This completes Ghost's email engagement analytics by adding the CTR metric alongside the existing open rate.

What does it do? This adds a "Click rate (all time)" filter to the members list in Ghost Admin. The CTR is calculated as:

(emails clicked / emails sent with click tracking enabled) × 100

The calculation requires a minimum of 5 tracked emails before displaying a rate (same threshold as open rate), and updates automatically as new email analytics are processed.

Why is this something Ghost users or developers need? This gives Ghost publishers better options to segment their audience and improve their reporting. Example use case: https://forum.ghost.org/t/memberlist-cleaning/60818/

Please check your PR against these items:

  • [x] I've read and followed the Contributor Guide
  • [x] I've explained my change
  • [x] I've written an automated test to prove my change works

We appreciate your contribution! 🙏

[!NOTE] Adds email_click_rate to members with DB/storage, analytics computation, API exposure, admin filter, and list ordering, plus tests and migrations.

  • Admin (UI):
    • Add new filter EMAIL_CLICK_RATE_FILTER (“Click rate (all time)”) in ghost/admin/app/components/members/filter.js and export via filters/index.js.
    • New filter definition in components/members/filters/email-click-rate.js (gated by settings.emailTrackClicks).
  • Backend (API/Model):
    • Expose email_click_rate in member serializer core/server/api/.../members.js.
    • Support ordering by email_click_rate in core/server/models/member.js (orderRawQuery).
    • Compute and persist click rate in core/server/services/email-analytics/lib/queries.js using click events and tracked-click emails.
  • Data/Schema:
    • Add email_click_rate column and index to members (migrations in core/server/data/migrations/versions/6.7/...) and schema in core/server/data/schema/schema.js.
  • Tests:
    • Update snapshots to include email_click_rate and content-length changes.
    • Add E2E tests for ordering by email_click_rate in core/test/e2e-api/admin/members.test.js.

Written by Cursor Bugbot for commit cd057d9994ab0fee4e68d13c12d0aaec447a05f1. This will update automatically on new commits. Configure here.

betschki avatar Nov 10 '25 18:11 betschki

It looks like this PR contains a migration 👀 Here's the checklist for reviewing migrations:

General requirements

  • [ ] :warning: Tested performance on staging database servers, as performance on local machines is not comparable to a production environment
  • [ ] Satisfies idempotency requirement (both up() and down())
  • [ ] Does not reference models
  • [ ] Filename is in the correct format (and correctly ordered)
  • [ ] Targets the next minor version
  • [ ] All code paths have appropriate log messages
  • [ ] Uses the correct utils
  • [ ] Contains a minimal changeset
  • [ ] Does not mix DDL/DML operations
  • [ ] Tested in MySQL and SQLite

Schema changes

  • [ ] Both schema change and related migration have been implemented
  • [ ] For index changes: has been performance tested for large tables
  • [ ] For new tables/columns: fields use the appropriate predefined field lengths
  • [ ] For new tables/columns: field names follow the appropriate conventions
  • [ ] Does not drop a non-alpha table outside of a major version

Data changes

  • [ ] Mass updates/inserts are batched appropriately
  • [ ] Does not loop over large tables/datasets
  • [ ] Defends against missing or invalid data
  • [ ] For settings updates: follows the appropriate guidelines

github-actions[bot] avatar Nov 10 '25 18:11 github-actions[bot]

[!NOTE]

Other AI code review bot(s) detected

CodeRabbit has detected other AI code review bot(s) in this pull request and will avoid duplicating their findings in the review comments. This may lead to a less comprehensive review.

Walkthrough

Adds member email click rate support across the stack: new nullable unsigned integer column email_click_rate (with index) and migrations; analytics query changes to compute and conditionally update email_click_rate; serialization of email_click_rate into API member output; ordering support for email_click_rate in member model queries; admin UI additions (new filter component, exports, and inclusion in FILTER_GROUPS); and end-to-end tests verifying ordering by email_click_rate.

Estimated code review effort

🎯 4 (Complex) | ⏱️ ~45 minutes

Areas requiring extra attention:

  • ghost/core/core/server/services/email-analytics/lib/queries.js — correctness of SQL queries, joins, counts, grouping, and conditional update logic for email_click_rate.
  • ghost/core/core/server/data/migrations/versions/6.7/* — migration correctness, nullable/index semantics, and rollback behavior.
  • ghost/core/core/server/data/schema/schema.js — schema entry for email_click_rate matches migration (type, unsigned, nullable, index).
  • ghost/core/core/server/models/member.js — ordering SQL generation for email_click_rate, NULL handling, and parity with email_open_rate.
  • ghost/core/core/server/api/endpoints/utils/serializers/output/members.js — typedef and serialization inclusion of email_click_rate.
  • ghost/admin/app/components/members/* — new filter component, exports, and FILTER_GROUPS integration.
  • ghost/core/test/e2e-api/admin/members.test.js — test expectations and snapshot validity for ordering by email_click_rate.

Pre-merge checks and finishing touches

✅ Passed checks (3 passed)
Check name Status Explanation
Title check ✅ Passed The title accurately and concisely summarizes the main change: adding an email click rate filter for members in Ghost Admin.
Description check ✅ Passed The description is comprehensive and directly related to the changeset, explaining the feature's purpose, implementation, calculation method, and user value.
Docstring Coverage ✅ Passed Docstring coverage is 100.00% which is sufficient. The required threshold is 80.00%.
✨ Finishing touches
  • [ ] 📝 Generate docstrings
🧪 Generate unit tests (beta)
  • [ ] Create PR with unit tests
  • [ ] Post copyable unit tests in a comment
📜 Recent review details

Configuration used: CodeRabbit UI

Review profile: CHILL

Plan: Pro

📥 Commits

Reviewing files that changed from the base of the PR and between f95a5a5aaf9abf672d26b5fa4ec57a6f37301941 and cd057d9994ab0fee4e68d13c12d0aaec447a05f1.

📒 Files selected for processing (1)
  • ghost/core/core/server/services/email-analytics/lib/queries.js (2 hunks)
🧰 Additional context used
📓 Path-based instructions (2)
ghost/core/core/server/**

📄 CodeRabbit inference engine (AGENTS.md)

Backend core logic should reside under ghost/core/core/server/

Files:

  • ghost/core/core/server/services/email-analytics/lib/queries.js
ghost/core/core/server/services/**

📄 CodeRabbit inference engine (AGENTS.md)

Backend services should be implemented under ghost/core/core/server/services/

Files:

  • ghost/core/core/server/services/email-analytics/lib/queries.js
⏰ Context from checks skipped due to timeout of 90000ms. You can increase the timeout in your CodeRabbit configuration to a maximum of 15 minutes (900000ms). (9)
  • GitHub Check: Ghost-CLI tests
  • GitHub Check: Acceptance tests (Node 22.13.1, mysql8)
  • GitHub Check: Acceptance tests (Node 22.13.1, sqlite3)
  • GitHub Check: Legacy tests (Node 22.13.1, sqlite3)
  • GitHub Check: Unit tests (Node 22.13.1)
  • GitHub Check: Legacy tests (Node 22.13.1, mysql8)
  • GitHub Check: Lint
  • GitHub Check: Admin tests - Chrome
  • GitHub Check: Build & Push
🔇 Additional comments (3)
ghost/core/core/server/services/email-analytics/lib/queries.js (3)

189-194: LGTM! Denominator query follows established pattern.

The query for trackedEmailCountForClicks correctly mirrors the structure of the existing trackedEmailCount query, ensuring consistency in how tracked email counts are calculated across different metrics.

199-210: Click count query correctly scopes to received emails.

The query properly addresses previous concerns by joining through email_recipients to ensure clicks are only counted for emails the member actually received, and uses DISTINCT email_id to avoid double-counting.

The theoretical edge case (multiple email sends for the same post) has been acknowledged by the author as extremely rare in practice. The implementation is correct for the standard scenario and follows the schema's existing constraints where redirects are post-scoped rather than email-scoped.

221-223: LGTM! Click rate calculation handles edge cases correctly.

The calculation properly uses (emailClickedCount || 0) to prevent NaN when the count is undefined, and correctly applies the minimum threshold check before computing the rate. The implementation mirrors the existing email_open_rate pattern.

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

Comment @coderabbitai help to get the list of available commands and usage tips.

coderabbitai[bot] avatar Nov 10 '25 18:11 coderabbitai[bot]