Ejegg has uploaded a new change for review. (
https://gerrit.wikimedia.org/r/394466 )
Change subject: WIP more Silverpop export refactor
......................................................................
WIP more Silverpop export refactor
Remove city, as it's not exported
Create indexes just before they're needed
Filter on opted_out for more stats
Set default language in same update as countrylang
One small issue: the fact that all the stats are generated by aggregating
on email now means we're potentially counting a latest donation by
someone whose non-primary email is a...@b.com for the person whose
primary email is a...@b.com
Change-Id: I42649cd9dce0431c3ad2d78521642376ca15ff30
---
M silverpop_export/update_table.sql
1 file changed, 25 insertions(+), 28 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/tools
refs/changes/66/394466/1
diff --git a/silverpop_export/update_table.sql
b/silverpop_export/update_table.sql
index 4c83015..9e361e1 100755
--- a/silverpop_export/update_table.sql
+++ b/silverpop_export/update_table.sql
@@ -42,14 +42,10 @@
highest_donation_date datetime null,
-- Address information
- city varchar(128),
country varchar(2),
state varchar(64),
postal_code varchar(128),
- timezone varchar(8),
-
- INDEX spex_country (country),
- INDEX spex_opted_out (opted_out)
+ timezone varchar(8)
) COLLATE 'utf8_unicode_ci';
CREATE TABLE IF NOT EXISTS silverpop_export_latest(
@@ -71,7 +67,7 @@
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
- LEFT JOIN civicrm.civicrm_contact c ON e.contact_id = c.id
+ 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 != ''
@@ -81,6 +77,9 @@
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);
-- Find the latest donation for each email address. Ordering by
-- receive_date and total_amount descending should always insert
@@ -100,7 +99,7 @@
ct.receive_date
FROM
civicrm.civicrm_email e FORCE INDEX(UI_email)
- INNER JOIN silverpop_export_staging exs ON e.email=exs.email
+ INNER JOIN silverpop_export_staging exs ON e.email = exs.email
INNER JOIN civicrm.civicrm_contribution ct
ON ct.contact_id = e.contact_id
INNER JOIN civicrm.wmf_contribution_extra ex
@@ -110,7 +109,8 @@
WHERE
ct.receive_date IS NOT NULL AND
ct.total_amount > 0 AND -- Refunds don't count
- ct.contribution_status_id = 1 -- 'Completed'
+ ct.contribution_status_id = 1 AND -- 'Completed'
+ exs.opted_out = 0
ORDER BY
ct.receive_date DESC,
ct.total_amount DESC
@@ -133,7 +133,7 @@
ct.receive_date
FROM
civicrm.civicrm_email e FORCE INDEX(UI_email)
- INNER JOIN silverpop_export_staging exs ON e.email=exs.email
+ INNER JOIN silverpop_export_staging exs ON e.email = exs.email
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
@@ -163,8 +163,8 @@
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
- JOIN civicrm.civicrm_contribution ct ON e.contact_id=ct.contact_id
+ JOIN silverpop_export_staging ex ON e.email = ex.email
+ 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
@@ -174,7 +174,6 @@
-- Postal addresses by email
CREATE TABLE silverpop_export_address (
email varchar(255) PRIMARY KEY,
- city varchar(128),
country varchar(2),
state varchar(64),
postal_code varchar(128),
@@ -184,7 +183,7 @@
-- (16 minutes)
-- Get latest address for each email.
INSERT INTO silverpop_export_address
-SELECT e.email, a.city, ctry.iso_code, st.name, a.postal_code, a.timezone
+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
@@ -234,24 +233,23 @@
ex.highest_native_amount = hg.highest_native_amount,
ex.highest_usd_amount = hg.highest_usd_amount,
ex.highest_donation_date = hg.highest_donation_date,
- ex.city = addr.city,
ex.country = addr.country,
ex.postal_code = addr.postal_code,
ex.state = addr.state,
ex.timezone = addr.timezone;
--- Reconstruct the donors likely language from their country if it
--- exists from a table of major language to country.
-UPDATE silverpop_export_staging ex, silverpop_countrylangs cl
- SET ex.preferred_language = cl.lang
- WHERE
- ex.country IS NOT NULL AND
- ex.preferred_language IS NULL AND
- ex.country = cl.country AND
- ex.opted_out = 0;
+ALTER TABLE silverpop_export_staging
+ ADD INDEX spex_cpo (country, preferred_language, opted_out);
--- Still no language? Default 'em to English
-UPDATE silverpop_export_staging SET preferred_language='en' WHERE
preferred_language IS NULL;
+-- Reconstruct the donors likely language from their country if it
+-- exists from a table of major language to country. If we have no
+-- country data, default the language to English
+UPDATE silverpop_export_staging ex
+ LEFT JOIN silverpop_countrylangs cl ON ex.country = cl.country
+ SET ex.preferred_language = COALESCE(cl.lang, 'en')
+ WHERE
+ ex.preferred_language IS NULL AND
+ ex.opted_out = 0;
--
-- Collect email addresses which should be excluded for various reasons, such
as:
@@ -319,7 +317,6 @@
first_donation_date datetime,
-- Address information
- city varchar(128),
country varchar(2),
state varchar(64),
postal_code varchar(128),
@@ -335,12 +332,12 @@
has_recurred_donation,highest_usd_amount,highest_native_amount,
highest_native_currency,highest_donation_date,lifetime_usd_total,donation_count,
latest_currency,latest_currency_symbol,latest_native_amount,latest_usd_amount,
- latest_donation, first_donation_date,city,country,state,postal_code,timezone
)
+ latest_donation, first_donation_date,country,state,postal_code,timezone )
SELECT contact_id,contact_hash,first_name,last_name,preferred_language,email,
has_recurred_donation,highest_usd_amount,highest_native_amount,
highest_native_currency,highest_donation_date,lifetime_usd_total,donation_count,
latest_currency,latest_currency_symbol,latest_native_amount,latest_usd_amount,
- latest_donation,first_donation_date,city,country,state,postal_code,timezone
+ latest_donation,first_donation_date,country,state,postal_code,timezone
FROM silverpop_export_staging
WHERE opted_out=0;
--
To view, visit https://gerrit.wikimedia.org/r/394466
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I42649cd9dce0431c3ad2d78521642376ca15ff30
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
