3 posts tagged


How to connect Google Analytics to Redash?

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:
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.

 No comments    9   1 d   google analytics   redash

Visualization of data in Redash

It is easy and handy to visualize the information in Redash, and in this post I will review the examples of data display on various charts. All the examples can be found in time series, constructed based on the data for each month.
Since I am all for analytics, along with graphs we will be exploring useful business indicators. Let’s start with quite a traditional metric for retail/e-commerce AOV (Average Order Value) – the average order value (in this case, for one month). The indicator allows to track changes, connected to consumer behaviour (whether they started buying less or more on average).

Example of a bar chart in Redash on the basis of AOV (Average Order Value) indicator

In terms of bar chart display, everything is pretty common, but handy – there is an opportunity to manage the colours of the chart, data labelling, format of data labelling (remove or display the data after comma).
Oftentimes, dynamics is much more evident, if looking either at traditional chart or at so-called area-chart. In this case, we are exploring new users’ dynamics, as well as which part of MAU (Monthly Active Users) belongs to new users.

This is a stacked chart – meaning that data of 2 rows are summarized, and one is shown above another.

In this example our chart is as informative as possible – we make one understand, which share belongs to new users, and, by stacking, we showcase the number of active users per month (in essence, killing two birds with one stone).
Actually, we could present the data in a slightly different way. For example, mix of various chart types is quite popular. Let’s imagine that MAU is represented by a bar chart (green on the chart), and the share of new users from MAU by red line, that is on an auxiliary (right) axis.

Two types of charts on one graph.

With Redash you can make pivot tables, display funnels and cohorts, and also use maps to display geo-data.
In the next posts I will tell you about a chart for funnel construction (however, prior to that we will learn how to involve google analytics).

 No comments    589   23 d   redash   visualisation

Redash – full-fledged on-demand analytics

Today we will examine and try to get to the bottom of one tool that is quite famous nowadays – Redash. The tool is extremely convenient due to the fact that it can work with Clickhouse. All the other developers of BI-tools don’t support Clickhouse. Just recently, an ODBC driver has appeared for Tableau.

I will make a short review of Redash’s useful features, and in the following number of posts I will figure out which useful requests and reports can be constructed using Redash.

Redash Homepage

So, what is Redash? It is the tool for on-demand analytics, moreover it can be used on completely different databases. For instance, Redash can be connected to a database under MySQL or HP Vertica.
The main thing, proposed by Redash is a handy console for writing SQL-queries to a database.

Console for writing SQL-queries

Consequently, the first major observation: in order to use Redash you should know SQL or have an employee in team who knows SQL.
Apart from the basic console, Redash provides visualization tools (inter alia, construction of Funnel-charts, cohort analysis and pivot tables (however, the functionality of the latter is very limited)), as well as tools for construction of dashboards and alert systems (via mail or in slack).
Let’s examine the basic console a bit more thoroughly. A user has an access to some useful features: application of filters, multifilters and query’s parameters:

Simultaneous application of parameters, filters and multifilters in queries

Further, opportunities of visualizations should be mentioned separately. They are, indeed, convenient and extensive, especially considering that one can set automatic updating of query results in accordance with the required timetable.
In the following note we will discuss visualization in Redash more closely, and later – construction of dashboards and alert systems.

 No comments    402   23 d   redash   sql