Purchase Reconciliation for MITx Online via dbt

[dseaton]

What intermediate & mart models support purchase reconciliation?

Here is a first pass at which dbt models will support purchase reconciliation.

Mart Models

• martmitxonlinepurchase_reconciliation

Intermediate Models

• intmitxonlinepurchases
• intmitxonlineenrollments
• intmitxonlineusers
• intmitxonlinecourse_runs

These models should also be able to generate a purchase reconciliation report for xPro.

Current State of Purchase Reconciliation

Purchase Reconciliation reporting allows the business ops team to validate charges, confirm details related to refunds, and produce reports related to purchases. We have created a working purchase reconciliation for MITx Online in BI: https://bi.odl.mit.edu/queries/1190

The following tables are utilized by this report:

• ecommerce_order
• ecommerce_transaction
• ecommerce_line
• ecommerce_product
• courses_courserun
• courses_courseruncertificate
• courses_program
• users_user
• courses_courserunenrollment
• ecommerce_discountredemption
• ecommerce_discount

The fields in the report are below.

• ecommerce_order.order_id
• user_info.username
• user_info..email
• courses_courserun.readable_id
• courses_courserun.title
• courses_courserunenrollment.id
• courses_courserunenrollment.created_on
• courses_courserunenrollment.enrollment_mode
• courses_courserunenrollment.active
• courses_courserunenrollment.change_status
• ecommerce_order.user_id
• ecommerce_order.created_on
• ecommerce_order."Receipt_Url" (https://mitxonline.mit.edu/orders/receipt/ || id as "Receipt_Url")
• courses_courserun.start_date
• courses_courserun.end_date
• courses_courserun.upgrade_deadline
• courses_courserun.course_id
• ecommerce_product.price
• courses_courseruncertificate.created_on
• courses_program.readable_id
• (ecommerce_discountredemption + ecommerce_discount).discount_type
• (ecommerce_discountredemption + ecommerce_discount).discount_code
• ecommerce_order.state
• ecommerce_transaction.data -> 'status'
• ecommerce_transaction.data -> 'req_amount'
• ecommerce_transaction.data -> 'req_transaction_uuid'
• ecommerce_transaction.data -> 'req_transaction_type'
• ecommerce_transaction.(data ->> 'req_bill_to_forename') || ' ' || (data ->> 'req_bill_to_surname')
• ecommerce_transaction.data -> 'auth_code'
• ecommerce_transaction.data -> 'card_type_name'
• ecommerce_transaction.data -> 'req_card_number'
• ecommerce_transaction.data -> 'req_customer_ip_address'
• ecommerce_transaction.data -> 'req_payment_method'
• ecommerce_transaction.date(data ->> 'signed_date_time')
• ecommerce_transaction.date(data ->> 'submitTimeUtc')

Note, the transformed names in the report were selected by business ops. We want to migrate to our new naming convention.

[rachellougee]

@Ferdi @pdpinch Should we start to create the Purchase Reconciliation report in the data platform? Looks like the fields are already in our data lake