2 posts tagged


Dbt Coalesce conference: best talks to watch

Время чтения текста – 3 минуты

The Coalesce 2020 conference, which I’ve mentioned before, took place from 7 till 11 of December 2020. This year, the organizers decided to carry out the conference in 5 days with a bunch of talks.

On the one hand, it’s an advantage as due to the abundance of information you have a sense of choosing what’s more interesting to watch. On the other side, such an amount of information is tiring as often it’s impossible to tell if the presentation will be interesting and useful just based on its name. In my opinion, it’s too much to have more than 3 days for a conference as the audience loses interest. Moreover, the need to deal with personal and professional issues cannot disappear because of the event that although online takes your time.

However, I managed to watch most of the talk, sometimes skimming through. First of all, my overall impression, it is great to study the presentations from conferences like Coalesce as they mostly cover modern BI tools and cloud solutions. Almost every talk mentions Redshift / BigQuery / Snowflake or BI tools like Mode / Tableau / Looker / Metabase. Obviously, dbt is in the middle of everything.

The shortlist of talks that I recommend for studying:

  1. dbt 101 — an introductory talk on what dbt is and how to use it.
  2. Kimball in the context of the modern data warehouse: what’s worth keeping, and what’s not 
    — an interesting but extremely controversial video that raised a lot of questions in dbt. In short, the author suggests using wide analytical tables and giving up normal forms everywhere.
  3. Building a robust data pipeline with dbt, Airflow, and Great Expectations — a talk about a rather interesting tool called greatexpectations which is used for data validation.
  4. Orchestrating dbt with Dagster — a video seemed a bit boring for me, but if you want to learn about Dagster, you’ll like it.
  5. Supercharging your data team — the guys created a wrapper for dbt called dbt executor 9000 and presented it.
  6. Presenting: SQLFluff — a video about a really cool feature called SQLFluff that automatically edits SQL code according to the SQL rules.
  7. QQuickstart your analytics with Fivetran dbt packages — from this video, you’ll learn about Fivetran and find out how to use it with dbt.
  8. Perfect complements: Using dbt with Looker for effective data governance
    about the interaction of dbt with Looker, differences and similarities of the tools.
 No comments    351   2020   analytics   coalesce   conference   dbt   education

Metrics for marketing analytics

Время чтения текста – 19 минут


Today in this update we have a long-read, supported by a telegram-channel Russian marketing on the subject of analytical metrics in marketing. Within the article we’ll discuss what marketing analytics is needed for, which metrics one should operate at calculation of marketing efficiency, how one can structure the work on building marketing reporting. Moreover, we will touch upon high-level KPI, discuss quite popular framework and sort out how to compute important analytical indicators. The article came up rather voluminous, and various abbreviations are used hereby, therefore we couldn’t do without a glossary.

  • Glossary
    • Revenue / Income / Sales — revenue, income (rub. / $ / euro)
    • GMV — Margin (% / rub. )
    • MAU — Monthly active users (persons)
    • WAU — Weekly active users (persons)
    • DAU — Daily active users (persons)
    • Requests — Requests (of advertising) (units)
    • Impressions — Displays (of advertising) (units)
    • Clicks — Clicks (on advertising) (units)
    • FR — Fill rate ( =Impressions / Requests) (%)
    • CTR — click through rate ( =Clicks / Impressions) (%)
    • С1 — conversion first purchase (%)
    • R, R1, R3, R7 — retention (of the 1st, the 3rd, the 7th day) (%)
    • RR (rolling retention) (%)
    • Churn — Outflow (%)
    • ARPU — average revenue per user (rub. / $ / euro)
    • ARPPU — average revenue per paying user (rub. / $ / euro)
    • cARPU — cumulative average revenue per users (rub. / $ / euro)
    • LTV (lifetime value) / CLV (customer lifetime value) — Customer lifetime value
    • ROI — return of investment (%)
    • ROAS — return on advertising spend (%)
    • ROMI — return of marketing investment (%)
    • CPA — cost per action (for example, purchase or installation of an app) (rub. / $ / euro)
    • CPC — cost per click (rub. / $ / euro)
    • CPO — cost per order (rub. / $ / euro)
    • CPS — cost per sale (rub. / $ / euro)
    • CPM (cost per mille) — cost per thousand advertising displays (rub. / $ / euro)
    • CAC — customer acquisition cost (rub. / $ / euro)
    • CARC — customer acquisition and retention cost (rub. / $ / euro)
    • SAC — share of advertising costs (%)

What marketing analytics is needed for?

In order to figure analytical metrics out, for starters we need to find out why we need marketing analytics and which questions it can reply. In general, marketing analytics is research and measurement of marketing activity in quantitative indicators. At that, most often, the aim of these actions is to evaluate the efficiency of marketing, calculate the return on marketing investments in the company.
Marketing analytics helps to find answers to the following questions:

  • How efficiently the marketing budget is allocated?
  • Which ROMI do various marketing channels provide?
  • Which target audience is the most effectively converted?
  • Which communication channels are the most / the least profitable?
  • What is the biggest source of company’s profit?

Marketing analysis is better to be initiated from identification of key business indicators and links therebetween, we’ll talk about it a bit later. In general, work on building of marketing analytics is more like creation of the system of proper metrics, their planning, measurement and reaction to changes of these metrics. More thoroughly the cycle PDCA is described in a book of W.Deming "Way out of crisis", I recommend you to get familiar with it.

Key principles of building proper analytics in marketing

Applying a systematic approach to analysis of data affecting the marketing activity can help marketologists to solve a problem, eliminate pain, provide recommendations for further marketing strategical steps. System approach implies abiding by a number of key principles, without which the analytics will turn out to be incomplete.

The task of marketing data analysis should be performed by a professional, who gets along with basics of mathematical statistics, econometrics and, obviously, can calculate, interpret the results and draw conclusions, relevant for a specific business (i.e. understanding the industry). Only in this case, the analytics can be fruitful, otherwise incorrect conclusions based on the data can even exacerbate the situation, that will lead not to budget optimization, but to devastation thereof.

Solving a task, one should address the data affecting a problem from various points of view. Different indicators, different aggregation of the data will allow to look at the problem objectively. It is desired, that the very same conclusion based on the data is repeated at least two times.

Studying current problems one shouldn’t operate the outdated retrospective data, the world is changing extremely fast, same way as the situation on the market / in a company. The analysis, held one year ago, today might have absolutely different results, therefore one needs to update the reports and data therein on a regular basis.

Results of analysis should be clear for a person from business, not familiar to technical terminology. In a perfect scenario – each report helps to wisely sort a problem out and pushes a reader to obvious conclusions. The situation, when an analyst is forced to dig into a huge pile of graphs, unclear charts and pages with numbers without any conclusions is inacceptable.

Such principles will surely help to hire competent analysts for building proper accountability.

How to structure indicators?

The system of metrics, helping to evaluate the marketing effectiveness, can be built based on the several considerations. One of the key approaches to structuration is customer’s lifecycle. Let’s try to sort it out and speak about one of the interesting frameworks for working on such system of metrics. The following major stages can be allocated in the life cycle of a customer.
1) Audience attraction – work of a marketing specialists starts even prior to the moment when potential audience turns into clients of a company
2) Engagement – stage of conversion of users who came to a website / mobile app into registered customers
3) Monetization – stage of formation of paying users (from the number of registered ones)
4) Retention / Churn – events dedicated to development and retention of the attracted audience, decreasing of the churn level

Method AARRR / Pirate Metrics

In 2007 Dave McClure developed and proposed a method called AARRR – the system of metrics, allowing start-ups to get to the bottom of business indicators. Another name of the method that can be also met, — "pirate metrics" due to the fact that the name is pronounced in a pirate way: "aarrr!".
So, let’s sort the approach out and speak about the metrics, responding to each step of the "funnel". The abbreviation consists of 5 key marketing stages:

  • Аcquisition — corresponds to paragraph 1 above
  • Аctivation — corresponds to paragraph 2 above
  • Retention — corresponds to paragraph 4 above
  • Revenue — corresponds to paragraph 3 above
  • Referral — recommendations (recently introduced stage)

At the entrance to the funnel our target audience that we desire to acquire is placed. Then, we are trying to do everything possible to register a potential buyer and turn him into a registered client (by that moment, a person who visited our site / app should realize the value of our product). Thereafter, a client conducts purchases and returns to us again and again. In the end, if he really likes our product, he will recommend it to his friends / acquaintances.

AARRR-funnel, pirate metrics (image source)

On every level of the funnel you need to choose metrics, describing transition from one state to another, that we can calculate and analyze. Let’s have an insight into each of the steps and metrics that correspond thereto. We’ll be studying each based on the example of real organizations, in order for calculation of indicators to be maximally comprehensible in practice.


Reach of potential customers is a key stage of formation of the new audience. let’s examine this crucial stage based on the example of a some mobile app and channels of traffic attraction. Oftentimes, the audience comes to application from several different sources:

  1. Organic traffic: search Google, Yandex, Bing, etc
  2. Organic mobile traffic: search in Apple Store / Google Play
  3. Commercial traffic: advertising in Facebook / Instagram, context advertising (Adwords), mobile advertising networks.

We’ll discuss it, using the example of an ad in Facebook. Each advertising announcement is targeted on the potential advertising audience, which is called "Reach" within the Facebook terminology. At that, we can optimize impressions of an ad by clicks / conversion / etc. Our task is to get as effective audience as possible for minimal amount of money. Consequently, we need to select metrics that will help us to evaluate efficiency. Let’s study them:

  • Impressions — number of advertisement displays, the indicator itself won’t say a lot and is extremely linked to the volume of potential audience, however we will need it for understanding of other metrics.
  • Clicks — number of clicks on ad in absolute figures also depends on the number of impressions.
  • Installs — number of clients who installed the mobile app
  • CTR — click through rate, calculated as Clicks / Impressions ratio and shows how efficient our ad is from the point of audience’s interest. in other words, what is click through rate of our ad.
  • CR (conversion rate) (= Installs / Clicks) — level of conversion, shows which percent of users have installed the app from those, who clicked on the advertising announcement
  • Spend — amount of money that we’ve spent on this advertisement
  • CPC (= Spend / Clicks) — shows us the cost of one click. We should operate this indicator in comparison with other ads / market benchmarks
  • CPM (= Spend / Impressions * 1000 ) — shows us the cost of thousands of impressions of an add. It is used for comparison with other ads / benchmarks
  • CPI (= Spend / Installs) — unit value of one install
  • Revenue — final revenue that we receive from this advertising announcement / campaign (you need to have tools for the proper attribution)
  • ROAS (= Revenue / Spend) — return on investments to advertising, gross income from a dollar spent. Metric shows the efficiency of advertising campaign from the point of money invested into it. For example, if ROAS is equal to 300%, it means that on every 1$ spent, 3$ were earned, and if ROAS is equal to 30%, it means that on every 1$ spent 30 cents were earned.

Thus, we already have quite good metrics palette that we can work with – examine their dynamics, compare the advertisements between each other and between various traffic sources. For example, a simple table, containing these indicators will already be the first approaching to understanding the advertising efficiency.

Facebook Campaign Efficiency

Advertisement Spend ($) Installs CPI Impressions CPM Clicks CTR CPC ROAS
Creative-1 x x x x x x x x x
Creative-2 x x x x x x x x x

This table can be reconstructed in a way, that we have dates vertically, and campaign is selected from the filter, thus we will start understanding the changes in dynamics of key indicators of traffic acquisition.

Summary: We can measure CTR of various banners and understand which from them is more appealing for the audience. this indicator can be used at A/B testing of the very same banner with selection of the most effective one. At calculation of effectiveness, you need to keep in mind CPC apart from CTR, in order to select not only the most clickable banner, but also not the most expensive one.

Key KPIs, indicators of efficiency in terms of money – CPI / ROAS, the first one shows how cheap / expensive we procure the traffic, and the second one – how well the traffic procured is monetized.


Let’s assume that we are developing a mobile game. Let’s think of what can be activation of user in this case? We have attracted users, who have installed a game to their smartphones. Our next task is to register a user (to make him a player), propose him an introduction tour to pass.
On this stage two metrics can be considered the key ones conversion into a registered user (= Registrations / Installs), conversion into users who passed a tutorial (=Tutorial Users / Installs).

Consequently, these two metrics will show us whether we require way too much from a user on the stage of registration, or, vice versa, the registration is very simple for him. The second metric will show how comprehensible the introduction to the game is, whether users are interested in passing the introduction tour, whether we require enough actions from a user.

Moreover, the last metric can be decomposed, if within the tutorial process a user needs to conduct several actions, we can examine the funnel of conversions into each of actions and understand the problematic spots of activation of new users. After activating our audience, we need to keep it, so we can make money thereafter.


Any organization would prefer to have an active base of loyal clients, who make repeating orders on a regular basis. Due to this fact, it is extremely important to track down a few key metrics: retention rate (or Rolling retention), Churn. I was sorting out construction of retention and rolling retention reports more thoroughly in one of the last blog’s articles.

Sticky Factor can be considered another crucial and fundamental metric — that is the level of engagement of users. Sticky Factor for a week is calculated rather easily: DAU / WAU * 100%. Let’s sort it out in more details based on the last example. The same way as before, we have a table — client_session, in which for every user_id the timestamps of activity are stored created_at. Thereafter, calculation of Sticky is quite easily performed with the following SQL-request:

SELECT daily.dau/weekly.wau*100 AS sticky
-- Calculating the average DAU for a week
( SELECT avg(dau) AS dau
(SELECT from_unixtime(cs.created_at, "yyyy-MM-dd") AS event_date,
ndv(cs.user_id) AS dau
FROM client_session cs
AND from_unixtime(cs.created_at)>=date_add(now(), -7)
AND from_unixtime(cs.created_at)<=now()
GROUP BY 1) d) daily,
-- Calculating WAU for a week
( SELECT ndv(cs.user_id) AS wau
FROM client_session cs
AND from_unixtime(cs.created_at)>=date_add(now(), -7)
AND from_unixtime(cs.created_at)<=now() ) weekly

Along with the fundamental metrics we shall address also the metrics, connected to the tools of client base retention. These can be represented by the tools of direct marketing: sms, email, push-notifications. Each tool usually has the following descriptive metrics: number of messages sent / number of messages delivered / number of users returned. They showcase the efficiency of each of the tools.


Finally, we have reached the very key metric, that represents a point of interest of all the business users – money. Revenue, profit – money that we receive from users at acquisition of our product. In absolute figures, this metric (or the result of company’s activity) is not highly indicative, however it’s important for understanding of the future trends.

Most often, the following number of relative metrics are used, that describe the behaviour of users:
ARPU ( = Revenue / Users )— average revenue per one user
cARPU( = cumulative Revenue / Users ) — cumulative average revenue per one user
ARPPU ( = Revenue / Payers ) — average revenue per paying user
Avg Receipt (= Revenue / Purchases ) — average check
LTV / CLV — aggregate revenue per one user (life value of a customer)

I am planning to dedicate a separate post to an issue of LTV, since it’s quite a wide subject. In this post we’ll figure out ARPU, cumulative ARPU and connection with LTV. The metric ARPU shows us how much we earn on a user on average for some period of time (normally, it is a week or a month). It is useful information, however it might be not enough. The task of efficient marketing is to attract such users, that bring company more money than the amount that is spent on attraction thereof. Thus, if we are modifying the indicator ARPU and review the cumulative ARPU for 30, 60, 90, 180 days, for instance, we will receive quite a good approximation to an LTV of a user. Would be even better if we build a curve of cumulative ARPU by days.

Curve of cumulative ARPU

By adding CPI as a horizontal line, we will obtain a graph that is extremely useful for understanding. In the point of two lines’ intersection we get a day, starting from which the revenue from a user becomes higher than the costs on his acquisition (user’s acquisition becomes effective). In the example that we observed above that is the 56th day of client’s life. Solution of this task is like searching for a break-even point, however we need to keep in mind that company bears also other indirect costs, that need to be considered in order to calculate the break-even point correctly.


Recommendation of products of the company to friends, relatives and acquaintances can be considered as the best scenario of interaction with clients and the highest level of reward for the company. With regard to metrics, the following can be allocated: number of activated invited new users per one client and NPS.

Number of activated referrals allows to increase CAC / CPI. For example, we attract a user for $1 and want to preserve such a tendency. We have developed mechanics of referral links and revealed that now, after implementation, an average user invites two other users. So, in this case, the cost of acquisition of a user will be equal to $1 / 3 = $0.33. Consequently, we can afford acquiring users for $3, maintaining the value of CAC that is acceptable for us.

NPS (Net Promote Score) — metric, that showcases the level of customer loyalty. The mechanics of calculation thereof is thoroughly described in Wikipedia, therefore we won’t stop on this point. Let’s just say that it is recommended to measure NPS on a regular basis, using direct marketing communication channels.

Hierarchy of metrics within an organization

We have examined crucial metrics of every stage of AARRR quite thoroughly, and now all we have left is to find out how we can structure the indicators in order to receive a perfect dashboard.

For the solution of this task, it would be wise to decompose the goals of the company and the metrics corresponding thereto into different levels. Oftentimes, each subsequent level corresponds to a company’s department and represents KPI of this department. To put it simply, we can imagine the main high-level goal of the company – Profit and decompose it into components: Revenue and Costs.

Hierarchy of metrics within an organization

A good example is a school of English – SkyEng, on the video you can get familiar to the thoroughly designed structure of metrics of SkyEng.

Another alternative might be construction of dashboard structure on the basis of AARRR framework, sorted out above. Schematically, such dashboard will look as follows:


Today we have studied the key marketing metrics, that will help to track the changes on every stage of marketing funnel and tell you about the efficiency of each stage, as well as become useful tool of any marketologist’s activity.

References to the topic:

 No comments    495   2019   analytics   Analytics engineering   longread   marketing