Unique calculation

s_f_OTT · November 14

I have an issue where there is a campaign "Campaign 1" that needs to be split out into 3 parts; NHL, NBA, and NCAAF. NHL will get 40% of the impressions, NBA will get 40%, and NCAAF will get 20%.

I cannot figure out how to break this into 3 parts and calculate the impressions. I have multiple other campaigns that I can easily pull the league out. Is there a way I can do this? Chat GPT was not as much help as I would have liked.

Thank you

ArborRose · November 14

Are you saying you can't just multiply by percentage based on the campaign?

CASE 

  WHEN Campaign = 'Campaign 1' AND League = 'NHL' THEN TotalImpressions * 0.40

  WHEN Campaign = 'Campaign 1' AND League = 'NBA' THEN TotalImpressions * 0.40

  WHEN Campaign = 'Campaign 1' AND League = 'NCAAF' THEN TotalImpressions * 0.20

  ELSE 0

END

s_f_OTT · November 14

https://community-forums.domo.com/main/discussion/comment/99422#Comment_99422

Sorry for the confusion.

Lets say I have a million impressions in Campaign 1. I am looking to say take 40% of those and call it NHL, take another 40% and call it NBA, and take the last 20% and call it NCAAF. In MOST other campaigns, I can say CASE WHEN Campaign LIKE NHL then NHL. But this campaign does not have that identifier.

ArborRose · November 14

If I grasp the concept properly:

In the context of digital marketing and advertising, an "impression" typically refers to the instance when an ad is displayed to a user. It's a measurement of how often an ad appears in front of someone, regardless of whether the person interacts with the ad or not.

If you have an online banner ad and it appears on a webpage, that counts as one impression.

If the ad is displayed to 1000 different users, the total number of impressions would be 1000.

Since "Campaign 1" does not have an explicit identifier for NHL, NBA, or NCAAF in the data itself, you would need a separate reclassification table that maps these impressions to their corresponding categories (NHL, NBA, NCAAF) based on some other attributes. This can be done through a join in your ETL (Extract, Transform, Load) process to reclassify the data.

A reclassification table will essentially contain a mapping of certain criteria (such as a portion of the campaign name, or a specific campaign identifier) to the league you're trying to associate it with. Here's an example of how you could structure this table:

Campaign Name	League	Impressions Split	Notes
Campaign 1	NHL	40%	Split from total impressions of Campaign 1
Campaign 1	NBA	40%	Split from total impressions of Campaign 1
Campaign 1	NCAAF	20%	Split from total impressions of Campaign 1
Campaign 2	NHL	100%	Entire campaign is NHL related
Campaign 3	NBA	100%	Entire campaign is NBA related
Campaign 4	NCAAF	100%	Entire campaign is NCAAF related
...	...	...	...

rco · November 15

Are you trying to randomly assign rows the value A, B, or C with approximate probability .4, .4, and .2 respectively? Or do you need to divide the table as close as possible to perfectly in that proportion?

If approximate is okay, you can do an Add Formula where you'll first generate a random number and then set the value of the assignment according to that:

name: "r", expression: RAND()

name: "assignment", expression: case when r < 0.4 then 'NHL' when r < 0.8 then 'NBA' else 'NCAAF' end

If you need them divvied up as close as possible to perfectly, then you'll need to use an Add Formula to generate a random number r, then use a Rank & Window sorted on the random number to get a row number n based on the random number, use a group by to get the maximum row number c (assuming it can change over time), join the result of the group by back to the dataset, and then use a case statement like this one to divvy them up: case when (n/c) < 0.4 then 'NHL' when (n/c) < 0.8 then 'NBA' else 'NCAAF' end

s_f_OTT · November 15

https://community-forums.domo.com/main/discussion/comment/99428#Comment_99428

I am not really following this; this isn't probabilities though.

s_f_OTT · November 15

Is there a way to take 1 one of data and split it into 3 rows of data?

rco · November 15

Sure, you could branch it three ways (perhaps into three different Add Constants tiles) and then union it with itself using Append Rows.

But I still don't feel like I understand what you're trying to do. You've said, "Lets say I have a million impressions in Campaign 1. I am looking to say take 40% of those and call it NHL, take another 40% and call it NBA, and take the last 20% and call it NCAAF."

Is each impression a row, or do you just have a single value that is an impression count?

If each impression is a row, what criteria are you using to decide which impressions will be called NHL, NBA, or NCAAF? Are you sure 40%, 40%, 20% will be the distribution, or were those just examples of what it might be?

s_f_OTT · November 15

https://community-forums.domo.com/main/discussion/comment/99453#Comment_99453

There are probably 5 rows that all say the same campaign name. Each row has thousands of impressions that total up to some number, say a million. I have to split it up because of the way the data was sent to us.

ArborRose · November 15

Hard for us to visualize what you are dealing with. @rco…is it possible the value he's looking at is a Bitwise operator? (Where each position in the value represents a bit value in binary. And each bit maybe corresponds to an impression?)

rco · November 15

Okay, so these "probably 5 rows" from the same campaign each have an impression count, but they're not divvied up the way you want. You want the campaign divided into exactly 3 rows, and you want the impressions divvied up 40%, 40%, and 20% between those three.

The first thing you may need to do is a Group By the campaign name with a sum of impression counts to get it down to one row per campaign with all the impressions together in one row. If you're okay having more than one row per campaign per association, you can skip this step.

Then, branch the data in three directions, to three different Add Formula tiles. In each Add Formula tile, you'll have two formulas.

In the first branch, the two formulas are:

Name: "association", Expression: 'NBA'
Name "impressions", Expression: ceil(impressions * 0.4)

In the second branch:

Name: "association", Expression: 'NHL'
Name "impressions", Expression: floor(impressions * 0.4)

And in the third branch:

Name: "association", Expression: 'NCAAF'
Name: "impressions", Expression: impressions - (ceil(impressions * 0.4) + floor(impressions * 0.4))

These formulas are written this way in an effort to ensure that your final impression total is the same as your incoming impression total. The results are somewhat odd for rows with very few impressions. For example, in a row with only 2 impressions, the NBA row will have 1, the NHL row will have 0, and the NCAAF will have 1. In rows with thousands of impressions like you describe, the results should be fine.

Finally, you'll use Append Rows to union these three branches back together.

s_f_OTT · November 15

@rco @ArborRose Thank you both for your help. Ultimately, I realized that I could just export the smaller dataset I needed to excel, make the transformation I needed and reupload making sure to filter out any data that could have been duplicated.

Unique calculation

Answers

Categories