Fundraising tech/Database schema
This is a description of WMF Fundraising database schema and fields.
The databases are only available in the private fundraising cluster, please request access through Phabricator.
See also Fun SQL Queries.
pgehres database
[edit]This database is named in honor of our dear friend Peter Gehres, and holds a slightly aggregated cache of banner impression statistics. It is written to by a command-line Django script called DjangoBannerStats (git repo). This cron job processes the series of logged web requests to `beacon/impression`.
One day, we hope to replace this database.
pgehres.bannerimpressions
[edit]Banner request counts, aggregated from the weblogs.
Banner impressions are now available in the `bannerimpressions` table. They are aggregated in 5-minute chunks and grouped by banner, campaign, project, language and country. The timestamp reflects the "middle" of the period (e.g. 1:00:00 to 1:05:00 is recorded as 1:02:30). The count is found in the `count` column and is corrected for any sampling.
P.S. - Raw impressions can be found in `bannerimpressions_raw`, but I would strongly recommend against querying it due to the massive number of rows. Queries on the aggregate table will be orders of magnitude faster.
-- pgehres
MariaDB [(none)]> use pgehres; MariaDB [pgehres]> describe bannerimpressions; +-------------+----------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+-------------------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | timestamp | timestamp | NO | MUL | CURRENT_TIMESTAMP | | | banner | varchar(255) | NO | MUL | | | | campaign | varchar(255) | NO | MUL | | | | project_id | smallint(3) unsigned | YES | MUL | NULL | | | language_id | smallint(3) unsigned | YES | MUL | NULL | | | country_id | smallint(3) unsigned | YES | MUL | NULL | | | count | mediumint(11) | YES | | 0 | | +-------------+----------------------+------+-----+-------------------+----------------+
`project_id`, `language_id` and `country_id` map to `project`, `language` and `country` tables in the same database.
One gotcha is that for performance reasons only the top 20-ish languages get a real language_id. Everything else gets a generic one (see gerrit:119740 for how to add to that list. TODO Split all languages.)
pgehres.landingpageimpression_raw
[edit]Sorry, there is no built-in aggregation. This table logs the landing page and UTM data for various URLs. We're making this data available for future landing page A/B testing, but there are no active consumers.
drupal database
[edit]Our Drupal modules add a few tables of interest.
drupal.contribution_source
[edit]Unpacked normalization of `contribution_tracking.utm_source` into its three components: banner, landing page, and payment method.
Join against `contribution_tracking`,
select * from contribution_tracking t left join contribution_source s on s.contribution_tracking_id = t.id;
(TODO: understand or fix why some rows are missing: phab:T98643)
banner - Name of the banner
drupal.contribution_tracking
[edit]For every person that lands on a payments.wiki (BROKEN: or donate.wiki) page, a row is created in the contribution_tracking table. This is what we have historically always used to track landing page impressions. The record is updated with a contribution ID if it results in a successful donation.
describe contribution_tracking; +-----------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | contribution_id | int(10) unsigned | YES | UNI | NULL | | | form_amount | varchar(20) | YES | | NULL | | | usd_amount | decimal(20,2) | YES | | NULL | | | note | text | YES | | NULL | | | referrer | varchar(4096) | YES | | NULL | | | anonymous | tinyint(3) unsigned | YES | | NULL | | | utm_source | varchar(128) | YES | MUL | NULL | | | utm_medium | varchar(128) | YES | MUL | NULL | | | utm_campaign | varchar(128) | YES | MUL | NULL | | | utm_key | varchar(128) | YES | | NULL | | | payments_form | varchar(128) | YES | | NULL | | | optout | tinyint(3) unsigned | YES | | NULL | | | language | varchar(8) | YES | MUL | NULL | | | country | varchar(2) | YES | | NULL | | | ts | varchar(14) | YES | MUL | NULL | | | owa_session | varchar(255) | YES | | NULL | | | owa_ref | int(11) | YES | | NULL | | +-----------------+---------------------+------+-----+---------+----------------+
Description of fields
[edit]- id: an autonumber
- contribution_id: Joins to the id column in civicrm_contribution. Contributions that were not actually completed, will be NULL.
- form_amount: The currency code and amount that the user had initially selected.
- usd_amount: Apparently broken
- note: No longer in use
- referrer: The page that got the user to our pipeline. Usually a wiki project page. Sometimes something totally different.
- anonymous: True if the user has selected an option indication that they wish to remain anonymous. THIS OPTION IS NOT PRESENT ON ALL FORMS
- utm_source: A string that builds when a user moves through our donation pipeline. Typically includes a banner name/email code, any landing page info, and a payment method
- utm_medium: A general indication of the group of places that this user came from (common ones are 'sitenotice', 'sidebar' or 'email')
- utm_campaign: The specific campaign that this person came from
- utm_key:
for recent-ish banners: how many times the person saw a banner (cookieCount) before they started this contributionNo longer in useA variety of "extra data" from banners and donatewiki such as screen size, whether user selected pay the fee, etc. Entries separated by ~ - payments_form: Also apparently broken
- optout: True if the user has selected an option indication that they wish to opt out of all bulk emails. This does not apply to the Thank You email, which we are legally obligated to send (at least in the US). THIS OPTION IS NOT PRESENT ON ALL FORMS
- language: The user's language preferences
- country: The user's country of web origin.
- ts: Timestamp
drupal.banner_history_contribution_associations
[edit]Links contribution_tracking id's with banner history log id's.
drupal.exchange_rates
[edit]Current and historical foreign exchange rates.
drupal.large_donation_notification
[edit]Donation amount thresholds that trigger an email, maintained by the `large_donation` module.
drupal.wmf_campaigns_campaign
[edit]Campaign names which will trigger an email upon matching donations. Maintained by the `wmf_campaigns` module.
civicrm database
[edit]This is the database that drives civi. As such, all information about completed transactions will be in there somewhere.
Note that we add many custom fields which are managed by CiviCRM and should not be queried directly due to dynamically generated table names, e.g. `civicrm_value_1_stock_information_10`.
civicrm.address
[edit]Billing address given by the donor. You must always restrict to `civicrm_address.is_primary = 1` when querying.
civicrm.civicrm_contact
[edit]Main record for a donor. We create a new contact for every donation, and deduping happens after the fact if ever.
civicrm.civicrm_contribution
[edit]This table contains all the financial information about every donation we have received.
describe civicrm_contribution; +--------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | contact_id | int(10) unsigned | NO | MUL | NULL | | | financial_type_id | int(10) unsigned | YES | MUL | NULL | | | contribution_page_id | int(10) unsigned | YES | MUL | NULL | | | payment_instrument_id | int(10) unsigned | YES | MUL | NULL | | | receive_date | datetime | YES | MUL | NULL | | | non_deductible_amount | decimal(20,2) | YES | | 0.00 | | | total_amount | decimal(20,2) | NO | MUL | NULL | | | fee_amount | decimal(20,2) | YES | | NULL | | | net_amount | decimal(20,2) | YES | | NULL | | | trxn_id | varchar(255) | YES | UNI | NULL | | | invoice_id | varchar(255) | YES | UNI | NULL | | | invoice_number | varchar(255) | YES | | NULL | | | currency | varchar(3) | YES | | NULL | | | cancel_date | datetime | YES | | NULL | | | cancel_reason | text | YES | | NULL | | | receipt_date | datetime | YES | | NULL | | | thankyou_date | datetime | YES | | NULL | | | source | varchar(255) | YES | MUL | NULL | | | amount_level | text | YES | | NULL | | | contribution_recur_id | int(10) unsigned | YES | MUL | NULL | | | is_test | tinyint(4) | YES | | 0 | | | is_pay_later | tinyint(4) | YES | | 0 | | | contribution_status_id | int(10) unsigned | YES | MUL | 1 | | | address_id | int(10) unsigned | YES | MUL | NULL | | | check_number | varchar(255) | YES | MUL | NULL | | | campaign_id | int(10) unsigned | YES | MUL | NULL | | | creditnote_id | varchar(255) | YES | MUL | NULL | | | tax_amount | decimal(20,2) | YES | | NULL | | | revenue_recognition_date | datetime | YES | | NULL | | +--------------------------+------------------+------+-----+---------+----------------+
Field Descriptions
[edit]- id - Primary key. Joins to drupal.contribution_tracking.contribution_id.
- contact_id - Joins to civicrm_contact.id
- financial_type_id - Joins to civicrm_financial_type.id. We're inconsistent about how we assign financial types.
- payment_instrument_id - Joins to civicrm_option_value where option_group_id = 10 ("payment_instrument"). This encodes the full payment method.
- receive_date - The date that the transaction was initiated on the payments system.
- total_amount - The donation amount in USD.
- trxn_id - A unique transaction identifier, not necessarily the same as the gateway's transaction ID. Usually starts with the gateway in all-caps, followed by the gateway's transaction id for this donation.
- thankyou_date - the date we sent the Thank You letter to the donor.
- source - Original currency and gross.
- contribution_recur_id - If this is a recurring payment, this will join to civicrm_contribution_recur.id, otherwise will be NULL.
- contribution_status_id - Joins to civicrm_option_value where option_group_id = 11 ("contribution_status").
- check_number - if it's a check, this should be a number.
civicrm.civicrm_contribution_tracking
[edit]This is the replacement for drupal.contribution_tracking. Notable changes:
- ts (timestamp) replaced with tracking_date (datetime)
- banner and landing_page are available as separate fields outside of utm_source
- Introduction of payment_method_id and payment_submethod_id - see https://phabricator.wikimedia.org/T354708#9489865 for how to join
- Addition of is_recurring field
- form_amount replaced with currency and amount
- Addition of user agent info: os, os_version, browser, browser_version
describe civicrm.civicrm_contribution_tracking;
+-----------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| contribution_id | int(10) unsigned | YES | MUL | NULL | |
| amount | decimal(20,3) | YES | | NULL | |
| currency | varchar(3) | YES | MUL | NULL | |
| usd_amount | decimal(20,2) | YES | | NULL | |
| is_recurring | tinyint(4) | YES | | NULL | |
| referrer | varchar(4096) | YES | | NULL | |
| utm_medium | varchar(128) | YES | MUL | NULL | |
| utm_campaign | varchar(128) | YES | MUL | NULL | |
| utm_key | varchar(128) | YES | | NULL | |
| gateway | varchar(32) | YES | | NULL | |
| appeal | varchar(64) | YES | | NULL | |
| payments_form_variant | varchar(64) | YES | | NULL | |
| banner | varchar(128) | YES | MUL | NULL | |
| landing_page | varchar(128) | YES | MUL | NULL | |
| payment_method_id | int(11) | YES | MUL | NULL | |
| payment_submethod_id | int(11) | YES | | NULL | |
| language | varchar(8) | YES | MUL | NULL | |
| country | varchar(2) | YES | MUL | NULL | |
| tracking_date | datetime | YES | MUL | NULL | |
| os | varchar(255) | YES | | NULL | |
| os_version | varchar(255) | YES | | NULL | |
| browser | varchar(255) | YES | | NULL | |
| browser_version | varchar(255) | YES | | NULL | |
| recurring_choice_id | int(11) | YES | | NULL | |
| device_type_id | int(11) | YES | | NULL | |
| banner_size_id | int(11) | YES | | NULL | |
| is_test_variant | tinyint(4) | YES | | NULL | |
| banner_variant | varchar(128) | YES | | NULL | |
| is_pay_fee | tinyint(4) | YES | | NULL | |
| mailing_identifier | varchar(32) | YES | MUL | NULL | |
| utm_source | varchar(128) | YES | | NULL | |
+-----------------------+------------------+------+-----+---------+-------+
Joining civicrm_contribution and contribution_tracking
[edit]select *
from civicrm.civicrm_contribution_tracking t
left join civicrm.civicrm_contribution c on c.id = t.contribution_id;
select *
from civicrm.civicrm_contribution c
left join civicrm.civicrm_contribution_tracking t on c.id = t.contribution_id;
civicrm.civicrm_email
[edit]Always restrict to civicrm_email.is_primary = 1 unless you're doing something crazy.
civicrm.wmf_contribution_extra
[edit]describe wmf_contribution_extra; +------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | entity_id | int(10) unsigned | NO | UNI | NULL | | | settlement_date | datetime | YES | MUL | NULL | | | total_usd | decimal(20,2) | YES | MUL | NULL | | | gateway_account | varchar(255) | YES | MUL | NULL | | | gateway | varchar(255) | YES | MUL | NULL | | | gateway_txn_id | varchar(255) | YES | MUL | NULL | | | gateway_status_raw | varchar(255) | YES | MUL | NULL | | | gateway_date | datetime | YES | MUL | NULL | | | original_amount | decimal(20,2) | YES | MUL | NULL | | | original_currency | varchar(255) | YES | MUL | NULL | | | settlement_usd | decimal(20,2) | YES | MUL | NULL | | | settlement_currency | varchar(255) | YES | MUL | NULL | | | deposit_date | datetime | YES | MUL | NULL | | | deposit_usd | decimal(20,2) | YES | MUL | NULL | | | deposit_currency | varchar(255) | YES | MUL | NULL | | | parent_contribution_id | int(11) | YES | MUL | NULL | | | finance_only | tinyint(4) | YES | MUL | NULL | | | source_name | varchar(255) | YES | MUL | NULL | | | source_type | varchar(255) | YES | MUL | NULL | | | source_host | varchar(255) | YES | MUL | NULL | | | source_run_id | varchar(255) | YES | MUL | NULL | | | source_version | varchar(255) | YES | MUL | NULL | | | source_enqueued_time | datetime | YES | MUL | NULL | | | postmark_date | datetime | YES | | NULL | | | no_thank_you | varchar(255) | YES | MUL | NULL | | +------------------------+------------------+------+-----+---------+----------------+
The `wmf_civicrm` module adds its own schema to `civicrm_contribution`, stored in the `wmf_contribution_extra` table. Perhaps it should be in the Drupal database, but CiviCRM core doesn't know about custom tables in another database.
Fields
[edit]- id - primary key
- entity_id - Joins to civicrm_contribution.id.
- gateway - Which payment processor handled this transaction.
- gateway_account - Account name for processors with multiple accounts.
- gateway_txn_id - Order ID at the processor. Note that this is not necessarily unique, processors have funny ways of recording refunds, recurring payments and so on.
- original_amount - Gross in the native currency.
- original_currency - Native currency code.
- parent_contribution_id - Link to civicrm_contribution.id, for refunds only. This should be deprecated with Civi 4.6.
- finance_only - We're hiding this record from most reports. (TODO: document why)
- source_name - Specific system responsible for creating this donation record.
- source_type - Class of source system.
- source_host - Originating machine.
- source_version - Revision of the code that produced this record.
- source_enqueued_time - Time at which this message was first sent to the completed donation queue.
- no_thank_you - A string explaining why we aren't sending an automatic thank-you letter. Usually NULL. If there is content in this field, the `thank_you` job will not send an automatic letter.
Joining wmf_contribution_extra and civicrm_contribution
[edit]select * from civicrm_contribution c join wmf_contribution_extra e on e.entity_id = c.id;
civicrm.wmf_donor
[edit]This table stores summary data about donors' donations in the wmf_donor table (Civicrm database). The data there is generated using mysql triggers (see below) from the contributions a contact has. The data covers such things as their total giving in a calendar year, a financial year, the last donation, the first donation. It is used by analytics and partially exported to Acoustic. It is also used for searching and reporting within CiviCRM.
Triggers
[edit]Triggers in CiviCRM generate entries in log tables and in wmf_donor. [TODO: anything else?]
On developers' local sites triggers are generated when logging is enabled (and removed when logging is disabled), which is CiviCRM normal behaviour. On production our CiviCRM db user does not have permission to create triggers and hence we have a process to track the db triggers through git and for Fr-tech-ops to run that file. In order for the triggers to be output rather than run directly we have a CiviCRM setting (logging_no_trigger_permission) which is set to TRUE on production and FALSE on developer instances.
Like other settings, it can be fetched and altered using the api:
drush @wmff cvapi setting.get return=logging_no_trigger_permission
drush @wmff cvapi Setting.create logging_no_trigger_permission=0
Triggers are rebuilt when logging is disabled & enabled or when a system flush is done:
drush @wmff cvapi System.flush triggers=1
When the above flush command is run, one of the following things will happen:
- If logging_no_trigger_permission is FALSE, the triggers in the database will be updated; or
- If logging_no_trigger_permission is TRUE, an output file will be generated in drupal/sites/default/files/civicrm/ConfigAndLog/CiviCRM.trigger*. This file should be checked in to git sites/all/modules/wmf_civicrm/scripts/triggers.mysql
The Civi UI can also be used to turn logging off and on (under Administer->System Settings->Misc). In this case, a dialogue should appear indicating where the new triggers SQL file has been saved.
This setting can also be toggled using the in-browser JS console api method (you will need to refresh the page afterwards):
CRM.api3('setting','create', {'logging': 0});
CRM.api3('setting','create', {'logging': 1});
When fields are added, new triggers are added manually on production.
Custom fields
[edit]Local dev Civi setups do not have custom fields that have been added on production via the UI. They should, however, have all the custom fields that have logic attached to them.
fredge database
[edit]fredge.payments_fraud
[edit]Summary of risk score and validation outcome for each donation attempt.
fredge.payments_fraud_breakdown
[edit]Individual components of `payments_fraud.risk_score`, join against that table like:
select * from payments_fraud f join payments_fraud_breakdown b on b.payments_fraud_id = f.id;
fredge.payments_initial
[edit]Information about donation outcome, measured when the initial donation workflow is completed.
Join to `contribution_tracking`,
select * from fredge.payments_initial i join drupal.contribution_tracking t on t.id = i.contribution_tracking_id;