# Finding active days for a subscription/user/account (date spining)

**URL:** <https://discourse.getdbt.com/t/finding-active-days-for-a-subscription-user-account-date-spining/265>\
**Category:** Show and Tell\
**Tags:** jinja, dbt-utils, date-and-time\
**Created:** [March 18, 2019, 11:22pm UTC](https://discourse.getdbt.com/t/finding-active-days-for-a-subscription-user-account-date-spining/265 "2019-03-18T23:22:08Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![claire](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/claire/32/522_2.png) [@claire](https://discourse.getdbt.com/u/claire)\
**Post date:** [March 18, 2019, 11:22pm UTC](https://discourse.getdbt.com/t/finding-active-days-for-a-subscription-user-account-date-spining/265/1 "2019-03-18T23:22:08Z")

</div>

Every often, someone asks us about finding the active days for a subscription/user/account. We call this pattern “date spining”

For example, this question was asked on dbt Slack last week:

> Any idea how to calculate the active products historically (we have products expiring after 1 year from the purchase, they can also be cancelled). It would be good to see how active products change over time.

So let’s assume you can get your data into this shape, in a model called `product_subscription_periods`:

| subscription\_id | user | product | start\_date | end\_date |
| --- | --- | --- | --- | --- |
| 1 | emily | widgets | 2018-01-01 | 2019-01-01 |
| 2 | jack | gizmos | 2018-05-02 | 2018-06-01 |

We want to create one record per day that the subscription was active, like this:

| subscription\_id | user | product | date\_day |
| --- | --- | --- | --- |
| 1 | emily | widgets | 2018-01-01 |
| 1 | emily | widgets | 2018-01-02 |
| … | … | … | … |
| 1 | emily | widgets | 2018-12-31 |
| 2 | jack | gizmos | 2018-05-02 |
| 2 | jack | gizmos | 2018-05-03 |
| … | … | … | … |
| 2 | jack | gizmos | 2018-05-31 |

To do this, you need a list of all dates in your warehouse. We often use the `dbt_utils.date_spine` [macro](https://github.com/fishtown-analytics/dbt-utils#date_spine-source) for this, to materialize a table of dates out of thin air (no matter which data warehouse you use!).

Our `util_days` model will look something like:

```auto
{{ dbt_utils.date_spine(
    datepart="day",
    start_date="'2018-01-01'",
    end_date="dateadd(month, 1, date_trunc('month', getdate()))"
   )
}}

```

To build our `product_subscriptions_by_day` model, the SQL looks something like:

```sql
with product_subscription_periods as (

    select * from {{ ref('product_subscription_periods') }}

),

util_days as (

    select * from {{ ref('util_days') }}

),

spined as (

    select 
        product_subscription_periods.subscription_id,
        product_subscription_periods.user,
        product_subscription_periods.product,
        util_days.date_day

    from product_subscription_periods

    left join util_days
        -- start date is before the date we want in our table
        on product_subscription_periods.start_date <= util_days.date_day
        -- end date is after the date we want in out model, or there is no end date
        and (product_subscription_periods.end_date > util_days.date_day
               or product_subscription_periods.end_date is null)

)

select * from spined

```

Now that we have this model, it makes it really easy to calculate metrics in our BI tool, e.g. how many active subscriptions we have on each day:

```sql
select
    date_day,
    count(subscription_id) as n_active_subscriptions,
    count(case when product = 'widgets' then subscription_id end) as n_active_widget_subscriptions,
    count(case when product = 'gizmos' then subscription_id end) as n_active_gizmo_subscriptions,
    count(distinct user) as n_distinct_users_with_subscriptions

from analytics.product_subscriptions_by_day

group by 1

```

We use this pattern to model monthly recurring revenue based on subscriptions, to calculate daily active users, to model inventory levels, and a lot more!

Want to see it in action? We use this pattern in the [Monthly Recurring Revenue (MRR) playbook](https://blog.getdbt.com/modeling-subscription-revenue/).

For a more in-depth example, the team at GitLab have open sourced their dbt project – check out their [date spine model](https://gitlab-data.gitlab.io/analytics/dbt/snowflake/#!/model/model.gitlab_snowflake.date_details) and an [amortized subscription model](https://gitlab-data.gitlab.io/analytics/dbt/snowflake/#!/model/model.gitlab_snowflake.zuora_base_mrr_amortized) that joins to it. Thanks @tmurphy and @emilie!

---

<div class="post-metadata">

**Author:** ![kyle\_r](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/kyle_r/32/378_2.png) [@kyle\_r](https://discourse.getdbt.com/u/kyle_r)\
**Post date:** [June 9, 2020, 6:30am UTC](https://discourse.getdbt.com/t/finding-active-days-for-a-subscription-user-account-date-spining/265/2 "2020-06-09T06:30:09Z")

</div>

This post was a great reference item - thanks @claire! As a breadcrumb for any BigQuery folks coming across this, heads up - there is currently an open PR to correct the `date_spine` macro. Additionally, BigQuery will not compute a `left join` with these conditions, though we can use a `cross join` w/ a corresponding `where` clause to achieve the same result. As it relates to the PR for `date_spine`- it largely appears to work (yay!), so I’ll get in that convo and see if we can get it prepped for an upcoming release. 🚀

---

<div class="post-metadata">

**Author:** ![gavin](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/gavin/32/488_2.png) [@gavin](https://discourse.getdbt.com/u/gavin)\
**Post date:** [July 31, 2020, 2:58pm UTC](https://discourse.getdbt.com/t/finding-active-days-for-a-subscription-user-account-date-spining/265/3 "2020-07-31T14:58:43Z")

</div>

Great post!

> [@claire](#):
>
> ```auto
> left join util_days
> -- start date is before the date we want in our table
> on product_subscription_periods.start_date <= util_days.date_day
> -- end date is after the date we want in out model
> and product_subscription_periods.end_date > util_days.date_day
> 
> ```

I believe the `end_date` condition should allow for NULLs to support records that are the most recent:

```auto
    left join util_days
        on product_subscription_periods.start_date <= util_days.date_day
        and (
                product_subscription_periods.end_date > util_days.date_day 
                or product_subscription_periods.end_date is null
        )

```

---

<div class="post-metadata">

**Author:** ![claire](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/claire/32/522_2.png) [@claire](https://discourse.getdbt.com/u/claire)\
**Post date:** [July 31, 2020, 8:37pm UTC](https://discourse.getdbt.com/t/finding-active-days-for-a-subscription-user-account-date-spining/265/4 "2020-07-31T20:37:23Z")

</div>

Ooh good catch, let me update it in the article 🙂

---

<div class="post-metadata">

**Author:** ![prratek](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/prratek/32/533_2.png) [@prratek](https://discourse.getdbt.com/u/prratek)\
**Post date:** [August 4, 2020, 2:34pm UTC](https://discourse.getdbt.com/t/finding-active-days-for-a-subscription-user-account-date-spining/265/5 "2020-08-04T14:34:54Z")

</div>

Any reason not to use an `inner join` here with that join condition if you’re using BigQuery?

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [August 3, 2021, 3:41am UTC](https://discourse.getdbt.com/t/finding-active-days-for-a-subscription-user-account-date-spining/265/6 "2021-08-03T03:41:43Z")

</div>

I always had trouble getting my inequality conditions consistent (`start_date <= date_day` and `end_date > date_day`? The other way around? Both have an equals sign? Neither?)

So I made a macro so I never have to think about it again!

```auto
{% macro calendar_fanout(other_model_name, max_day=today_nz(), calendar_model_name = 'calendar', date_column_name='date_day', from='dbt_valid_from', to='dbt_valid_to') -%}
    inner join {{ calendar_model_name }} on {{ calendar_model_name }}.{{ date_column_name }} >= {{ other_model_name }}.{{ from }} 
        and {{ calendar_model_name }}.{{ date_column_name }} < coalesce( {{ other_model_name }}.{{ to }}, {{ var('the_distant_future') }} )
        and {{ calendar_model_name }}.{{ date_column_name }} <= {{ max_day }}
{%- endmacro %}

```

Almost everything has default values (which assume you’re fanning out a dbt snapshot). In the default case, the only thing that needs configured is `other_model_name`, the thing that the calendar table will join against.

Usage:

```auto
with calendar as (select * from {{ ref('constants__calendar_day') }}),
status_date_ranges as (select * from {{ ref('all_b2c_subscriber_status_date_ranges') }}),

final as (
    select 
        calendar.date_day, 
        status_date_ranges.* 
    from status_date_ranges
    {{ calendar_fanout('status_date_ranges', from='status_valid_from', to='status_valid_to') }}
)

select *
from final
order by date_day

```

Renders to:

```auto
with calendar as (select * from "reporting"."dev_jlabes"."constants__calendar_day"),
status_date_ranges as (select * from __dbt__ cte__all_b2c_subscriber_status_date_ranges),

final as (
    select 
        calendar.date_day, 
        status_date_ranges.* 
    from status_date_ranges
    inner join calendar on calendar.date_day >= status_date_ranges.status_valid_from 
        and calendar.date_day < coalesce( status_date_ranges.status_valid_to, '2999-12-31' )
        and calendar.date_day <= convert_timezone('Pacific/Auckland', getdate())::date
)

select *
from final
order by date_day

```

Bonus: note the sneaky macro-inside-macro to get the current day in NZ, a nice UX sweetener for data consumers who don’t care that _technically_ the data is in UTC time, they just want the freshest stuff.