9 posts tagged


Later Ctrl + ↑

Building a funnel-report in redash

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.
 No comments    14   2018   google analytics   redash   visualisation

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    23   2018   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    605   2018   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    413   2018   redash   sql