This business has several features that significantly affect the ability to track the effectiveness of different marketing communications. Here are those features:
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.
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:
Have all the information about the company’s interaction with clients and sales stored in one place.
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.
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:
The omnichannel business model.
The significant time required to make a purchase decision.
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.
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.
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?
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:
Below you can see a sub-scheme for how different entities are connected in the database:
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.
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.
-- 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 result. Our database is complete, and we have all the descriptions of the work and processes required for the next stage — the implementation.
During this stage, a Data Warehouse is created:
- 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.
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.
During that stage, we start integrating Data WareHouse into the company’s business processes. Here’s what we do to achieve that:
The result. Our database is stable and integrated into the business.
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.
Data visualization example:
The questions that this dashboard answers:
The business was able to use the information on actual sales to:
What has been done to achieve that:
Here are some signs that indicate that your business might need a Data Warehouse:
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.