We are a performance agency. We work with:
- Meta Ads
- Google Ads
- SMM
- e-commerce and lead generation
- SEO
We need to build a unified management and client reporting system for each project.
We already have:
- Google Sheets (operational tables with DRR, plan-fact)
- Data from Meta Ads and Google Ads
- Partially - custom analytics (API)
- KPI PM tied to financial results and client retention
We need a specialist who:
- Will design the architecture of Power BI
- Will connect data sources
- Will set up automatic updates
- Will create a client dashboard template
- Will create a management dashboard for the leadership
- Will provide documentation and system logic
2. Data sources (at the start)
Advertising and analytical:
- Meta Ads
- Google Ads
- Google analytics
- Google search console
- Client CRM
Tabular:
- Google Sheets (operational analytics)
Data updates - automatic (every 3 hours).
3. Metrics that should be calculated
Financial:
- Revenue
- Ad Spend
- COGS
- Margin
- DRR
- ROAS
- ROMI
- AOV
- CAC
Management:
- Turnover plan
- Fact
- % of execution
- Monthly forecast
- 7-day trends
- Period comparisons (MoM, WoW, DoD)
Assortment (for e-commerce):
- ROI by SKU
- ROI by placement
- ROI by channel
4. What needs to be implemented
A. Data architecture
- Unified data model
- Correct normalization of tables
- Calculated fields through DAX
- Scalability (ability to add new projects without restructuring)
B. Client dashboard
Each client should receive:
1. Main screen:
- Revenue
- DRR
- ROAS
- Spend
- Orders
- AOV
- Plan / fact
- Dynamics chart
2. Channel analytics:
- Meta vs Google
- Placement
- Conversions
- Cost per conversion
3. Product analytics:
- SKU effectiveness
- Margin
- DRR by product
4. Period comparisons:
- Day to day
- Week to week
- Month to month
- Day to day last year
C. Management dashboard (for leadership)
- All projects in one place
- DRR for each project
- ROMI for each project
- Plan execution
- Risk indicator (if DRR is above normal)
- PM rating
- Retention rate
5. Automation
- Automatic data updates
- Minimum manual intervention
- Ability to add a new client through a template
6. Expected result
At the output, we want to receive:
- Ready Power BI file (.pbix)
- Configured connections to sources
- Documentation:
- model structure
- calculation logic
- metric descriptions
- Video instruction (screencast)
- Recommendations for scaling
7. Requirements for the performer:
- Experience working with advertising data
- Understanding of performance marketing
- Experience in building management reporting specifically for digital agencies
8. Collaboration format
- Structure design
- Implementation
- Testing on 1-2 projects
- Scaling to all projects
9. Please indicate in the feedback
- Examples of dashboards (screenshots)
- Experience with similar projects
- Estimated architecture of the solution
- Implementation timelines
- Cost
