Practice: Social Media Analytics
Scenario: You're designing a data model for Facebook to analyze user engagement, content performance, and advertising effectiveness.
Requirements:
- Track daily and monthly active users (DAU/MAU)
- Analyze post engagement (likes, comments, shares) by content type
- Monitor user growth and churn rates
- Measure ad performance (impressions, clicks, conversions)
- Analyze user behavior across different devices
Task: Create a high-level dimensional model to support these requirements.
Identify core business processes. What are the main events that generate the data needed for these analyses? Consider user actions and ad interactions.
Think about different types of fact tables. Given the variety of metrics needed, would a single fact table suffice, or do you need different types of fact tables?
Consider the granularity of your fact tables. What level of detail do you need to store to support all required analyses? Think about the trade-offs between granularity and table size.
Identify dimensions. What contextual information is needed to analyze these events? Consider the "who, what, where, when" of each process.
Think about slowly changing dimensions. Which entities might have attributes that change over time, and how should these changes be tracked?
Here's a high-level dimensional model for the social media analytics platform:
Fact Tables:
- User_Activity_Fact
- Post_Engagement_Fact
- Ad_Performance_Fact
Dimension Tables:
- Date_Dim
- User_Dim
- Content_Dim
- Ad_Dim
- Device_Dim
Fact Table Schemas:
User_Activity_Fact ( date_key (FK), user_key (FK), device_key (FK), login_count, time_spent ) Post_Engagement_Fact ( date_key (FK), user_key (FK), content_key (FK), device_key (FK), like_count, comment_count, share_count ) Ad_Performance_Fact ( date_key (FK), ad_key (FK), user_key (FK), device_key (FK), impression_count, click_count, conversion_count )
Explanation
Core Business Processes:
- We've identified three main processes: user activity (logins), post engagement, and ad interactions. Each of these corresponds to a fact table in our model.
- Granularity:
- User_Activity_Fact: Daily grain per user and device. This supports DAU/MAU calculations and device-specific analysis.
- Post_Engagement_Fact: Individual engagement event grain. This allows for detailed engagement analysis.
- Ad_Performance_Fact: Individual ad interaction grain. This provides detailed ad performance data.
- Dimensions:
- Date_Dim: Supports time-based analysis.
- User_Dim: Contains user attributes for segmentation and demographic analysis.
- Content_Dim: Stores information about posts, including content type.
- Ad_Dim: Contains information about ads.
- Device_Dim: Stores device information for cross-device analysis.
- Facts:
- User_Activity_Fact: login_count and time_spent for user engagement metrics.
- Post_Engagement_Fact: like_count, comment_count, and share_count for detailed engagement analysis.
- Ad_Performance_Fact: impression_count, click_count, and conversion_count for ad effectiveness metrics.
- Slowly Changing Dimensions:
- User_Dim could be a Type 2 SCD to track changes in user profiles over time.
- Content_Dim might be Type 1 or Type 2 depending on whether historical content changes need to be tracked.
- Additional Considerations:
- DAU/MAU can be calculated from User_Activity_Fact.
- User growth and churn can be derived from User_Activity_Fact by analyzing first and last login dates.
- The model supports analysis across different devices via the Device_Dim.
- Ad performance metrics are comprehensive, allowing for detailed ROI analysis.
This model supports all the required analyses:
- DAU/MAU can be calculated from User_Activity_Fact.
- Post engagement can be analyzed using Post_Engagement_Fact joined with Content_Dim.
- User growth and churn can be derived from User_Activity_Fact.
- Ad performance is captured in Ad_Performance_Fact.
- Cross-device behavior can be analyzed using the Device_Dim in all fact tables.
The model balances detail (supporting granular analysis) with performance considerations. It's also flexible enough to accommodate future requirements, such as new types of content or ad formats.