0

I have a query that aims to pull counts for: total subscriber records, total for each subscriber status, total optins, total optins for each source, total unsubs, total unsubs for each source.

I am pulling from the _ListSubscribers data view for most of the counts and pulling in our main data extension to add in the source conditions.

There are some elements within the query that work as expected (total subscriber records, totals for each subscriber status, total unsubs, total unsubs for each source). The section of the query that pulls counts for optins and optins per source is what seems to be failing for me. When I pull a list of subscribers with CreatedDates matching the original query I get an actual count versus a zero count.


Query code:

Select

    Count(case when sub.SubscriberKey is not null then 1 else null end) as total_subscribers,
    Count(case when sub.Status = 'Active' then 1 else null end) as active,
    Count(case when sub.Status = 'Unsubscribed' then 1 else null end) as unsubscribed,
    Count(case when sub.Status = 'Bounced' then 1 else null end) as bounced,
    Count(case when sub.Status = 'Held' then 1 else null end) as held,
    Count(case when (sub.CreatedDate >= dateadd(d, -1, getdate())) then 1 else null end) as total_optins,
    Count(case when (sub.CreatedDate >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'Retail') then 1 else null end) as optins_retail,
    Count(case when (sub.CreatedDate >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'Social') then 1 else null end) as optins_social,
    Count(case when (sub.CreatedDate >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_Lightbox') then 1 else null end) as optins_lightbox,
    Count(case when (sub.CreatedDate >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_Footer') then 1 else null end) as optins_footer,
    Count(case when (sub.CreatedDate >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_Checkout') then 1 else null end) as optins_checkout,
    Count(case when (sub.CreatedDate >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_Account') then 1 else null end) as optins_account,
    Count(case when (sub.CreatedDate >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_CatalogRequest') then 1 else null end) as optins_catalog_request,
    Count(case when (sub.CreatedDate >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_SignupPage') then 1 else null end) as optins_signup_page,
    Count(case when (sub.DateUnsubscribed >= dateadd(d, -1, getdate())) then 1 else null end) as total_unsubs,
    Count(case when (sub.DateUnsubscribed >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'Retail') then 1 else null end) as unsubs_retail,
    Count(case when (sub.DateUnsubscribed >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'Social') then 1 else null end) as unsubs_social,
    Count(case when (sub.DateUnsubscribed >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_Lightbox') then 1 else null end) as unsubs_lightbox,
    Count(case when (sub.DateUnsubscribed >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_Footer') then 1 else null end) as unsubs_footer,
    Count(case when (sub.DateUnsubscribed >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_Checkout') then 1 else null end) as unsubs_checkout,
    Count(case when (sub.DateUnsubscribed >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_Account') then 1 else null end) as unsubs_account,
    Count(case when (sub.DateUnsubscribed >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_CatalogRequest') then 1 else null end) as unsubs_catalog_request,
    Count(case when (sub.DateUnsubscribed >= dateadd(d, -1, getdate())) and (de.acquisition_source = 'US_SignupPage') then 1 else null end) as unsubs_signup_page


from _ListSubscribers sub

Join [Main DE Name] de
    on sub.SubscriberKey = de.SubscriberKey

Where ListID = 11
0

Optin calculations are based on createddate check.Please ensure 1) the correct timezone is used for this session. 2) the comparison is done with both date and time parts

Let us know if we try these steps and also putting some sample data to explain your example would be great.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.