Looker Studio Data Processing Specialist, BigQuery

Task: Preparation of Technical Specification for Creating a Dashboard on Consultants

🎯 Goal

Prepare a technical specification (TS) based on the general description, to verify understanding of data structure, relationships, and logic of building the dashboard.

🧾 Expected Result

1. A document in free form (Google Doc or PDF) containing:

   • A list of blocks/visualizations with required fields, calculation logic, and data sources

   • Description of filters (dates, consultants, etc.)

Description for the executor

🖼 Visual example

This task includes an example of dashboard visualization demonstrating the desired placement of blocks in Looker Studio (tables, charts, filters). (attached file)

📶 List of blocks and fields:
⚠️ Field names must be in Russian

📦 Orders

• Date

• Deal

• Service - data substitution from reference tables

• Sailor - data substitution from reference tables

• Consultant - data substitution from reference tables

• Purchase Price - DealLog_Cost

• Status

• Date of status change

💳 Transactions

• Transaction date - combine date and time, if not available, use Transactions_TimeRecord

• Amount

• Currency

• Payment type

• Amount in USD

• Consultant

📥 Purchases

* summaries for each consultant from DealLog

• Consultant - unique names of all consultants

• Service

• Purchases - total amount of all purchases across all services *DealLog_Cost

• Margin - difference between DealLog_Price_Plan and DealLog_Cost in numeric value

• Margin in %

💼 Balance

• Consultant

• Paid - total amount of all payments from Transactions_ExpSubcategory

• Ordered services - total amount of all ordered services from DealLog

• Balance of ordered services - difference between orders and payments

• Services provided - total amount of services delivered (status Completed)

• Balance of services provided- difference between services delivered and payments

📊 Expenses by consultants

• Pie chart
  *Displays the share of expenses (purchases) for each consultant in percentage terms

🔽  Filters (by dates and consultants)

📚 Basic information about data structure

1. Data stored in BigQuery:

• DealLog — deals

• Transactions — financial inflows

• Sailor, Service, Consultant and others — reference tables

• Data has ID relationships and requires "decoding" through corresponding tables

2. Examples of field decoding:

• Sailor
  → In DealLog there is a field DealLog_Sailor_ID
  → Find the corresponding record in the Sailor table and form:
  "id_name" (e.g., 490_Petro Drobnytskyi)

• Service
  → Field DealLog_Service_ID
  → Link to Service table, from which we take the name

• Consultant
  → In DealLog there is no direct ID of the consultant
  → Algorithm:

  1. Find DealLog_Service_ID

  2. Go to Service table

  3. Take Service_Consultant (ID)

  4. Using this ID, find the full name in the Consultant table

  5. Form: "id_name"

🔁 After approval of the TS — a separate task for implementation will be issued.