4 posts tagged


Kazakhstan Marketing Conference 2020

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

Yesterday I had a chance to address the largest marketing conference in Kazakhstan: Kazakhstan Marketing Conference 2020.

Almaty, as a city, has made a positive impression on me, whereas the conference itself turned out to be highly professional event, filled with plenty of smart, versatile and kind people.

A pleasant bonus for conference participants: presentation of my speech available on slideshare (careful, VPN!), so one can recall what it was about.

Apart from the speech, in the main forum’s section I was holding a masterclass on “How to construct a comprehensible technical specification on analytics?”.
And, within the framework of work with the audience, we managed to formulate points for a template of a technical specification.

Sharing the template, it will be helpful for those, who faced with difficulties in translating of a task from business language to the technical one.

 No comments    485   2020   analysis   conference   marketing   template

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    480   2019   analytics   Analytics engineering   longread   marketing

Analytical merch

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

The other day I received an American analytical merch as a present, which picture I am sharing with you:

Merch from American software company Looker

I’ll explain how this merch is connected to me. Over this year I’ve had a chance to work on implementation of an analytical tool Looker. Right now I am preparing a big detailed article for the blog, where I’ll try to explain the functionality of Looker, its distinctive features and analytical capabilities.

I assume, that the article will come out approximately in the middle of December, and in the nearest time I’ll provide you with a number of other useful posts.

 No comments    214   2019   branding   marketing

Diagram of BCG (Boston Consulting Group) Matrix

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

I will water down the blog with an interesting report, that was developed for Yota company on November, 2011. BCG Matrix has inspired us to develop this report.

We had: one Excel package, 75 VBA macro, ODBC connection to Oracle, SQL queries to databases of all sorts and colours. We will review report construction within this stack, but first, let’s speak about the very idea of the report.

BCG Matrix – is 2x2 matrix, whereon the clients’ segments are displayed by circumferences with their centres in the intersection of coordinates, formed by the relevant paces of two indicators selected.

To make it simple, we had to divide all the clients of the company into 4 segments: ARPU above average/below average, traffic consumption (main service) above average/below average. Thus, it turned out that 4 quadrants appear, and you need to place a bubble chart into each one of them, whereas the size of a bubble means the total amount of users within a segment. In addition to that, one more bubble was added to each quadrant (smaller one), that showcased the churn in each segment (author’s improvement).

What did we want to get at the output?
A chart of the following type:

Representation of the BCG matrix on the data of Yota company

The task statement is more or less clear, let’s move to the realization.
Let’s assume, that we’ve already collected all the required data (meaning that, we’ve learned to identify the average ARPU and average traffic consumption, in this post we won’t examine SQL-query), then the paramount task lies in understanding how to display the bubbles in the required places by means of Excel tools.

For this aim, a bubble chart comes to help:

Insert – Chart – Bubble

Going to the menu Selection of data source and evaluating, what is required in order to build a chart in the type that we need: coordinates X, coordinates Y, values of bubbles’ sizes.

Great, so it turns out that if we assume that our chart will be located in coordinates on the X axis from -1 to 1, and on the Y axis from -1 to 1, then the centre of the right upper bubble will be the spot (0.5; 0.5) on the chart. Likewise, we’ll place all the other bubbles.

We should separately consider the bubbles of Churn type (for displaying of the churn), they are located more to the right then the main bubble and might intersect with it, therefore we will place the right upper bubble to empirically obtained coordinates (0.65; 0.35).

Thus, for four main and four additional bubbles, we can organize the data as follows:

Let’s review more thoroughly how we’ll use them:

So, we set on X-axis – horizontal coordinates of the centres of our bubbles, that lie in the cells A9:A12, on Y-axis – vertical coordinates of the centres of our bubbles, that lie in the cells B9:B12, and the sizes of the bubbles are stored in the cells E9:E12.
Furthermore, we add another data set for the Churn, once more indicating all the required parameters.

We’ll get the following chart:

Then, we’re making it pretty: changing colours, deleting axis and getting a beautiful result.

By adding the required data labels, we receive what we initially needed in the task.

Share your experience in comments – did you build such charts and how you solved the task?

 No comments    913   2019   analysis   data analytics   excel   marketing   sql   strategy   visualisation