Combining Revenue and Spend
I have a dataset with Facebook spend data and a dataset with revenue data. There are two join keys that will join the data... the date and AdID.
I can combine the data for both, but what ends up happening, the AdIDs are duplicated for every instance shared which does happen. Ideally, I'd want to divide the spend based on the amount of AdIDs shared in a single day...
Count of AdID per day / Spend = Shared spend by AdID
Does anyone have a solution for this? Or a different way to setup the data is fine too. Just trying to get a 1:1 match to spend and revenue based on a shared parameter between the data sets.
Best Answer
-
You need to aggregate the data to the same level prior to joining the tables.
You will need your revenue table and your spend table to aggregate their values by date and AdID.
SELECT
`AdID`
,count(`AdID`) -- this will tell you how many times the AdID appears each day
,`Date`
,sum(`Spend`) as `Total Spend`
FROM spend_table
GROUP BY `AdID`,`Date`
ORDER BY `AdID`,`Date`SELECT
`AdID`
,count(`AdID`) -- this will tell you how many times the AdID appears each day
,`Date`
,sum(`Revenue`) as `Total Revenue`
FROM revenue_table
GROUP BY `AdID`,`Date`
ORDER BY `AdID`,`Date`Now, when you join these two tables, you will have a 1:1 ratio for each combo of AdID and Date
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman3
Answers
-
You need to aggregate the data to the same level prior to joining the tables.
You will need your revenue table and your spend table to aggregate their values by date and AdID.
SELECT
`AdID`
,count(`AdID`) -- this will tell you how many times the AdID appears each day
,`Date`
,sum(`Spend`) as `Total Spend`
FROM spend_table
GROUP BY `AdID`,`Date`
ORDER BY `AdID`,`Date`SELECT
`AdID`
,count(`AdID`) -- this will tell you how many times the AdID appears each day
,`Date`
,sum(`Revenue`) as `Total Revenue`
FROM revenue_table
GROUP BY `AdID`,`Date`
ORDER BY `AdID`,`Date`Now, when you join these two tables, you will have a 1:1 ratio for each combo of AdID and Date
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman3
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 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