The model of the effectiveness of media advertising for online stores

We at People & Screens have been working with online businesses for many years as an advertising partner. When we had the idea to evaluate the contribution of display advertising to the sales of online stores, it seemed unrealizable and even crazy. As soon as we realized that all the elements of the mosaic can be found and put together, we decided to try it. The first hypotheses began to be confirmed, together with Data Insight, we delved into this story and in a few months of painstaking work created such a study, which, in fact, is an applied working tool – a model for evaluating advertising performance in 12 e-commerce product categories. In this article, we will talk about the results and the analysis methods used.

image

Research Objectives and Results

The key hypothesis of our study: display advertising, developing an online store brand, increases conversion in the entire sales funnel. In the analysis of sales data, advertising and external data over the past four years, the hypothesis was confirmed. As a result, we built econometric sales models for 60 online stores in 12 product categories.

  1. Only the short-term contribution of display advertising amounted to 39% of the growth of online stores with an average market dynamics of 50-60%.

    image

  2. Display advertising allows you to support sales through increased knowledge.

    image

  3. The greatest return on the whole in e-commerce comes from online video advertising.

    image

  4. The effectiveness of the media is highly dependent on the category: in the categories of clothing and online hypermarkets TV showed high efficiency, in electronics and auto products – online video advertising.

What we analyzed

Data collection for the study took place by the forces of both companies participating in the study. The following data was collected by People & Screens:

  1. Display ad exits data. We used downloads from the Mediascope databases that all ad groups have access to. We unloaded advertising costs for all media and advertising contacts to a wide target audience (All 18+) in details by day (for advertising on TV, radio, in the press, the Internet) and by month (for outdoor advertising) for the period from January 2016 to June 2019 To maximize the speed of work at this stage, we used the internal development of Dentsu Aegis Network Russia to work with industrial data, in particular the Atomizer platform.
  2. Unload data from SimilarWeb daily for the last 18 months. We looked at the dynamics by days of Visits on Desktop / Mobile, the dynamics by days of desktop traffic by source (channel), and the dynamics of installations on Android.
  3. Dynamics of knowledge / visits / purchases from the TGI / Marketing Index database for 2016-2019 by quarters. This is a download from Mediascope's Gallileo industrial software.
  4. Google Trends search queries for January 2016 – July 2019 across Russia.

On the Data Insight side, the following data was collected and provided:

  1. The dynamics of orders for 72 online stores from the TOP-100 ranking by month for the period from January 2016 to August 2019.
  2. The li.ru counter data for the period from January 2018 to August 2019 (traffic to the site, separately total, only in Russia and only mobile) for TOP-11 sites.
  3. Mail.ru counter data for the period from June 2017 to September 2019 for 53 sites.
  4. Rambler counter data for the period from June 2017 to September 2019 for 38 sites.
  5. Yandex Wordstat search query data for 24 months from October 2017 to September 2019.
  6. Evaluation of average checks of TOP-100 online stores as of 2018.

Data Algorithm

Data collection for the study was carried out in several stages. We will leave outside the scope of the article the work that our colleagues from Data Insight conducted to generate the data necessary for the study, but we will tell you what work was done on the side of People & Screens:

  • Search for all online stores from the TOP-100 rating in the industrial databases available to us and compile name matching dictionaries. For this, we used the Elasticsearch semantic search engine.
  • Formation of templates and uploading data on them. At this stage, the most important thing was to pre-think the architecture of the data tables.
  • Combining data from all sources into a single data set (dataset).
    To do this, we used the processing of uploaded data in Python using packages pandas and sqlalchemy. The set of life hacks here is pretty standard:
    when processing raw data from csv tables larger than 1 million rows, we first loaded the table column names with a query of the form:

    col_names = pd.read_csv (FILE_PATH, sep = ';', nrows = 0) .columns

    then the data types were added through the dictionary:

    types_dict = {'Cost RUB': int}
    types_dict.update ({col: str for col in col_names if col not in types_dict})
    

    and the data itself loaded function

    pd.read_csv (FILE_PATH, sep = ';', usecols = col_names, dtype = types_dict, chunksize = chunksize)

    The conversion results were uploaded to PostgreSQL.

  • Cross-validation of order dynamics based on analysis of traffic dynamics, search queries and actual sales across the People & Screens agency customer pool. Here we built correlation matrices using df.corr () for different data sets within a fixed site, then they analyzed in detail the “suspicious” series with outliers. This is one of the key stages of the study, at which we checked the reliability of the dynamics of the studied indicators.
  • Construction of econometric models on validated data. Here we used the direct and inverse Fourier transform from the package numpy (functions np.fft.fft and np.fft.ifft) to extract seasonality, piecewise smooth approximation for trend estimation and linear regression models (linear_model) package sklearn to evaluate the contribution of advertising. When choosing a class of models for this task, we proceeded from the fact that the simulation result should be easily interpreted and used to numerically evaluate the effectiveness of advertising, taking into account the quality of the data. We investigated the reliability of the models by dividing the data into training and test samples of a variable time interval. Those. we compared how the model trained on data from January 2016 to December 2018 behaves in the test time interval from January to August 2019, then we trained the model in the time interval from January 2016 to January 2019 and looked at how the model behaves on data from February to August 2019. The quality of the models was studied by the stability of the contribution of advertising factors in different training samples as the forecast on the test sample
  • The final step was to prepare a presentation based on the findings. Here we laid a bridge of mathematical models to practical business conclusions and once again tested the models from the point of view of common sense of the results.

The specifics of the analysis of e-commerce and the difficulties that arise in the process

  1. At the stage of data collection, there were difficulties with the correct assessment of the search interest in the resource. In Google Trends there is no way to group search queries and use negative keywords as in Yandex Wordstat. It was important to study the semantic core of each online store and upload the central request. For example, M.Video needs to be written in Russian – this is the central request for this site.

    For stores that sell goods both online and offline, colleagues from Data Insight took the following approach in Yandex wordstat data:
    Make sure there are no irrelevant questions (the main thing is not to estimate the volume of demand, but to track changes in dynamics). We were quite tough on filtering search words. Where there was a risk by the brand name to pick up inappropriate requests, we took statistics on key combinations. For example, “ozone store” instead of “ozone” – with this approach, the retailer’s search popularity is underestimated, but demand dynamics are measured more reliably and become cleared of “noise”. In relation to search statistics, there is a methodological problem that apparently does not have a reliable solution – for many retailers, these statistics are distorted by SEO tools that optimize search results through behavioral factors, but distort statistics on real demand.

  2. At the stage of combining data from different sources, there was a need to bring the data to a single granularity: data on TV advertising and traffic from SimilarWeb was daily, data for search queries was weekly, and data on orders and meter data was monthly. As a result, we formed a separate database with date fields that allow you to aggregate data at the required level, and a cached monthly aggregation database for further work with all the details of sales data.
  3. At the stage of cross-validation of data, we found noticeable discrepancies in the dynamics of sales with our own data. This required a discussion with colleagues from Data Insight. As a result, thanks to an accurate understanding of the months in which the largest errors occur, analysts have identified two errors that are deep in the bottom of the algorithm for assessing monthly sales dynamics.
  4. At the stage of model development, several difficulties arose. To correctly assess the effect of advertising, it was necessary to isolate external factors. Any sales dynamics (and e-commerce is no exception) is associated not only with advertising, but also with many other factors: UX / UI changes on the site, prices, assortment, competition, currency fluctuations, etc.
    To solve this problem, we used an approach based on regression analysis of data for a long period – from January 2016 to August 2019. As part of this approach, we analyzed changes (surges) in the dynamics of orders that can be attributed to advertising in this period.

It is important to understand that if at some point an advertisement started, but the expected value of sales, according to the model, was not higher than the actual one, then the model will show that this advertisement did not work during this period. Of course, such a sales behavior can be a superposition of several factors (for example, price increases / launch of competitors simultaneously with the start of an advertising campaign, or a website that has “fallen” from the influx of customers).

Since we average the effects over a long period of time over a large number of brands, the effect of such random coincidences should be leveled out on a large sample, although it can lead to overestimated or underestimated effects for individual brands. As a result, this allowed us to determine the general rules and patterns for the e-commerce category as a whole. At the same time, for a detailed analysis of the influence of advertising within individual brands, of course, it is still necessary to study the whole set of influence factors.

Output

As part of this study, we set ourselves the goal of obtaining the most reliable results based on data from heterogeneous sources. By themselves, these data are not exact values, but only an assessment of these values ​​by means of third-party monitoring (monitoring of advertising outputs, traffic dynamics, search interest and, finally, orders).

Each link has data quality limitations, and this is a problem that analysts and researchers face at one scale or another every day. We hope that within the framework of this article we were able to show which methods can ensure the reliability of the conclusions of the analytical study, while preserving the explanatory power of the results.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *