Jonathan Miller
21e892013b
Generic: Repo Health / Site Health (push) Has been skipped
Generic: Repo Health / Access control (push) Successful in 1s
Universal: Auto Version Bump / Version Bump (push) Failing after 5s
Generic: Project CI / Lint & Validate (push) Successful in 26s
Generic: Project CI / Tests (push) Has been cancelled
Generic: Repo Health / Scripts governance (push) Has been cancelled
Generic: Repo Health / Repository health (push) Has been cancelled
Generic: Repo Health / Report Issues (push) Has been cancelled
4 sub-extensions: com, system plugin, webservices, task plugin. 6 DB tables: pageviews, visitors, campaigns, ad_accounts, ad_metrics, reports. Privacy-first: no cookies, daily visitor hash rotation, IP anonymization.
79 lines
3.5 KiB
SQL
79 lines
3.5 KiB
SQL
CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_pageviews` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`page_url` VARCHAR(1024) NOT NULL DEFAULT '',
|
|
`page_title` VARCHAR(500) NOT NULL DEFAULT '',
|
|
`referrer_url` VARCHAR(1024) DEFAULT NULL,
|
|
`referrer_domain` VARCHAR(255) DEFAULT NULL,
|
|
`visitor_hash` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`device_type` VARCHAR(20) DEFAULT NULL,
|
|
`browser` VARCHAR(100) DEFAULT NULL,
|
|
`os` VARCHAR(100) DEFAULT NULL,
|
|
`country_code` CHAR(2) DEFAULT NULL,
|
|
`session_id` VARCHAR(64) DEFAULT NULL,
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_mokosuiteanalytics_pv_visitor` (`visitor_hash`),
|
|
KEY `idx_mokosuiteanalytics_pv_created` (`created_at`),
|
|
KEY `idx_mokosuiteanalytics_pv_url` (`page_url`(191))
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_visitors` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`visitor_hash` VARCHAR(64) NOT NULL,
|
|
`first_seen` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`last_seen` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
`pageview_count` INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_mokosuiteanalytics_visitors_hash` (`visitor_hash`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_campaigns` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`name` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`source` VARCHAR(100) DEFAULT NULL,
|
|
`medium` VARCHAR(100) DEFAULT NULL,
|
|
`campaign_code` VARCHAR(100) DEFAULT NULL,
|
|
`click_count` INT NOT NULL DEFAULT 0,
|
|
`conversion_count` INT NOT NULL DEFAULT 0,
|
|
`published` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_ad_accounts` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`platform` VARCHAR(50) NOT NULL DEFAULT '',
|
|
`account_id` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`api_credentials` TEXT COMMENT 'Encrypted JSON',
|
|
`sync_enabled` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`last_sync` DATETIME DEFAULT NULL,
|
|
`published` TINYINT(1) NOT NULL DEFAULT 1,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_ad_metrics` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`ad_account_id` INT UNSIGNED NOT NULL,
|
|
`date` DATE NOT NULL,
|
|
`impressions` INT NOT NULL DEFAULT 0,
|
|
`clicks` INT NOT NULL DEFAULT 0,
|
|
`conversions` INT NOT NULL DEFAULT 0,
|
|
`spend` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
|
`revenue` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
|
|
`campaign_name` VARCHAR(255) DEFAULT NULL,
|
|
`ad_group_name` VARCHAR(255) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_mokosuiteanalytics_adm_account_date` (`ad_account_id`, `date`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_reports` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`title` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`config` TEXT COMMENT 'JSON report configuration',
|
|
`schedule` VARCHAR(20) NOT NULL DEFAULT 'none',
|
|
`email_recipients` TEXT,
|
|
`created_by` INT NOT NULL DEFAULT 0,
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;
|