Fundraising tech/Database schema

From MediaWiki.org
Jump to navigation Jump to search

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, see the source `git clone https://gerrit.wikimedia.org/r/wikimedia/fundraising/tools/DjangoBannerStats`. 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 https://gerrit.wikimedia.org/r/#/c/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 contribution No longer in use
  • 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.

Joining civicrm_contribution and contribution_tracking[edit]

select * from drupal.contribution_tracking t left join civicrm.civicrm_contribution c 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;

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;