Understanding the Dataset and Data Model
In this lesson, we’ll explore the complete dataset used for analyzing the 2019 credit card marketing campaign. We’ll go through the tables, the key metrics and DAX formulas created, and how all the data is related. By the end of this lesson, you'll be able to understand how data is structured and how it can be used for deeper analysis in Power BI.
1. Table: cost_type – Marketing & Cost Data
This table contains all cost-related data, especially related to marketing activities, broken down by type and quarter.
Important Columns & Measures:
-
CostPerType_Q: Cost per type per quarter.
-
Marketing_Cost: Total marketing expenses.
DAX: SUM(cost_type[Marketing Cost]) -
MCAC (Marketing Cost per Acquired Customer): Efficiency of marketing spend.
DAX: DIVIDE([Marketing_Cost], [New_Customers]) -
Profit: Revenue minus cost.
DAX: [Total Revenue] - [Total Cost] -
Total Cost: Sum of all costs.
DAX: SUM(cost_type[CostPerType_Q]) -
Quarter and Year: Time tracking.
-
Type: Type of cost (e.g., Marketing, Operational).
2. Table: Finance_all – Revenue & Transaction Data
This table holds all customer transactions and revenue information.
Key Columns & Measures:
-
Actual Revenue: Total revenue.
DAX: SUM(Finance_all[Revenue]) -
CLV (Customer Lifetime Value): Value of customer over time.
DAX: Revenue * Months_on_book -
First_Quarter: The quarter when the customer joined.
DAX: CALCULATE(MIN(Date), ALLEXCEPT(Finance_all, CLIENTNUM)) -
New_Customers: Customers acquired in the first quarter.
DAX: DISTINCTCOUNT where Date = First_Quarter -
RevenuePerCustomer: Average revenue per customer.
DAX: DIVIDE(SUM(Revenue), DISTINCTCOUNT(CLIENTNUM)) -
Trans_Amount, Quarter, Year, Date: Help in time-based analysis.
3. Table: info_all – Customer Demographics & Behavior
This table includes customer details such as age, income, card type, and churn information.
Key Columns & Measures:
-
Age_Group: Categorizes millennials.
DAX: IF(Age between 23-39, "Millennials", "Others") -
Churn Rate: Rate of customers who left.
DAX: DIVIDE(Churned Customers, Customer Count) -
Churned Customers:
DAX: COUNT where Attrition_Flag = "Attrited Customer" -
Customer Count:
DAX: DISTINCTCOUNT(CLIENTNUM) -
Tenure_Bucket: Categorizes based on how long customers stayed.
Example:-
0–6 Months
-
7–12 Months
-
13–24 Months
DAX: SWITCH using Months_on_book
-
-
Utilization_Bucket: Grouped by how much credit is used.
Example:-
0–20%
-
21–40%
-
Up to 100%
DAX: SWITCH using Avg_Utilization_Ratio
-
-
Other columns: Gender, Income, Education, Marital Status, etc.
4. Table: Revenue Target – Goals & Variance
This table tracks revenue targets, actual achievements, and customer-specific goals.
Important Metrics:
-
Revenue_Variance: Difference between actual and target.
DAX: Actual Revenue - Target Revenue -
Target Achievement %:
DAX: DIVIDE(Actual Revenue, Target Revenue) -
Target Revenue: Total goal per period.
DAX: SUM(Target Revenue) -
Includes columns like
Card_Category,CLIENTNUM,Date,Quarter,Type,Year.
Data Model: How the Tables Are Connected
Understanding relationships between tables is critical for accurate reporting and insights.
|
Table 1 |
Column |
Table 2 |
Column |
Cardinality |
Active |
Cross Filter |
Security Filter |
|---|---|---|---|---|---|---|---|
|
Finance_all |
CLIENTNUM |
Info_all |
CLIENTNUM |
Many to One |
Yes |
Both |
Yes |
|
Cost_type |
YEAR |
Finance_all |
YEAR |
Many to Many |
Yes |
Both |
No |
|
Finance_all |
Date |
Revenue_target |
Date |
Many to Many |
Yes |
Both |
No |
|
Revenue_target |
CLIENTNUM |
Info_all |
CLIENTNUM |
Many to Many |
No |
Both |
No |
|
Finance_all |
YEAR |
Info_all |
YEAR |
Many to Many |
Yes |
Both |
No |
Summary: Why This Matters
-
These tables and relationships allow us to analyze marketing effectiveness, customer behavior, and revenue generation across different customer segments.
-
Using Power BI and DAX, we can create interactive dashboards to make data-driven decisions.
-
The structure helps track performance, identify churn risks, and optimize campaigns for better ROI.
