I am relatively new to PowerBi. I have been working in PowerBi with data from a mock excel data sheet with dates and values Campaign Analysis Data.xlsx (3.5 MB)
extending to March, 2022. There are columns I believe are missing from the worksheet and some columns e.g. ‘rand’ and ‘cost per exposure’ I haven’t been able to confidently decide how to integrate into my analysis. It’s campaign analysis data and I would appreciate pointers on how to approach the dataset, especially to how to calculate Campaign marketing costs.
The initial excel file is attached and it is from a fake marketing campaign.
Are you able to add columns using calculated columns in Power Query?
You can also reference the Enterprise Dna beginner Power Query or Power Bi to help guide
Hi @CyricAL - It depends upon how you want to Slice/Dice and analyse data in your report. As data is in a single table, first need to determine if this table will suffice for your analysis requirements. if not and preferably, shall look to split into formation of Lookup tables and Fact table.
But it will depend how much work is involved in converting some data into Lookup tables and adding Indexes in the Fact table and also can it be automated for future.
if possible, can try creating new Lookup tables like below with Index Columns and insert index values into “CampaignAnalysisDataImpressions” table.
Date table - To be linked with Sales Date column.
Compaign table - Capturing Compaign level information
Hi Ankit,
Thanks for the response. I did exactly as you suggested.
I created index columns in the fact table, created new lookup tables from there on. I also brought in other tables such as US population estimates from 2020 and Regions. One of the assumptions I made with regards to marketing cost was based on these variables.
Still a bit unsure as to what the “Rand” column represents. Any insight into that would be very much appreciated.
Regards
Cyric
Hi Ankit,
The Rand column and the dates extending to March 17, 2022 are the main challenges I have with the dataset. Unfortunately, I can’t ask source any questions regarding the data. Apparently, I am to figure a way around it.
What I did:
I made an assumption Rand figures represents Reach for each State. So brought in population figures for each state for year 2020. I have no doubt this could be a wrong approach to calculating impressions but that’s what I am working with for now unless/untill I get a better idea.
Thanks