Awight has uploaded a new change for review.
https://gerrit.wikimedia.org/r/282304
Change subject: [WIP] Simplify export by not explicitly tracking opt-outs
......................................................................
[WIP] Simplify export by not explicitly tracking opt-outs
By doing the opt-out check up front, and inverting the logic to calculate the
opt-in set and subtract it from all known addresses, we avoid having to manage
the opted_out column everywhere. We lose information about opted out addresses,
which I think is fair.
Change-Id: I8f5d390ef287ea446e964146872be52655af096f
---
M silverpop_export/update_table.sql
1 file changed, 29 insertions(+), 39 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools
refs/changes/04/282304/1
diff --git a/silverpop_export/update_table.sql
b/silverpop_export/update_table.sql
index 76c8963..a617b1a 100755
--- a/silverpop_export/update_table.sql
+++ b/silverpop_export/update_table.sql
@@ -19,7 +19,6 @@
last_name varchar(128),
preferred_language varchar(5),
email varchar(255),
- opted_out tinyint(1),
-- Lifetime contribution statistics
has_recurred_donation tinyint(1),
@@ -42,8 +41,7 @@
INDEX spex_contact_id (contact_id),
INDEX spex_email (email),
INDEX spex_city (city),
- INDEX spex_country (country),
- INDEX spex_opted_out (opted_out)
+ INDEX spex_country (country)
) COLLATE 'utf8_unicode_ci';
CREATE TABLE IF NOT EXISTS silverpop_export_latest(
@@ -57,18 +55,21 @@
-- Populate, or append to, the storage table all contacts that
-- have an email address. ID is civicrm_email.id.
INSERT INTO silverpop_export_staging
- (id, contact_id, email, first_name, last_name, preferred_language, opted_out)
+ (id, contact_id, email, first_name, last_name, preferred_language)
SELECT
e.id, e.contact_id, e.email, c.first_name, c.last_name,
- IF(SUBSTRING(c.preferred_language, 1, 1) = '_', 'en',
SUBSTRING(c.preferred_language, 1, 2)),
- (c.is_opt_out OR c.do_not_email OR e.on_hold OR COALESCE(d.do_not_solicit,
0))
+ IF(SUBSTRING(c.preferred_language, 1, 1) = '_', 'en',
SUBSTRING(c.preferred_language, 1, 2))
FROM civicrm.civicrm_email e
LEFT JOIN civicrm.civicrm_contact c ON e.contact_id = c.id
LEFT JOIN civicrm.wmf_donor d ON d.entity_id = c.id
WHERE
e.email IS NOT NULL AND e.email != ''
AND c.is_deleted = 0
- AND e.is_primary = 1;
+ AND !c.is_opt_out
+ AND !c.do_not_email
+ AND !COALESCE(d.do_not_solicit, 0)
+ AND e.is_primary = 1
+ AND !e.on_hold;
-- Find the latest donation for each email address. Ordering by
-- recieve_date and total_amount descending should always insert
@@ -131,13 +132,12 @@
maxid int,
preferred_language varchar(5),
country varchar(2),
- opted_out tinyint(1),
INDEX spexde_email (email)
) COLLATE 'utf8_unicode_ci';
-INSERT INTO silverpop_export_dedupe_email (email, maxid, opted_out)
- SELECT email, max(id) maxid, max(opted_out) opted_out
+INSERT INTO silverpop_export_dedupe_email (email, maxid)
+ SELECT email, max(id) maxid
FROM silverpop_export_staging
FORCE INDEX (spex_email)
GROUP BY email
@@ -166,7 +166,6 @@
UPDATE silverpop_export_staging ex, silverpop_export_dedupe_email exde
SET
- ex.opted_out = exde.opted_out,
ex.preferred_language = exde.preferred_language,
ex.country = exde.country
WHERE
@@ -220,8 +219,7 @@
-- Leave opted out non-contributors so we don't spam anyone
DELETE FROM silverpop_export_staging
WHERE
- silverpop_export_staging.latest_donation IS NULL AND
- silverpop_export_staging.opted_out = 0;
+ silverpop_export_staging.latest_donation IS NULL;
-- Join on civicrm address where we do not already have a geolocated
-- address from contribution tracking
@@ -235,8 +233,7 @@
ex.postal_code = addr.postal_code,
ex.state = st.name
WHERE
- ex.country IS NULL AND
- ex.opted_out = 0;
+ ex.country IS NULL;
-- And now updated by civicrm address where we have a country but no
-- city from contribution tracking; the countries must match
@@ -251,8 +248,7 @@
ex.postal_code = addr.postal_code,
ex.state = st.name
WHERE
- ex.city IS NULL AND
- ex.opted_out = 0;
+ ex.city IS NULL;
-- Reconstruct the donors likely language from their country if it
-- exists from a table of major language to country.
@@ -261,8 +257,7 @@
WHERE
ex.country IS NOT NULL AND
ex.preferred_language IS NULL AND
- ex.country = cl.country AND
- ex.opted_out = 0;
+ ex.country = cl.country;
-- Normalize the data prior to final export
UPDATE silverpop_export_staging SET preferred_language='en' WHERE
preferred_language IS NULL;
@@ -275,8 +270,8 @@
latest_usd_amount = 0,
latest_donation = NOW(),
has_recurred_donation = 0
- WHERE donation_count IS NULL AND opted_out = 0;
-UPDATE silverpop_export_staging SET country='US' where country IS NULL AND
opted_out = 0;
+ WHERE donation_count IS NULL;
+UPDATE silverpop_export_staging SET country='US' where country IS NULL;
--
-- Collect email addresses which should be excluded for various reasons, such
as:
@@ -287,24 +282,23 @@
DROP TABLE IF EXISTS silverpop_excluded;
CREATE TABLE IF NOT EXISTS silverpop_excluded(
- contact_id int unsigned,
- email varchar(255)
+ email varchar(255),
+
+ INDEX sx_email (email),
+ CONSTRAINT sx_email UNIQUE (email)
);
--- Grab logged old addresses and exclude. FIXME: We take a long shortcut and
--- include all current addresses as well, because we'll have to subtract the
--- active addresses either way. I don't know whether this is wasteful.
+-- Grab all addresses ever entered in the CRM, and exclude everything by
+-- default.
INSERT INTO silverpop_excluded
- SELECT contact_id, email
- FROM log_civicrm.log_civicrm_email e
- GROUP BY email;
+ SELECT email
+ FROM log_civicrm.log_civicrm_email e;
+-- Don't exclude any primary address used by an active, n contact.
DELETE silverpop_excluded
FROM silverpop_excluded
JOIN silverpop_export_staging s
- ON s.email = silverpop_excluded.email
- WHERE
- s.opted_out = 0;
+ ON s.email = silverpop_excluded.email;
-- Prepare the persistent export table.
DROP TABLE IF EXISTS silverpop_export;
@@ -318,7 +312,6 @@
last_name varchar(128),
preferred_language varchar(5),
email varchar(255),
- opted_out tinyint(1),
-- Lifetime contribution statistics
has_recurred_donation tinyint(1),
@@ -343,17 +336,16 @@
INDEX rspex_city (city),
INDEX rspex_country (country),
INDEX rspex_postal (postal_code),
- INDEX rspex_opted_out (opted_out),
CONSTRAINT sp_email UNIQUE (email)
) COLLATE 'utf8_unicode_ci';
-- Move the data from the staging table into the persistent one
INSERT INTO silverpop_export (
- id,contact_id,first_name,last_name,preferred_language,email,opted_out,
+ id,contact_id,first_name,last_name,preferred_language,email,
has_recurred_donation,highest_usd_amount,lifetime_usd_total,donation_count,
latest_currency,latest_native_amount,latest_usd_amount,latest_donation,
city,country,state,postal_code )
-SELECT id,contact_id,first_name,last_name,preferred_language,email,opted_out,
+SELECT id,contact_id,first_name,last_name,preferred_language,email,
has_recurred_donation,highest_usd_amount,lifetime_usd_total,donation_count,
latest_currency,latest_native_amount,latest_usd_amount,latest_donation,
city,country,state,postal_code
@@ -378,6 +370,4 @@
latest_currency,
latest_native_amount,
donation_count
- FROM silverpop_export
- WHERE opted_out=0;
-
+ FROM silverpop_export;
--
To view, visit https://gerrit.wikimedia.org/r/282304
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I8f5d390ef287ea446e964146872be52655af096f
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Awight <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
