How Building A Data Warehouse For Omnichannel Retailer Increased The Return On Marketing Investment By 25%
eye

Our client: Omnichannel retailer. HOLZ is a network of innovative stores selling flooring, doors, and supporting client services.
Time of cooperation: 2 years (for the Analytics product)
The Problem

This business has several features that significantly affect the ability to track the effectiveness of different marketing communications. Here are those features:

  • it takes up to 6 months for the customers to make a purchase decision;
  • it takes up to 3 months to complete an order;
  • the users pick the goods online, but in 90% of cases they call or come to offline stores to make a purchase;
  • this business uses various data storage systems.

Taking these features into account, it’s not enough to only use Google Analytics to analyze marketing effectiveness. We needed something different: an integrated approach.

The Task

This business uses the omnichannel development strategy. Such an approach focuses on making the marketing communications of offline and online stores work together to increase the overall profit and encourage the growth of the business as a whole instead of focusing on a particular direction of a business.

Our client invests a lot of resources on the internet marketing strategy and therefore they wanted to understand how exactly does the internet marketing affects sales for business as a whole.

In order to answer that question, we have to:

1

Have all the information about the company’s interaction with clients and sales stored in one place.

2

Estimate the impact of internet marketing on overall sales.

As a result, we can use the obtained data for different purposes: for instance, to optimize the promotional campaigns for the actual sales.

The Solution

We decided to build a Data Warehouse (a database that stores the data from different sources and maintains connections between that data).

We based our decision on three main factors:

1

The omnichannel business model.

2

The significant time required to make a purchase decision.

3

The usage of different systems for data collection and storage.

We used the following tools and services to build Data Warehouses: Google BigQuery, Google App Script, Google Sheets, Google Analytics, Bitrix API, Binotel.

Stages Of A Data Warehouse Creation:

Screenshot_34.png Discovery - Analysis - Design - Implementation - Integration - Exploitation

Discovery (Research)

During this stage, we research the client’s business processes. This enables us to build data storage and infrastructure that would be useful to the business during different stages.

Here’s what we do during that stage:

Identify a business’s needs and goals.

Study business processes.

Study reporting that business uses.

Develop use cases for future Data Warehouse.

Model business processes.

Create a business description for a data storage system.

Below you can see a sub-scheme for the online sales and client interaction processes.

99999.png

The result. High-level understanding of the Data Warehouse concept (without any technical details):

What should be stored in a base?

Who gets access to data? How? During which stage?

How can business processes be improved? Do we have any recommendations for that?

Analysis

Deep processing of the data collected during the previous stage. During this stage, we create a data model and agree upon its functionality with a client:

  • Select metrics, parameters, and keys to bind the data from different sources. For instance, we can link CallTracker and CRM using a phone number. We can also link Google Analytics and CallTracker using a clientAD.
  • Create a conceptual data model (a scheme of data and its interaction) that could be easily understood even by those who aren’t technical specialists.

Below you can see a sub-scheme for how different entities are connected in the database:

8888.png

  • Selection of the implementation tools.
  • Creation of the Terms of Reference.
  • Project approval.

It’s imperative to evaluate the economic component during this stage. It’s easy to build a database that could store all the data (even the one that a business doesn’t use) and be perfect in theory. However, if the price for its maintenance is higher than its impact on business, such a database wouldn’t be useful.

The result. Project approval.

Design

  • The creation of three-level database architecture:

1. conceptual data scheme is the heart of a Data Warehouse as it connects the internal and the external database schemes.

2. external data schemes are created for each user group to limit the data and to convert them to a required format.

For instance:

-- Sales Department has access to clients’ data and could see from where the clients come; however, they don’t know the marketing budgets.

-- Marketing Department has access to a number of sales and could see revenue from them by traffic sources. This department also has access to clients’ LTV, but cannot see their contact and personal data.

3. internal data scheme includes different technical aspects that are important for implementation but have no value to end users.

  • The selection of the implemented algorithms.
  • The process of registering and setting up the BigQuery project.

The result. Our database is complete, and we have all the descriptions of the work and processes required for the next stage — the implementation.

Implementation

During this stage, a Data Warehouse is created:

  • We integrate it with different services (Google analytics, Binotel, Bitrix, etc.):

- data transformation: the data could be stored in different formats on various sources. Let’s take the date as an example: in some systems, it will look like ‘YY/MM/DD,’ while in some — like ‘DD/MM/YY.’ Fractional numbers could also be written differently on different sources (1.01 or 1,01). All these things could lead to calculation errors.

- creation of scripts and BigQuery data sending mechanisms: many services could take the data from them programmatically; however, the way they do it could differ. Therefore, we create a special script for each source.

- automation of the process.

  • We create the scripts and a mechanism that sends actual sales data to Google Analytics.
Screenshot_37.png

  • We create inquiries to get the answers to business’s questions.
  • We visualize the data.
  • We test to ensure that all the processes are set up correctly.

The result. A beta version of a Data Warehouse is created. We call it a beta because it’s hard to evaluate all the pitfalls that might appear during its operation before a Data Warehouse is integrated.

Integration

During that stage, we start integrating Data WareHouse into the company’s business processes. Here’s what we do to achieve that:

  • Create a usage manual.
  • Adjust business processes to achieve more effective and optimized resource usage.
  • Support the company while its employees start working with the system we’ve created.
  • Identify flaws and fix them.
  • Identify additional needs.
  • Improve the database, update it, and add additional functions if necessary.

The result. Our database is stable and integrated into the business.

Exploitation

During this stage (if necessary), we support the debugged work processes of a Data Warehouse and additional software. Mostly we do so by implementing additional functions.

The result:

  • Database in BigQuery; all the data is integrated and connected.
  • It’s possible to create SQL to connected data from different sources simultaneously.
  • The database is updated automatically. People are minimally involved in report creation.
  • Technical documentation. The description of the architecture and processes.
  • Google Analytics with new goals related to actual sales data.
  • Data visualization: dashboards, charts.
  • New Google Analytics goals that reflect actual sales.
  • Google Ads campaigns optimized by actual sales.
66666666.png

Data visualization example:

555555.png

The questions that this dashboard answers:

  • How does it differ for the internet, depending on the tracking type?
  • Which channels aid lead generation?
  • How are the leads distributed by regions?
  • How does the sales funnel look for this business?
General Results For A Project

The business was able to use the information on actual sales to:

  • Increase the conversion from sales applications by 10%.
  • Increase the return on marketing investments by 25%.
  • Increase the accuracy of the information that is used for promo campaign optimization by 50%.

What has been done to achieve that:

  • Tracking of all the stages of a sales funnel.
  • Tracking of the ROPO effect.
  • The ability to create promo campaigns that are optimized according to actual sales data.
  • In-depth user behavior analysis.
  • The analysis of managers’ work effectiveness.
  • The integration of an end-to-end analytics process.
The Conclusion

Here are some signs that indicate that your business might need a Data Warehouse:

  • Your customers don’t buy on a website.
  • It could take more than 90 days for them to make a purchase decision.
  • Big data volume.
  • A significant number of accounting and communication systems that your business uses.
  • A significant number of marketing communication types.

When you start building a Data Warehouse, you have to answer the main questions first: ‘What would be payback for creating a database? How would it occur?’. Profit growth is essential for businesses; therefore, you need to ensure that any actions with the database would either enable you to save budget or grow profit with the help of additional data usage.

It’s essential to change and improve business processes to create a Data Warehouse. It’s also crucial to involve either business owners or their proxies in the development process. Otherwise, there’s a chance that an end tool would be left useless in cloud service space.

When you create a Data Warehouse, you have to find a balance between the number of data used, the costs for its creation/maintenance, and its benefit for a business.