Ejegg has uploaded a new change for review. (
https://gerrit.wikimedia.org/r/394494 )
Change subject: WIP further refactor
......................................................................
WIP further refactor
Use a different intermediary table to generate the aggregate
statistics. This ensures that we're only considering valid
combinations of primary email and not-deleted contacts.
It also lets us do fewer joins when we're generating the stats.
Change-Id: Iddd44ac5ca4d4914dc9e156264c2447b11ab1f60
---
M silverpop_export/update_table.sql
1 file changed, 34 insertions(+), 26 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools
refs/changes/94/394494/1
diff --git a/silverpop_export/update_table.sql
b/silverpop_export/update_table.sql
index 9e361e1..e598cd2 100755
--- a/silverpop_export/update_table.sql
+++ b/silverpop_export/update_table.sql
@@ -13,6 +13,7 @@
DROP TABLE IF EXISTS silverpop_export_dedupe_email;
DROP TABLE IF EXISTS silverpop_export_stat;
DROP TABLE IF EXISTS silverpop_export_address;
+DROP TABLE IF EXISTS silverpop_email_contact_ids;
CREATE TABLE IF NOT EXISTS silverpop_export_staging(
-- General information about the contact
@@ -57,6 +58,23 @@
latest_donation datetime
) COLLATE 'utf8_unicode_ci';
+-- This table maps all email addresses to all the associated,
+-- not-deleted contact IDs for whom they are the primary email
+CREATE TABLE silverpop_email_contact_ids (
+ contact_id int unsigned PRIMARY KEY,
+ email varchar(255),
+ on_hold tinyint,
+ INDEX seci_email(email)
+);
+
+INSERT INTO silverpop_email_contact_ids
+ SELECT e.contact_id, e.email, e.on_hold
+ FROM civicrm.civicrm_email e
+ INNER JOIN civicrm.civicrm_contact c ON e.contact_id = c.id
+ WHERE c.is_deleted = 0
+ AND e.is_primary = 1
+ AND e.email IS NOT NULL AND e.email != '';
+
-- Populate, or append to, the storage table all contacts that
-- have an email address.
-- (15 minutes)
@@ -66,20 +84,22 @@
e.contact_id, c.hash, e.email, c.first_name, c.last_name,
REPLACE(c.preferred_language, '_', '-'),
(c.is_opt_out OR c.do_not_email OR e.on_hold OR COALESCE(d.do_not_solicit,
0))
- FROM civicrm.civicrm_email e
+ FROM silverpop_email_contact_ids e
INNER 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
- ORDER BY e.id DESC
+ ORDER BY e.contact_id DESC
ON DUPLICATE KEY UPDATE
preferred_language =
COALESCE(silverpop_export_staging.preferred_language,
REPLACE(c.preferred_language, '_', '-')),
opted_out = (opted_out OR c.is_opt_out OR c.do_not_email OR e.on_hold OR
COALESCE(d.do_not_solicit, 0));
ALTER TABLE silverpop_export_staging
ADD INDEX spex_opted_out (opted_out);
+
+-- We only need stats for people who are opted in
+DELETE seci
+FROM silverpop_email_contact_ids seci
+INNER JOIN silverpop_export_staging ex ON seci.email = ex.email
+WHERE ex.opted_out = 1;
-- Find the latest donation for each email address. Ordering by
-- receive_date and total_amount descending should always insert
@@ -98,8 +118,7 @@
ct.total_amount,
ct.receive_date
FROM
- civicrm.civicrm_email e FORCE INDEX(UI_email)
- INNER JOIN silverpop_export_staging exs ON e.email = exs.email
+ silverpop_email_contact_ids e
INNER JOIN civicrm.civicrm_contribution ct
ON ct.contact_id = e.contact_id
INNER JOIN civicrm.wmf_contribution_extra ex
@@ -109,8 +128,7 @@
WHERE
ct.receive_date IS NOT NULL AND
ct.total_amount > 0 AND -- Refunds don't count
- ct.contribution_status_id = 1 AND -- 'Completed'
- exs.opted_out = 0
+ ct.contribution_status_id = 1 -- 'Completed'
ORDER BY
ct.receive_date DESC,
ct.total_amount DESC
@@ -132,15 +150,13 @@
ct.total_amount,
ct.receive_date
FROM
- civicrm.civicrm_email e FORCE INDEX(UI_email)
- INNER JOIN silverpop_export_staging exs ON e.email = exs.email
+ silverpop_email_contact_ids e
INNER JOIN civicrm.civicrm_contribution ct ON ct.contact_id = e.contact_id
INNER JOIN civicrm.wmf_contribution_extra ex ON ex.entity_id = ct.id
WHERE
ct.receive_date IS NOT NULL AND
ct.total_amount > 0 AND -- Refunds don't count
- ct.contribution_status_id = 1 AND -- 'Completed'
- exs.opted_out = 0
+ ct.contribution_status_id = 1 -- 'Completed'
ORDER BY
ct.total_amount DESC,
ct.receive_date DESC
@@ -162,13 +178,11 @@
e.email, SUM(ct.total_amount), COUNT(*),
MAX(IF(SUBSTRING(ct.trxn_id, 1, 9) = 'RECURRING', 1, 0)),
MIN(ct.receive_date)
- FROM civicrm.civicrm_email e FORCE INDEX(UI_email)
- JOIN silverpop_export_staging ex ON e.email = ex.email
+ FROM silverpop_email_contact_ids e
JOIN civicrm.civicrm_contribution ct ON e.contact_id = ct.contact_id
WHERE ct.receive_date IS NOT NULL AND
ct.total_amount > 0 AND -- Refunds don't count
- ct.contribution_status_id = 1 AND -- Only completed status
- ex.opted_out = 0
+ ct.contribution_status_id = 1 -- Only completed status
GROUP BY e.email;
-- Postal addresses by email
@@ -184,32 +198,26 @@
-- Get latest address for each email.
INSERT INTO silverpop_export_address
SELECT e.email, ctry.iso_code, st.name, a.postal_code, a.timezone
- FROM civicrm.civicrm_email e
- JOIN silverpop_export_staging ex
- ON e.email = ex.email
+ FROM silverpop_email_contact_ids e
JOIN civicrm.civicrm_address a
ON e.contact_id = a.contact_id AND a.is_primary = 1
JOIN civicrm.civicrm_country ctry
ON a.country_id = ctry.id
LEFT JOIN civicrm.civicrm_state_province st
ON a.state_province_id = st.id
- WHERE ex.opted_out = 0
ORDER BY a.id DESC
ON DUPLICATE KEY UPDATE email = e.email;
-- Fill in missing countries from contribution_tracking
INSERT INTO silverpop_export_address (email, country)
SELECT e.email, ct.country
- FROM civicrm.civicrm_email e
- JOIN silverpop_export_staging ex
- ON e.email = ex.email
+ FROM silverpop_email_contact_ids e
JOIN civicrm.civicrm_contribution cc
ON cc.contact_id = e.contact_id
JOIN drupal.contribution_tracking ct
ON ct.contribution_id = cc.id
JOIN civicrm.civicrm_country ctry
ON ct.country = ctry.iso_code # filter out invalid c_t countries
- WHERE ex.opted_out = 0
ORDER BY cc.id DESC
ON DUPLICATE KEY UPDATE email = e.email;
--
To view, visit https://gerrit.wikimedia.org/r/394494
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Iddd44ac5ca4d4914dc9e156264c2447b11ab1f60
Gerrit-PatchSet: 1
Gerrit-Project: wikimedia/fundraising/tools
Gerrit-Branch: master
Gerrit-Owner: Ejegg <ej...@ejegg.com>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
