Skip to main content

Query data through Subscription Status History data source

Zuora

Query data through Subscription Status History data source

  1. Last updated
  2. Save as PDF

This article introduces the Subscription Status History object and how to query subscriptions, subscription rate plans, and subscription rate plan charges by subscription status through the Subscription Status History data source.

Subscription Status History object

With the Subscription Status History object, you can query the status of a subscription on any given date in the subscription lifecycle.

The Subscription Status History object is introduced because the status field of a subscription only reflects its last status. However, you cannot access the status change history of the subscription through the status field. Since the Subscription object is actually an object corresponding to a date range, the status of a subscription should also be an object corresponding to the date range.

Therefore, the Subscription Status History object is defined as an array of object items. Each of the object items corresponds to a date range and indicates the status of a subscription in that date range. The following example illustrates how the Subscription Status History object presents the statuses of a subscription in the subscription lifecycle.

As shown in the following diagram, a subscription is effective from 2022/1/1 to 2024/4/1. The subscription is suspended on 2023/1/1,  resumed on 2023/4/1, and cancelled on 2024/4/1.

sample-status-change-history.jpg

For the preceding subscription, its Subscription Status History object contains four object items to present the statuses of the subscription. Each object item corresponds to a date range and is indicated as a row in the following table:

Start Date End Date Status
2022/1/1 2023/1/1 Active
2023/1/1 2023/4/1 Suspended
2023/4/1 2024/4/1 Active
2024/4/1 NULL Cancelled

As shown in the preceding table, the statuses of the subscription are as follows:

  • Active from 2022/1/1 to 2023/1/1
  • Suspended from 2023/1/1 to 2023/4/1
  • Active from 2023/4/1 to 2024/4/1
  • Cancelled from 2024/4/1

Common use cases and sample queries

With the Subscription Status History data source, you can query subscriptions, subscription rate plans, and subscription rate plan charges by subscription status in a date range or on a specific date.

The query result returns only the objects with the latest subscription version.

To perform the preceding queries, query the Subscription Status History object and join from the Subscription object and the subordinate objects including Subscription Rate Plan, Subscription Rate Plan Charge, and Subscription Rate Plan Charge Tier.

There are two ways to perform the queries:

  • Query data source through ZOQL.
  • Query data source through UI.

Query data source through ZOQL

This section introduces the common use cases for using the Subscription Status History data source through ZOQL.

Query data by date range

You can perform a ZOQL query on the data sources to query the subscriptions, subscription rate plans, and subscription rate plan charges by subscription status in a date range.

For example, to query all the subscriptions which are in the Active status from 2022-01-01 to 2022-02-01, perform the following ZOQL query:

Select * from Subscription where SubscriptionStatusHistory.Status=’Active’ and 
SubscriptionStatusHistory.StartDate>=’2022-01-01’ and 
SubscriptionStatusHistory.EndDate<’2022-02-01

Query data by date

You can perform an “As-of-Day” query to find the subscriptions, subscription rate plans, and subscription rate plan charges with a specific subscription status on a specific date. Use the EffectiveDate field of the Subscription Status History object in your query criteria to perform such queries. Note that the EffectiveDate field is only available in the query criterion and cannot be used anywhere else. Also, the  = operator is the only supported operator for the EffectiveDate field.

For example, to query all the subscriptions which are in the Active status on 2022-01-01, perform the following ZOQL query:

Select * from Subscription where SubscriptionStatusHistory.status=’Active’ and 
SubscriptionStatusHistory.EffectiveDate=’2022-01-01’

Query data on current date

You can perform an “As-of-Today” query to find subscriptions, subscription rate plans, and subscription rate plan charges with a specific subscription status on the current date. Use the keyword today as the value of the EffectiveDate field in your query criterion to perform such queries.

For example, to query all the subscriptions which are in the Active status on the current date, perform the following ZOQL query:

Select * from Subscription where SubscriptionStatusHistory.status=’Active’ and 
SubscriptionStatusHistory.EffectiveDate=’today’

Query data source through UI

This section introduces how to filter and export subscriptions, subscription rate plans, and subscription rate plan charges by subscription status in a date range or on a specific date through the data source UI. 

Note that the examples in this section show how to filter the Subscription object with its joined object Subscription Status History. You can filter the Subscription Rate Plan and Subscription Rate Plan Charge objects with the joined object Subscription Status History in a similar way.

Filter data by date range

To filter and export subscriptions by status in a date range from the data source UI, complete the following steps:

  1. Navigate to Reporting > Data Sources. The Data Sources page opens.
  2. From the Data Source dropdown list, select Subscription. A list of joined objects is displayed.
  3. From the list of joined objects, select Subscription Status History.
  4. In the Filters section, apply the filters by the Status, Start Date, and End Date fields of the Subscription Status History object, and set the operators and values appropriately.
  5. In the Format section, select the proper formats.
  6. Click Export.

The following diagram illustrates the key configurations (steps 2 - 4) in the preceding operation flow. 

query-by-date-range.jpeg

Filter data by date

To filter and export subscriptions by status on a specific from the data source UI, complete the following steps:

  1. Navigate to Reporting > Data Sources. The Data Sources page opens.
  2. From the Data Source dropdown list, select Subscription. A list of joined objects is displayed.
  3. From the list of joined objects, select Subscription Status History.
  4. In the Filters section, apply the filters by the Status and Effective Date fields of the Subscription Status History object and set the operators and values appropriately.
  5. In the Format section, select the proper formats.
  6. Click Export.

The following diagram illustrates the key configurations (steps 2 - 4) in the preceding operation flow. 

query-by-date.jpeg

Condition and limitations

This section introduces the condition that must be met when you query the Subscription Status History data source and the limitations on the EffectiveDate field of the Subscription Status History object.

Condition that must be met

When querying subscriptions, subscription rate plans, and subscription rate plan charges with the Subscription Status History object, your query criterion must contain either a data range (defined by the StartDate and EndDate fields) or the EffectiveDate field. Otherwise, the following error is returned:

SubscriptionStatusHistory.EffectiveDate(SubscriptionStatusHistory.StartDate and 
SubscriptionStatusHistory.EndDate) is required in WHERE condition.

Limitations on EffectiveDate

The EffectiveDate field of the Subscription Status History object is a virtual field and has the following limitations:

  • Available only in the query criterion 
  • Supports only the = operator 
  • Not support the NOT operator
  • Not support functions