Unique calculation
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
Answers
-
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** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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.
0 -
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
...
...
...
...
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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 numbern
based on the random number, use a group by to get the maximum row numberc
(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
Randall Oveson <randall.oveson@domo.com>
0 -
I am not really following this; this isn't probabilities though.
0 -
Is there a way to take 1 one of data and split it into 3 rows of data?
0 -
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?Randall Oveson <randall.oveson@domo.com>
0 -
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.
0 -
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?)
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
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.Randall Oveson <randall.oveson@domo.com>
0 -
@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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive