Analysis of Data in E-Marketing. Part 1: Channels for Attracting Customers

Customer Acquisition Cost and Lifetime Value, RFM-segmentation and Cohort analysis — these words for many marketers and owners of electronic businesses are like magic, same mysterious and miraculous. Mysterious, because if someone even understands what all this means, then he knows at least 22 ways how to calculate these values. Well, it needless to say about miraculous, because like any mean of “digitizing” marketing – it is a miracle in itself.

By this article, we open a series of analytical publications in which we show how we do this analysis ourselves and call on analysts to discuss difficult, sometimes controversial, but very topical issues.

We offer an unusual format for our blog - the articles will refer to a tabular file that shows examples of calculations. You can view, comment on, and even upload the file for your own experiments.

Getting Acquainted with the File

A spreadsheet file in Google Sheets format is hosted on Google Drive. It can be downloaded in Microsoft Excel format to your computer, but due to incompatibility of some of the Google and Microsoft formulas, some of the calculations may be lost. Therefore, we recommend viewing it directly in the browser, and if you have a Google account, you can make yourself a copy of the file and edit it at your own discretion.

As an example, the file contains random data from a fictitious online store. We decided to consider the analysis of ecommerce business, as it is the most common among our customers and readers and, at the same time, many popular analytical approaches in it work in a special way and cause a lot of controversy.

The number of tables that are in the file can be frightening, but most of the figures there are calculated automatically, and important results are displayed on diagrams. All you need for calculations is the sales base with information about order date, buyer id and price. Information on cost and channels of attraction is needed only for individual calculations, but we also analyze these values.

Analysis_Sample File Sheets

So, the file contains several sheets with original data and calculations.

The “Title” sheet contains content and explanations.

The “General_Analysis”, “RFM_Analysis”, “Cohort_Analysis” sheets contain examples of analysis. This time we will need only the first of these sheets.

The “Sales_Base” sheet contains, in fact, the base of the original data, and the “Base_of_Customers” - intermediate calculation data.

All sheets use the color differentiation of cells: pale orange - original data, blue – formulas.

General Analysis of the “Tip-Tops” Online Store

As already mentioned, the data under consideration were generated only for the study of analytical approaches and should in no way be taken as guidelines for business!

From what is important to understand about our store is that we have statistics for 2 years, the store has 1,711 unique customers who made 3,000 purchases during this time. All this information is in the header table on the “General_Analysis” sheet, with which we are currently working.

Revenue, cost, profit - all this have financiers and accountants in any business, we will deploy these data in the way marketer needs them.

Analysis of Channels for Attracting Customers

There are many options for segmenting data, each of which allows you to see some special aspects of the business. One of the most controversial and discussed in ecommerce is the analysis of channels of attraction.

In our example, four channels are considered:

1. Social Networks
2. Organic
3. Paid 1
4. Paid 2

In the database of original data on the “Sales_Base” sheet, the “Acquisition_Channel” field is filled for each order, showing the channel, as a result of which the sale was made. It is clear that this is an ideal option, when we can compare any sale with marketing activity, but it is worth to strive for this!

Channels for Attracting Orders

The maximum profit, according to our calculations, brings the “Paid 1” channel (see the diagrams below). This is understandable, because the same channel provided the largest number of orders. The weakest in this case was the “Social Networks” channel, it turns out to be the first candidate to get optimized for spending.

However, if we look at the value of specific profit per order, then we see that our leader became an outsider - the average profit from the order on the “Paid 1” channel is the lowest. And “Social Networks”, on the contrary, looks very good.

Thus, we should think about the possibility of scaling channels “Organic” and “Social Network”, which have the greatest specific profitability. Will they be able to provide a stream of customers that is comparable to the “Paid 1” and “Paid 2” channels? If so, they, in the end, can bring more money at a lower cost.

Cost of Attracting Customers to Ecommerce

Please note that so far, comparing the cost of attracting customers through different channels, we have talked, in fact, about the channels for attracting orders. I.e., in our base, one customer may have several purchases, stimulated through different channels. For example, the customer made the first purchase as a result of a search on the Internet, and the second - after paid advertising on the “Paid 2” channel. We refer each purchase to your channel of attraction/promotion.

At the same time, under the term channel of customer’s attraction, as a rule, is understood the channel on which the customer “came”, i.e., in our case, made the first purchase. This works well for subscription-based services, such as SaaS. There, each subsequent subscription fee is on schedule, and not as a result of new marketing efforts. Accordingly, all recognition is rightly obtained by the channel, which “brought” the customer.

But in ecommerce everything is different. If we attribute the second and subsequent purchases to the same channel through which the customer came, then we will be miles out in our conclusions. After all, one channel can work better to attract, and another - to repeat sales, i.e., retention. For example, email, it is most effective for repeat sales, whereas as a channel of “cold” first sales it is used today only by spammers.

So What about Customer Acquisition Cost?

Thus, trying to assess what we did to attract a customer to ecommerce, we cannot ignore the costs that brought him to the second and subsequent purchases. Or did not bring at all, but the costs, however, were. These costs can be substantial and even exceed the costs of attracting, as they accumulate over the lifetime of the customer.

As a result, the calculation of the CAC indicator in ecommerce turns into a rather complex task. At the same time, one must understand that it is possible to calculate this value only in general, without dividing it by the channels of attraction. Otherwise, we will have a problem of “mixing” channels on one customer.

In general, for ecommerce we see this situation as follows:

1. If it is necessary to evaluate the effectiveness of attraction channels, then we can use the cost indicators to attract orders or any other not tied to the customer base (for example, profit through channels).
2. If we want to get the value of CAC, then we calculate it as a whole, without splitting into channels, taking into account all costs for the lifetime of customer.

And how do you calculate CAC in ecommerce? We will be glad to discuss this topic in the comments.

To Be Continued…

The examples that we handled in this article suggest that customers who come one day stay with us forever. In real life, such, unfortunately, does not happen. In the following articles, which will be devoted to RFM and cohort analysis, we will certainly touch upon interesting topics of analysis of churn of customers in ecommerce and calculation of Lifetime value.

Stay tuned :)

Analysis of data in e-marketing. Article 2 - RFM segmentation

Analysis of data in e-marketing. Article 3 - cohort analysis and LTV