Overview

Understanding Data Storage Architecture and Usage

This section aims to provide a comprehensive understanding of the architecture of the data storage system within our databases and its usage across various products.
Our data storage and retrieval system utilizes Databricks and Data Lake/ Data Warehouse.

Databricks
Databricks offers a comprehensive set of tools that make it easier to bring data from different sources together to one platform. With this, we can organize the data, store it securely, share it, analyse it to find insights, and create models to predict future trends.
Databricks operates within a Spark/Hive environment. In the Spark/Hive environment, Spark is often used for data processing and analytics tasks that require fast, in-memory computation. On the other hand, Hive serves as a data warehouse solution for managing structured data and executing SQL queries on large datasets stored in distributed storage systems. Together, they form a robust ecosystem for processing, querying, and analysing big data.

Data Lake/ Data Warehouse
A data lake is a large and centralized repository that stores vast amounts of structured, semi-structured, and unstructured data in its raw form. Data lake allows data to be stored as-is, without any prior formatting or organization. This flexibility enables organizations to store diverse types of data from various sources.

1. Storing Existing Data Components
   Our data storage system is structured in a STAR Schema Layout, comprising facts, dimensions, and summaries. Additionally, we operate within a Multi-Tenant Environment. In the Multi-Tenant Environment, multiple users or entities (tenants) share the same infrastructure or platform. However, within this shared environment, each tenant's data is kept separate and isolated from others at both the storage and SQL level. Each tenant has its own designated storage space and SQL processing capabilities, ensuring that their data remains private and secure from other tenants' data. This isolation helps maintain data integrity, confidentiality, and security within the shared environment.

Star Schema Layout
In a star schema, there are typically two types of tables: fact tables and dimension tables. For example, A customer buys a product at a particular store. Here, sales transaction represents the fact and attributes related to this sale, such as product and customer information represent the dimension. So a single facts table is linked to multiple dimensions tables.

• Fact Tables:
  These tables contain quantitative data, typically numerical values that represent the measurements or metrics of a business process. Sales Transactions Fact Table in the current example (of a customer buying a product) will have columns such as transaction ID, product ID, customer ID, sale amount, sale date, etc. Also, each row represents a single sale transaction, with references to the associated product and customer.
• Dimension Tables:
  Dimension tables contain descriptive attributes or characteristics of the data in the fact table. These attributes provide context for analysing the data in the fact table. In the current example, Dimension tables would contain the following details:

2. Utilization of Data
   The data stored in the data warehouse is then utilized for various purposes. The data utilization process involves issuing SQL queries to interact with the stored data, processing these queries within a data warehousing environment, and creating tables useful for further analysis.

Given below are a few examples where the extracted data is utilized.

• Creating Reports
  The data is utilized while creating reports for the brands. Reports are primarily comprised of charts. Two major components are used while creating charts. They are Key Performance Indicators (KPIs) and Dimensions.
  • KPIs are the numerical measurements or metrics used to evaluate the performance or success of a particular aspect of a business. They are created using DSL/JSON, then converted to SQL.
  • Dimensions are like labels or categories that help organize data. They act as filters, letting us group data based on different categories, like time periods or types of products.
    Example - You can analyse sales data for a particular zone. So sales information will be the KPI and store information will be the dimension. This analysis will give you insights to how the brand is performing in a particular zone. Also, you can add more dimensions like product type and date, to get a more comprehensive business analysis.
• Audience Grouping
  Using Audience Manager, you can create different audience groups based on specific criteria. Creating audience groups helps target the right customer at the right time, making campaigns more effective. You can create and manage groups separately based on information like behaviour, interests, demographic details, transactions, and more. For instance, you can get high spending customers that did not visit your stores for the last three months. Example: You can see the product performance (based on product category) for an audience group (high-value customers) for a specific time duration e.g.. January 2023 and January 2024. This will give a comparison of the audience behaviour with respect to time.
• Exporting
  We can export the datasets in raw format. While exporting, it does not group data by dimensions, but instead provides raw data for additional visualizations.
  You can export the data using Export Templates. These templates consist of a list of queries that prohibit fact-to-fact joins. Fact-to-fact joining refers to the process of combining or merging two sets of data that are both at the same level of detail and represent different measures or facts. It involves joining two fact tables together directly, without any dimensions acting as intermediary layers. This type of joining is often avoided in data modelling and analysis because it can lead to redundant or inaccurate results and can be computationally exhaustive. Instead, data is typically joined through dimension tables, which act as intermediary layers between fact tables and help to maintain data integrity and accuracy. So the export templates generate SQL queries that retrieve data from Databricks and export it. This feature offers flexibility, allowing users to specify conditions such as date range and frequency. Example: Various types of data, such as customer information, transaction details, coupon usage, loyalty points, footfall data (the number of people visiting a physical location), and information related to marketing campaigns, can be exported. Once exported, this data can be analysed and processed by users or analysts at their end.

3. Addition of New Data
   Suppose, you want to introduce a new operational model, such as badges. Badges are like virtual stickers given to customers in loyalty programs to reward them for doing certain things or reaching goals. They make the program more interesting and give customers a sense of pride and achievement, encouraging them to keep participating and stay engaged.
   So when badges model is introduced, a new table is generated following the star schema structure. This table incorporates new facts and dimensions relevant to the badges data. Once the onboarding process (i.e, data ingestion) is done, we can leverage this new dataset to create Key Performance Indicators (KPIs) for analysis and monitoring purposes. This process ensures that the newly acquired data is seamlessly integrated into our existing data architecture, enabling us to derive valuable insights from it.

Example of Badges Facts and Dimensions Tables:
For Badges, there are 3 facts tables linked to 5 dimension tables.
Badges Facts Tables are:
Badges Earn
Badges Issued and
Badges Earned Benefits

E.g. Badges_earn Facts Table Screenshot (sample)

Dimension Tables that are linked to Badges Facts Table are:
Badge meta
Badges owner type
Coupon series
Users
Date
E.g. Badge_meta Dimension Table Screenshot (sample)

Points Fact Table

---

Customer Point Summary

Points redemption summary

Points expiry reminder info

Return bill line item

Concept till

Slab change log

Customer summary

Coupons

Payment Mode Details

Customer Merge Log

Call Task Customer Status

Messages

Contact Info

Response Info

Members

Badges

Introduction to Badges Facts and Dimension Tables

Badges are a form of recognition or achievement typically used in various systems or platforms to acknowledge users' accomplishments or participation in certain activities. They serve as visual indicators of accomplishment and can be earned through completing tasks, reaching milestones, or meeting specific criteria set by the system administrators. Badges can enhance user engagement, motivate users to participate more actively, and foster a sense of accomplishment and community within the platform.

According to the Star Schema Layout, for badges, there are three fact tables:
badges_earn, badges_issue, and badges_earned_benefits.
Each of these tables contains information about the earning, issuance, and associated benefits of badges, respectively. Additionally, there are several dimension tables providing context and additional details for the badges data.

Here's a brief description of the fact tables:

• badges_earn: This table records the earning of badges by customers. It includes information such as the badge ID, customer ID, earning date, expiry date, and whether the badge is active or revoked.
• badges_issue: This table logs the issuance of badges to customers. It contains details such as the badge ID, customer ID, issuance date, expiry date, and whether the badge is active or revoked.
• badges_earned_benefits: This table associates benefits with badges earned by customers. It includes information about the benefit type, coupon code, description, points earned, and validity of the benefit.

Linking these fact tables with dimension tables allows analysts to perform more comprehensive analyses and derive insights. Below are the ways in which these linkages can prove beneficial with respect to the following use case:
Awarding Activity-Based Badge for Purchases. For example a customer earns an activity based badge by making a purchase. The objective is to incentivize customer engagement and increase sales by rewarding users with activity-based badges upon making purchases, utilizing the Badges Facts and Dimension Tables.

• Understanding Badge Attributes: Dimension tables like badgemeta and coupon_series provide additional attributes related to badges and benefits. By linking these tables with the fact tables, analysts can enrich their understanding of the badges being earned or issued and the associated benefits.
  Example - The badge is configured in the system to be awarded to customers who meet the specified criteria (eg. a purchase of more or equal to Rs.1000). A customer makes a purchase.
  The purchase transaction details, including the customer ID, purchase date, and purchase amount, are recorded in the badges_earn fact table. This facts table is linked to badgemeta and users dimension table giving access to the information contained in these tables.
• Customer Analysis: Linking the dim_customer_id in fact tables with the users dimension table allows analysts to analyse the badge earning and issuance patterns for specific customers. This helps in understanding customer behaviour, preferences, and engagement levels.
  Example - The customer meets the criteria (e.g., customer has purchased for Rs. 1100), and earns the badge. The badges_earn table is linked to the `dim_customer_id’ and ‘users’ dimension table, where the analysts can get more information related to the customer and his behaviour pattern.
• Temporal Analysis: The date dimension table facilitates temporal analysis by providing various date-related attributes.
  Example - When a customer earns a badge, the badge is issued and the customer gets the associated benefit. All these actions are recorded in the facts tables which are connected to the date dimension table. Analysts can link these attributes with the corresponding date IDs in the fact tables to analyse badge earning and issuance trends over time, such as monthly or quarterly trends.
• Benefit Utilization: Analysts can examine the utilization of benefits associated with earned badges by linking dim_coupon_series_id with the coupon_series dimension table.
  Example - The customer has received some coupons as the benefit from the earned badge. The benefits facts table is linked to the dim_coupon_series_id with the coupon_series dimension table. This helps in creating a relationship between the benefits accrued by customers, and the specific characteristics or attributes of the coupon series itself. This linkage facilitates tracking and analysis of how benefits are associated with different coupon series, providing insights into the effectiveness and performance of various promotional campaigns or incentive programs.

Overall, by establishing these linkages between fact and dimension tables, analysts can perform various analyses such as customer segmentation, trend analysis, program effectiveness assessment, and optimization of reward strategies, leading to informed decision-making and improved user engagement within the platform.

Badges - Earn (badges_earn)

Badges - Issue (badges_issue)

Badges - Earned benefit (badges_earned_benefits)

Streaks

Rewards