• Replies 1 reply
  • Subscribers 701 subscribers
  • Views 72 views
  • Users 0 members are here
Options
Related

Membership acquisition targets

Carlo Palazzi
$organization

Hello everyone,

Does anyone have a neat way of getting hard-coded targets to show up for future membership acquisitions? We look at membership acquisition by purchase date - and I think no data loads into the cubes for future months until a membership has been purchased in that month - making it impossible to display even a target. As you can see in my formula in the screenshot  and copied below, September is showing as it has already begun, but nothing for the future months. 

CASE
WHEN (MAX([Fiscal Period])=1 AND MAX([Fiscal Year])= 2023) THEN 220
WHEN (MAX([Fiscal Period])=2 AND MAX([Fiscal Year])= 2023) THEN 320
WHEN (MAX([Fiscal Period])=3 AND MAX([Fiscal Year])= 2023) THEN 670
WHEN (MAX([Fiscal Period])=4 AND MAX([Fiscal Year])= 2023) THEN 620
WHEN (MAX([Fiscal Period])=5 AND MAX([Fiscal Year])= 2023) THEN 370
WHEN (MAX([Fiscal Period])=6 AND MAX([Fiscal Year])= 2023) THEN 420
WHEN (MAX([Fiscal Period])=7 AND MAX([Fiscal Year])= 2023) THEN 420
WHEN (MAX([Fiscal Period])=8 AND MAX([Fiscal Year])= 2023) THEN 320
WHEN (MAX([Fiscal Period])=9 AND MAX([Fiscal Year])= 2023) THEN 220
WHEN (MAX([Fiscal Period])=10 AND MAX([Fiscal Year])= 2023) THEN 220
WHEN (MAX([Fiscal Period])=11 AND MAX([Fiscal Year])= 2023) THEN 370
WHEN (MAX([Fiscal Period])=12 AND MAX([Fiscal Year])= 2023) THEN 370
ELSE 0 END

  • $organization

    Hi Carlo,

    Analytics doesn't currently support showing dimensional data that isn't also associated with a piece of data that lives at the core of the given data perspective. Or as you said, if there isn't a membership with a given Purchase Month, then that Purchase Month isn't visible in an Analytics Pivot widget. Even on a column or line chart set to display a continuous timeline, while the missing purchase month will appear on the axis, it will still not render a value.

    CASE WHEN [Max Calendar Month] = 5 THEN 10
    WHEN [Max Calendar Month] = 6 THEN 10
    WHEN [Max Calendar Month] = 7 THEN 10
    ELSE 10 END

    A thought... Even when a date on a continuous timeline has no purchases, the PASTYEAR() function will return the value for that month from the prior year. So we could leverage the value from PASTYEAR() to produce a goal value...

    For each of my months I just used "10" as the goal. I used this for just the one missing month, but you could use this liberally. Be aware though that if the PASTYEAR() value is 0, we get 10 * 0 / 0, but let's only cross that bridge if we come to it.

    CASE WHEN [Max Calendar Month] = 5 THEN 10
    WHEN [Max Calendar Month] = 6 THEN 10 * PASTYEAR([Total Membership Count]) / PASTYEAR([Total Membership Count])
    WHEN [Max Calendar Month] = 7 THEN 10
    ELSE 10 END

    Hope that helps,
    Chris