• Replies 10 replies
  • Subscribers 961 subscribers
  • Views 249 views
  • Users 0 members are here
Related

YOY Weekly comparison

Tony Hatch
$organization

Hello Everyone,

I am trying to create a dashboard widget that will show YOY weekly revenue by order date.  I can't seem to find a way to line everything up.  What i would like is each week regardless of fiscal year to line up.  Any help would be appreciated.

Thanks,

  • $organization

    Hey Anthony. 

    2 options come to mind: 
    a) You might look at X axis ORDER DATE > CALENDAR WEEK OF YEAR and Y axis TICKET PAID AMOUNT.  The break by is the interesting thing. You could do the Campaign that the Performances are in by Break by PERFORMANCE CAMPAIGN > CAMPAIGN 

    b) you could bucket them by having a different filtered values  ( sum([Ticket  Paid  Amount]) , [Campaign1] ) - right click and filter the red bit.  I go into bucketing a but here if that helps https://community.tessituranetwork.com/topical_groups/analytics-coffee/f/discussions/29816/grouping-rows-of-a-pivot-table



  • $organization in reply to Heath Wilder

    Also, (a) using ORDER DATE > Fiscal Week of Year.

  • $organization

    I tried to use Campaign Week of the Year but it looks like it is still counting it as the  Calendar Year of the Week. How is the Campaign Week of the Year calculated?

  • $organization in reply to Phillip De Leon

    Hi Phillip,

    I'm confused by [Campaign Week of the Year]. as that's not a standard field in the Seats and Tickets cube. Are you in the Seats and Tickets cube? Do you mean Fiscal Week of Year? Or could you be in the Contributions cube looking at Weeks Since Campaign Start?

  • $organization
    I am in the Seat and Tickets Cube. When I search for campaign I get a results for Campaign Week of the Year for a bunch of different things. I am using Performance Campaign Start Date> Calendar Week of the Year and Package Campaign Start Date > Calendar week of the year as a filter for a pivot table. I get very odd results.
     
     
    In the widget I get odd results. The first set of cells is just regular CWotY, The second is Perf CWotY, and the third is PKG CWotY.  They do not line up at all. It says that those three weeks, 17, 18, and 19, are all week 17 according to the campaign week. I would like it to say 17 = 1, 18 = 2, 19 = 3 as our package onsale started on calendar week 17.
     
     
    Thank you,
    Phillip De Leon
     
    Looking forward I will be out:
    Friday, May 6th
    Tuesday May 31st
     
  • $organization in reply to Phillip De Leon

    Hi Phillip,

    Thank you for all of that. It's really helpful. I can see now where the various DATE dimensions are tripping us up, I think...

    • Performance Campaign Start Date> Calendar Week of the Year returns the week number associated with the start date set on the campaign associated with the performance. 
    • Package Campaign Start Date> Calendar Week of the Year returns the week number associated with the start date set on the campaign associated with the package. 
    • Order Source Campaign Start Date> Calendar Week of the Year returns the week number associated with the start date set on the campaign associated with the source on the order. 
    • Lineitem Source Campaign Start Date> Calendar Week of the Year returns the week number associated with the start date set on the campaign associated with the source on the lineitem.

    If for a given set of data these are different campaigns with different campaign start dates, they won't line up when compared. In the leftmost column, the 17 represents week 17 in which one or more campaigns started. Of the campaigns that started in week 17, the performances in those orders are associated with a mix of campaigns that started in weeks 1, 6, 12, and 17... 

    Based on this thread, these fields are likely not what you're after. Are you looking for the weeks between a given campaign start date and the related order dates? For example, the relative Order Weeks from Package Campaign Start Date? Orders placed in the the first 7 days after the package campaign's start date would then be grouped into week 0?

  • $organization
    So what I am looking for is comparing package sales across 5 years by week. I was asked to even out the data so that week one is when the renewal on-sale started. I though If I tied it to campaign week then It would line up on the widget. I would just adjust the start date for all package campaigns to start with the Monday of the package on sale date across the 5 years and that would line up the package sales for an easier comparison.
     
    Thank you,
    Phillip De Leon
     
    Looking forward I will be out:
    Friday, May 6th
    Tuesday May 31st
     
  • $organization in reply to Phillip De Leon

    Makes perfect sense, Phillip.

    I'll put in an enhancement request for time between package campaign start date and order date. In the meantime...

    You could adjust all the Default Sales Dates Start Dates across all the performances in the packages to the dates in question and then after a full data load, use the Order Weeks Since Onsale field.

    You could explore a customization to Analytics for Order Weeks from Package Campaign Start Date by opening a ticket with hello@tessituranetwork.com.

  • $organization
    I think this will work, Thank you for your help! Also, could that campaign enhancement request be used in the donor and memberships cubes?
     
    Thank you,
    Phillip De Leon
     
    Looking forward I will be out:
    Friday, May 6th
    Tuesday May 31st
     
  • $organization in reply to Phillip De Leon

    As it happens, Days / Weeks / Months Since Campaign Start is already in both the Contributions and Memberships cubes in the Contribution Details and Membership Details dimensions respectively.

    Cheers,
    Chris