Data Warehousing & Marketing Intelligence: Systems, Tools & Strategic Use

TAKEAWAYS:

• A data warehouse is a centralized repository that integrates structured and unstructured data from multiple sources to support business intelligence and CRM analytics.
• Marketing intelligence involves gathering, analyzing, and distributing actionable insights about competitors, customers, and market trends to drive strategic decisions.
• ETL (Extract, Transform, Load) processes clean and organize raw data before it is stored in a data warehouse for analysis.
• OLAP (Online Analytical Processing) enables multidimensional analysis of data, helping businesses identify patterns like customer churn or purchase trends.
• Predictive analytics uses historical data to forecast future customer behavior, enabling proactive CRM strategies.
• Data warehousing and marketing intelligence reduce costs, improve customer retention, and enhance personalized marketing efforts.

1. What is Data Warehousing?

A data warehouse (DW) is a subject-oriented, integrated, time-variant, and non-volatile collection of data designed to support decision-making and business intelligence (BI). Unlike transactional databases (OLTP), which focus on day-to-day operations, data warehouses are optimized for complex queries, reporting, and analytics.

Key Characteristics of a Data Warehouse

mindmap
  root((Data Warehouse))
    Characteristics
      Subject-Oriented["Focuses on specific business areas (e.g., customers, sales, marketing)"]
      Integrated["Combines data from multiple sources (ERP, CRM, social media, etc.)"]
      Time-Variant["Stores historical data for trend analysis (e.g., customer behavior over 5 years)"]
      Non-Volatile["Data is read-only; no updates or deletions"]
    Components
      Data Sources["ERP (e.g., SAP), CRM (e.g., Salesforce), Social Media, IoT Devices"]
      ETL Process["Extract, Transform, Load"]
      Data Storage["Relational databases, data lakes, or cloud storage"]
      Metadata["Describes data structure, origin, and usage"]
      OLAP["Online Analytical Processing for multidimensional analysis"]
      Front-End Tools["BI tools (Tableau, Power BI), Reporting Tools (SQL Server Reporting Services)"]

Why is Data Warehousing Important for CRM?

• Centralized Customer Data: Combines data from eSewa transactions, Daraz purchase histories, and Ncell customer service logs into a single view.
• Historical Analysis: Tracks customer lifetime value (CLV) over time (e.g., how often a Khalti user makes transactions).
• Personalized Marketing: Enables segmentation (e.g., identifying high-value NEPSE investors vs. casual traders).
• Predictive Insights: Uses machine learning to forecast churn (e.g., Pathao drivers who may leave if incentives drop).

2. Components of a Data Warehouse

A data warehouse consists of five core components:

3. How Data Warehousing Works: The ETL Process

ETL (Extract, Transform, Load) is the backbone of data warehousing. It ensures data is clean, consistent, and ready for analysis.

flowchart TD
    A["Data Sources<br/>(e.g., ERP, CRM, Social Media)"] --> B["Extract"]
    B --> C["Transform<br/>(Clean, Deduplicate, Standardize)"]
    C --> D["Load<br/>(Into Data Warehouse)"]
    D --> E["Data Warehouse<br/>(Centralized Storage)"]
    E --> F["OLAP & BI Tools<br/>(Analysis, Reporting)"]
    F --> G["Business Decisions<br/>(e.g., Targeted Marketing Campaigns)"]

Step-by-Step ETL Example: NEPSE Stock Analysis

1. Extract:
   • Pull daily stock prices from NEPSE’s API.
   • Pull investor profiles from a brokerage CRM (e.g., CIBIL Nepal).
2. Transform:
   • Clean missing/inconsistent data (e.g., incorrect investor IDs).
   • Standardize formats (e.g., convert all dates to YYYY-MM-DD).
   • Calculate moving averages for trend analysis.
3. Load:
   • Store in a data warehouse with schema:

     CREATE TABLE investor_trends (
         investor_id INT,
         stock_symbol VARCHAR(10),
         purchase_date DATE,
         amount DECIMAL(10,2),
         moving_avg_30day DECIMAL(10,2)
     );
     

4. Analyze:
   • Use OLAP to find: "Which investor segment (retail vs. institutional) buys more during bull markets?"

4. Online Analytical Processing (OLAP) for CRM

OLAP allows multidimensional analysis of data (e.g., slicing customer data by region, purchase history, and demographics).

OLAP Operations

5. Marketing Intelligence System (MIS)

A Marketing Intelligence System (MIS) collects, analyzes, and distributes actionable insights about:

• Customers (behavior, preferences).
• Competitors (strategies, market share).
• Market Trends (economic indicators, social media sentiment).

Sources of Marketing Intelligence

How MIS Supports CRM

• Competitor Benchmarking:
  • Compare Khalti’s transaction fees vs. eSewa’s to adjust pricing.
• Customer Sentiment Analysis:
  • Use NLP on Twitter to detect complaints about NTC’s internet speeds and proactively improve service.
• Predictive Modeling:
  • Forecast Daraz’s Black Friday sales using past data to optimize inventory.

6. Data Warehousing vs. Traditional Databases

7. Real-World Applications in Nepal

Case Study 1: Daraz’s Data Warehouse for Personalized Recommendations

• Problem: Daraz wanted to reduce cart abandonment and increase repeat purchases.
• Solution:
  • Built a data warehouse integrating:
    • Customer browsing history (from Daraz website).
    • Purchase data (from past orders).
    • Third-party data (e.g., weather for seasonal product promotions).
  • Used OLAP to segment customers (e.g., "frequent buyers of electronics").
  • Implemented predictive analytics to recommend products like:

    "Customers who bought a smartphone also bought a screen protector (80% chance)."

• Result:
  • 25% increase in repeat purchases.
  • 15% reduction in cart abandonment via targeted discounts.

Case Study 2: Ncell’s Marketing Intelligence for Churn Prediction

• Problem: Ncell was losing 10% of customers annually due to competitor promotions (e.g., NTC’s bundled offers).
• Solution:
  • Developed an MIS to analyze:
    • Call drop rates (technical churn driver).
    • Usage patterns (e.g., customers using less data after promotions).
    • Social media sentiment (complaints about billing).
  • Used predictive models to identify high-risk customers (e.g., those who reduced data usage by 30%).
  • Proactive retention:
    • Offered personalized discounts to at-risk users.
    • Sent SMS alerts about new features (e.g., "Unlimited calling in your area").
• Result:
  • Reduced churn by 20% in 6 months.
  • Increased customer lifetime value (CLV) by 15%.

Case Study 3: eSewa’s Fraud Detection Using Data Warehousing

• Problem: eSewa faced fraudulent transactions, costing millions annually.
• Solution:
  • Integrated transaction logs, user behavior data, and device fingerprints into a data warehouse.
  • Used OLAP to detect anomalies like:
    • "A single IP address making 50 transactions in 1 hour."
    • "A user suddenly transferring large amounts to unknown wallets."
  • Implemented real-time alerts for suspicious activity.
• Result:
  • Detected 40% more fraud cases than before.
  • Reduced false positives by 30% using machine learning.

8. Challenges of Data Warehousing in CRM

9. Tools for Data Warehousing & Marketing Intelligence

10. Exam Tip: How to Score Full Marks

For Short Questions (e.g., "Enlist components of a data warehouse")

• Structure: Use bullet points or a table (as shown above).
• Depth: Mention 2-3 key details per component (e.g., for ETL: "Extracts raw data from multiple sources like ERP and CRM").
• Example: Always tie to a Nepali company (e.g., "Ncell uses ETL to combine call logs with customer surveys").

For Long Questions (e.g., "Discuss steps in data warehousing")

1. Define the concept clearly (e.g., "Data warehousing is a process of collecting, integrating, and storing data...").
2. Use a flowchart (like the ETL diagram above) to visually explain steps.
3. Give a real-world trace:

   "For example, when Daraz builds a data warehouse, it first extracts data from its website, supplier databases, and payment gateways (eSewa/Khalti). It then transforms this data by removing duplicates and standardizing formats (e.g., converting all dates to ISO format). Finally, it loads the cleaned data into a centralized repository where OLAP tools can analyze trends like ‘customers in Pokhara buy more electronics in monsoon.’"

4. Link to CRM: Always explain how this helps CRM (e.g., "This enables personalized recommendations, reducing churn by 25% as seen in Daraz’s case study").

For Application-Based Questions (e.g., "How does marketing intelligence help NTC?")

• Step 1: Identify the problem (e.g., "NTC faces high customer complaints about network drops").
• Step 2: Explain the MIS solution (e.g., "NTC can use social media listening tools to detect spikes in complaints during peak hours").
• Step 3: Describe the outcome (e.g., "This data helps NTC proactively upgrade towers in high-complaint areas, improving NPS scores").
• Step 4: Use metrics (e.g., "Reduced complaints by 30% in 6 months").

Common Mistakes to Avoid

• ❌ Vague definitions: Instead of "Data warehousing stores data," say "A subject-oriented, integrated, time-variant repository optimized for analytical queries."
• ❌ Ignoring Nepali context: Always relate to eSewa, Daraz, Ncell, etc..
• ❌ Skipping diagrams: Draw a flowchart or table for processes (e.g., ETL, OLAP operations).
• ❌ Overcomplicating: Focus on CRM-relevant applications (e.g., customer retention, churn prediction).