PayloadJson — Submission analytics store

PayloadJson is the canonical store for per-submission data. Each lead form submission writes one row to LeadSubmissions with the full request body stored in the PayloadJson column. This gives you a single place to run submission-level analytics without changing existing Worker behavior.

Workers Analytics Engine: The same submissions are also streamed to Workers Analytics Engine (3‑month retention) for dashboard queries via the SQL API — project, source, referer, CTA, and numeric metrics. Use WAE for time-series and aggregated dashboards; use D1/PayloadJson for long-term storage and full payload detail.

What is stored

• Table: LeadSubmissions
• Column: PayloadJson (TEXT) — JSON string of the entire request body at submit time
• Written by: src/lead.ts → writeLeadSubmission() → JSON.stringify(body) where body is the parsed request (JSON, form-data, multipart, or text)

So every submission keeps a full snapshot of what was sent: form fields, hidden fields, and any extra keys the form or integrator included.

Known payload keys (for analytics)

These keys are commonly present and can be used for analytics. Custom forms may add more.

Key	Description	Example
FIRSTNAME	Contact name	"Rahul"
EMAIL	Email	"user@example.com"
PHONE	Phone	"9876543210"
PROJECT	Project/campaign name	"Sky Towers"
LOCATION	Location	"pune"
TEAM	Team/source team	"internal"
DOMAIN	Domain or base URL	"example.com"
PAGE_URL	Full submission page URL	"https://example.com/contact"
SOURCE	Lead source	"FB", "99", etc.
COUNTRYCODE	Phone country code	"+91"
KEYWORD	Search keyword	—
USER_MESSAGE	User message	—
__visitor_id	Visitor ID from tracker	UUID
__pg_hp	Honeypot (bot detection)	—
__pg_tts	Time to submit (seconds)	"12"
CTA, CTA_NAME, __pg_cta, __pg_cta_name	CTA identifier	—
utm_source, utm_medium, utm_campaign, utm_term, utm_content	Google Ads / UTM (any casing: UTM_SOURCE, utm-source, etc.)	Stored as sent; also sent to Analytics Engine (blob10–14).
Any other field	Custom form fields, gclid, fbclid, etc.	Stored as-is in PayloadJson. No code change needed — the full request body is JSON.stringify(body).

Inserting more fields: Add any input to your form (hidden or visible). Whatever the client sends in the request body is stored in PayloadJson. The Worker does not whitelist keys; it persists the entire parsed body. For UTM, use query params or hidden fields with names like utm_source, UTM_SOURCE, or utm-source; the lead handler normalizes these for Analytics Engine, but in D1 the raw keys and values are stored unchanged.

Use the shared type LeadSubmissionPayloadJson in src/types.ts when parsing PayloadJson in Worker or dashboard code. The type lists known keys; the [key: string]: unknown allows any additional fields.

Retention and scope

• Retention: Same as D1 — no fixed 90-day limit; data stays until you prune or archive.
• Scope: One JSON object per submission (one row per form submit in LeadSubmissions).
• Use case: Submission-level analytics (counts by project, by page, by CTA, by date; funnel by PAGE_URL; etc.).

How to query

From SQL (D1)

• By column: Use LeadSubmissions columns (ProjectName, VisitorId, SubmittedAt, Source, Referer, CtaName, IsPrimaryLead, IsDuplicateWindow, ContactCountryCode) for filters and group-bys.
• By payload fields: Use SQLite JSON1:
  • json_extract(PayloadJson, '$.FIRSTNAME')
  • json_extract(PayloadJson, '$.PAGE_URL')
  • json_extract(PayloadJson, '$.__pg_tts') (time to submit)
  • json_extract(PayloadJson, '$.utm_campaign'), json_extract(PayloadJson, '$.utm_source') (UTM; key may vary by form)

Example: submissions per project and day:

SELECT ProjectName, date(SubmittedAt) AS day, COUNT(*) AS submissions
FROM LeadSubmissions
WHERE SubmittedAt >= ?
GROUP BY ProjectName, date(SubmittedAt)
ORDER BY day DESC, submissions DESC;

Example: submissions by submission page (from PayloadJson):

SELECT json_extract(PayloadJson, '$.PAGE_URL') AS page_url, COUNT(*) AS n
FROM LeadSubmissions
WHERE PayloadJson IS NOT NULL
GROUP BY page_url;

From application code

• Worker / API: Parse PayloadJson with JSON.parse(row.PayloadJson) and use LeadSubmissionPayloadJson for typing.
• Dashboard: Use parseSubmissionPayload() in dashboard/src/lib/lead-utils.ts for display; for analytics you can parse full JSON and read any key.

Using PayloadJson for analytics later

• No Worker change required: PayloadJson is already written for every submission.
• Add analytics on top: New dashboards, reports, or API endpoints can read from LeadSubmissions and use PayloadJson for dimensions (e.g. CTA, PAGE_URL, PROJECT, date).
• Single source of truth: For “what was submitted this time,” use PayloadJson; for “canonical lead record” use Leads and link via LeadId.

Related

• Lead capture — flow and storage model
• Analytics API — dashboard analytics endpoints; lead detail includes payload_json per submission
• Migration: migrations/009_lead_submissions_history.sql (adds LeadSubmissions and PayloadJson)