LEFT JOIN: blog on analytics, visualisation & data science, posts tagged: google analytics

How to: Google App Script

Wed, 10 Nov 2021 16:43:25 +0300

Today’s post will shed some light on how you can use one of the very handy basic analytics tools – Google Sheets – even more effectively.
Often, analysts need to work a lot with tables and create selections for various conditions. In working with tables, the problem usually lies in the fact that you have to process and organize large amounts of data stored on different sheets. For example, you keep track of customers on one sheet of a spreadsheet, uploading data from third-party services, and you would like to add or change the data on another sheet. Let’s see how easy and simple we suggest to solve this problem.

Script editor

If you have a Google account and data sheets uploaded to Google Sheets, then you can create a script for this table. Select the table in which you want to automate the transfer of information from one sheet to another, open it and select the “Script Editor” item from the “Tools” menu. The browser will redirect you to the Apps Script page, where you can create and edit scripts for the table.

Automating line transfer to another sheet

So, our script should automatically perform the task of line transfer to another sheet. To do this, we create one more column in the table, in which it will be possible to check the box to transfer the line or remove it to cancel this action. Let’s see how exactly this is done.
When writing a script, you can use a trigger function that fires when a certain condition is met. You can read more about trigger functions in the documentation. In our script, we use the OnEdit(e) function, which is triggered when a table cell is edited (toggle the checkbox on and off). We will call this function at the end of the script, but for now, we will write everything that should happen in the function.

function myScript(e) {   
  // We set the following condition for the function: you only need to react to clicking the checkmark in the eighth column on the "Лиды-воронка" sheet.
  if (e.source.getActiveSheet().getName() == "Лиды- воронка" && e.range.getColumn() == 8)
  {
    // Saving the objects of the source sheet and the destination sheet
    destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
    sourceSheet = e.source.getActiveSheet();
    // We clear the destination sheet, starting from the second line, since we have a title in the table.
    destSheet.getRange(2, 1, destSheet.getLastRow(), destSheet.getLastColumn()).clearContent();
    // We go through all the cells with check marks, looking for those cells in which the check marks are put down.
    range = sourceSheet.getRange(1, 1, sourceSheet.getLastRow(), sourceSheet.getLastColumn());       
    for (i = 2; i <= range.getNumRows(); i++) 
    {      
      // We get all the checkboxes.
      if (range.getCell(i,8).getValue())
      {        
        // If the checkbox is filled, the current line is transferred to a new sheet.
        currentRow = sourceSheet.getRange(i, 1, i, sourceSheet.getLastColumn());           
        destSheet.appendRow(currentRow.getValues()[0]);
      }      
    }    
  }

// Then, we call a trigger function that will call our script every time we edit a cell.
function onEdit(e) {
  myScript(e)
}

As a result, if a check mark in a column is selected, then the corresponding row will be transferred to another sheet. Also, it is important to remember that when you remove a check mark, the line is removed from another sheet.
In our example, we got the following two tables:

Conclusions

Today we showed you an example of a script that will simplify, and maybe even improve, the routine work with tables. Of course, many more processes can be automated with Google Sheets scripts. You can read about other features yourself, and we’ll cover some of them in detail in future posts!

Building a funnel-report in redash

Tue, 04 Dec 2018 14:36:00 +0300

So, we’ve been planning to review Funnel-visualization of a report in Redash.
First and foremost, let’s build a request to the data source that we’ve created – Google Analytics.

The following text needs to be placed in the request console:

{
    "ids": "ga:128886640",
    "start_date": "30daysAgo",
    "end_date": "yesterday",
    "metrics": "ga:users,ga:goal1Completions,ga:goal2Completions,ga:goal3Completions"
}

In this request we are asking API Google Analytics to provide data for the last 30 days on the account GA: 128886640. We want to see the number of users and the number of completion of goals 1, 2 and 3.

As a result, our table will look as follows:

ga:users	ga:goal1Completions	ga:goal2Completions	ga:goal3Completions
3,926	105	41	32

Great, that’s right what we need in order to build a funnel.
Now I will tell you about one very useful Redash feature: query-results. In order to connect tables with results of queries’ execution, we go to Data Sources and search for query-results (beta). Connecting new data source.
Now we have an opportunity to refer to results of Redash queries. Thus, for instance, we can use the results of a requests to Google Analytics API.

How to do it?
We need to choose a data source query-results on the left:

Drop down menu with selection of data sources (in the console – on the left)

Now we’ll learn to make funnel-visualization. For this purpose, we write the following SQL-query:

select 'Add a good to the shopping cart' as step_name, ga_goal1Completions as goalCompletion from query_8
union all
select 'View the shopping cart' as step_name, ga_goal2Completions from query_8
union all
select 'Order processing' as step_name, ga_goal3Completions from query_8

In this case query_8 – is the very table with results of request to the data source Google Analytics.

Let’s set visualization:

Carefully selecting parameters, in order to achieve the desired result

As a result, we receive the funnel of conversions from one goal to another:

You can display this funnel in the dashboard and add filters / parameters thereto.

How to connect Google Analytics to Redash?

Tue, 27 Nov 2018 14:07:07 +0300

In this article we will take a look at how to connect the data source Google Analytic to the service Redash [We have already examined Redash and its opportunities more thoroughly in the previous notes].

Creating service account in Google

Moving to console of service accounts.

Creating new service account

In the window of account creating we insert the name, forming a new key afterwards. We select that we need JSON key and then press “Create”.

Integrating Analytics API

For the service account we’ve created, we need to integrate Analytics API.

When we’ve set everything up, Analytics API should be of green colour

Adding service user to Google Analytics

Next, we need to create the service user we’ve created to Google Analytics. The user will look approximately as follows:
user@PROJECT-ID.iam.gserviceaccount.com.
It is necessary to add the user with rights to Reading and View.

Creating new data source in Redash

Moving to settings (Settings) -> Adding new data source

Connecting new data source.

We are interested in data source Google Analytics, therefore we search “google”:

Searching google analytics in data sources.

Let’s recall where we’ve saved JSON file, we are going to need it now

Selecting the JSON file created before

Writing a query to the new data source

The query in Redash looks as follows:

{
    "ids": "ga:128886640",
    "start_date": "30daysAgo",
    "end_date": "yesterday",
    "metrics": "ga:users,ga:newUsers,ga:goal1Starts,ga:goal2Completions,ga:goal3Starts,ga:transactions,ga:transactionRevenue", 
    "dimensions": "ga:date"
}

How to know parameters for query execution?

Google has a great resource Query Explorer, in which one can find all the required metrics and measurements, that are available in Google Analytics.

I hope, this instruction was useful for you, further on we will find out how to make a goals funnel in Redash, basing on the data from Google Analytics.