Struggling to write Beastmode that reflects current MTD values

Golfoholic · 2021-02-12T22:19:31+00:00

There was an error rendering this rich post.

I have a column of marketing sources where I am trying to breakout individual sources to be their own individual columns, and have them only reflect current MTD amounts. I have multiple YOY and MOM % and net differences in columns that interact with that date setting portion, so changing the date setting is going to change those Period Over Period columns. I have started by writing the following calc, but I'm unsure how to write and call out those specific values in the marketing source column.

CASE WHEN ((year(`Date_Entered`)=year(curdate())) AND (month(`Date_Entered`)=month(CURDATE()))) THEN `marketing_source` END

Marketing Source is the column that contains fields like Google, Google paid that I am trying to pull out in to their own columns

Find more posts tagged with

Accepted answers

GrantSmith

Hi @user048760

You'd need three separate beast modes, one for each source. You just need to include the condition to filter the appropriate source in your YOY, MOM etc calculations.

For example for Google:

(These are all untested code)

Inquiries

COUNT(CASE WHEN year(`Date_Entered`)=year(curdate()) AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)

(
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate()) AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
-
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate())-1 AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
)
/
(
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate())-1 AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
)

(
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate()) AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
-
COUNT(CASE WHEN LAST_DAY(`Date_Entered`) = LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 month)) AND `marketing_source_c` = 'Google' THEN `id` END)
)
/
(
COUNT(CASE WHEN LAST_DAY(`Date_Entered`) = LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 month)) AND `marketing_source_c` = 'Google' THEN `id` END)
)

For each of your different sources you'd need a different beast mode then you can drag those beast modes onto your card. Hopefully this helps explain things a bit better for you.

jaeW_at_Onyx

@user048760 , @GrantSmith is spot on in the math. It will get the job done.

But this design / approach is ... difficult to scale because it requires you to never change the filters on your cards (notice how you currently have it set for 13 months. If a user wants to drill into a specific month or applied a date filter to the dashboard this card is hosted on, the card would 'break.'

This tutorial, is a bit of a stretch to wrap your mind around, but it's a much more scalable solution that uses an "offset date dimension" table design pattern that we consultants find ourselves using in Domo very frequently.

https://www.youtube.com/watch?v=CDKNOmKClms&t=239s

Either way. Good luck!

GrantSmith

Google:

CASE WHEN `marketing_source_c` = 'Google' THEN `Date_entered` END

Google Paid:

CASE WHEN `marketing_source_c` = 'Google Paid' THEN `Date_entered` END

SPS_Website:

CASE WHEN `marketing_source_c` = 'SPS_Website' THEN `Date_entered` END

Essentially you'd have those three case statements in 3 separate beast modes when you're calculating anything dealing with your Date_entered column instead of just plain `Date_entered`.

I'm not certain of your exact beast modes you're using to calculate the YOY or MOM numbers but doing a YOY might look something like this: (this is all untested)

Google:

(
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`)`marketing_source_c` = 'Google' THEN `id` END)
-
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google' THEN `id` END)
)
/
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google' THEN `id` END)

Google Paid:

(
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`)`marketing_source_c` = 'Google Paid' THEN `id` END)
-
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google Paid' THEN `id` END)
)
/
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google Paid' THEN `id` END)

SPS_Website:

(
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`)`marketing_source_c` = 'SPS_Website' THEN `id` END)
-
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'SPS_Website' THEN `id` END)
)
/
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'SPS_Website' THEN `id` END)

All comments

jaeW_at_Onyx

if you have different marketing sources blended into the same column then you need

sum( case when marekting_source = 'value' then `amount` end )

i would recommend that you not calculate YOY or MOM as separate columns in your dataset. it makes it difficult to apply filters.

instead just use either window functions in analyzer or beast modes like you've done.

sum(case when year = year(curdate()) and `marketing_source` = 'value' then amount end )

Good luck!

Golfoholic

@jaeW_at_Onyx Ok now I can see. I am on the right path. So this data is meant to be in a table so that why there were custom calculated column for YOY and MOM and now the MTD amounts. sorry if I missed something but where is 'amount' coming from?

GrantSmith

Hi @user048760

The amount would be the column in your table that contains the MTD amounts in your table. It's the column name.

What @jaeW_at_Onyx is suggesting is that you split out each source into its own beast mode. For example:

Google:

sum( case when marekting_source = 'Google' then `Current MTD Amount` end )

Google Paid:

sum( case when marekting_source = 'Google Paid' then `Current MTD Amount` end )

And so on. You'd need to make sure the sources are correct (I'm guessing since I don't have any sample data to go off of) and that the name of the column storing the value you wish to bucket is correct as well (again I'm assuming Current MTD Amount is the name of your column but it'll need to be changed to the name you have)

Golfoholic

Source Table Snippet.PNG

Source Table Sample Data.xlsx

@GrantSmith @jaeW_at_Onyx I attached what the table currently looks like. Showing the calculated columns for YOY and MOM values both the % and the net difference, along with the table date setting reflecting the past 13 months, which is what is interacting with the 2 period over period calculated columns. We are really just dealing with 1 column of data and comparing it to YOY and MOM, and reflect the MTD amounts for those 3 channels. I currently put the marketing_source_c column in and then just filtered to those 3 channels, but unfortunately they need to be broken out in to their own columns, and the current inquiries column is to reflect the total of those 3.

GrantSmith

Hi @user048760

You'd need three separate beast modes, one for each source. You just need to include the condition to filter the appropriate source in your YOY, MOM etc calculations.

For example for Google:

(These are all untested code)

Inquiries

COUNT(CASE WHEN year(`Date_Entered`)=year(curdate()) AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)

(
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate()) AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
-
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate())-1 AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
)
/
(
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate())-1 AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
)

(
COUNT(CASE WHEN year(`Date_Entered`)=year(curdate()) AND month(`Date_Entered`)=month(CURDATE()) AND `marketing_source_c` = 'Google' THEN `id` END)
-
COUNT(CASE WHEN LAST_DAY(`Date_Entered`) = LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 month)) AND `marketing_source_c` = 'Google' THEN `id` END)
)
/
(
COUNT(CASE WHEN LAST_DAY(`Date_Entered`) = LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 month)) AND `marketing_source_c` = 'Google' THEN `id` END)
)

For each of your different sources you'd need a different beast mode then you can drag those beast modes onto your card. Hopefully this helps explain things a bit better for you.

jaeW_at_Onyx

@user048760 , @GrantSmith is spot on in the math. It will get the job done.

https://www.youtube.com/watch?v=CDKNOmKClms&t=239s

Either way. Good luck!

GrantSmith

Google:

CASE WHEN `marketing_source_c` = 'Google' THEN `Date_entered` END

Google Paid:

CASE WHEN `marketing_source_c` = 'Google Paid' THEN `Date_entered` END

SPS_Website:

CASE WHEN `marketing_source_c` = 'SPS_Website' THEN `Date_entered` END

Essentially you'd have those three case statements in 3 separate beast modes when you're calculating anything dealing with your Date_entered column instead of just plain `Date_entered`.

I'm not certain of your exact beast modes you're using to calculate the YOY or MOM numbers but doing a YOY might look something like this: (this is all untested)

Google:

(
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`)`marketing_source_c` = 'Google' THEN `id` END)
-
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google' THEN `id` END)
)
/
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google' THEN `id` END)

Google Paid:

(
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`)`marketing_source_c` = 'Google Paid' THEN `id` END)
-
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google Paid' THEN `id` END)
)
/
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'Google Paid' THEN `id` END)

SPS_Website:

(
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`)`marketing_source_c` = 'SPS_Website' THEN `id` END)
-
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'SPS_Website' THEN `id` END)
)
/
COUNT(CASE WHEN YEAR(CURRENT_DATE) = YEAR(`Date_entered`) + 1 AND MONTH(CURRENT_DATE) = MONTH(`Date_entered`) AND `marketing_source_c` = 'SPS_Website' THEN `id` END)