12 posts tagged

redash

Calculating User Retention by 24-hour windows and calendar days in SQL

Estimated read time – 3 min

Yesterday I got a message from one of our blog readers, asking:

Let’s say today’s Monday, and an app was downloaded 187 times. If I want to find out what’s the Retention rate was on day 1, what day of the week should I start taking the count with?

He is referring to the blog post on Сalculating Retention Rate. I would like to clarify that since retention rate can be calculated by 24-hour windows, as well as by calendar days. In our case, day 0 will be Monday, and day 1 will be Tuesday. However, there is one little hiccup...

For example, if we started promoting our product on Monday, October 12 at 23:59, then all downloads of this day will have the retention rate of day 1. It’s a problem of performing calendar calculations. To address this, some data analysts calculate retention rate not only by calendar days but also by 24-hour windows.

Let’s apply this idea to the above case:

  • The Retention rate for day 0 can be calculated using the number of downloads from October 5, 23:59 to October 6, 23:59.
  • The Retention rate for day 1: from October 6, 23:59 to October 7, 23:59
  • And so on rolling 24-hour window.

How to calculate Retention Rate by 24-hour windows in SQL?

Let’s recall one of the queries from our previous post. It was written to calculate the difference between the download date and the user activity date. We need to change the query so that user activity is calculated by 24-hour windows. To accomplish this just change the calculation for datediff to 24-hour windows, updating the lines in bold.


SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
   floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) as date_diff,
   ndv(user.id) AS ret_base
   FROM USER
   LEFT JOIN client_session cs ON cs.user_id=user.id
   WHERE 1=1
     AND floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) between 0 and 30
     AND from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1,2

Updated query:

SELECT 
       cohort.date_diff AS day_difference,
       avg(reg.users) AS cohort_size,
       avg(cohort.ret_base) AS retention_base,
       avg(cohort.ret_base)/avg(reg.users)*100 AS retention_rate
FROM
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
          ndv(user.id) AS users
   FROM USER
   WHERE from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1) reg
LEFT JOIN
  (SELECT from_unixtime(user.installed_at, "yyyy-MM-dd") AS reg_date,
    floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) as date_diff,
          ndv(user.id) AS ret_base
   FROM USER
   LEFT JOIN client_session cs ON cs.user_id=user.id
    WHERE 1=1
     AND floor((cast(cs.created_at as int)-cast(installed_at as int))/(24*3600)) between 0 and 30
     AND from_unixtime(user.installed_at)>=date_add(now(), -60)
     AND from_unixtime(user.installed_at)<=date_add(now(), -31)
   GROUP BY 1,2
  ) cohort ON reg.reg_date=cohort.reg_date
    GROUP BY 1        
    ORDER BY 1

Final output:

1-19.png

Compare it with the previous one:

2-19.png

And as you see, the retention rate calculated using 24-hour windows is slightly lower in the first days.

 No comments    3   1 d   Q&A   redash   retention

Guide to modern Business Intelligence Tools

Estimated read time – 2 min

In our new series, we will try to give a detailed representation of  several BI tools using the SuperStore Sales dataset. The data in SuperStore Sales reflect sales and profit of the retail chain in US dollars.

In the upcoming blog post, we will discuss a real problem statement that could arise when creating a dashboard based on the SuperStore Sales data and design a functional layout to provide clear answers. Throughout this task, we’ll stick with a predefined set of colors to make the comparison more unbiased.

Next, we’re going to create a dashboard that would assist in data-based decision-making with each of the BI tools. We also plan to involve industry experts to learn from their experience.

A complete list of BI systems and tools to be tested in our experiment is provided below. I want to welcome everyone who is willing to help us in solving this challenge to message me on Telegram  – @valiotti. I will be glad to hear from you. Although it’s a non-profit project, it’ll be really useful for the open-source community.

1@2x.jpeg

We plan to cover the following list of tools:

Free Open Source:

  • Metabase
  • Redash
  • Apache Superset
  • Dash / Plotly

Free Cloud-Based:

  • Google Studio
  • Yandex Datalens

Paid Cloud-Based:

  • Mode
  • Cluvio
  • Holistic
  • Chartio
  • Periscope
  • DeltaDNA
  • Klipfolio
  • Count.co

Paid:

  • PowerBI
  • Tableau
  • Looker
  • Excel
  • Alteryx
  • Qlik Sense
  • Qlik View

The final goal is to evaluate the BI tools against the following criteria:

  • learning curve of BI tool (1 — too hard to learn, 10 — easy)
  • tool functionality (1 — very poor functionality, 10 — multifunctional)
  • ease of use (1 — very inconvenient, 10 — super convenient)
  • compliance of the result (1 — far from the designed layout, 10 — too close to the designed layout and objective)
  • visual evaluation (1 — poor appearance, 10 — great visual appearance)

An integral weighted score for each tool will be calculated based on the internal estimates.

The results will be posted to our Telegram channel @leftjoin_en and followers will also be able to share their thoughts on the experiment.
By the end, each tool will be represented as a point in the plane, which will be divided into 4 parts.

This article will be updated with links and ratings as we new posts come out.

 No comments    9   13 d   BI-tools   excel   looker   powerbi   redash   tableau

Analysing data on Facebook Ad Campaigns with Redash

Estimated read time – 4 min

Our previous article was dedicated to collecting data on Facebook ad campaigns. Today we will analyze this data using Redash. As a first step, we need to upload our script to AWS cloud and create a server with AIOHTTP before passing the data to Redash. Let’s improve the script a little bit for this task:

from facebook_business.api import FacebookAdsApi
from facebook_business.adobjects.adaccount import AdAccount
from facebook_business.adobjects.adreportrun import AdReportRun
from facebook_business.adobjects.adsinsights import AdsInsights
from facebook_business.adobjects.adaccountuser import AdAccountUser as AdUser
from facebook_business.exceptions import FacebookRequestError
import time

Redash receives data in JSON format, which we haven’t covered yet. A JSON file is a file that consists of a list of dictionaries. The script will convert it to JSON and pass the data to Redash. We’ll need the variable that stores our access token, app id, app secret and two functions: wait_for_async_job() and get_insights(). The latter function receives account parameters and collects data on ad campaigns. Select the following fields: clicks, impressions, costs and dates.

We need the entire script from our previous article – Collecting Data on Facebook Ad Campaigns

The return_json() function will call the get_insights(), which will append data to insights_lists. Since we may have several ad campaigns, our output can be a list of lists with dictionaries instead of just a list of dictionaries. Create a simple lambda expression that will smooth the output and return our insights_lists_flatten. Now, the script returns a list of dictionaries:

def return_json():
   insights_lists = []
   date_preset = 'last_year'
   for elem in my_accounts:
       elem_insights = get_insights(elem, date_preset)
       insights_lists.append(elem_insights)
   flatten = lambda lst: [item for sublist in lst for item in sublist]
   insights_lists_flatten = flatten(insights_lists)
   return insights_lists_flatten

We also need a AIOHTTP server that will return our output as JSON. Create a new file, import the AIOHTTP library and the get_json() function from the preceding script. Write a simple query handler: the script will receive data from Facebook asynchronously and our asynchronous handler function will sleep until all data is collected and transferred. The function uses json_response to transfer data in json format.

from aiohttp import web
from get_json import return_json
async def handler(request):
   data = return_json()
   return web.json_response(data)

Initialize and run our application.

app = web.Application()
app.add_routes([web.get('/json', handler)])
web.run_app(app)

Now, go to AWS cloud, create a new folder and upload the two scripts via SFTP connection. Check if the needed port is open, click console — network & security — security groups — default.

Run our file from the server. You can check whether it works by accessing it via server IP address specifying 0880 in the route field. Connect to Redash using URL and we’ll get the same table returned by our script:

url: server ip

Having query results, we can write the following query:

select date_start, sum(clicks) as clicks, sum(impressions) as impressions, sum(spend) as spend from query_45
group by date_start

It returns this table, grouped by the date_start column:

We can now plot this data on the chart, let’s see if there is any correlation between ad costs and clicks:

Bingo! Next time, we will show you how to get data on ad campaigns from Vkontakte.

 No comments    281   5 mon   BI-tools   data analytics   redash

Cohort analysis in Redash

Estimated read time – 5 min

In one of the previous articles we have reviewed building of Retention-report and have partially addressed the concept of cohorts therein.
Cohort usually implies group of users of a product or a company. Most often, groups are allocated on the basis of time of app installation / appearance of a user in a system.
It turns out, that, using cohort analysis, one can track down how the changes in a product affected the behaviour of users (for example, of old and new users).

Along with that, cohorts can be defined also proceeding from other parameters: geography of a user, traffic source, device platform and other important parameters of your product.

We will figure out, how to compare Retention of users of weekly cohorts in Redash, since Redash has special type of visualization for building such type of report.
Firstly, let’s sort out SQL-query. We still have two tables – user (id of a user and time of app installation) and client_session – timestamps (created_at) of activity of each user (user_id). Let’s consider the Retention of the first seven days for last 60 days.
The query is written in Cloudera Impala, let’s review it.

For starters, let’s build the total size of cohorts:

select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
	ndv(distinct user.id) as cohort_size //counting the number of users in the cohort
	from user 
	where from_unixtime(user.installed_at) between date_add(now(), -60) and now() //taking registered users for last 60 days
group by trunc(from_unixtime(user.installed_at), "WW")

The second part of the query can calculate the quantity of active users for every day during the first thirty days:

select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
        datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) as days,
	ndv(distinct user.id) as value  //counting the number of active users for every day
		from user 
		left join client_session cs on user.id=cs.user_id
where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
and from_unixtime(cs.created_at) >= date_add(now(), -60) //taking sessions for last 60 days
and datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) between 0 and 30 //cutting off only the first 30 days of activity
group by 1,2

Bottom line, all the query entirely:

select size.cohort_week, size.cohort_size, ret.days, ret.value from
(select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
		ndv(distinct user.id) as cohort_size 
	from user 
	where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
group by trunc(from_unixtime(user.installed_at), "WW")) size
left join (select trunc(from_unixtime(user.installed_at), "WW") AS cohort_week, 
        datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) as days,
		ndv(distinct user.id) as value 
		from user 
		left join client_session cs on user.id=cs.user_id
where from_unixtime(user.installed_at) between date_add(now(), -60) and now()
and from_unixtime(cs.created_at) >= date_add(now(), -60)
and datediff(cast(cs.created_at as timestamp),cast(user.installed_at as timestamp)) between 0 and 30
group by 1,2) ret on size.cohort_week=ret.cohort_week

Great, now correctly calculated data is available to us.

Data of cohorts in tabular form

Let’s create new visualization in Redash and indicate the parameters correctly:

It’s important to indicate the parameters correctly – the columns of the resulting query are compliant therewith.

Let’s make sure to indicate that we have weekly cohorts:

Voila, our visualization of cohorts is ready:

You can add filters and parameters to it and use in a dashboard

Materials on the topic

 No comments    761   7 mon   BI-tools   redash   sql   visualisation

Using parameters in Redash

Estimated read time – 2 min

The most convenient and useful thing in Redash is parameters. Parameters can be both in a report and in dashboards.
Parameter – is an element of the interface, controlled by a user producing a report.

Parameter in a report can be of the following types:

Each type explained separately:

  • Text – text input field, can be used in constructions of LIKE type, such parameter can’t be applied in public dashboards
  • Number – number, entered by a user
  • Dropdown list – list of values, from which a user can select just one or several values (not long ago, an option of multi-entry of parameters in a dropdown list appeared)
  • Query dropdown list – similarly to the previous one, however the values will be taken from the existing query results
  • Date / Date and Time / Date and Time (with seconds) – fields of date entry
  • Date Range / Date and Time Range / Date and Time Range (with seconds) – fields of date ranges entry.
    It’s convenient to use for the following construction
between '{{parameter.start}}' and '{{parameter.end}}'

In case of dashboard, a situation might arise, when a parameter is named the same way in several queries, then it will become common for all the dashboard, it’s handy.

One of not obvious, but rather useful solutions: how to make a parameter disabled?
Let’s assume, that parameter of “dropdown list” type is named parameter and we want to set it for a column geo of a table, then the code of the query will look approximately like that:

WHERE
    ('{{parameter}}' = 'Disabled' or geo = '{{parameter}}')

At that, surely, ‘Disabled’ should be added to the values of the dropdown list.

 No comments    415   8 mon   BI-tools   parameter   redash
Earlier Ctrl + ↓