Zuora data query and workflow for mobile subscription management

1. Counting active subscriptions by channel 

This query provides an overview of active subscriptions across all channels (Direct, Apple, Google, and Roku), helping you assess the distribution of your customer base and optimize channel-specific strategies.

/** Count direct subscribers */
WITH direct AS (
    SELECT
        'Direct' AS channel,
        COUNT(1) AS active_subscriptions
    FROM
        rateplancharge rpc
        INNER JOIN rateplan rp ON rpc.rateplanid = rp.id
        INNER JOIN subscription s ON rp.subscriptionid = s.id
    WHERE
        s.status NOT IN ('Draft', 'Expired')
        AND rpc.effectivestartdate <= CURRENT_DATE
        AND (rpc.effectiveenddate > CURRENT_DATE OR rpc.effectiveenddate IS NULL)
    GROUP BY 1
),
/** Count mobile subscribers */
mobile AS (
    SELECT
        externalsourcesystem AS channel,
        COUNT(1) AS active_subscriptions
    FROM
        subscription
    WHERE
        externalsourcesystem IN ('Apple App Store', 'Google Play Store', 'Roku')
        AND type = 'OmniChannel'
        AND state = 'Active'
    GROUP BY 1
)

SELECT * FROM direct
UNION ALL
SELECT * FROM mobile;

Use Case: Assess active subscriptions by channel to prioritize engagement efforts. For example, a high count in Apple App Store could prompt targeted Apple-specific campaigns, while Roku may benefit from a separate focus.

2. Apple subscriptions due for renewal with auto-renew disabled

Identify Apple subscriptions up for renewal in the next 30 days that have auto-renew disabled. This list enables proactive retention efforts to reduce churn and improve retention.

SELECT
    a.id AS account_id,
    a.name AS account_name,
    a.accountnumber AS account_accountnumber,
    s.id AS subscription_id,
    s.name AS subscription_name,
    s.externalproductid AS subscription_externalproductid,
    s.state AS subscription_state,
    s.externalexpirationdate AS subscription_expirationdate
FROM
    subscription s
    INNER JOIN account a ON s.accountid = a.id
WHERE
    type = 'OmniChannel'
    AND state = 'Active'
    AND externalexpirationdate <= CURRENT_DATE + INTERVAL '30' DAY
    AND externalexpirationdate > CURRENT_DATE
    AND autorenew = FALSE;

Use Case: Enable targeted engagement to encourage Apple subscribers with upcoming expiration to renew, using automated email or in-app reminders.

SELECT
    a.id AS account_id,
    a.name AS account_name,
    a.accountnumber AS account_accountnumber,
    s.id AS subscription_id,
    s.name AS subscription_name,
    s.externalproductid AS subscription_externalproductid,
    s.state AS subscription_state,
    s.externalexpirationdate AS subscription_expirationdate
FROM
    subscription s
    INNER JOIN account a ON s.accountid = a.id
WHERE
    externalsourcesystem IN ('Google Play Store', 'Roku')
    AND state = 'Active'
    AND externalstate = 'In Grace';

SELECT
    state,
    externalproductid,
    COUNT(1) AS subscription_count
FROM
    subscription
WHERE
    type = 'OmniChannel'
GROUP BY state, externalproductid
ORDER BY subscription_count;

Ensure data integrity for app store subscriptions using Zuora workflow

To maintain accurate subscription data across Apple, Google, and Roku, Zuora Workflow allows for a nightly data integrity check. This process addresses discrepancies caused by missed or out-of-order notifications from the app stores, ensuring your subscription data remains consistent.