Ejegg has uploaded a new change for review.
https://gerrit.wikimedia.org/r/197406
Change subject: Delete more unused fields
......................................................................
Delete more unused fields
Per CCogdill, the is_20XX_donor fields, last contribution id, and
unsub_hash are all unused.
Change-Id: I911e734f59ca8e042d9f30d978f1a95233caabbd
---
M silverpop_export/update_table.sql
1 file changed, 8 insertions(+), 93 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools
refs/changes/06/197406/1
diff --git a/silverpop_export/update_table.sql
b/silverpop_export/update_table.sql
index 3a3eeeb..591de2e 100755
--- a/silverpop_export/update_table.sql
+++ b/silverpop_export/update_table.sql
@@ -22,15 +22,6 @@
highest_usd_amount decimal(20,2),
lifetime_usd_total decimal(20,2),
donation_count int,
- is_2006_donor tinyint(1),
- is_2007_donor tinyint(1),
- is_2008_donor tinyint(1),
- is_2009_donor tinyint(1),
- is_2010_donor tinyint(1),
- is_2011_donor tinyint(1),
- is_2012_donor tinyint(1),
- is_2013_donor tinyint(1),
- is_2014_donor tinyint(1),
-- Latest contribution statistics
last_ctid int unsigned,
@@ -44,9 +35,6 @@
country varchar(2),
state varchar(64),
postal_code varchar(128),
-
- -- Unsubcribe hash
- unsub_hash varchar(255),
INDEX spex_contact_id (contact_id),
INDEX spex_email (email),
@@ -157,36 +145,17 @@
max_amount_currency varchar(3), -- STEP 5
has_recurred_donation tinyint(1),
total_usd decimal(20,2), -- STEP 5
- cnt_total int unsigned, -- STEP 5
- cnt_2006 int unsigned, -- STEP 5
- cnt_2007 int unsigned,
- cnt_2008 int unsigned,
- cnt_2009 int unsigned,
- cnt_2010 int unsigned,
- cnt_2011 int unsigned,
- cnt_2012 int unsigned,
- cnt_2013 int unsigned,
- cnt_2014 int unsigned,
+ cnt_total int unsigned,
INDEX spexs_email (email)
) COLLATE 'utf8_unicode_ci';
INSERT INTO temp_silverpop_export_stat
- (email, exid, max_ctid, max_amount_usd, total_usd, cnt_total,
has_recurred_donation,
- cnt_2006, cnt_2007, cnt_2008, cnt_2009, cnt_2010, cnt_2011, cnt_2012,
cnt_2013, cnt_2014)
+ (email, exid, max_ctid, max_amount_usd, total_usd, cnt_total,
has_recurred_donation)
SELECT
e.email, ex.id, MAX(ct.id), MAX(ct.total_amount), SUM(ct.total_amount),
count(*),
- MAX(IF(SUBSTRING(ct.trxn_id, 1, 9) = 'RECURRING', 1, 0)),
- SUM(IF('2006-07-1' <= ct.receive_date AND ct.receive_date < '2007-07-01',
1, 0)),
- SUM(IF('2007-07-1' <= ct.receive_date AND ct.receive_date < '2008-07-01',
1, 0)),
- SUM(IF('2008-07-1' <= ct.receive_date AND ct.receive_date < '2009-07-01',
1, 0)),
- SUM(IF('2009-07-1' <= ct.receive_date AND ct.receive_date < '2010-07-01',
1, 0)),
- SUM(IF('2010-07-1' <= ct.receive_date AND ct.receive_date < '2011-07-01',
1, 0)),
- SUM(IF('2011-07-1' <= ct.receive_date AND ct.receive_date < '2012-07-01',
1, 0)),
- SUM(IF('2012-07-1' <= ct.receive_date AND ct.receive_date <
'2013-07-01', 1, 0)),
- SUM(IF('2013-07-1' <= ct.receive_date AND ct.receive_date < '2014-07-01',
1, 0)),
- SUM(IF('2014-07-1' <= ct.receive_date AND ct.receive_date < '2015-07-01',
1, 0))
+ MAX(IF(SUBSTRING(ct.trxn_id, 1, 9) = 'RECURRING', 1, 0))
FROM civicrm.civicrm_email e FORCE INDEX(UI_email)
JOIN temp_silverpop_export ex ON e.email=ex.email
JOIN civicrm.civicrm_contribution ct ON e.contact_id=ct.contact_id
@@ -198,16 +167,7 @@
ex.highest_usd_amount = exs.max_amount_usd,
ex.lifetime_usd_total = exs.total_usd,
ex.donation_count = exs.cnt_total,
- ex.has_recurred_donation = IF(exs.has_recurred_donation > 0, 1, 0),
- ex.is_2006_donor = IF(exs.cnt_2006 > 0, 1, 0),
- ex.is_2007_donor = IF(exs.cnt_2007 > 0, 1, 0),
- ex.is_2008_donor = IF(exs.cnt_2008 > 0, 1, 0),
- ex.is_2009_donor = IF(exs.cnt_2009 > 0, 1, 0),
- ex.is_2010_donor = IF(exs.cnt_2010 > 0, 1, 0),
- ex.is_2011_donor = IF(exs.cnt_2011 > 0, 1, 0),
- ex.is_2012_donor = IF(exs.cnt_2012 > 0, 1, 0),
- ex.is_2013_donor = IF(exs.cnt_2013 > 0, 1, 0),
- ex.is_2014_donor = IF(exs.cnt_2014 > 0, 1, 0)
+ ex.has_recurred_donation = IF(exs.has_recurred_donation > 0, 1, 0)
WHERE
ex.id = exs.exid;
@@ -276,14 +236,6 @@
highest_usd_amount = 0,
lifetime_usd_total = 0,
donation_count = 0,
- is_2007_donor = 0,
- is_2008_donor = 0,
- is_2009_donor = 0,
- is_2010_donor = 0,
- is_2011_donor = 0,
- is_2012_donor = 0,
- is_2013_donor = 0,
- is_2014_donor = 0,
latest_currency = 'USD',
latest_native_amount = 0,
latest_usd_amount = 0,
@@ -310,18 +262,8 @@
highest_usd_amount decimal(20,2),
lifetime_usd_total decimal(20,2),
donation_count int,
- is_2006_donor tinyint(1),
- is_2007_donor tinyint(1),
- is_2008_donor tinyint(1),
- is_2009_donor tinyint(1),
- is_2010_donor tinyint(1),
- is_2011_donor tinyint(1),
- is_2012_donor tinyint(1),
- is_2013_donor tinyint(1),
- is_2014_donor tinyint(1),
-- Latest contribution statistics
- last_ctid int unsigned,
latest_currency varchar(3),
latest_native_amount decimal(20,2),
latest_usd_amount decimal(20,2),
@@ -333,40 +275,24 @@
state varchar(24),
postal_code varchar(128),
- -- Unsubcribe hash
- unsub_hash varchar(255),
-
INDEX rspex_contact_id (contact_id),
INDEX rspex_email (email),
INDEX rspex_city (city),
INDEX rspex_country (country),
INDEX rspex_postal (postal_code),
- INDEX rspex_opted_out (opted_out),
- INDEX rspex_is_2006_donor (is_2006_donor),
- INDEX rspex_is_2007_donor (is_2007_donor),
- INDEX rspex_is_2008_donor (is_2008_donor),
- INDEX rspex_is_2009_donor (is_2009_donor),
- INDEX rspex_is_2010_donor (is_2010_donor),
- INDEX rspex_is_2011_donor (is_2011_donor),
- INDEX rspex_is_2012_donor (is_2012_donor),
- INDEX rspex_is_2013_donor (is_2013_donor),
- INDEX rspex_is_2014_donor (is_2014_donor)
+ INDEX rspex_opted_out (opted_out)
) COLLATE 'utf8_unicode_ci';
-- Move the data from the temp table into the persistent one
INSERT INTO silverpop_export (
id,contact_id,first_name,last_name,preferred_language,email,opted_out,
has_recurred_donation,highest_usd_amount,lifetime_usd_total,donation_count,
- is_2006_donor,is_2007_donor,is_2008_donor,is_2009_donor,is_2010_donor,
- is_2011_donor,is_2012_donor,is_2013_donor,is_2014_donor,last_ctid,
latest_currency,latest_native_amount,latest_usd_amount,latest_donation,
- city,country,state,postal_code,unsub_hash )
+ city,country,state,postal_code )
SELECT id,contact_id,first_name,last_name,preferred_language,email,opted_out,
has_recurred_donation,highest_usd_amount,lifetime_usd_total,donation_count,
- is_2006_donor,is_2007_donor,is_2008_donor,is_2009_donor,is_2010_donor,
- is_2011_donor,is_2012_donor,is_2013_donor,is_2014_donor,last_ctid,
latest_currency,latest_native_amount,latest_usd_amount,latest_donation,
- city,country,state,postal_code,unsub_hash
+ city,country,state,postal_code
FROM temp_silverpop_export;
-- Create a nice view to export from
@@ -376,7 +302,6 @@
email,
IFNULL(first_name, '') firstname,
IFNULL(last_name, '') lastname,
- last_ctid ContributionID,
country,
state,
postal_code,
@@ -388,17 +313,7 @@
latest_usd_amount,
latest_currency,
latest_native_amount,
- donation_count,
- IF(is_2006_donor, 'YES', 'NO') is_2006_donor,
- IF(is_2007_donor, 'YES', 'NO') is_2007_donor,
- IF(is_2008_donor, 'YES', 'NO') is_2008_donor,
- IF(is_2009_donor, 'YES', 'NO') is_2009_donor,
- IF(is_2010_donor, 'YES', 'NO') is_2010_donor,
- IF(is_2011_donor, 'YES', 'NO') is_2011_donor,
- IF(is_2012_donor, 'YES', 'NO') is_2012_donor,
- IF(is_2013_donor, 'YES', 'NO') is_2013_donor,
- IF(is_2014_donor, 'YES', 'NO') is_2014_donor,
- unsub_hash
+ donation_count
FROM silverpop_export
WHERE opted_out=0;
--
To view, visit https://gerrit.wikimedia.org/r/197406
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I911e734f59ca8e042d9f30d978f1a95233caabbd
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Ejegg <eeggles...@wikimedia.org>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
