feat(analytics): database schema — pageviews, visitors, campaigns, reports #212

New Issue

2026-06-18T15:57:06Z

jmiller commented

2026-06-18 15:57:06 +00:00

Parent issue: #210
Migrated from: MokoSuiteAnalytics#2

Summary

Design the database schema for privacy-first analytics tracking.

Tables

`#__mokosuiteclient_analytics_pageviews`

id, page_url, page_title, referrer_url, referrer_domain
visitor_hash (anonymized — hashed IP + user agent, rotated daily)
device_type (desktop, mobile, tablet), browser, os
country_code (GeoIP lookup, no IP stored)
session_id (short-lived, no cookies)
created_at

`#__mokosuiteclient_analytics_visitors`

id, visitor_hash, first_seen, last_seen, pageview_count
Note: visitor_hash rotates daily for privacy — same person gets new hash next day

`#__mokosuiteclient_analytics_campaigns`

id, name, source, medium, campaign_code (UTM params)
click_count, conversion_count
published, created_at

`#__mokosuiteclient_analytics_ad_accounts`

id, platform (google_ads, meta_ads, linkedin_ads)
account_id, api_credentials (encrypted JSON)
sync_enabled, last_sync, published

`#__mokosuiteclient_analytics_ad_metrics`

id, ad_account_id, date, impressions, clicks, conversions, spend, revenue
campaign_name, ad_group_name

`#__mokosuiteclient_analytics_reports`

id, title, config (JSON — date range, metrics, dimensions, filters)
schedule (none, daily, weekly, monthly), email_recipients
created_by, created_at

Privacy Design

No cookies — use session fingerprinting with daily rotation
IP addresses never stored — only anonymized country-level GeoIP
Visitor hashes rotated daily — cannot track individuals across days
All tracking opt-out via Do Not Track header respect
Data retention: configurable, default 90 days auto-purge

Tasks

Write install SQL for com_mokosuiteclient (add analytics tables)
Write update SQL migration
Table and Model classes
Daily visitor hash rotation (scheduled task)

**Parent issue:** #210 **Migrated from:** MokoSuiteAnalytics#2 ## Summary Design the database schema for privacy-first analytics tracking. ## Tables ### `#__mokosuiteclient_analytics_pageviews` - id, page_url, page_title, referrer_url, referrer_domain - visitor_hash (anonymized — hashed IP + user agent, rotated daily) - device_type (desktop, mobile, tablet), browser, os - country_code (GeoIP lookup, no IP stored) - session_id (short-lived, no cookies) - created_at ### `#__mokosuiteclient_analytics_visitors` - id, visitor_hash, first_seen, last_seen, pageview_count - Note: visitor_hash rotates daily for privacy — same person gets new hash next day ### `#__mokosuiteclient_analytics_campaigns` - id, name, source, medium, campaign_code (UTM params) - click_count, conversion_count - published, created_at ### `#__mokosuiteclient_analytics_ad_accounts` - id, platform (google_ads, meta_ads, linkedin_ads) - account_id, api_credentials (encrypted JSON) - sync_enabled, last_sync, published ### `#__mokosuiteclient_analytics_ad_metrics` - id, ad_account_id, date, impressions, clicks, conversions, spend, revenue - campaign_name, ad_group_name ### `#__mokosuiteclient_analytics_reports` - id, title, config (JSON — date range, metrics, dimensions, filters) - schedule (none, daily, weekly, monthly), email_recipients - created_by, created_at ## Privacy Design - No cookies — use session fingerprinting with daily rotation - IP addresses never stored — only anonymized country-level GeoIP - Visitor hashes rotated daily — cannot track individuals across days - All tracking opt-out via `Do Not Track` header respect - Data retention: configurable, default 90 days auto-purge ## Tasks - [ ] Write install SQL for `com_mokosuiteclient` (add analytics tables) - [ ] Write update SQL migration - [ ] Table and Model classes - [ ] Daily visitor hash rotation (scheduled task)

jmiller commented

2026-06-18 15:57:08 +00:00

Branch created: feature/212-feat-analytics-database-schema-pageviews

git fetch origin
git checkout feature/212-feat-analytics-database-schema-pageviews

Branch created: [`feature/212-feat-analytics-database-schema-pageviews`](https://git.mokoconsulting.tech/MokoConsulting/MokoSuiteClient/src/branch/feature/212-feat-analytics-database-schema-pageviews) ```bash git fetch origin git checkout feature/212-feat-analytics-database-schema-pageviews ```

jmiller referenced this issue

2026-06-18 15:59:28 +00:00

feat: analytics module — privacy-first site traffic and ad platform insights #210

Sign in to join this conversation.

Priority Medium

Type Feature

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: MokoConsulting/MokoSuiteClient#212