Manually Insert Ad Spend

[haydenDMS]

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? 

[parry2k]

@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.