Jacek Pyziak
5e491d626c
- Nowy moduł CampaignTerms z widokiem grup reklam, fraz wyszukiwanych i fraz wykluczających - Frazy wyszukiwane dodane do wykluczonych oznaczane czerwonym kolorem w tabeli - Instalator migracji (install.php) z obsługą schema_migrations - Migracja 003 dla tabel campaign_ad_groups, campaign_search_terms, campaign_negative_keywords - CLAUDE.md z dokumentacją architektury projektu - Aktualizacja layoutu, stylów i konfiguracji Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
78 lines
3.8 KiB
SQL
78 lines
3.8 KiB
SQL
-- Migracja: grupy reklam + frazy wyszukiwane i wykluczajace dla kampanii
|
|
-- Opis: struktura pod import z Google Ads API (30 dni + all time)
|
|
|
|
CREATE TABLE IF NOT EXISTS `campaign_ad_groups` (
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`campaign_id` INT(11) NOT NULL,
|
|
`ad_group_id` BIGINT(20) NOT NULL,
|
|
`ad_group_name` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`impressions_30` INT(11) NOT NULL DEFAULT 0,
|
|
`clicks_30` INT(11) NOT NULL DEFAULT 0,
|
|
`cost_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`conversions_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`conversion_value_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`roas_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`impressions_all_time` INT(11) NOT NULL DEFAULT 0,
|
|
`clicks_all_time` INT(11) NOT NULL DEFAULT 0,
|
|
`cost_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`conversions_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`conversion_value_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`roas_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`date_sync` DATE DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_campaign_ad_groups_campaign_ad_group` (`campaign_id`, `ad_group_id`),
|
|
KEY `idx_campaign_ad_groups_campaign_id` (`campaign_id`),
|
|
CONSTRAINT `FK_campaign_ad_groups_campaigns`
|
|
FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `campaign_search_terms` (
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`campaign_id` INT(11) NOT NULL,
|
|
`ad_group_id` INT(11) NOT NULL,
|
|
`search_term` VARCHAR(255) NOT NULL,
|
|
`impressions_30` INT(11) NOT NULL DEFAULT 0,
|
|
`clicks_30` INT(11) NOT NULL DEFAULT 0,
|
|
`cost_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`conversions_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`conversion_value_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`roas_30` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`impressions_all_time` INT(11) NOT NULL DEFAULT 0,
|
|
`clicks_all_time` INT(11) NOT NULL DEFAULT 0,
|
|
`cost_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`conversions_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`conversion_value_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`roas_all_time` DECIMAL(20,6) NOT NULL DEFAULT 0.000000,
|
|
`date_sync` DATE DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_campaign_search_terms` (`campaign_id`, `ad_group_id`, `search_term`),
|
|
KEY `idx_campaign_search_terms_campaign_id` (`campaign_id`),
|
|
KEY `idx_campaign_search_terms_ad_group_id` (`ad_group_id`),
|
|
CONSTRAINT `FK_campaign_search_terms_campaigns`
|
|
FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `FK_campaign_search_terms_ad_groups`
|
|
FOREIGN KEY (`ad_group_id`) REFERENCES `campaign_ad_groups` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `campaign_negative_keywords` (
|
|
`id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`campaign_id` INT(11) NOT NULL,
|
|
`ad_group_id` INT(11) DEFAULT NULL,
|
|
`scope` VARCHAR(20) NOT NULL DEFAULT 'campaign',
|
|
`keyword_text` VARCHAR(255) NOT NULL,
|
|
`match_type` VARCHAR(40) DEFAULT NULL,
|
|
`date_sync` DATE DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_campaign_negative_keywords_campaign_id` (`campaign_id`),
|
|
KEY `idx_campaign_negative_keywords_ad_group_id` (`ad_group_id`),
|
|
CONSTRAINT `FK_campaign_negative_keywords_campaigns`
|
|
FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT `FK_campaign_negative_keywords_ad_groups`
|
|
FOREIGN KEY (`ad_group_id`) REFERENCES `campaign_ad_groups` (`id`)
|
|
ON DELETE SET NULL ON UPDATE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|