14 — Analytics (Blueprint Screen 11, `/app/testimonials/analytics`)

14 — Analytics (Blueprint Screen 11)

Source: 02-Implementation-Blueprint.md — Screen 11 - Analytics (/app/testimonials/analytics).

Naming note: This is blueprint “Screen 11” (analytics). It is not the same as the repo file 11-published-content-screen-7.md (blueprint Screen 7).

Product alignment: 01-Post-Purchase-Video-Testimonial-Collector-Plan.md — §6 Analytics, §11 funnel thinking.

This document is a build spec only. No code changes are implied until a task references this file.

Prerequisites: Populated TestimonialRequest, TestimonialRequestEvent, TestimonialSubmission, and optionally TestimonialDailyAnalytics (§5.11). Pipeline 07 and submission 06 should be running for non-empty charts.

---

0) Goal (one sentence)

Merchants open /app/testimonials/analytics to see a date-bounded view of the testimonial funnel, product-level coverage, video vs photo performance, and (optionally) UTM/source—using fast queries (pre-aggregated daily rows and/or indexed aggregates).

---

1) Route and file

Item	Value
Suggested file	app/routes/app.testimonial-analytics.jsx (blueprint §6).
URL	/app/testimonials/analytics.

Auth: authenticate.admin(request); scope all metrics by shopId.

Nav: “Analytics” under the testimonial section.

UI library: recharts is already a dependency in this monorepo (app/routes/app._index.jsx); use ResponsiveContainer + BarChart / Funnel / LineChart for consistency, or Polaris skeleton while loading.

---

2) Global controls (all charts)

• Date range: presets Last 7 / 30 / 90 days + custom range; store in URL searchParams (from, to) as UTC date-only strings; interpret range as inclusive start, inclusive end of day UTC (document edge semantics).
• Compare period (optional v2): out of scope for v1.

---

3) Funnel chart (blueprint)

Stages (in order): Sent → Opened → Clicked → Submitted → Approved → Published

3.1 Definitions (must be explicit in code comments)

Stage	Count definition (per shop, per selected date range)
Sent	TestimonialRequest where sentAt in range (or status indicates sent) — count distinct requests (or count rows; one row = one product outreach).
Opened	If email open events exist: TestimonialRequestEvent with eventType=opened in range; else 0 or null with UI disclaimer “Open tracking not configured”.
Clicked	Requests with clickedAt in range or event clicked in range.
Submitted	TestimonialSubmission with submittedAt in range (or TestimonialRequest.submittedAt in range — dedupe if both exist: prefer submission as source of truth).
Approved	Submissions with status transition to approved — use updatedAt when status became approved or add approvedAt column in a migration if you need accuracy (recommended for analytics).
Published	Submissions with published=true and publishedAt in range (add publishedAt if missing).

Display: horizontal funnel or bar chart with stage labels and absolute counts; optional conversion % to previous stage (handle divide-by-zero).

3.2 Known gaps

• Opened may be unavailable — show stage as grayed or omit with helper text (13 requests log mapping notes apply).

---

4) Product-level testimonial coverage

Purpose: “Which catalog products have at least one published testimonial?”

4.1 Metrics

• productsWithTestimonials: count distinct shopifyProductId on TestimonialSubmission where published=true and media acceptable for storefront (same rules as 05).
• totalProducts: count Product rows for shop (synced catalog) or Shopify count via Admin API — pick one; local mirror preferred for speed (§5 Product).

4.2 Visualization

• Ratio card: productsWithTestimonials / totalProducts as percentage.
• Table (optional): top uncovered products by sales — out of scope v1 unless order stats exist; v1 can show count only.

---

5) Conversion comparison (blueprint wording)

Interpretation: compare SKU/catalog coverage with vs without testimonials — same data as §4; optional second chart: bar comparing avg submissions per product for covered vs uncovered (needs order volume data — defer unless easy).

v1 minimum: single donut or progress bar: X% of products have published testimonials.

---

6) Media type performance split

Metrics (date range scoped):

• Video submit rate = video_submissions / (video_submissions + photo_submissions) among new submissions in range.
• Photo submit rate = complement.

Use TestimonialSubmission.mediaType (video | photo).

Chart: pie or two-bar chart; show raw counts next to percentages.

---

7) Optional UTM / source analysis

Status: defer unless request URLs carry UTM params stored on TestimonialRequest or TestimonialRequestEvent.payload.

v1: placeholder Card “Source attribution — coming soon” or omit entirely.

---

8) Data implementation strategies

8.1 Option A — Live aggregation (simpler)

• Single loader runs parallel count / groupBy Prisma queries with where: { shopId, createdAt/sentAt/submittedAt between range }.
• Pros: no cron. Cons: slower on large shops; requires indexes on all timestamp columns used.

8.2 Option B — TestimonialDailyAnalytics (§5.11)

• Nightly or hourly cron increments buckets per shop/day from events and submissions.
• Loader reads sum over days in range — fast charts.
• Pros: scalable. Cons: must implement rollup job and backfill.

Recommendation: start Option A for MVP; add §5.11 + cron when performance requires it (document in ticket).

8.3 Indexes

Ensure indexes on:

• testimonial_requests (shopId, sentAt), (shopId, submittedAt)
• testimonial_submissions (shopId, submittedAt), (shopId, published, publishedAt), (shopId, mediaType)

---

9) Loader shape (for UI)

Return JSON like:

{
  "range": { "from": "2026-01-01", "to": "2026-01-31" },
  "funnel": {
    "sent": 0,
    "opened": null,
    "clicked": 0,
    "submitted": 0,
    "approved": 0,
    "published": 0
  },
  "coverage": {
    "withPublished": 12,
    "totalProducts": 200,
    "pct": 6.0
  },
  "mediaSplit": { "video": 40, "photo": 10 }
}

Use null for opened when tracking absent so UI can show “N/A”.

---

10) Polaris layout

• Page title “Analytics”
• Layout: BlockStack gap="400"
  1. Date range ButtonGroup + optional DatePicker
  2. Key metric cards (optional): Total submissions, Publish rate
  3. Funnel chart card
  4. Coverage card + chart
  5. Media split card
  6. UTM placeholder card (optional)

Empty state: if shop has no data in range, EmptyState with CTA to campaigns or help doc.

---

11) Testing checklist (acceptance criteria)

• Changing date range updates all figures consistently (manual spot-check vs SQL).
• Funnel stages monotonic approximately (published ≤ approved ≤ submitted is ideal; if not, document business reasons e.g. auto-publish).
• Coverage denominator matches chosen totalProducts source.
• Media split sums match submission counts in range.
• No cross-shop data leakage.

---

12) Implementation order (for Cursor)

1. Add approvedAt / publishedAt on submission if analytics need precise funnel (migration).
2. Implement loader (Option A aggregates).
3. Build charts with recharts + Polaris cards.
4. Wire date controls to searchParams + shouldRevalidate.
5. (Later) Rollup cron + §5.11 if needed.

---

13) References

• 02-Implementation-Blueprint.md — Screen 11, §5.11
• 07-email-sms-request-delivery-pipeline.md — sent/clicked semantics
• 06-public-submission-page-screen-13.md — submitted
• 11-published-content-screen-7.md — published visibility

---

14) Note on numbering

This folder already includes 05–13 numbered plans. This file is 14-… (not 05-…).