Tired of having to go to each individual marketing platform (Google Ads, Google Analytics, Facebook Ads, Linkedin Ads, Ads, Ads… and more ADS!) filtering your data just right, and then exporting to .CSV so you can painstakingly copy and paste your data to a master sheet in Excel for every single one of your marketing reports? By leveraging the synergy between Power BI and Supermetrics, you can push all of your marketing reports to one platform that refreshes continuously and is in the cloud for easy access (Power BI online service). The main reason why Power BI is so great is that you only have to create your report one time and then it updates the data all by itself (with automatic refreshing). In 2020, it is critical to leverage all of your data so that you can augment your marketing efforts and increase your Marketing Return On Investment (MROI). 

Link to this blog in Video Format: https://www.youtube.com/watch?v=AAbuvEPk6mw&feature=youtu.be

To fully unlock the power of Power BI and Supermetrics, you will need to purchase a few items. Depending on the size of your organization and the frequency of your reports, there are a few different options that you can use. Let’s first talk about Power BI.

Power BI (PBI) Pricing & Plans: 

There are 3 main ways that you can use Power BI to build your automated marketing reporting structure. https://powerbi.microsoft.com/en-us/pricing/

Plan 1: Desktop Only (Free) The first method is to completely avoid the Power BI online cloud service and simply download the Power BI desktop version (which is free) and have all of your reports stored locally or in a shared drive as a .pbix file. 

Pros: 

  • Lowest cost (free) 
  • Able to share across an organization through means such as Sharepoint/OneDrive. 

Cons:

  • Cannot perform automated refreshes in the desktop version of Power BI
  • Every person who wants to see the report must have Power BI downloaded on their computer to open the .pbix file. 
  • Cannot send people links to easily share across organizations. (Must send the whole file which can be up to 2 GB).
  • Users can be overwhelmed with options and setting in PBI, whereas a web page is more user friendly. 
  • Forcing users to manually refresh the report every time they want to update it can take a lot of time if pulling data in from many different sources. This hampers the user experience as any load time over 15-30 seconds is seen as taking forever. 

Plan 2: Pro license to use PBI Cloud Service  ($9.99 per user per month)- What OpGo uses

The second option is to buy a Power BI Pro license. This is a SaaS product that is billed monthly. This subscription gives you access to Power BI’s online service which helps you share reports to outside users. 

Pros:

  • Minimal cost when compared to the Premium subscription ($4,995 per month).
  • Gives access to Power BI online service which allows you to more easily share reports with app workspaces as well as setup automated refreshing. 
  • Allows you to update reports without having to send a whole new .pbix file. 
  • Allows you to share reports & workspaces with other PBI Pro users. 
  • The report is in the cloud, so the user viewing the report does not need to download Power BI.
  • Gives access to developer pipelines which makes having multiple people build 1 report significantly easier.

Cons

  • To share reports, users must also have a PBI subscription (Pro or Premium). 
  • Does not allow paginated reports.
  • Can have a max data size of 1 GB.
  • Can have only 10 GB of storage per user.
  • No access to AI-powered data modeling.
  • No incremental data refreshing.

Plan 3: Premium license for Power BI ($4,995 per month per dedicated cloud compute and storage resource)

The final option for your Power BI implementation is to buy a Power BI Premium license for your organization. This plan is basically Power BI Pro on steroids. 

Pros:

  • Users do not need a Power BI Pro subscription to view reports. 
  • Significantly larger data storage limits when compared to PBI Pro. (10 GB per user vs. 100 TB total).
  • Incremental refresh (load only new data which lowers refresh times).
  • Access to AI-powered data modeling.
  • Allows for paginated reports.
  • Great for massive enterprises. 

Cons:

  • High price point
  • Hard to “jump in to” (Have to have a good idea on what your PBI implementation will look like to justify the high cost).
  • Death by choice (So many options and features can lead to paralysis by analysis).

Supermetrics Pricing & Plans:

Supermetrics is a useful tool to bring all of your marketing data into one spot (Google Sheets). Let’s talk about how we can structure our Supermetrics data pulls to help us get all of our data into Power BI. 

Plan 1: Supermetrics for Google Sheets

  1. Pro License ($99 per month)

This is the most basic Supermetrics implementation. With the Pro license, you get a taste of the type of analytics you can access with Supermetrics.
Pros:

  • Pull data in from over 30 data sources.
  • Very easy to use the Google Sheets add on (No Coding).
  • Allows for weekly refreshes to keep your data up to date. 

Cons:

  • Limited data sources (Does not include LinkedIn Ads).
  • Only weekly refreshes, not daily.
  • Have to use Dimensions that are defined by the data source (i.e. Cost in Google Ads vs. Total Spent in LI Ads). 
  • 1 license is 1 user. So if you have multiple users, they will need to use the same email if you want to save $. 
  • Constrained to Google Sheets 5,000,000 cells max limit
  • Limited to 1,000,000 rows per query limit
  1. Super Pro License ($199 per month) – What OpGo uses

This license allows you to fully scale your marketing reporting with Power BI.

Pros:

  • Brings in data from all of your possible marketing data sources.
  • Allows for daily automated refreshes.
  • Very easy to use the Google Sheets add on (No Coding)

Cons:

  • Missing certain data sources such as Twitter Premium. 
  • Have to use dimensions that are defined by the data source (i.e. Cost in Google Ads vs. Amount Spent in FB Ads). 
  • 1 license is 1 user. So if you have multiple users, they will need to use the same email if you want to save $. 
  • Limited to 1,000,000 rows per query limit
  • Constrained to Google Sheets 5,000,000 cell max limit

Plan 2: Supermetics API (Cost is associated with use, so varies)

Pros:

  • Can pull data from anywhere that uses an API.
  • Allows for daily automated query pulls.
  • Great for big enterprises. 

Cons:

  • High price (Starts at multiple thousands of dollars per month).
  • Customer service does not seem to justify the high price point. 

Seeing all of these different options can be a bit confusing at first. The main deciders in what route you should choose is organization size, data set size, and cost. At OpGo, we use a combination of Power BI Pro and Supermetrics Super Pro for Google Sheets. This will be the featured method in this guide. 

Building Data Pulls in Google Sheets Using Supermetrics Super Pro Subscription: 

Once you have your Supermetrics Super Pro subscription for Google Sheets setup, the next step is to build your data pull/query. I will be showing you how to pull data in from Google Ads, Facebook Ads, and LinkedIn Ads. The first step is to launch the Supermetrics for Google Sheets add on. To get this add on go to Add-ons > Get Add-ons and search for Supermetrics. 

Once you have the Add-on you can go to the Add-ons section and launch the Supermetrics sidebar.

Note: The sidebar will have difficulties loading if you are logged into multiple Google accounts. To alleviate the annoyance of constantly disabling sync and logging back in, I use my laptop to create data pulls in Supermetrics and I use my desktop for Power BI analysis.  

Now that you have the Supermetrics sidebar launched, let’s pull in our first data source (Google Ads). 

On the sidebar, go to Data Source and scroll down to Google Ads. You will be prompted to login to your Google Ads account. 

Now you need to set up the query. You do not have to worry about coding anything as Supermetrics has an easy-to-understand query builder. Under “Select accounts” you will be choosing the Google Ads account that you want to analyze. I will be using All Google Ads accounts for the sake of our clients’ anonymity.

Now you go to “Select dates’ and select your Date Range. I will be using last year (2019) & this year (2020). I don’t like to use the “Compare to” range because I leverage DAX’s time intelligence functions to build my own time periods in Power BI (more on this later). 

Now, it’s time to set up your metrics. This is the most important part of the data query process. These metrics will need to be the same for every platform so that you will be able to cleanly append your data tables together in Power Query later. What I do is pull in the raw metrics (Clicks, Cost, Impressions, Conversions) so that I can set up a measure to create calculated metrics in Power BI. 

Next is pulling in your dimensions. These are similar to metrics in the fact that they must be the same across all of your data pulls so that you can append the table properly in Power Query. I use “Date” and “Campaign name” if I am analyzing data for an individual client. I add “Account name” if I am analyzing multiple clients’ ad data. Make sure to make the # of rows to fetch large enough for your data set (I just do 1,000,000). The sort rows doesn’t matter, but I like to see the latest data on top so that I know that the query is refreshing properly. 

I set the filter to “COST is greater than 0” to get rid of any weird data points. 

I don’t change any of the options at the bottom of the sidebar. 

Finally, you have to make sure your Google Sheet is big enough to pull in all of your data. The default size of a Google Sheet is 1000 cells. Since I will be pulling in data split by both campaign and date, the number of rows will be quite large. I add 25,000 rows to fix this issue. The add rows is all the way at the bottom of your last row in the spreadsheet. I also change the sheet name to Google Ads. 

Now, simply click the blue “Get data to table” button and your query will run. 

Now that your query is set up, you need to schedule daily refreshes. In the Supermetrics sidebar click on the schedule tab. Once in the schedule window, set your data to refresh daily starting at whatever time you like (I use midnight). 

We will be repeating the above steps for each different advertising platform on separate sheets within the same workbook. The one thing to note with the different sources is that there can be slight different name variations for the same thing. Do not worry about this as we will fix this in Power Query. One example of this is “Total Spent” in LI ads being the same thing as “Cost” in Google Ads. Below are screenshots for the query for each different data source. Remember to extend the sheet size on every page, but don’t go over Google Sheets hard cap of 5,000,000 cells per workbook (the sum of all cells on the Google Sheet).

Facebook Ads:

:

LinkedIn Ads:

Getting Data from Google Sheets into Power BI (Desktop):

Now that your data pulls are set up properly, it is time to get the data into Power BI. The first thing you need to do is set the Google Sheet privacy settings to anyone with a link can edit your Google Sheet. DO NOT LINK THIS SHEET TO PEOPLE YOU DON’T TRUST! It is important that if this data is sensitive that you do not give this data to people you do not trust. To access the privacy settings, go to File > Share. Once you get the link, copy & paste it somewhere like a Google Doc or notepad file. To be able to pull the data into PBI, you will need to edit this link. You will be taking out the “edit?usp=sharing” snippet at the end and changing the last part of the link to be “export?format=xlsx”

The link will go from 

https://docs.google.com/spreadsheets/d/xseoiwejfsoeijfewofjiwe/edit?usp=sharing

To

https://docs.google.com/spreadsheets/d/xseoiwejfsoeijfewofjiwe/export?format=xlsx

Now, you will import this new link as a “Web” data source in Power BI Desktop. In the top left corner of PBI, click on “Get Data”. Select the source as “Web” and paste your new link in. 

Once the preview loads, tick the boxes for your data tables and click transform data to import your data into Power Query. 

This is when you will standardize your data across all platforms. I like to make my data the same as Google Ads. First, you will open your Google Ads query and make sure the data has the right formatting (First row is headers, conversions are whole numbers, campaign name is text, Cost is fixed decimal etc.). Next, you will go to the “Add Column” section and add a custom column. This custom column will be called “Channel”. For the formula do “Google”. This will help you filter your data later when you append all of your ad data tables together. Change the data type of the “Channel” column to Text. 

Here is what my Google Ads query looks like 

Next, you need to make all of your other queries look like your Google Ads Query (Same # of columns and same column names).

Let’s start with Facebook Ads. 

The columns that you will need to change are “Link clicks” → “Clicks”, “Website conversions” → “Conversions”, and “Amount Spent” → “Cost”. Next, add the Channel column again, but this time make it “Facebook” or “FB”. Ensure all the column data types are correct.

Here is what my FB ads query looks like: 

Now, do the same thing for LinkedIn Ads. You will only need to change the “Total spent” column to “Cost”. Again, add a channel column and your individual queries are all good to go. 

Here is what my LinkedIn query looks like: 

Now that your individual queries are set up, it’s time to append them together to form one table. 

To do this, go to the “Home” portion of the top ribbon and click “Append queries as new” from the “combine” section. 

Go to the “Three or more tables” section and put all of your tables in the right section and click Ok. 

Make sure that your appended table only has 7 columns that match your Google Ads query and rename the query to “Ads Data”. You shouldn’t have to do any edits on this appended table. 

Now that you have your appended table, you can remove your individual ad data queries from loading into your model. To do this, right click on one of the individual queries and uncheck “Enable Load.” If done properly, the query text will become italicized. 

Do this for all of the individual ad channel queries. 

Now, you can finally click close and apply to load the ad data into your Power BI data model. Open the “Ads Data” table in Data view and ensure that the data looks good. 

Here is how my data looks:

Setting up your Date Table: 

One of the most powerful features in DAX is the time intelligence functions. To take advantage of this, you will need to build your own Date table. I went over the whole process on how to build a Date Table in my KPI blog here (https://opgomarketing.com/unlocking-the-full-power-of-powerbis-key-performance-indicator-card-compare-across-different-years/)

For the sake of this blog, all we need to do is set up the unique date values by using the CALENDAR DAX function. In the Data view, go to “Home” and click “New table”.

Put this formula in for the new table to create your Date table. 

Date_tbl = CALENDAR(DATE(2019,1,1),TODAY())  

Go to “Table tools” and mark this as the Date table. 

Here is what the Date table will look like:

Creating Relationships:

Go to the relationship view in Power BI. Click “Manage Relationships” and add a One to Many relationship between the Ads Data table and the Date Table. The Date table will be the One side and the Ads Data table will be the Many side of the relationship. This is because there are multiple campaigns running on a single date in the Ads Data table and there are only unique Date values in your Date Table. 

Creating a Measures Table:

This next step is key in scaling your PPC reporting. You will now create all of the calculated measures using the 4 metrics that you pulled from Supermetrics. These calculated measures are Conversion Rate, Cost per Conversion, CPC, CPM, and CTR. First, create a new table and name it “Measures_tbl”. Here are the measures that you will need to put in this table. 

Conversion Rate = CALCULATE( 

SUM(‘Ads Data'[Conversions])/SUM(‘Ads Data'[Clicks])

)

Cost/Conv. = CALCULATE(

SUM(‘Ads Data'[Cost])/SUM(‘Ads Data'[Conversions])

)

CPC = CALCULATE(

SUM(‘Ads Data'[Cost])/SUM(‘Ads Data'[Clicks])

)

CPM = SUM(‘Ads Data'[Cost])/SUM(‘Ads Data'[Impressions])*100 

CTR = CALCULATE(

    SUM(‘Ads Data'[Clicks])/SUM(‘Ads Data'[Impressions])

)

Now, change the data types to the proper format (Conversion Rate & CTR to Percentage, CPC and CPM and CPC and Cost/Conv. to Currency)

Here is what my measures table looks like: 

Building a Report/Dashboard Template:

Now that you have all of your data pulled in and cleaned, it’s time to actually make the report. This can be different for every organization. If you have previous reports, try to make a dashboard that emulates those reports. If you are unsure on where to start, you can look at/use templates from either the Power BI gallery or Supermetrics. When building reports, make sure that any user could just walk up and understand it—if you only make the report for PBI literate people, you may lose your audience. 

Scaling your Reporting Across Clients & Datasets:

The synergy of Power BI and Supermetrics for Google Sheets allows you to apply a report template to all of your clients without having to manually build a new report for each client. The only thing that you will need to set up is a new data pull Google Sheet workbook for the client. Once you have a new Google Sheet, repeat the steps that you used earlier to get your new Marketing data into a Google Sheet workbook. Now, this is the really cool part about Power BI and Supermetrics. Once you have changed your Google Sheet privacy settings to anyone with a link can edit, copy this new link and open your Power BI report template. In the fields tab on the right hand side, right click your Ads Data table and hit “Edit query”

Once in Power Query, go to your “Google Ads” query that is italicized and go to the “Source” step on the right hand side of your screen.

With your new Google Sheet share link, paste it in where the current google doc link is. Remember to replace the end of the url with “export?Format=xlsx”. Do this for each data source that you want to update. If you are adding new ad data sources, you will need to add a new Web source and then make sure to add the new query into your append. Now, go to “Home” and “Refresh Preview” to verify that your appended “Ads Data” query has your new PPC data loaded in. 

Once you hit “Close & Apply” your measures and visuals will update to the new dataset. Make sure to use “Save As” to save the report so that you don’t overwrite your current file. You can now publish the report to the PBI Service and set up daily refreshes in an App workspace or share the .pbix file to get the report/dashboard to the end users. 

Conclusion:

I hope that you found a lot of value from this blog. In this blog I went over how to use Power BI and Supermetrics to scale your marketing reporting structure to the next level. If you use these tools together, it is possible to set up all of your client reporting in less than 1-2 hours. This will save massive amounts of time and headache when compared to exporting your data to Excel and then doing analysis every week or month. 

Be sure to Follow our LinkedIn and Subscribe to our YouTube channel to receive all of our new Power BI content. 

Link to this blog in Video Format: https://www.youtube.com/watch?v=AAbuvEPk6mw&feature=youtu.be

Follow OpGo! 

YouTube: https://www.youtube.com/channel/UC4hqKlJJ3yRxkXb3dq4aw4Q

LinkedIn: https://www.linkedin.com/company/opgo-marketing 

Website: https://opgomarketing.com/contact

Connect with Logan!

LinkedIn: https://www.linkedin.com/in/logan-riebel/ Email: logan.riebel@opgomarketing.com