Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I need to manually get spend data into power bi to make calulations like cost per action and cost per conversion with data brought in from Google Analytics.
The project I am working on involves senstive data so I cannot share this data.
I have data imported from Google analytics and am able to see the actions I want to mesaure along with the conversions. However my client does not have access to their ad data like cost per click so they want to be able to input this manually for the reports. They just know what they have spent per source and ad campaign per month.
So my problem is being able to use the spend data they have and make a relationship with the GA data to make the cost per action and cost per conversion calulations.
I have tried to merge the quieries with an excel file of the total cost per source/campaign (like pictured). However when I do this it attaches the total for that source/campaign to each line of data from GA that has that source/campaign. So when totalled it is the total times the number of lines of data. ex: every line of the table I merge the pictured table with puts 550 in the spend for AdSource1/6month_social. So say there are 10 lines of data from AdSource1/6month_social the when it is totalled it is 5500 instead of 550.
Is there a way I can do this?
@haydenDMS you should create a dimension table to link both together, most likely your dimension table will be a unique value of source + campaign, and add a surrogate key to combine these two fields, add the same surrogate key in cost and ga tables.
In the power bi semantic model, set the relationship between the dimension table and GA and the Cost table. From here onward you can calculate anything using DAX measures.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |