sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions

fields: - name: id type: STRING mode: NULLABLE description: Subscription ID. - name: customer type: RECORD mode: NULLABLE description: Customer who owns the subscription. fields: - name: id type: STRING mode: NULLABLE description: Customer ID. - name: address type: RECORD mode: NULLABLE description: The customer's address. fields: - name: country type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). - name: created type: TIMESTAMP mode: NULLABLE description: Time at which the customer was created. - name: default_source_id type: STRING mode: NULLABLE description: 'ID of the default payment source for the customer. This isn''t available for customers that were deleted before the initial Fivetran Stripe sync.' - name: discount type: RECORD mode: NULLABLE description: 'The current discount active on the customer, if there is one. This isn''t available for customers that were deleted before the initial Fivetran Stripe sync.' fields: - name: id type: STRING mode: NULLABLE description: Discount ID. - name: coupon type: RECORD mode: NULLABLE description: The coupon applied to create this discount. fields: - name: id type: STRING mode: NULLABLE description: Coupon ID. - name: amount_off type: INTEGER mode: NULLABLE description: Amount (in the `currency` specified) that will be taken off the subtotal of any invoices for this customer. - name: created type: TIMESTAMP mode: NULLABLE description: Time at which the coupon was created. - name: currency type: STRING mode: NULLABLE description: If `amount_off` has been set, the three-letter ISO code for the currency of the amount to take off. - name: duration type: STRING mode: NULLABLE description: 'One of "forever", "once", and "repeating". Describes how long a customer who applies this coupon will get the discount. "forever" applies to all charges from a subscription with this coupon applied. "once" applies to the first charge from a subscription with this coupon applied. "repeating" applies to charges in the first `duration_in_months` months from a subscription with this coupon applied.' - name: duration_in_months type: INTEGER mode: NULLABLE description: If `duration` is "repeating", the number of months the coupon applies. Null if coupon `duration` is "forever" or "once". - name: metadata type: JSON mode: NULLABLE description: Set of key-value pairs attached to the coupon, stored as a JSON object. - name: name type: STRING mode: NULLABLE description: Name of the coupon displayed to customers on invoices or receipts. - name: percent_off type: FLOAT mode: NULLABLE description: Percent that will be taken off the subtotal of any invoices for this customer for the duration of the coupon. For example, a coupon with `percent_off` of 50 will make a $100 invoice $50 instead. - name: redeem_by type: TIMESTAMP mode: NULLABLE description: Time after which the coupon can no longer be redeemed. - name: end type: TIMESTAMP mode: NULLABLE description: If the coupon has a duration of "repeating", the time that this discount will end. If the coupon has a duration of "once" or "forever", this attribute will be null. - name: invoice_id type: STRING mode: NULLABLE description: ID of the invoice that the discount's coupon was applied to, if it was applied directly to a particular invoice. - name: invoice_item_id type: STRING mode: NULLABLE description: ID of the invoice item (or invoice line item for invoice line items of `type` = "subscription") that the discount's coupon was applied to, if it was applied directly to a particular invoice item or invoice line item. - name: promotion_code_id type: STRING mode: NULLABLE description: ID of the promotion code applied to create this discount. - name: start type: TIMESTAMP mode: NULLABLE description: Time at which the coupon was applied. - name: subscription_id type: STRING mode: NULLABLE description: ID of the subscription that this coupon is applied to, if it is applied to a particular subscription. - name: is_deleted type: BOOLEAN mode: NULLABLE description: Whether the customer is deleted. - name: metadata type: RECORD mode: NULLABLE description: Set of key-value pairs attached to the customer. fields: - name: geoip_date type: TIMESTAMP mode: NULLABLE description: 'Time at which IP address-based geo-location was done for the customer. This isn''t available for customers that were deleted before the initial Fivetran Stripe sync.' - name: paypalAgreementId type: STRING mode: NULLABLE description: The customer's PayPal agreement ID (if any). - name: userid type: STRING mode: NULLABLE description: 'The customer''s Firefox Account user ID. This isn''t available for customers that were deleted before the initial Fivetran Stripe sync.' - name: userid_sha256 type: STRING mode: NULLABLE description: SHA256 hash of the customer's Firefox Account user ID. - name: shipping type: RECORD mode: NULLABLE description: 'Mailing and shipping address for the customer. Appears on invoices emailed to this customer. This isn''t available for customers that were deleted before the initial Fivetran Stripe sync.' fields: - name: address type: RECORD mode: NULLABLE description: Customer shipping address. fields: - name: country type: STRING mode: NULLABLE description: Two-letter country code (ISO 3166-1 alpha-2). - name: tax_exempt type: STRING mode: NULLABLE description: 'The customer''s tax exemption status. One of "none", "exempt", or "reverse". This isn''t available for customers that were deleted before the initial Fivetran Stripe sync.' - name: billing_cycle_anchor type: TIMESTAMP mode: NULLABLE description: The time of the first full invoice, and, for plans with month or year intervals, the day of the month for subsequent invoices. - name: cancel_at type: TIMESTAMP mode: NULLABLE description: A time in the future at which the subscription will automatically get canceled. - name: cancel_at_period_end type: BOOLEAN mode: NULLABLE description: If the subscription has been canceled with the `at_period_end` flag set to true, `cancel_at_period_end` on the subscription will be true. You can use this attribute to determine whether a subscription that has a status of "active" is scheduled to be canceled at the end of the current period. - name: canceled_at type: TIMESTAMP mode: NULLABLE description: If the subscription has been canceled, the time at which it was canceled. If the subscription was canceled with `cancel_at_period_end`, `canceled_at` will reflect the time of the most recent update request, not the end of the subscription period when the subscription is automatically moved to a canceled state. - name: collection_method type: STRING mode: NULLABLE description: Either "charge_automatically", or "send_invoice". When charging automatically, Stripe will attempt to pay this subscription at the end of the cycle using the default source attached to the customer. When sending an invoice, Stripe will email your customer an invoice with payment instructions and mark the subscription as active. - name: created type: TIMESTAMP mode: NULLABLE description: Time at which the subscription was created. - name: current_period_end type: TIMESTAMP mode: NULLABLE description: End time of the current period that the subscription has been invoiced for. At the end of this period, a new invoice will be created. - name: current_period_start type: TIMESTAMP mode: NULLABLE description: Start time of the current period that the subscription has been invoiced for. - name: days_until_due type: INTEGER mode: NULLABLE description: Number of days a customer has to pay invoices generated by this subscription. This value will be null for subscriptions where `collection_method` = "charge_automatically". - name: default_payment_method_id type: STRING mode: NULLABLE description: ID of the default payment method for the subscription. It must belong to the customer associated with the subscription. This takes precedence over `default_source_id`. If neither are set, invoices will use the customer's `invoice_settings.default_payment_method` or `default_source`. - name: default_source_id type: STRING mode: NULLABLE description: ID of the default payment source for the subscription. It must belong to the customer associated with the subscription and be in a chargeable state. If `default_payment_method` is also set, `default_payment_method` will take precedence. If neither are set, invoices will use the customer's `invoice_settings.default_payment_method` or `default_source`. - name: default_tax_rates type: RECORD mode: REPEATED description: The tax rates that will apply to any subscription item that does not have `tax_rates` set. Invoices created will have their `default_tax_rates` populated from the subscription. fields: - name: id type: STRING mode: NULLABLE description: Tax rate ID. - name: created type: TIMESTAMP mode: NULLABLE description: Time at which the tax rate was created. - name: description type: STRING mode: NULLABLE description: An arbitrary description of the tax rate for your internal use only. It will not be visible to your customers. - name: display_name type: STRING mode: NULLABLE description: The display name of the tax rate as it will appear to your customer on their receipt email, PDF, and the hosted invoice page. - name: inclusive type: BOOLEAN mode: NULLABLE description: Whether the tax rate is inclusive. - name: jurisdiction type: STRING mode: NULLABLE description: The jurisdiction for the tax rate. You can use this label field for tax reporting purposes. It also appears on your customer's invoice. - name: metadata type: JSON mode: NULLABLE description: Set of key-value pairs attached to the tax rate, stored as a JSON object. - name: percentage type: FLOAT mode: NULLABLE description: Tax rate percentage out of 100. For tax calculations with `automatic_tax[enabled]=true`, this percentage includes the statutory tax rate of non-taxable jurisdictions. - name: discount type: RECORD mode: NULLABLE description: Describes the current discount applied to this subscription, if there is one. When billing, a discount applied to a subscription overrides a discount applied on a customer-wide basis. fields: - name: id type: STRING mode: NULLABLE description: Discount ID. - name: coupon type: RECORD mode: NULLABLE description: The coupon applied to create this discount. fields: - name: id type: STRING mode: NULLABLE description: Coupon ID. - name: amount_off type: INTEGER mode: NULLABLE description: Amount (in the `currency` specified) that will be taken off the subtotal of any invoices for this customer. - name: created type: TIMESTAMP mode: NULLABLE description: Time at which the coupon was created. - name: currency type: STRING mode: NULLABLE description: If `amount_off` has been set, the three-letter ISO code for the currency of the amount to take off. - name: duration type: STRING mode: NULLABLE description: 'One of "forever", "once", and "repeating". Describes how long a customer who applies this coupon will get the discount. "forever" applies to all charges from a subscription with this coupon applied. "once" applies to the first charge from a subscription with this coupon applied. "repeating" applies to charges in the first `duration_in_months` months from a subscription with this coupon applied.' - name: duration_in_months type: INTEGER mode: NULLABLE description: If `duration` is "repeating", the number of months the coupon applies. Null if coupon `duration` is "forever" or "once". - name: metadata type: JSON mode: NULLABLE description: Set of key-value pairs attached to the coupon, stored as a JSON object. - name: name type: STRING mode: NULLABLE description: Name of the coupon displayed to customers on invoices or receipts. - name: percent_off type: FLOAT mode: NULLABLE description: Percent that will be taken off the subtotal of any invoices for this customer for the duration of the coupon. For example, a coupon with `percent_off` of 50 will make a $100 invoice $50 instead. - name: redeem_by type: TIMESTAMP mode: NULLABLE description: Time after which the coupon can no longer be redeemed. - name: end type: TIMESTAMP mode: NULLABLE description: If the coupon has a duration of "repeating", the time that this discount will end. If the coupon has a duration of "once" or "forever", this attribute will be null. - name: invoice_id type: STRING mode: NULLABLE description: ID of the invoice that the discount's coupon was applied to, if it was applied directly to a particular invoice. - name: invoice_item_id type: STRING mode: NULLABLE description: ID of the invoice item (or invoice line item for invoice line items of `type` = "subscription") that the discount's coupon was applied to, if it was applied directly to a particular invoice item or invoice line item. - name: promotion_code_id type: STRING mode: NULLABLE description: ID of the promotion code applied to create this discount. - name: start type: TIMESTAMP mode: NULLABLE description: Time at which the coupon was applied. - name: ended_at type: TIMESTAMP mode: NULLABLE description: If the subscription has ended, the time at which the subscription ended. - name: items type: RECORD mode: REPEATED description: List of subscription items, each with an attached plan. fields: - name: id type: STRING mode: NULLABLE description: Subscription item ID. - name: created type: TIMESTAMP mode: NULLABLE description: Time at which the subscription item was created. - name: metadata type: JSON mode: NULLABLE description: Set of key-value pairs attached to the subscription item, stored as a JSON object. - name: plan type: RECORD mode: NULLABLE description: The plan the customer is subscribed to. fields: - name: id type: STRING mode: NULLABLE description: Plan ID. - name: aggregate_usage type: STRING mode: NULLABLE description: Specifies a usage aggregation strategy for plans of `usage_type` = "metered". Allowed values are "sum" for summing up all usage during a period, "last_during_period" for using the last usage record reported within a period, "last_ever" for using the last usage record ever (across period bounds) or "max" which uses the usage record with the maximum reported usage during a period. - name: amount type: INTEGER mode: NULLABLE description: The unit amount in cents to be charged, represented as a whole integer if possible. Only set if `billing_scheme` = "per_unit". - name: billing_scheme type: STRING mode: NULLABLE description: Describes how to compute the price per period. Either "per_unit" or "tiered". "per_unit" indicates that the fixed amount (specified in `amount`) will be charged per unit in `quantity` (for plans with `usage_type` = "licensed"), or per unit of total usage (for plans with `usage_type` = "metered"). "tiered" indicates that the unit pricing will be computed using a tiering strategy as defined using the `tiers` and `tiers_mode` attributes. - name: created type: TIMESTAMP mode: NULLABLE description: Time at which the plan was created. - name: currency type: STRING mode: NULLABLE description: Three-letter ISO currency code, in lowercase. - name: interval type: STRING mode: NULLABLE description: The frequency at which a subscription is billed. One of "day", "week", "month" or "year". - name: interval_count type: INTEGER mode: NULLABLE description: The number of intervals (specified in the `interval` attribute) between subscription billings. - name: metadata type: JSON mode: NULLABLE description: Set of key-value pairs attached to the plan, stored as a JSON object. - name: nickname type: STRING mode: NULLABLE description: A brief description of the plan, hidden from customers. - name: product type: RECORD mode: NULLABLE description: The product whose pricing this plan determines. fields: - name: id type: STRING mode: NULLABLE description: Product ID. - name: created type: TIMESTAMP mode: NULLABLE description: Time at which the product was created. - name: description type: STRING mode: NULLABLE description: The product's description, meant to be displayable to the customer. - name: metadata type: JSON mode: NULLABLE description: Set of key-value pairs attached to the product, stored as a JSON object. - name: name type: STRING mode: NULLABLE description: The product's name, meant to be displayable to the customer. - name: statement_descriptor type: STRING mode: NULLABLE description: Extra information about a product which will appear on your customer's credit card statement. - name: updated type: TIMESTAMP mode: NULLABLE description: Time at which the product was last updated. - name: tiers_mode type: STRING mode: NULLABLE description: Defines if the tiering price should be "graduated" or "volume". In volume-based tiering, the maximum quantity within a period determines the per unit price. In graduated tiering, pricing can change as the quantity grows. - name: trial_period_days type: INTEGER mode: NULLABLE description: Default number of trial days when subscribing a customer to this plan using `trial_from_plan=true`. - name: usage_type type: STRING mode: NULLABLE description: Configures how the quantity per period should be determined. Can be either "metered" or "licensed". "licensed" automatically bills the quantity set when adding it to a subscription. "metered" aggregates the total usage based on usage records. - name: quantity type: INTEGER mode: NULLABLE description: The quantity of the plan to which the customer should be subscribed. - name: latest_invoice_id type: STRING mode: NULLABLE description: ID of the most recent invoice this subscription has generated. - name: metadata type: RECORD mode: NULLABLE description: Set of key-value pairs attached to the subscription. fields: - name: appliedPromotionCode type: STRING mode: NULLABLE description: Promotion code applied to the subscription (if any). - name: cancellation_reason type: STRING mode: NULLABLE description: Reason the subscription was canceled (if any). - name: cancelled_for_customer_at type: TIMESTAMP mode: NULLABLE description: When the customer canceled the subscription, if they've done so. - name: plan_change_date type: TIMESTAMP mode: NULLABLE description: The most recent time when the subscription plan was changed (if any). - name: previous_plan_id type: STRING mode: NULLABLE description: ID of the previous plan the customer was subscribed to via this subscription (if any). - name: pending_setup_intent_id type: STRING mode: NULLABLE description: ID of a setup intent to collect user authentication when creating a subscription without immediate payment or updating a subscription's payment method. - name: start_date type: TIMESTAMP mode: NULLABLE description: Start time of the subscription. This might differ from `created` due to backdating. - name: status type: STRING mode: NULLABLE description: 'Possible values are "incomplete", "incomplete_expired", "trialing", "active", "past_due", "canceled", "unpaid", or "paused". For `collection_method` = "charge_automatically" a subscription moves into "incomplete" if the initial payment attempt fails. Once the first invoice is paid, the subscription moves into an "active" state. If the first invoice is not paid within 23 hours, the subscription transitions to "incomplete_expired". A subscription that is currently in a trial period is "trialing" and moves to "active" when the trial period is over. If subscription `collection_method` = "charge_automatically" it becomes "past_due" when payment to renew it fails and "canceled" or "unpaid" (depending on your subscriptions settings) when Stripe has exhausted all payment retry attempts. If subscription `collection_method` = "send_invoice" it becomes "past_due" when its invoice is not paid by the due date, and "canceled" or "unpaid" if it is still not paid by an additional deadline after that.' - name: trial_end type: TIMESTAMP mode: NULLABLE description: If the subscription has a trial, the end time of that trial. - name: trial_start type: TIMESTAMP mode: NULLABLE description: If the subscription has a trial, the start time of that trial.

sql/moz-fx-data-shared-prod/subscription_platform_derived/stripe_subscriptions_v2/schema.yaml (580 lines of code) (raw):