Technical Task (TT) for the development of dashboards for the sales and marketing departments in Power BI
Project Goal
To develop interactive dashboards in Power BI for analyzing sales data, lead generation, and the effectiveness of advertising campaigns. Data will be loaded from:
• 1C: Small Business — to obtain data on sales, customers, and product stocks.
• Advertising accounts of Facebook and Google Ads — for analyzing marketing metrics (CTR, CPA, ROI, expenses, etc.).
1. Functional Requirements
1.1. Dashboard “Sales”
• Key Metrics:
• Total revenue.
• Number of sales.
• Average check.
• Margin (global and by products).
• Sales plan execution in %.
• Filters:
• By period (day, month, year).
• By regions/branches.
• By managers.
• By product categories and individual products.
• Details:
• Sales breakdown by categories.
• Sales dynamics by days/months.
• Number of returns.
• Top-10 products by profitability.
1.2. Dashboard “Lead Generation and Advertising”
• Key Metrics:
• Total number of leads.
• Cost per lead (CPL).
• Lead conversion to sales (%).
• Total budget and advertising expenses.
• ROI (return on investment in advertising).
• CTR, CPA, CPM.
• Filters:
• By advertising platforms (Facebook/Google Ads).
• By campaigns/ad groups.
• By audience segments.
• By regions.
• Details:
• Dynamics of expenses and number of leads by days/months.
• Comparison of campaign effectiveness.
• Traffic sources and lead distribution by regions.
2. Technical Requirements
2.1. Data Sources
1. 1C: Small Business:
• Data format: PostgreSQL/MySQL or export file (CSV, Excel, XML).
• Data for export:
• Sales table (date, client, product, amount, manager, region, margin).
• Product catalog (SKU, name, category, stock, cost).
• Customer information (name, region, registration date).
2. Advertising accounts of Facebook and Google Ads:
• Connection via API.
• Data for export:
• Advertising expenses, clicks, impressions, leads, conversions.
• Metrics (CTR, CPA, CPM, ROI).
2.2. Data Updates
• Automatic dashboard updates:
• 1C: daily data export.
• Facebook/Google Ads: updates via API every 6 hours.
2.3. Integration
• Configure Power BI to work with different sources:
• Via ODBC driver for the 1C database.
• Via API web requests for advertising accounts.
2.4. Dashboard Architecture
• Data model logic:
• Creation of fact tables (sales, expenses, leads).
• Connection between tables through unique identifiers (client ID, campaign ID).
• Optimization:
• Aggregation of large volumes of data for fast operation.
• Minimization of calculated fields in Power BI, moving calculations to the source level.
3. UI/UX Requirements
• Design:
• Simple and intuitive interface.
• Charts: bar charts, line graphs, pie charts, cards.
• Use of corporate colors.
• Functionality:
• Ability for deep data filtering.
• Interactivity: clicks on charts update related data.
• KPI panel on the main