Beginner’s Guide to E.T.L (Extract, Transform and Load) – Introduction

Introduction into E.T.L (Extract, Transform and Load)
This a process related to data warehousing which involves the extracting of data out of the source system/Systems
and placing it into a repository or Target.

Extraction
Extracting the data from source systems (Flat Files or other operational systems) and converted into one consolidated data warehouse format which is ready for transformation.

Transformation
Transforming the data may involve the following tasks:

  • Cleaning: One of many very important task in the transforming stage because the Source data would always have data that the target system doesn’t support or understand hence cleaning is required.
    In some cases the Source can be from many source inputs so Lookup are important to avoid duplication.
  • Filtering: Now the Source Data would have so many rows but it’s important to send relevant data to your target and  filter out the unnecessary data.
  • Business Rules: Calculations or Derivations can be performed Here so we can have Correct and readable data at the target.

and many more.

Loading

After proper transformation and data matches the Business Rules loading the data into a target or repository is the final step in the E.T.L (Extract, Transform and Load)

in my next blog we will look into the basic Loading Data from Source to Target

Thanks
Sohail Izebhijie

Business Intelligence in E-commerce Sector

Business Intelligence in E-commerce Sector

Electronic Commerce or e-commerce is the trade of products and services by means of the Internet or other computer networks. E-commerce provides customers with a platform to search product information through global markets with a wider range of choices,which makes comparison and evaluation easier and more efficient.Managers can make right decision only if they get precise information in a required form in right time. To this goal,recent development of information systems is oriented to business intelligence. Business Intelligence is rapidly becoming a major source to achieve competitive advantage and often aims to support  better business decision-making, among others, in the sphere of e-commerce (online shopping).

 

The Paradigm Shift

As a small-to-medium online store owner, the resources are finite, which means that time and burn rate are critical factors to success. Without knowing which marketing activities are working, it will be waste of both time and money.

The online marketing space is in constant shift as new technologies, services, and marketing tactics gain popularity. In order for these business owners to survive and thrive, they need to be able to make better decisions faster.This is where Business Analytics and Intelligence comes into play.

 

Data in E-commerce Systems

Also, an E-commerce system produces a huge amount of data collection. Data has potentially great value. Data must be accepted, processed and appropriately presented.

This involves hundreds of reports to make sense of them.This is the main prerequisite for successful management. To adopt a right decision, managers have to get correct information in right time.

Data in the unstructured format (data from multiple sources) can be processed by the use of BI systems and then valuable information can be obtained for the purpose like decision-making. This helps the organization to find new customer segments,  analyze trends or uncover new business opportunities, etc and opportunities can be sector specific.

Business Intelligence aims to support better business decision-making and also can be helpful in process of retaining existing customers and acquiring new customers.

 

Business Intelligence

Business Intelligence(BI) is a technology-driven process for analyzing data and presenting actionable information to help corporate executives, business managers and other end users make more informed business decisions.BI encompasses a variety of tools, applications and methodologies that enable organizations to collect data from internal systems and external sources, prepare it for analysis, develop and run queries against the data, and create reports, dashboards and data visualizations to make the analytical results available to corporate decision makers as well as operational workers. BI analysis supports both strategic and tactical decision-making processes.

 BI system in ecommerce diagram

The advantages enjoyed by market leaders and made possible by Business Intelligence include the high responsiveness of the company which are :

  • Understanding and Recognition of customer needs
  • Ability to act on market changes and
  • New ways to generate revenues
  • Optimization of operations and Increasing operational efficiency
  • Cost-effectiveness
  • Improving decision making and Quality analysis

 

Intelligence Decision Support System in E-commerce

Almost all requisite data for the decision making supporting e-commerce comes from CRM and ERP systems.

E-commerce companies have recently started to capture data on the Social Interaction between consumers in their websites, with the potential objective of understanding and leveraging social influence in customers’ purchase decision making to improve customer relationship management and increase sales.

Business intelligence systems are able to provide the managers quite a number of statistics dealing with customers and their environment. As important customer statistics can be considered, for example, matching sales revenues with site visitor activity, by week and month, in total and by product line, matching weekly and monthly sales with site visitor activity overtime (Trend Analysis), in total and by product line.

 

Following are list of Key Performance Indicators (KPIs) for E-commerce Industry :

 Marketing Key Performance Indicators: 

  • Website Traffic
  • Unique visitors versus returning visitors
  • Time spent on site
  • Page views per visit
  • Referral sources
  • Location of traffic
  • Day part monitoring (when site visitors come)
  • Newsletter subscribers
  • Texting subscribers
  • Chat sessions initiated
  • Social Media followers
  • Pay-per-click traffic volume
  • Blog traffic
  • Number and quality of product reviews
  • Brand or display advertising click-through rates
  • Trial downloads

 Sales Key Performance Indicators:

  • Hourly, daily, weekly, monthly, quarterly, and annual sales
  • Average order size
  • Average margin
  • Conversion rate
  • Shopping cart abandonment rate
  • New customer orders versus returning customer sales
  • Cost of goods sold
  • Total available market relative to a retailer’s share of market
  • Product affinity (which products are purchased together)
  • Product relationship (which products are viewed consecutively)
  • Inventory levels
  • Product pricing

Customer Service Key Performance Indicators:

  • Customer service email count
  • Customer service phone call count
  • Customer service chat count
  • Average resolution time
  • Concern classification

 

Continue reading