powerbi-campaign-roi-analyzer

# Power BI Campaign ROI Analyzer Skill > Skill by [ara.so](https://ara.so) — Marketing Skills collection. This skill enables AI agents to work with the Power BI Campaign ROI Analyzer, a comprehensive social media marketing analytics dashboard that transforms raw campaign data into actionable insights. The system tracks multi-channel performance, product sales attribution, demographic segmentation, and predictive trend scoring across Facebook, Instagram, LinkedIn, TikTok, and other platforms. ## What This Project Does The Campaign ROI Analyzer is a Power BI-based analytics platform that: - **Multi-Dimensional Campaign Analysis**: Tracks reach, engagement, conversion, and retention metrics with weighted business value scoring - **Product Sales Attribution**: Breaks down sales by social channel with quality metrics (AOV, repeat purchase rate, time to conversion) - **Demographic Segmentation**: Analyzes audience clusters by psychographics, device preference, engagement patterns, and sentiment - **Predictive Trend Scoring**: Assigns forward-looking scores to campaigns using historical patterns and seasonal variations - **Real-Time Collaboration**: Supports team annotations, customizable alerts, and multilingual reporting - **Unified Data Pipeline**: Normalizes data from social APIs, CRM systems, and e-commerce platforms into a single analytical model ## Installation ### Prerequisites - Power BI Desktop (latest version recommended) - Windows 10/11 or macOS (via browser for Power BI Service) - Minimum 8 GB RAM (16 GB+ for large datasets) - 500 MB storage for application files - Active internet connection for data source APIs ### Setup Steps 1. **Clone the Repository** ```bash git clone https://github.com/thanhtu150/powerbi-campaign-roi-analyzer.git cd powerbi-campaign-roi-analyzer ``` 2. **Open the Power BI File** ```bash # On Windows start "campaign-roi-analyzer.pbix" # On macOS (Power BI Service) open https://app.powerbi.com # Then upload the .pbix file ``` 3. **Configure Data Sources** Power BI will prompt for data source credentials. Configure each connector: ```powerquery // Example: Facebook/Instagram connector configuration let Source = Facebook.Graph("https://graph.facebook.com/v18.0/me/insights"), AccessToken = Environment.GetEnvironmentVariable("FACEBOOK_ACCESS_TOKEN"), Headers = [#"Authorization" = "Bearer " & AccessToken], Data = Json.Document(Web.Contents(Source, [Headers=Headers])) in Data ``` 4. **Set Environment Variables** Configure API credentials via environment variables (never hardcode): ```bash # Windows PowerShell $env:FACEBOOK_ACCESS_TOKEN="your_token_here" $env:LINKEDIN_CLIENT_ID="your_client_id" $env:LINKEDIN_CLIENT_SECRET="your_client_secret" $env:TIKTOK_API_KEY="your_api_key" $env:GOOGLE_ANALYTICS_PROPERTY_ID="your_property_id" $env:SHOPIFY_API_TOKEN="your_shopify_token" # Linux/macOS export FACEBOOK_ACCESS_TOKEN="your_token_here" export LINKEDIN_CLIENT_ID="your_client_id" export LINKEDIN_CLIENT_SECRET="your_client_secret" export TIKTOK_API_KEY="your_api_key" export GOOGLE_ANALYTICS_PROPERTY_ID="your_property_id" export SHOPIFY_API_TOKEN="your_shopify_token" ``` ## Data Model Structure The dashboard uses a star schema with the following key tables: ### Fact Tables - **FactCampaigns**: Campaign performance metrics (impressions, clicks, conversions, spend) - **FactSales**: Product sales transactions attributed to campaigns - **FactEngagement**: Detailed engagement events (likes, comments, shares, saves) ### Dimension Tables - **DimDate**: Time dimension with fiscal calendars and holidays - **DimCampaign**: Campaign metadata (name, platform, type, budget) - **DimProduct**: Product catalog with categories and pricing - **DimAudience**: Demographic and psychographic segments - **DimChannel**: Social media platform definitions ## Key DAX Measures ### 1. Campaign ROI Calculation ```dax Campaign ROI = VAR TotalRevenue = SUM(FactSales[Revenue]) VAR TotalSpend = SUM(FactCampaigns[AdSpend]) VAR ROI = DIVIDE(TotalRevenue - TotalSpend, TotalSpend, 0) RETURN ROI ``` ### 2. Weighted Engagement Rate ```dax Weighted Engagement Rate = VAR Likes = SUM(FactEngagement[Likes]) * 1 VAR Comments = SUM(FactEngagement[Comments]) * 2 VAR Shares = SUM(FactEngagement[Shares]) * 3 VAR Saves = SUM(FactEngagement[Saves]) * 2.5 VAR TotalImpressions = SUM(FactCampaigns[Impressions]) VAR WeightedEngagements = Likes + Comments + Shares + Saves RETURN DIVIDE(WeightedEngagements, TotalImpressions, 0) ``` ### 3. Cost Per Acquisition (CPA) ```dax CPA = VAR TotalSpend = SUM(FactCampaigns[AdSpend]) VAR TotalConversions = COUNTROWS(FILTER(FactSales, FactSales[IsFirstPurchase] = TRUE)) RETURN DIVIDE(TotalSpend, TotalConversions, 0) ``` ### 4. Channel Attribution Score ```dax Channel Attribution Score = VAR ChannelRevenue = SUM(FactSales[Revenue]) VAR ChannelSpend = SUM(FactCampaigns[AdSpend]) VAR AvgOrderValue = AVERAGE(FactSales[OrderValue]) VAR RepeatPurchaseRate = DIVIDE( COUNTROWS(FILTER(FactSales, FactSales[IsRepeatPurchase] = TRUE)), COUNTROWS(FactSales), 0 ) VAR Score = (DIVIDE(ChannelRevenue, ChannelSpend, 0) * 0.4) + (AvgOrderValue / 100 * 0.3) + (RepeatPurchaseRate * 100 * 0.3) RETURN Score ``` ### 5. Predictive Trend Score ```dax Trend Score = VAR CurrentPeriod = CALCULATE(SUM(FactEngagement[TotalEngagements]), DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -7, DAY)) VAR PreviousPeriod = CALCULATE(SUM(FactEngagement[TotalEngagements]), DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -14, DAY)) VAR TrendSlope = DIVIDE(CurrentPeriod - PreviousPeriod, PreviousPeriod, 0) VAR SeasonalFactor = SWITCH( TRUE(), MONTH(MAX(DimDate[Date])) IN {11, 12}, 1.2, // Holiday season MONTH(MAX(DimDate[Date])) IN {1, 2}, 0.8, // Post-holiday slump MONTH(MAX(DimDate[Date])) IN {7, 8}, 0.9, // Summer slowdown 1.0 ) VAR AdjustedScore = TrendSlope * SeasonalFactor * 100 RETURN AdjustedScore ``` ## Power Query Data Transformation ### Normalize Social Media Data ```powerquery let // Import raw data from multiple sources FacebookData = Facebook.Graph(Environment.GetEnvironmentVariable("FACEBOOK_PAGE_ID")), LinkedInData = LinkedIn.Campaigns(Environment.GetEnvironmentVariable("LINKEDIN_ACCOUNT_ID")), TikTokData = Json.Document(Web.Contents("https://open-api.tiktok.com/v1.3/", [Headers=[#"Access-Token"=Environment.GetEnvironmentVariable("TIKTOK_API_KEY")]])), // Standardize Facebook data NormalizedFacebook = Table.TransformColumns(FacebookData, { {"impressions", each _, Int64.Type}, {"reach", each _, Int64.Type}, {"clicks", each _, Int64.Type}, {"spend", each _, Currency.Type}, {"date_start", each Date.From(_), type date}, {"campaign_name", each Text.Proper(_), type text} }), AddedPlatformFacebook = Table.AddColumn(NormalizedFacebook, "Platform", each "Facebook", type text), // Standardize LinkedIn data NormalizedLinkedIn = Table.TransformColumns(LinkedInData, { {"impressions", each _, Int64.Type}, {"clicks", each _, Int64.Type}, {"costInLocalCurrency", each _, Currency.Type}, {"startDate", each Date.From(_), type date}, {"name", each Text.Proper(_), type text} }), RenamedLinkedIn = Table.RenameColumns(NormalizedLinkedIn, { {"costInLocalCurrency", "spend"}, {"startDate", "date_start"}, {"name", "campaign_name"} }), AddedPlatformLinkedIn = Table.AddColumn(RenamedLinkedIn, "Platform", each "LinkedIn", type text), // Combine all sources CombinedData = Table.Combine({AddedPlatformFacebook, AddedPlatformLinkedIn}), // Add derived metrics AddEngagementRate = Table.AddColumn(CombinedData, "EngagementRate", each if [impressions] > 0 then ([clicks] / [impressions]) else 0, type number), AddCPC = Table.AddColumn(AddEngagementRate, "CPC", each if [clicks] > 0 then ([spend] / [clicks]) else 0, Currency.Type), // Sort by date descending SortedData = Table.Sort(AddCPC, {{"date_start", Order.Descending}}) in SortedData ``` ### Calculate Demographic Segments ```powerquery let Source = FactEngagement, // Group by user demographics GroupedByDemo = Table.Group(Source, {"UserID", "AgeGroup", "Gender", "Location"}, { {"TotalEngagements", each List.Sum([Engagements]), type number}, {"AvgSessionDuration", each List.Average([SessionDuration]), type number}, {"PreferredContentType", each List.Mode([ContentType]), type text} }), // Calculate engagement intensity AddIntensityScore = Table.AddColumn(GroupedByDemo, "IntensityScore", each ([TotalEngagements] * 0.6) + ([AvgSessionDuration] / 60 * 0.4), type number), // Assign psychographic cluster AddCluster = Table.AddColumn(AddIntensityScore, "PsychographicCluster", each if [IntensityScore] >= 75 then "Power Users" else if [IntensityScore] >= 50 then "Active Engagers" else if [IntensityScore] >= 25 then "Casual Browsers" else "Passive Viewers", type text) in AddCluster ``` ## Common Dashboard Configurations ### 1. Set Custom Alert Thresholds Navigate to the dashboard settings and configure alerts using DAX: ```dax Engagement Alert = VAR CurrentRate = [Weighted Engagement Rate] VAR Threshold = 0.02 // 2% threshold VAR IsUnderperforming = CurrentRate < Threshold RETURN IF(IsUnderperforming, "⚠️ Engagement below 2%", "✓ Performance normal") ``` ### 2. Configure Time Granularity ```dax // Dynamic time grouping based on date range Time Grouping = VAR DateRange = DATEDIFF(MIN(DimDate[Date]), MAX(DimDate[Date]), DAY) VAR Grouping = SWITCH( TRUE(), DateRange <= 7, "Hour", DateRange <= 31, "Day", DateRange <= 90, "Week", "Month" ) RETURN Grouping ``` ### 3. Set Metric Weighting ```dax // Custom weighted performance score Custom Performance Score = VAR ReachWeight = 0.15 VAR EngagementWeight = 0.30 VAR ConversionWeight = 0.40 VAR RetentionWeight = 0.15 VAR NormalizedReach = DIVIDE([Total Reach], MAX(FactCampaigns[Reach]), 0) VAR NormalizedEngagement = DIVIDE([Weighted Engagement Rate], 0.10, 0) // Assume 10% is max VAR NormalizedConversion = DIVIDE([Conversion Rate], 0.05, 0) // Assume 5% is max VAR NormalizedRetention = DIVIDE([Retention Rate], 0.80, 0) // Assume 80% is max VAR Score = (NormalizedReach * ReachWeight) + (NormalizedEngagement * EngagementWeight) + (NormalizedConversion * ConversionWeight) + (NormalizedRetention * RetentionWeight) RETURN Score * 100 // Convert to 0-100 scale ``` ## Real-World Usage Patterns ### Pattern 1: Product Launch Analysis ```dax // Compare product performance across channels Product Channel Performance = SUMMARIZE( FactSales, DimProduct[ProductName], DimChannel[ChannelName], "Total Revenue", SUM(FactSales[Revenue]), "Units Sold", SUM(FactSales[Quantity]), "Avg Order Value", AVERAGE(FactSales[OrderValue]), "Conversion Rate", DIVIDE( COUNTROWS(FactSales), CALCULATE(SUM(FactCampaigns[Clicks]), USERELATIONSHIP(FactCampaigns[CampaignID], FactSales[CampaignID])), 0 ) ) ``` ### Pattern 2: Budget Optimization ```dax // Calculate optimal budget allocation based on ROI Optimal Budget Allocation = VAR TotalBudget = SUM(FactCampaigns[Budget]) VAR ChannelROI = [Campaign ROI] VAR TotalROI = CALCULATE([Campaign ROI], ALL(DimChannel)) VAR OptimalAllocation = DIVIDE(ChannelROI, TotalROI, 0) * TotalBudget RETURN OptimalAllocation ``` ### Pattern 3: Audience Segment Targeting ```dax // Identify high-value audience segments High Value Segments = CALCULATETABLE( SUMMARIZE( DimAudience, DimAudience[SegmentName], "Avg LTV", AVERAGE(FactSales[CustomerLifetimeValue]), "Purchase Frequency", COUNTROWS(FactSales) / DISTINCTCOUNT(FactSales[CustomerID]), "Engagement Score", [Weighted Engagement Rate] ), FILTER( DimAudience, [Avg LTV] > 500 && [Engagement Score] > 0.03 ) ) ``` ### Pattern 4: Campaign Fatigue Detection ```dax // Detect campaigns showing declining performance Campaign Fatigue Score = VAR DaysActive = DATEDIFF(MIN(FactCampaigns[StartDate]), MAX(DimDate[Date]), DAY) VAR InitialEngagement = CALCULATE([Weighted Engagement Rate], DATESINPERIOD(DimDate[Date], MIN(FactCampaigns[StartDate]), 7, DAY)) VAR CurrentEngagement = CALCULATE([Weighted Engagement Rate], DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -7, DAY)) VAR PerformanceDecline = DIVIDE(InitialEngagement - CurrentEngagement, InitialEngagement, 0) VAR FatigueScore = IF( DaysActive > 14 && PerformanceDecline > 0.20, "High Fatigue", IF( DaysActive > 7 && PerformanceDecline > 0.10, "Moderate Fatigue", "Normal" ) ) RETURN FatigueScore ``` ## Data Refresh Configuration ### Scheduled Refresh via Power BI Service 1. Publish the dashboard to Power BI Service 2. Configure data source credentials in the Power BI Service settings 3. Set refresh schedule: ```json { "refreshSchedule": {