Business Intelligence for eCommerce
Company/Industry
Since I have worked with eCommerce industry as a business
user, I would like to analyze my company latestone.com and eCommerce industry
in general for the purpose of this blog.
Description
Latestone.com is an eCommerce website founded in 2013 by
Palred Technologies Limited. It specializes in the niche market of procuring,
designing and selling electronic accessories all over india. It fulfills around
3000 orders per day across india and has 2 warehouses 1 in delhi and 1 in Hyderabad
to store its inventory. Sourcing happens from various cities within India like
Mumbai, Delhi & Hyderabad and also from outside India, specifically China.
The technology that runs the website and backend admin panel was inhouse
developed by software team whereas database and ERP are purchased through a license.
Performance Metrics
Teams at latestone.com are divided by functionality like Procurement,
Operations (Shipping etc.,), Warehousing, Technology, Sales & Marketing,
HR, Content Management etc., So each team reports to either CEO, CTO, COO or CFO.
So ill pickup the metrics for two departments here: Marketing and Warehousing. Sales
& Marketing team basically has various avenues of marketing like Social
media ads – facebook and google, newspaper ads, radio ads etc.,
Sales and Marketing Metrics:
- No of unique visitors per day
- Visitors through different online ads
- Return on Investment through facebook vs google ads
- ROI for online vs offline advertising
- Bounce rate per day/hour
- What type of payment options is most common? By size of purchase? By socioeconomic level?
- Of multiple product orders is there any correlation between the purchases of any products? Can we make combo products out of this?
- Most searched/ordered/time spend products
- Since our last price schedule adjustment which products have improved and which have deteriorated?
- What are average order amounts from different kinds of marketing across days
- What are the top 5 most profitable products by product category and demographic location?
Warehousing metrics:
- No.of products received/picked/packed/invoiced/delivered per day
- Turnaround time for picking/packing/invoicing/printing
- Comparision of Proof of delivery time reported by different carriers
- Most sold/unsold products
- Inventory space/aisles movements per day
- What is the average time from ordering date to shipping date? Does this vary by product?
- Replenishment levels in different bins from bulk locations/pick locations
- Is there a change in delivery type at different times of the year, i.e. preceding major holidays?
- Re-ordering analysis/alerts for different products every day
- Do we have adequate inventory for a particular product to meet anticipate demand?
Dimensional modelling
For analyzing the above queries I wanted
to build a bus architecture 1st to get a clear understanding of
which dimensions are suitable for which business process/team/context.
Bus Matrix:
Business Process/ Dimensions
|
Date
|
Time
|
Product
|
Customer
|
Promotion
|
Vendor
|
Carrier
|
Payment mode
|
Employee
|
Sales
|
x
|
x
|
x
|
x
|
x
|
x
|
x
|
||
Advertising
|
x
|
x
|
x
|
x
|
x
|
x
|
|||
Inventory
|
x
|
x
|
x
|
x
|
x
|
x
|
x
|
||
Procurement
|
x
|
x
|
x
|
x
|
x
|
||||
Warehousing
|
x
|
x
|
x
|
x
|
x
|
||||
Shipping
|
x
|
x
|
x
|
x
|
x
|
x
|
x
|
||
Finance
|
x
|
x
|
x
|
x
|
x
|
x
|
|||
Technology
|
x
|
x
|
x
|
x
|
After the business processes are identified, forming the grain for each individual context should be focused. After the grain is identified, the facts and dimensions should be established for those processes.
Some of the dimensions will be as
shown in figures below:
Advertising dimension:
Customer Dimension:
So the fact table for Sales process will be something like:
So above model is a transaction type of model as it is based on individual order/transaction. In the eCommerce scenario, we can also have an accumulating and periodic snapshot as the CXO’s might be interested in knowing metrics every month or what is the status of something till date.
References:
No comments:
Post a Comment