Data Warehousing Options for E-commerce
One might say that digital data storage began with the simple excel spreadsheet. It was one of the first places we could see the data we had collected side by side and make insightful decisions.
As the data we gathered grew, so did our spreadsheets
But even being able to link various spreadsheets together, this wasn’t going to be a viable solution for long.
Then, in 2005, the concept of Big Data started to gain popularity and was defined as “a large set of data that is almost impossible to manage and process using traditional business intelligence tools”.
You only need to consider the amount of data that is collected and used on a daily basis to understand why humans aren’t able to successfully extract insights from big data. Everything from online payments, biometrics, medical statistics, supermarkets, schools, etc. require the collection of data.
It’s difficult to imagine an industry in today’s world that doesn’t rely on statistics in one way or another
It is estimated that 5 exabytes of information are created globally in just two days- that’s 5 billion gigabytes. E-commerce will gather the same statistics as physical shops, such as email marketing statistics, and pay per click, and social media analytics. But on top of that, company owners need to collect unique data for the industry, for example, conversion rates and shopping cart abandonment rate.
The question now is how to organize this data so that it can be of use to businesses.
What Is A Data Warehouse?
Essentially, it is what the name implies, a central location that allows you to store all of your data. This data, which can be received from multiple sources, can then be analyzed and used for extracting actionable insights.
A data warehouse can be on-site, or cloud-based
One of the key features of a data warehouse is the huge amount of data that can be stored and also for the granularity- the level of the detail of your data. It allows for consistent data analysis and reduces the likelihood of errors because of missing data.
Imagine a data warehouse as a funnel with a filter. Into the funnel goes your social media statistics, sales, stock, client information, marketing campaigns, data from your website, etc. The funnel and filter will separate your data through data exploration, analytics and reporting with the final aim of clean, high-quality data.
How is this different from a database?
While they are similar in that they store data, the use cases are different. A database is used to store data, usually from a single source and in real-time. The amount of data stored in a database is far smaller than that of a data warehouse. A. data warehouse is used to store data that is generated over a longer period of time.
As we have already mentioned, a data warehouse can receive data from numerous sources, even hundreds. They are designed to work on a significantly larger scale. Also, the data is updated differently. Unlike with a traditional database, the information is updated in its original form. The computing capabilities of a data warehouse are far superior to those of a database.
A data warehouse can be seen in two parts; the storage and retention of the massive volume data and the high granularity in the data warehouse, and compute, the sophisticated manner in which the complex data is processed and analyzed.
What Are the Benefits of a Data Warehouse?
The ultimate goal of a data warehouse is analyzing almost unlimited amounts of data in order to extract meaningful insights to make business decisions.
That being said, there are a great number of benefits that we will discuss now
- Control of your data. You have full data ownership as the data remains in your own storage. This is preferred over having to depend on data retention policies.
- Unified storage – regardless of where your data is coming from, it can be grouped together in one location, combined, and stored.
- Advanced hardware – data warehouses are built using robust hardware that is capable of complex queries in a matter of seconds.
- Less chance of errors – relying on computer analysis will not only be faster but also more accurate than the human eye, especially when considering the sheer amount of data.
- Automatically updated reports – with reporting and dashboard tools, reports can be automatically updated as new data is uploaded.
- Lowering maintenance with cloud-based solutions – cloud providers take care of server provisioning and backend maintenance, which means the user doesn’t need to.
- Cost-effective – the majority of the latest cloud data warehouses charge for the usage, rather than having to pay for storage that you won’t end up using.
- Better decision making – with precise insights from your data warehouse, the decisions you make will enhance your business, particularly if you include marketing analytic tools. The information you need will also be available quicker.
- Spotting new trends – as data warehouses take care of historical data, you will be able to see patterns over longer periods of time. Spotting trends and preparing for them ahead of time will enable you to stay ahead of your competition.
Each individual e-commerce business will have their own set of data parameters and will use its data in different ways to extract the insights that they need.
Nevertheless, the benefits are clear
In summary, the benefits of a data warehouse include faster, more accurate analysis of massive amounts of data that will lead company owners to make better decisions.
The information that is extracted can be used to consider trends and assist in the planning of the future of the company. Data warehouses require less maintenance and are cost-effective. All of this will result in higher profits and more opportunities to invest in your business and ultimately grow.
What Are the Potential Drawbacks of a Data Warehouse?
In order to gain a complete understanding of data warehouses, it is necessary to consider some of the potential downsides to data warehouses.
In many cases, the drawbacks will often depend on the size of the company
This is certainly true when looking at the cost/benefit ratio. Owning a data warehouse can be expensive as it is a large IT project. The cost of implementing and running a data warehouse might outweigh the benefits.
A data warehouse may also require more work from your employees
It may be that the data is simply copied over from existing databases. It could also mean that staff have to collect new data, taking up valuable time. Data ownership was listed as a benefit, but at the same time, it can be a drawback.
Data warehouses are frequently implemented as Software as a Service or cloud service
In these cases, the data ownership will very much depend on the service provider. Before choosing any service provider, it is essential that you are aware of the data ownership policies to prevent potential data leaks.
Choosing Between On-Site and Cloud Data Warehouses
Now that you have weighed up the advantages and potential disadvantages to having a data warehouse for your E-commerce business, it’s time to decide on whether you will gain more from running your data management on-site or use a cloud service provider. This decision can be made by looking at five key aspects; scalability, security, reliability, speed, and cost.
Scalability
It is clear that the amount of data we acquire is only going to continue to grow and because of this, it is important that your data warehouse is scalable. This means that you need to be able to increase your storage space as your data grows. Cloud solutions are far more scalable as there are practically no limits of space for storage.
Security
With an on-site data warehouse, you will benefit from heightening security because the data server room will be within your location and you are able to implement restrictions on who can access the data. At the same time, if you choose a leading cloud provider, you can guarantee you will have the latest security updates.
Reliability
As with security, the reliability of your data warehouse will depend on the service provider of the cloud or the professionals you use to manage your on-site data warehouse. This is a crucial area, if you are going to invest in a data warehouse, it must be reliable for you to be able to maximize its use.
Speed
On-site data warehouses may have the upper hand as there is no need to wait for delays in the system such as limited bandwidth or server capabilities.
Cost
There are fewer costs associated with cloud solutions as the company doesn’t have to pay the initial costs of servers and hardware.
How to Choose the Right Cloud Solution for Your Data Warehouse
With such an arsenal of modern digital technology, the choice of hardware and software is very much a personal choice. This is the same when deciding on the right cloud solution for your data warehouse. It is worth investigating your options to find one that will match the needs of your company. Here are 6 of the most popular cloud solutions for data warehouses:
Google BigQuery
Google BigQuery is ideal for those who want to use standard SQL queries. The data warehouse setup and resource positioning are taken care of by Google and it is simple to query data with SQL or Open Database Connectivity. You can connect with other databases and spreadsheets. One of the main advantages is the integration of BigQuery ML for machine learning.
Microsoft Azure SQL Data Warehouse
This is a good solution for small, medium, and large-sized companies. The last update in 2019 included Gen2 with extra features and above all, advanced security settings. With Dynamic Data Masking (DDM) there is a granular level of security. Users are able to run more than 100 concurrent queries simultaneously because of Microsoft’s parallel processing architecture.
Oracle Autonomous Data Warehouse
This is certainly an easy solution if you already use Oracle databases, the hardware systems (Oracle’s Exadata) were built for Oracle databases. The web-based notebook and reporting services allow for sharing data analysis and they are great for encouraging collaboration. You can mitigate data from other databases, clouds, and even on-site data stores.
Amazon Redshift
Amazon joined the data warehouse market with Amazon Redshift, the logical choice for companies who currently use AWS tools and deployment. You can save time and money by connecting with AWS 3. It is a high performing cloud solution with large parallel processing. By using AWS Glue, users can extract data from outside S3. There is also a solid level of security with AWS network isolation policies.
IBM Db2 Warehouse
This would be the preferred choice for companies that want an integrated in-memory database engine and Apache Spark analytics and is another high performing database. Insights can be extracted by using SQL and Spark queries. IBM’s Netezza technology offers superior data lookup capabilities.
Snowflake
As one of the youngest popular cloud solutions, Snowflake is perfect for those with a smaller budget. With its columnar database engine capacity, Snowflake can manage structured and semi-structured data like JSON and XML. Compute and storage can be scaled individually, and queries can be made through standard SQL. It is integrated with R and Python programming languages.
What is ELT?
ELT is a data interaction process that transfers a company’s raw data to a data warehouse. Essentially, it speeds up the time that data spends in transit.
There are three parts to the operation; Extract, Load, Transform
When data is extracted, it is identified and read from a number of sources including CRM, databases or files. The data is then loaded to the new location. Finally, the data is transformed from its original format into the format that is needed for analysis. This might include replacing code with values or modifying text strings, among others.
How your data is extracted, loaded, and transformed will have a direct impact on your data warehouse. Be sure to add this to your list when you are looking at the ideal solution for you. Although ELT is still a working process in terms of age and development, it is making data warehouses much faster and will be an integral part of their future.
A Summary of How to Design Your E-commerce Data Warehouse
The first thing that anybody should do is make sure their business plan and company objectives are up to date. With all of your data, you need to know what kind of insights you are looking for. For example, you want to know which of your marketing campaigns are the most effective so that you can create targeted marketing campaigns.
There has to be a purpose for your data warehouse
The next step is to decide on your storage location, whether that’s by using cloud solutions like we mentioned before or on-site servers such as Oracle or Microsoft SQL Server. From there, you can decide on your software. We have discussed storage and compute, the centralization software that handles how the data is collected and maintained.
You will also want to look into the visualization software that will determine how your analyzed data is presented
Finally, data warehouses do require a certain amount of technical skill and time. It is worth hiring experienced IT technicians to maintain your data warehouse and ensure that it operates smoothly. Cloud solutions are easy to manage but you might still need some professional guidance.
The Average Costs of an On-Site Data Warehouse
For the sake of comparison, we will look at an on-site data warehouse with a storage of 1TB and a further 0.5TB each year to compensate for growth. The storage options we have chosen shouldn’t need updating within 5-10 years.
The cost of equipment for the storage would be approximately $7,500 plus another $3000 for 4 CPU cores. A 24 port 10GB network will cost a minimum of $2000. Annual expenses will include the physical space that you need, an estimated $1000, $1,100 in electrical consumption and another $300 on cooling. Monthly costs will be in the form of technical staff and system admins, which could range from $3,000 to $5,600.
The Average Costs of a Cloud Data Warehouse
Again, we will look at 1TB of storage with 0.5TB extra per year for growth. The estimated costs are based on a yearly subscription, but you will often find longer subscriptions with discounts. Our detailed analysis will be based on prices from Amazon.
For the storage and Amazon DRS, it will cost $6,500. The other associated costs such as the network, space and electrical consumption are included in the subscription fee. You may need to hire a cloud administrator who will cost between $1,300 and $4,400.
Cost-effective Data Warehouses for small to medium-sized E-commerce Businesses
In the previous section, we saw some pretty large numbers that would not benefit smaller companies. However, many of the cloud-based data warehouses use ‘pay-as-you-use’ pricing models. Google BigQuery starts off at $0.02 per GB per month.
Use Cases of Data Warehouses in the E-Commerce Industry
EBay
Back in 2013, eBay began switching its data storage system over to a data warehouse. At the time, the online auction site had 100 million active users and in one week alone, the company had 500 million live auctions with 50,000 categories.
Each new visit to the online store led to more data that was getting impossible to handle. One of the systems was a Teradata enterprise data warehouse that held 7.5PB of data. This system was regularly accessed by over 50 executives as well as data scientists and non-technical business people.
Wish
In 2016, Wish was in the top global positions for most downloaded apps on iOS and Android with 2 million items sold daily. Its rapid growth caused a significant increase in data which required a data warehouse. The company chose to create its own data warehouse using Amazon Redshift and Google BigQuery and two years later they had a team of 30 staff dedicated to data analysis.
Walmart
Though technically not solely online, Walmart is one of the biggest retailers worldwide and in 2017, the company was looking at building the world’s biggest private cloud to process 2.5PB of data per hour, known as the Data Café. Their own data warehouse processed 200 billion rows of transactional data in just a few weeks. Not only this, but this information came from more than 200 sources.
LGR Telecommunications
This use case steps away from E-commerce but it’s an amazing example of just how powerful a data warehouse can be. LGR telecommunications owns a 310TB Oracle data warehouse. Queries can be made 24 hours a day, 365 days a week and the system is used daily by 2,500 people. Up to 13 billion records are added each day and everything runs in near-real-time.
In Conclusion
If you are sat in your living room and contemplating setting up an E-commerce site, don’t assume that data warehouses are only for the giants. The E-commerce industry is expected to grow to $5 trillion by 2021. It’s not just the convenience of being able to buy anything from anywhere with just the touch of a few buttons.
Thanks to Big Data, the customer experience is enhanced
We can appreciate real-time updates of new products being launched, exclusive offers and promotions. This personalized online shopping we experience is because companies are now able to gather immense amounts of data, store this data correctly and use complex computing to extract meaningful insights in order to make informed business decisions. And all of this occurs within data warehouses.
The size of your e-commerce site is probably irrelevant in the decision of whether to start a data warehouse
If your goal is to expand, you need precise information regarding your clients, your profit and loss, your stock, and your marketing campaigns. Even if your business is young, it is better to include a data warehouse in your budget now before the amount of data you begin to gather becomes uncontrollable.
The data warehouse you decide on will depend on many different factors. When considering your options, remember the 5 key aspects; scalability, reliability, security, speed, and cost. Once you have analyzed each area against your business needs, you will have a much better idea of the best data warehouse for your e-commerce business.
