diff options
Diffstat (limited to 'db/postgresql/data-migrations')
| -rw-r--r-- | db/postgresql/data-migrations/convert-utm-clid-columns.sql | 49 | ||||
| -rw-r--r-- | db/postgresql/data-migrations/populate-revenue-table.sql | 41 |
2 files changed, 90 insertions, 0 deletions
diff --git a/db/postgresql/data-migrations/convert-utm-clid-columns.sql b/db/postgresql/data-migrations/convert-utm-clid-columns.sql new file mode 100644 index 0000000..fffe1dd --- /dev/null +++ b/db/postgresql/data-migrations/convert-utm-clid-columns.sql @@ -0,0 +1,49 @@ +----------------------------------------------------- +-- PostgreSQL +----------------------------------------------------- +UPDATE "website_event" we +SET fbclid = LEFT(url.fbclid, 255), + gclid = LEFT(url.gclid, 255), + li_fat_id = LEFT(url.li_fat_id, 255), + msclkid = LEFT(url.msclkid, 255), + ttclid = LEFT(url.ttclid, 255), + twclid = LEFT(url.twclid, 255), + utm_campaign = LEFT(url.utm_campaign, 255), + utm_content = LEFT(url.utm_content, 255), + utm_medium = LEFT(url.utm_medium, 255), + utm_source = LEFT(url.utm_source, 255), + utm_term = LEFT(url.utm_term, 255) +FROM (SELECT event_id, website_id, session_id, + (regexp_matches(url_query, '(?:[&?]|^)fbclid=([^&]+)', 'i'))[1] AS fbclid, + (regexp_matches(url_query, '(?:[&?]|^)gclid=([^&]+)', 'i'))[1] AS gclid, + (regexp_matches(url_query, '(?:[&?]|^)li_fat_id=([^&]+)', 'i'))[1] AS li_fat_id, + (regexp_matches(url_query, '(?:[&?]|^)msclkid=([^&]+)', 'i'))[1] AS msclkid, + (regexp_matches(url_query, '(?:[&?]|^)ttclid=([^&]+)', 'i'))[1] AS ttclid, + (regexp_matches(url_query, '(?:[&?]|^)twclid=([^&]+)', 'i'))[1] AS twclid, + (regexp_matches(url_query, '(?:[&?]|^)utm_campaign=([^&]+)', 'i'))[1] AS utm_campaign, + (regexp_matches(url_query, '(?:[&?]|^)utm_content=([^&]+)', 'i'))[1] AS utm_content, + (regexp_matches(url_query, '(?:[&?]|^)utm_medium=([^&]+)', 'i'))[1] AS utm_medium, + (regexp_matches(url_query, '(?:[&?]|^)utm_source=([^&]+)', 'i'))[1] AS utm_source, + (regexp_matches(url_query, '(?:[&?]|^)utm_term=([^&]+)', 'i'))[1] AS utm_term + FROM "website_event" + WHERE url_query IS NOT NULL) url +WHERE we.event_id = url.event_id + and we.session_id = url.session_id + and we.website_id = url.website_id; + +----------------------------------------------------- +-- MySQL +----------------------------------------------------- +UPDATE `website_event` +SET fbclid = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)fbclid=[^&]+'), '=', -1), '&', 1), 255), + gclid = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)gclid=[^&]+'), '=', -1), '&', 1), 255), + li_fat_id = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)li_fat_id=[^&]+'), '=', -1), '&', 1), 255), + msclkid = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)msclkid=[^&]+'), '=', -1), '&', 1), 255), + ttclid = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)ttclid=[^&]+'), '=', -1), '&', 1), 255), + twclid = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)twclid=[^&]+'), '=', -1), '&', 1), 255), + utm_campaign = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)utm_campaign=[^&]+'), '=', -1), '&', 1), 255), + utm_content = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)utm_content=[^&]+'), '=', -1), '&', 1), 255), + utm_medium = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)utm_medium=[^&]+'), '=', -1), '&', 1), 255), + utm_source = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)utm_source=[^&]+'), '=', -1), '&', 1), 255), + utm_term = LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_SUBSTR(url_query, '(?:[&?]|^)utm_term=[^&]+'), '=', -1), '&', 1), 255) +WHERE url_query IS NOT NULL; diff --git a/db/postgresql/data-migrations/populate-revenue-table.sql b/db/postgresql/data-migrations/populate-revenue-table.sql new file mode 100644 index 0000000..9df7518 --- /dev/null +++ b/db/postgresql/data-migrations/populate-revenue-table.sql @@ -0,0 +1,41 @@ +----------------------------------------------------- +-- PostgreSQL +----------------------------------------------------- +INSERT INTO "revenue" +SELECT gen_random_uuid() revenue_id, + ed.website_id, + we.session_id, + we.event_id, + we.event_name, + currency.string_value currency, + coalesce(ed.number_value, cast(ed.string_value as numeric(19,4))) revenue, + ed.created_at +FROM event_data ed +JOIN website_event we +ON we.event_id = ed.website_event_id +JOIN (SELECT website_event_id, string_value + FROM event_data + WHERE data_key ilike '%currency%') currency +ON currency.website_event_id = ed.website_event_id +WHERE ed.data_key ilike '%revenue%'; + +----------------------------------------------------- +-- MySQL +----------------------------------------------------- +INSERT INTO `revenue` +SELECT UUID() revenue_id, + ed.website_id, + we.session_id, + we.event_id, + we.event_name, + currency.string_value currency, + coalesce(ed.number_value, cast(ed.string_value as decimal(19,4))) revenue, + ed.created_at +FROM event_data ed +JOIN website_event we +ON we.event_id = ed.website_event_id +JOIN (SELECT website_event_id, string_value + FROM event_data + WHERE data_key like '%currency%') currency +ON currency.website_event_id = ed.website_event_id +WHERE ed.data_key like '%revenue%';
\ No newline at end of file |