Create combined enrollments mart

[rachellougee]

Description/Context

Create a combined enrollments mart from different platforms - MITx, xPro, bootcamps, including common fields for enrollments, orders, coupons and certificates. So we can have a unified mart for enrollment details from our OL apps, and eventually replace query like https://bi.odl.mit.edu/queries/1142/source

[rachellougee]

In testing https://github.com/mitodl/ol-data-platform/pull/897, I realized that enrollments to orders are not always 1 to 1, meaning that there could be multiple fulfilled orders for the same enrollment, e.g. MicroMasters and Bootcamp, so we will need to figure out how to present the order information in enrollments if we have to fit them into one table

• platform (MITx, xPro, Bootcamp)
• courserunenrollment_is_active
• courserunenrollment_created_on
• courserunenrollment_enrollment_mode
• courserunenrollment_enrollment_status
• user_id
• courserun_id
• courserun_title
• courserun_readable_id
• user_username
• user_email
• user_full_name

These below fields could be multiple fulfilled orders

• order_reference_number
• order_total_price_paid
• order_created_on
• order_line_price
• coupon_code
• coupon_redeemed_on
• coupon_discount_amount_text
• payment_transaction_id
• payment_transaction_bill_to_address_state
• payment_transaction_bill_to_address_country

For Bootcamp, the payment can be split into multiple orders (installments), order_total_price_paid would be SUM of orders for the same application here, i am not entirely sure the order_referencenumber and payment* would be useful for Bootcamp

@pdpinch @KatelynGit Any idea how we should handle these orders in enrollment table?

[rachellougee]

Per discussion with @pdpinch @KatelynGit in data platform meeting, we decided to

• For MicroMasters orders, pick the most recent order/payment if users paid for same course run multiple times
• For xPro/MITx Online, there should always be one fulfilled order per enrollment
• For Bootcamps, sum of all the orders (installments) paid for the same application/enrollment, but we need to wait for @KatelynGit to communicate with Finance team https://github.mit.edu/bootcamps/bootcamps-issues/issues/31 on what to do with order_reference_number and cybersource authorization code for payments made from multiple orders

I will update https://github.com/mitodl/ol-data-platform/pull/897 to reflect these changes except bootcamps

[KatelynGit]

I've followed up with Bryan will let you know when I hear back thanks

[rachellougee]

For Bootcamps, I think it might makes sense to show orders in separate rows if paid in multiple orders, It would be similar to https://bi.odl.mit.edu/queries/968 (e.g. order id 5127, 5136,5186,5188,5193,5215,5253). It's per application/enrollment in https://bi.odl.mit.edu/queries/969 but only total_paid is related to these orders. Finance team might still want to see individual payments. @KatelynGit let me know if that's what Finance want

[KatelynGit]

@rachellougee I know we talked but just adding it here- I confirmed with Bryan that they'd like to see data the individual payment level for example authorization code is a field they'd want for each payment.

[rachellougee]

@KatelynGit talked to Bryan. I think they want to see the individual payments. So I will show individual orders/payments for the same enrollment for Bootcamps