From this guide you will learn how to:
- Export data from Google Analytics to the SQL Server database and use them in Power BI.
- Use measures in Power BI.
As an example, we will generate a report to monitor the efficiency of blog subscribers acquisition.
Report generation process can be divided into four stages which include:
- Import of Google Analytics data to Azure SQL Server Database.
- Connection of Power BI to SQL Server.
- Data preparation in Power BI.
- Dashboard design. At the end of this guide, you will be able to download the ready template for Power BI Desktop.
Well, let’s go.
Import of Google Analytics data to Azure SQL Database
Today, every digital specialist must be able to work with databases.
If you never worked with them before, don’t worry, because we will sort out a simple way of working with them. We will use the automatic Google Analytics data export service for the data import.
The import setup consists of three stages.
You should sign up and choose Google Analytics as the data source. Then add an account from where you need to obtain the data.
This is not complicated at all.
You need to specify a set of metrics and dimensions which are necessary to generate the report. All titles correspond to names in the Google Analytics interface.
In our case these are:
- As well as all completed goals. In this example, it is the completion of goals named goal1, goal2 и goal3.
In the date range, you need to select the data for the last two months.
You need to specify the database.
If you never worked with databases before, just activate the trial version. In this case, the database will be created automatically, thereafter you will receive settings to connect to it.
If you are already using SQL Server, then you can specify the connection settings to the existing database.
After that the database will become available for selection. You can find a more detailed instruction on the integration setup in the help section.
From then on, information on the database will update daily. This will ensure stability and relevance of data.
Data Preparation in Power BI
When the data collection issue is resolved, we can proceed to report setup in Power BI.
First, you need to connect to SQL Server database. For that you need to select Get Data, go to the Database tab and select SQL Server Database.
After that you will need to enter data to connect.
Data for connection include:
Please note that login and password are required on Stage 2 and you need to select Database during sign-in process.
If your database is tied to Renta, all connection data are available on the Connections page.
After a successful sign in, the table selection window will open in Power BI.
Look at a name of the table that contains Google Analytics data on the Integrations page and select it in Power BI.
Data processing in Power BI
One of the major functions of Power BI is Edit Queries mode.
In layman’s terms, this is an opportunity to edit data uploaded to Power BI on a preset algorithm.
Let’s see how it works.
Every one of us used to design reports, and we all know how exhausting this is. Seriously, I’ve never met anyone who would like this thing 🙂
Remember what you felt when you needed to tinker with the same report for hours. You were probably pissed off by the monotony of this work and a feeling of wasted time.
The whole cycle could look like this:
- You exported data from Google Analytics, then from the CRM system.
- You grouped data by certain columns. Probably you were lucky enough to use the VLOOKUP function in Excel.
- You filtered data by a necessary word or a metric.
- You calculated the sum of advertising expenditures.
This list can go on forever.
Well, the Edit Queries function allows to specify the step-by-step algorithm of data preparation once, and then Power BI automatically gets new data and processes them itself.
Thus, the reporting process is completely automated.
As an example, we will handle simple conversions.
Let’s draw two separate columns: Source and Medium from the column sourceMedium.
For that you need to switch to the Edit Queries mode and select the necessary column. Then choose Split Column – By Delimiter in the dashboard.
Choose custom value as a delimiter and specify ” / “. Please note that I reserved spaces.
Then the column is transformed into two columns. You only need to rename them: Source and Medium.
Next, you need to calculate the number of all completed goals.
For that go to the Add Column tab and select Custom Column. Assign Leads as the column name, and as the custom column formula summarize all completed goals.
In the end save and change the column format to Whole Number that will further help us avoid errors when creating measures.
Templates of calculation measures
Actually, we can proceed to dashboard design, but I prefer to prepare all calculation measures beforehand, and then switch to data visualization.
If we look at the example of our report, we can note that the measures are divided into three groups.
- This month’s data, more precisely – data for the last 30 days (MTY — Month To Yesterday).
- Data for previous 30 days (SMLM – Same Period Last Month).
- Month to month comparison (MOM – Month Over Month).
In order to create a measure, click New Measure on the dashboard, and copy the formula in the occurring window.
You need to create all of the necessary measures this way.
By the way, if you don’t rename columns in the source table and save their standard names, then you only need to copy formulas below.
First, we create measures that represent this month’s metrics.
The number of sessions for the last 30 days:
MTY Sessions = CALCULATE ( SUM ( Source[ga_sessions] ), DATESBETWEEN ( 'Source'[ga_date], TODAY () - 31, TODAY () ) )
The number of leads for the last 30 days:
MTY Leads = CALCULATE ( SUM ( Source[Leads] ), DATESBETWEEN ( 'Source'[ga_date], TODAY () - 31, TODAY () - 1 ) )
Conversion Rate for the last 30 days:
MTY CR = DIVIDE([MTY Leads],[MTY Sessions],0)
Now let’s calculate the same metrics, but for the same period in the past.
The number of sessions:
SMLM Sessions = CALCULATE(SUM(Source[ga_sessions]), DATESBETWEEN('Source'[ga_date],TODAY()-60,TODAY()-31))
The number of leads for the same period in the past:
SMLM Leads = CALCULATE(SUM(Source[Leads]), DATESBETWEEN('Source'[ga_date],TODAY()-60,TODAY()-31))
CR for the same period in the past:
SPLM CR = DIVIDE([SMLM Leads],[SMLM Sessions],0)
As the final step, let’s compare this period’s metrics with the previous one. We use the following measures for that.
Comparison of the number of sessions:
MOM Sessions = Var thisWeek = IF(ISBLANK([MTY Sessions]), 0, [MTY Sessions]) Var prevWeek = IF(ISBLANK([SMLM Sessions]), 0, [SMLM Sessions]) Return IF(thisWeek = 0, IF(prevWeek > 0, -1, BLANK()), ( thisWeek - prevWeek) / thisWeek)
Comparison of the number of leads:
MOM Leads = Var thisWeek = IF(ISBLANK([MTY Leads]), 0, [MTY Leads]) Var prevWeek = IF(ISBLANK([SMLM Leads]), 0, [SMLM Leads])ReturnIF(thisWeek = 0, IF(prevWeek > 0, -1, BLANK()), (thisWeek - prevWeek) / thisWeek)
Comparison of the Conversion Rate:
MOM CR = DIVIDE([MTY Leads],[MTY Sessions],0) - DIVIDE([SMLM Leads],[SMLM Sessions],0)
We might stop here, but it’s also interesting for us to assess percentages of value share of each advertising channels by sessions and leads:
%GT Sessions = [MTY Sessions] / SUMX(ALL(Source),[MTY Sessions])
%GT Leads = sum(Source[Leads]) / SUMX(ALL(Source),Source[Leads])
Now we are completely ready to design the dashboard.
Dashboard design in Power BI
Dashboard design usually raises no questions and only implies the choice of visualizations.
You can also upload the prepared template and assign your own data source to it. It saves up much time.
Fill in the form below to download.
First, I create a table and draw the created measures there, and then check that there are no mistakes 😉
Then we draw Card and Line Chart to see dynamics of the stated KPIs.
As the final step we draw the Stacked Chart Bar to assess the share of leads by channel and traffic source.
Now our report is ready and we only need to upload it to a web version where we need to set up automatic data update.
Publication of the Power BI Service report
Power BI Service is a cloud service that allows organizing storage and internal availability of reports that you generate Power BI Desktop.
The advantage is that all reports are updated automatically inside the system.
All reports are always accessible online as well as in the mobile application.
In order to download the final dashboard, you need to click the Publish button on the toolbar. After a successful sign in the report will be downloaded to Power BI Service.
After the report is published, you need to do two simple things:
- Schedule the data update.
- Confirm access to the source of data – in our case this is SQL Server Database.
These actions are described in detail in the Power BI help.
We have sorted out a simple case of data export from Google Analytics, but the major value of Power BI is that users are able to combine data from different sources.
For instance, data obtained from Google Analytics can be combined with data from Salesforce, and thus you can define how advertising campaigns influenced sales.
We have even prepared a step-by-step guide for combining data from Google Analytics and Salesforce. You can download it for free.
If you liked this article, please share it on your social media.