ETL - How to spread a Budget over two dates within a variable date range.

Gojo
Gojo Member
in Magic ETL

I have various start and end dates for campaigns. I know how to calculate the # of days between any get set of dates. But, what I am trying to set up in the etl is this -  if I have a budget amount, start date and end date, what is the easiest way for me to have the etl calculate the division of the budget over the given start and end dates?


e.g. let's say I have a $9800 budget, and I set the start date/end date  for 4/17/2024 - 10/12/2024. I want it evenly distributed over the number of days, etc.

I would want the to know a couple metrics from these inputted values,

The Daily Budget

The Monthly Budget (taking into account that April and October are not full months)

And those values projected for future months.

Is there a way to set this up in the ETL?

Tagged:

Best Answer

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    The easiest way would be to use a date dimension table and a cross join (Cartesian Product). Say you have your budget dataset.

    image.png

    You would join your date dimension table (a dataset that has a row for every day) with your budget dataset.

    image.png

    You would then filter to where dt (the day in your date dataset is >= start_date and < = end_date.

    You can then calculate the number of days between your start and end dates. Note, you could also make use of the "Date Operations" tile if you wanted the number of working days between these dates. Then divide your total budget by the number of days to get the daily budget.

    For the monthly the easiest way would be to connect this dataset to a card and then aggregate the daily total by month.

    image.png

    For example

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    david_cunningham Coach
    Answer ✓

    The easiest way would be to use a date dimension table and a cross join (Cartesian Product). Say you have your budget dataset.

    image.png

    You would join your date dimension table (a dataset that has a row for every day) with your budget dataset.

    image.png

    You would then filter to where dt (the day in your date dataset is >= start_date and < = end_date.

    You can then calculate the number of days between your start and end dates. Note, you could also make use of the "Date Operations" tile if you wanted the number of working days between these dates. Then divide your total budget by the number of days to get the daily budget.

    For the monthly the easiest way would be to connect this dataset to a card and then aggregate the daily total by month.

    image.png

    For example

    image.png

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • Gojo
    Gojo Member

    Hi David,

    My apologies for my late reply. Thank you for the detailed response! I set this up, so the budgets appear on each day.

    I do have another follow-up question, if you have two datasets, one primary, and the other the calendar dataset, and both have date columns. So eventhough there is a unique identifier, because they are repeated multiple times per day, with various other metrics. What would be the best method to joining to avoid a one to many join? As it stands now, the current join, produces millions of rows. I know that I can include additional join keys, but is there another option if that isn't as efective? The data is updated daily.

    Thanks again for your help.

Categories