Designing dashboards for web analytics e-commerce site. Part 2: Email newsletters. Strategic dashboard
- Tutorial
Email channel in e-commerce projects used for a long time. Mailing lists are launched to inform customers about new products and company news, talk about current and planned promotions, share interesting content. I consider the main goals of using this channel: increasing the average check and the number of customer orders by sending out related products, stimulating impulse purchases through unique offers and promotions, increasing audience loyalty to the product and “warming up” the audience through a story about unique trading qualities of the product.
Mailing Lists
Suppose that in a company the results of emails are interesting for three people: the marketing director (strategic report), analytics (analytical report) and email marketing specialist (operational report). We will design a dashboard via the email channel for the marketing director.
Dashboard will answer the following questions:
The share of revenue from the email channel of the total revenue . It will show how important the role is played by email-marketing compared to other channels, whether the result on it is worth our efforts.
Average check . Shows whether we increase the average order check due to special offers. Whether we select the right products for the content of the letters - are there any too cheap among them, that delay all attention while viewing the letter to themselves?
Revenue, Gross profit (the difference between revenue and cost of goods). Do we lose money because of discounts, are our stocks effective, or do high-margin products buy from mailings?
Marginality (the ratio of gross profit to revenue). Was there a loss-making campaign in some month, did we increase the channel margin from month to month?
The number of orders .
Share of shipments (ratio of orders shipped to all orders). Shows how well we process orders from email mailings. There may be cases when managers are not notified of a secret action, and orders for this action come. As a result, customers expected a single cost of goods and gifts, but in fact did not receive. Let's display in the form of a graph with the dynamics of the share of distribution of orders by status.
The share of discoveries . How many were unique discoveries of letters from all delivered letters. Shows how interesting and relevant headlines are used.
Share conversions . How many sessions were on the day of sending the letter from the unique readings of this letter. It shows how the title corresponds to the content of the letter, how interesting the content and suitable products are used.
Conversion coefficients . How many orders were issued after the transition to the site from the letter. Displays the status of our subscribers and the site: they only want to see the products on the site or after the transition they are interested to buy goods, is the site convenient for making a purchase.
Share spam, Share unsubscribe . How much subscribers are tired of our letters, whether the frequency of distribution is high.
Subscriber base size and its dynamics . How many subscribers come to us through what method of subscription. The subscriber base is growing or decreasing.
To calculate the figures above, we will need to download from CRM with orders, download from CRM with cost of the order, download from the statistics counter (let it be Google Analytics) with the source channel of each order, download from the ESP system with data on promo and trigger emails.
To upload from GA, we’ll use a custom connector for Google Analytics from Power BI, but for lack of sampling, we’ll write our own function, which uploads data by day.
To calculate the dynamics of indicators compared with last month and compared with last year, we use an additional table with the date. The common filter per sheet by date will use the column with the date from the additional table, and the calculation of the change in indicators will remove these filters and use them as a search for the current date.
As a result of the manipulations, the following type of dashboard was obtained.
Indicators in dynamics
Data for the current reporting period
The algorithm for using dashboards by the director of the marketing department can be as follows.
Summing up, in the strategic report I showed the main performance indicators of the channel (there were a lot of them, but it is always easier to remove the excess than to add). From the dashboard, you can clearly see which indicators do not reach the target values and which way to go in order to find the causes of negative trends and organize measures to improve the situation.
Articles from the cycle
Mailing Lists
Suppose that in a company the results of emails are interesting for three people: the marketing director (strategic report), analytics (analytical report) and email marketing specialist (operational report). We will design a dashboard via the email channel for the marketing director.
Dashboard will answer the following questions:
- What is the profitability and revenue from the channel?
- Can we increase the profitability of the channel?
- What is the situation with clients: is the level of formal replies growing, are we increasing the size of the subscriber base?
Key performance indicators
Financial performance
The share of revenue from the email channel of the total revenue . It will show how important the role is played by email-marketing compared to other channels, whether the result on it is worth our efforts.
Average check . Shows whether we increase the average order check due to special offers. Whether we select the right products for the content of the letters - are there any too cheap among them, that delay all attention while viewing the letter to themselves?
Revenue, Gross profit (the difference between revenue and cost of goods). Do we lose money because of discounts, are our stocks effective, or do high-margin products buy from mailings?
Marginality (the ratio of gross profit to revenue). Was there a loss-making campaign in some month, did we increase the channel margin from month to month?
The number of orders .
Mailing List Indicators
Share of shipments (ratio of orders shipped to all orders). Shows how well we process orders from email mailings. There may be cases when managers are not notified of a secret action, and orders for this action come. As a result, customers expected a single cost of goods and gifts, but in fact did not receive. Let's display in the form of a graph with the dynamics of the share of distribution of orders by status.
The share of discoveries . How many were unique discoveries of letters from all delivered letters. Shows how interesting and relevant headlines are used.
Share conversions . How many sessions were on the day of sending the letter from the unique readings of this letter. It shows how the title corresponds to the content of the letter, how interesting the content and suitable products are used.
Conversion coefficients . How many orders were issued after the transition to the site from the letter. Displays the status of our subscribers and the site: they only want to see the products on the site or after the transition they are interested to buy goods, is the site convenient for making a purchase.
Loyalty base
Share spam, Share unsubscribe . How much subscribers are tired of our letters, whether the frequency of distribution is high.
Subscriber base size and its dynamics . How many subscribers come to us through what method of subscription. The subscriber base is growing or decreasing.
Upload and calculations in Power BI
To calculate the figures above, we will need to download from CRM with orders, download from CRM with cost of the order, download from the statistics counter (let it be Google Analytics) with the source channel of each order, download from the ESP system with data on promo and trigger emails.
Unloading structure
To upload from GA, we’ll use a custom connector for Google Analytics from Power BI, but for lack of sampling, we’ll write our own function, which uploads data by day.
Text function for unloading order sources
let
Источник = (date) => let
Источник = GoogleAnalytics.Accounts(),
#"***" = Источник{[Id="***"]}[Data],
#"UA-***-2" = #"***"{[Id="UA-***-2"]}[Data],
#"***" = #"UA-***-2"{[Id="***"]}[Data],
#"Добавленные элементы" = Cube.Transform(#"***",
{
{Cube.AddAndExpandDimensionColumn, "ga:transactionId", {"ga:transactionId"}, {"Transaction ID"}},
{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
{Cube.AddAndExpandDimensionColumn, "ga:campaign", {"ga:campaign"}, {"Campaign"}},
{Cube.AddAndExpandDimensionColumn, "ga:sourceMedium", {"ga:sourceMedium"}, {"Source / Medium"}},
{Cube.AddMeasureColumn, "Transactions", "ga:transactions"}
}),
#"Строки с примененным фильтром" = Table.SelectRows(#"Добавленные элементы", each [Date] = date)
in
#"Строки с примененным фильтром"
in
Источник
Generating a column with dates and calling a function
let
x = Number.From(DateTime.LocalNow())-Number.From(#date(2017, 1, 1)),
Source = List.Dates(#date(2017, 1, 1), x, #duration(1, 0, 0, 0)),#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "DimDate"}}),#"Вызвана настраиваемая функция" = Table.AddColumn(#"Renamed Columns", "ga - trans id", each #"ga - trans id"([DimDate])),#"Развернутый элемент ga - trans id" = Table.ExpandTableColumn(#"Вызвана настраиваемая функция", "ga - trans id", {"Date", "Campaign", "Source / Medium", "Transactions", "Transaction ID"}, {"Date", "Campaign", "Source / Medium", "Transactions", "Transaction ID"})in#"Развернутый элемент ga - trans id"To calculate the dynamics of indicators compared with last month and compared with last year, we use an additional table with the date. The common filter per sheet by date will use the column with the date from the additional table, and the calculation of the change in indicators will remove these filters and use them as a search for the current date.
Calculation of revenue dynamics
Правда, сравнение января 2018 года и декабря 2017 не удастся через такую формулу. Если сравнение месяца текущего года и месяца прошлого года критично, то формулу придется существенно увеличить.
Изм. Выручка, пр месяц =
(sum('CRMorders'[Выручка]) —
CALCULATE(SUM('CRMorders'[Выручка]);
Filter(ALL('CRMorders');
And(And(And(year('CRMorders'[placed_on])=year(Max('DateNow'[Дата]));
month('CRMorders'[placed_on])=month(Max('DateNow'[Дата]))-1);
'CRMorders'[Source / Medium]=«UniSender / email»);
'CRMorders'[status]=«logistic_delivered»))))/
CALCULATE(SUM('CRMorders'[Выручка]);
Filter(ALL('CRMorders');
And(And(And(year('CRMorders'[placed_on])=year(Max('DateNow'[Дата]));
month('CRMorders'[placed_on])=month(Max('DateNow'[Дата]))-1);
'CRMorders'[Source / Medium]=«UniSender / email»);
'CRMorders'[status]=«logistic_delivered»)))
Изм. Выручка, пр месяц =
(sum('CRMorders'[Выручка]) —
CALCULATE(SUM('CRMorders'[Выручка]);
Filter(ALL('CRMorders');
And(And(And(year('CRMorders'[placed_on])=year(Max('DateNow'[Дата]));
month('CRMorders'[placed_on])=month(Max('DateNow'[Дата]))-1);
'CRMorders'[Source / Medium]=«UniSender / email»);
'CRMorders'[status]=«logistic_delivered»))))/
CALCULATE(SUM('CRMorders'[Выручка]);
Filter(ALL('CRMorders');
And(And(And(year('CRMorders'[placed_on])=year(Max('DateNow'[Дата]));
month('CRMorders'[placed_on])=month(Max('DateNow'[Дата]))-1);
'CRMorders'[Source / Medium]=«UniSender / email»);
'CRMorders'[status]=«logistic_delivered»)))
Strategic Dashboard for Email Analytics
As a result of the manipulations, the following type of dashboard was obtained.
Indicators in dynamics
Data for the current reporting period
The algorithm for using dashboards by the director of the marketing department can be as follows.
- Determine the share of revenue from the channel in the current month. If it is insignificant (for example, less than 10%), then we look at the report briefly. We are discussing with the email-marketer quality opportunities for the growth of the channel. This could be a switch to another ESP system, an increase in the frequency of distribution, more active collection of subscribers to the database. If the share of revenue from the channel is large, then delve into the report.
- We select the last month in the filter and look at the value of financial indicators and their dynamics compared to last year. If the dynamics is positive and we are completely satisfied, close the report. If something is wrong, then look deeper.
- The reason for the decline in revenue at approximately constant other indicators may be a decrease in the average check, the number of orders, marginality, and the share of shipment. If we see a fall in any one indicator, then we set the task for the analyst to understand the reasons.
- If the fall did not succeed in explaining through financial indicators, then we look deeper at mailing rates. Have we begun to send less letters, how well do letters open, do they click on the links in the letter, and do orders.
- To understand the overall situation of the channel and its impact on customer loyalty, we look at the dynamics of subscribers, what forms customers subscribe to, whether they began to send emails to spam and unsubscribe more often.
Summing up, in the strategic report I showed the main performance indicators of the channel (there were a lot of them, but it is always easier to remove the excess than to add). From the dashboard, you can clearly see which indicators do not reach the target values and which way to go in order to find the causes of negative trends and organize measures to improve the situation.
Articles from the cycle