Table: stripe_subscription - Query Stripe Subscriptions using SQL

Stripe Subscriptions is a service within Stripe that allows businesses to manage recurring billing for their customers. It provides a simplified way to set up and manage subscriptions, including various billing models, prorations, and tax rates. Stripe Subscriptions helps businesses automate their recurring billing, stay informed about subscription changes, and take appropriate actions when predefined conditions are met.

Table Usage Guide

The stripe_subscription table provides insights into subscriptions within Stripe. As a financial analyst or a business owner, explore subscription-specific details through this table, including the status, items, and associated customer information. Utilize it to uncover information about subscriptions, such as those with upcoming invoices, the relationships between customers and their subscriptions, and the verification of billing details.

Examples

List all subscriptions

Explore all active subscriptions to understand the scope and scale of your service usage. This can aid in assessing revenue streams and identifying potential areas for growth or improvement.

select
  *
from
  stripe_subscription;

select
  *
from
  stripe_subscription;

Subscriptions currently in the trial period

Explore which subscriptions are presently in their trial period to manage customer engagement and retention strategies effectively. This allows for a timely review of customer behavior and interactions during the trial phase.

select
  *
from
  stripe_subscription
where
  status = 'trialing'
order by
  created desc;

select
  *
from
  stripe_subscription
where
  status = 'trialing'
order by
  created desc;

Subscriptions set to cancel at the end of this period

Identify the Stripe subscriptions that are scheduled to cancel at the end of the current billing period. This is useful for understanding your upcoming subscription churn and potentially reaching out to these customers to prevent cancellation.

select
  *
from
  stripe_subscription
where
  cancel_at_period_end;

select
  *
from
  stripe_subscription
where
  cancel_at_period_end = 1;

Subscriptions created in the last 7 days

Discover the segments that have newly subscribed to your service within the past week. This can be useful in understanding recent customer behavior and trends.

select
  *
from
  stripe_subscription
where
  created > current_timestamp - interval '7 days'
order by
  created;

select
  *
from
  stripe_subscription
where
  created > datetime('now', '-7 days')
order by
  created;

Schema for stripe_subscription

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
application_fee_percent	double precision		A non-negative decimal between 0 and 100, with at most two decimal places. This represents the percentage of the subscription invoice subtotal that will be transferred to the application owner’s Stripe account.
billing_cycle_anchor	timestamp with time zone		Determines the date of the first full invoice, and, for plans with month or year intervals, the day of the month for subsequent invoices.
billing_thresholds	jsonb		Define thresholds at which an invoice will be sent, and the subscription advanced to a new billing period.
cancel_at	timestamp with time zone		A date in the future at which the subscription will automatically get canceled.
cancel_at_period_end	boolean		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.
canceled_at	timestamp with time zone		If the subscription has been canceled, the date of that cancellation. 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.
collection_method	text	=	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.
created	timestamp with time zone	>, >=, =, <, <=	Time at which the subscription was created.
current_period_end	timestamp with time zone	>, >=, =, <, <=	End of the current period that the subscription has been invoiced for. At the end of this period, a new invoice will be created.
current_period_start	timestamp with time zone	>, >=, =, <, <=	Start of the current period that the subscription has been invoiced for.
customer_id	text	=	ID of the customer who owns the subscription.
days_until_due	bigint		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.
default_payment_method	jsonb		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. If neither are set, invoices will use the customer’s invoice_settings.default_payment_method or default_source.
default_source	jsonb		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.
default_tax_rates	jsonb		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.
discount	jsonb		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.
ended_at	timestamp with time zone		If the subscription has ended, the date the subscription ended.
id	text	=	Unique identifier for the subscription.
items	jsonb		List of subscription items, each with an attached price.
latest_invoice_id	text		ID of the most recent invoice this subscription has generated.
livemode	boolean		Has the value true if the subscription exists in live mode or the value false if the subscription exists in test mode.
metadata	jsonb		Set of key-value pairs that you can attach to an subscription. This can be useful for storing additional information about the subscription in a structured format.
next_pending_invoice_item_invoice	timestamp with time zone		Specifies the approximate timestamp on which any pending invoice items will be billed according to the schedule provided at pending_invoice_item_interval.
pause_collection	jsonb		If specified, payment collection for this subscription will be paused.
pending_invoice_item_interval	jsonb		Specifies an interval for how often to bill for any pending invoice items. It is analogous to calling Create an invoice for the given subscription at the specified interval.
pending_setup_intent	text		You can use this SetupIntent to collect user authentication when creating a subscription without immediate payment or updating a subscription’s payment method, allowing you to optimize for off-session payments. Learn more in the SCA Migration Guide.
pending_update	jsonb		If specified, pending updates that will be applied to the subscription once the latest_invoice has been paid.
schedule	jsonb		The schedule attached to the subscription.
start_date	timestamp with time zone		Date when the subscription was first created. The date might differ from the created date due to backdating.
status	text	=	Possible values are incomplete, incomplete_expired, trialing, active, past_due, canceled, or unpaid.
transfer_data	jsonb		The account (if any) the subscription’s payments will be attributed to for tax reporting, and where funds from each payment will be transferred to for each of the subscription’s invoices.
trial_end	timestamp with time zone		If the subscription has a trial, the end of that trial.
trial_start	timestamp with time zone		If the subscription has a trial, the start of that trial.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- stripe

You can pass the configuration to the command with the --config argument:

steampipe_export_stripe --config '<your_config>' stripe_subscription