A Power BI report on PPC advertising before sales usually duplicates the account structure: advertising system – campaign – ad group, etc. But in such a report it is difficult to evaluate the effectiveness of different types of semantics or types of targeting. For example, we might have three campaigns for the advertiser’s brand, and the aggregate data for them in Power BI cannot be seen by default. To do this, you need to do additional integration with a ready-made report.
Since it is often difficult to make changes to the source file (for example, if the report was developed by the client and the access level required for such changes is not available), with one-time integrations it is easier to make adjustments manually. I will tell you how I solved this problem.
This is how the report on advertising channels and campaigns in Power BI looks like: we see a list of all campaigns in Yandex.Direct.
We need to add our own groups to the second level – between the Yandex Direct source and campaigns – that will combine campaigns with the same type of semantics or targeting. For example, all campaigns that include audience in their title must be part of the same Audiences group.
It is important that the campaign names are unique in each advertising system, then further evaluation of the effectiveness will be greatly simplified or even possible.
For example, we will be able to filter campaigns from different systems by some criterion, for example, by the advertised product.
So let’s get started.
First, in a separate Excel file, you need create a directory of campaign names and their corresponding groups (in the screenshot – the “Segment” column).
When the file is ready, load it into Power BI…
The table will appear in the list of tables for this Power BI.
And now the most important thing needs to be done – link existing report to group table… To do this, we will use the names of the campaigns. Go to the “Data Model” section and link the main table of the report with the table of groups through the names of campaigns.
Remains only display groups in the report, for this we add “Segments” to the second level (you can specify the name of the level yourself) from the new table.
And we get the type of report we need with the analysis of groups of advertising campaigns, united by the type of semantics and targeting.
Instead of a conclusion
Depending on the parameters that you pass in your advertisements, you can use a similar method for anything. For example, to group ads or keywords.
Plus, grouping campaigns based on their name isn’t just useful in Power BI. For example, with a large number of campaigns in Direct Commander or Google Ads Editor, it becomes much easier and faster to manage your account.
It also simplifies work in analytics systems like Google Analytics or Yandex.Metrica, when we use the utm_campaign parameter to collect custom audiences for building reports or preparing remarketing campaigns.
Subscribe to receive useful paid traffic content