SQL
Complex queries, JOINs, CTEs, window functions, aggregations, data marts and data validation.
Aleksandr
Kochmarik
Data & BI Analyst
I work with data to find patterns, explain changes in metrics and support business decisions. I use SQL, Python and BI tools for analysis, automation and reporting.
Considering in-house roles
Moscow · On-site, hybrid or remote
SQL Python Excel Statistics Power BI Apache Airflow
Skills and tools
The tools I have used and the tasks I used them for. Follow the links to read the related case studies.
Python [pandas, numpy] / Excel
Data processing and transformation, exploratory analysis, recurring calculations and API integrations.
Data analysis / business metrics
Sales, customers, funnels, trends, conversion, retention and customer segments. Investigating changes and patterns in data.
Ad-hoc analysis / reporting
Ad-hoc requests, recurring reports, hypothesis testing, identifying anomalies and preparing business insights.
Power BI / DAX / DataLens
Data models, metric calculations, interactive dashboards and visual communication of findings.
DWH / Datamarts / Apache Airflow
Warehouses, data marts, Stage / ODS / Core layers and DAGs for recurring data loads.
Case studies
01 / BI & DATA
Building an analytics system
Data ingestion and processing, DWH, data marts, a semantic model and Power BI reports
The project involved a second outsourced analyst. An external developer implemented the 1C API changes based on my specifications
Project details — Building an analytics system
I initiated and presented the analytics project to management. I implemented key stages: DWH modelling, DAGs, processing layers and data marts, a metrics model and Power BI dashboards
Before
Data was stored in several systems: 1C, CRM, advertising platforms and Google Sheets. Managers manually exported and combined data in spreadsheets to prepare reports. There was no unified data structure or automated reporting
After
A unified analytics system with a central data warehouse was created. Data from 1C, CRM and other sources is automatically loaded, processed and checked, then used to build data marts and a Power BI model. Reports refresh automatically and are used by different departments
Context
Sales were analysed through several 1C reports: data was exported, transferred to Google Sheets and reconciled manually.
Marketing data was fragmented. Leads entered Bitrix24, managers entered them separately into 1C, and CRM, advertising and 1C data were combined in Excel on request. Reports took time to prepare, and new analytical cuts required more manual work
Objective
Build a unified analytics system for marketing, sales and management: connect sources, establish end-to-end customer journey analytics, organise data processing and provide teams with ready-to-use BI reports.
My contribution
- Proposed an implementation plan and presented it to management and the operations team
- Prepared the warehouse and initial Airflow DAGs to extract part of the source data
- Specified 1C API changes for customer, sales, event and reference data
- Built sequential processing through Stage, ODS and Core and created analytical data marts
- Worked with the second analyst on the data model: configured relationships between fact and dimension tables and created DAX measures
- Developed dashboards in Power BI and Yandex DataLens
Project results
- Marketing performance and customer journey dashboard
- A customer-events dashboard covering scheduled and attended appointments, calls and conversions
- A financial-performance dashboard for management
- A centralised data warehouse replaced repeated manual reconciliation of exports and supports analysis and ad-hoc queries.
How the data workflow is organised
Incremental API ingestion, DWH processing and BI reporting
Orchestrated with Airflow DAGs
Data pipeline
- API
1C, CRM, Bitrix24 and advertising systems
- Stage
Initial data ingestion
- ODS
Operational processing layer
- Core
Integrated data
- Data marts
Business subject areas
- Semantic model
Relationships and business logic
- BI reporting
Power BI · Yandex DataLens
Reporting across key business areas
Data marts and reports tailored to each department
-
Marketing
Leads, sources, CPL and conversions
-
Service operations
Lead handling, meeting scheduling and manager performance
-
Customer events
Meetings, calls and sales
-
Financial metrics
Consolidated management reports
Explore the data model and table relationships
Table and field names are generalised: the diagram shows the main relationships, not the full schema.
The data journey
Sources
CRM, 1C ERP and advertising systems used by the business
APIs / Airflow
API-based ingestion and workflow orchestration with DAGs
Stage
The initial layer of ingested data
ODS
The operational data-processing layer
Core
The central layer of linked data for analysis
Data marts
Prepared datasets for analytical questions and reports
Model / DAX
Fact and dimension relationships and metric calculations
Power BI
Dashboards for the customer funnel, customer events and financial performance
02
Customer and sales data analysis
Sales trends, RFM analysis and conversion across the customer funnel
Analysed sales, the customer funnel, repeat purchases, cohorts and RFM segments.
Context
Regular reporting and business questions led to analytical tasks: why revenue changes, which customers return, where enquiries drop out of the funnel, how branch performance changes, which channels attract more valuable customers and how purchasing behaviour differs across segments.
Objective
Answer business questions using data: prepare datasets, calculate metrics, compare segments and periods, investigate changes and turn the findings into clear business insights.
My contribution
- Analysed sales trends, revenue, buyer counts, average order value and sales mix across different dimensions
- Compared branches, regions, periods and customer segments
- Built and analysed funnels from first enquiry to booking, attendance or purchase, calculating stage conversions
- Examined repeat purchases, order frequency, time between purchases and customer lifecycles
- Used cohort analysis to compare customer groups
- Performed RFM segmentation to identify active, loyal, new and lapsing customers
- Calculated and analysed LTV and customer segment value
- Investigated website behaviour and its relationship to enquiries and purchases
Example tasks
Fashion retail
Analysed transactions and purchasing behaviour: repeat purchases, order frequency, average order value and customer lifecycles. Performed RFM and cohort analysis, calculated LTV and compared acquisition channels.
Customer segmentation informed further communication and retention. Work with customer segments helped increase the share of repeat purchases by approximately 20%.
Medical clinic
Analysed the patient funnel, calculated conversions and investigated drop-offs.
Worked with repeat visits, average transaction value, cohorts and LTV. Compared acquisition channels and customer metrics for marketing and service teams.
Logistics company
Analysed orders, seasonality, geography and demand mix. Examined delivery times, vehicle utilisation, deliveries per vehicle and downtime.
Used findings for recurring BI reporting and identifying operational deviations.
03
Marketing reporting automation
Daily lead monitoring and weekly summaries in Bitrix24
Built a bot and introduced it into marketing workflows to deliver daily lead monitoring and weekly metrics for marketing operations meetings.
Context
The marketing manager and team needed regular performance metrics in their everyday work channel: Bitrix24.
Objective
Automate preparing and sending reports to the marketing chat in Bitrix24.
Implementation
- Built a Python bot to send analytical messages to the marketing chat in Bitrix24.
- Set up daily monitoring of new leads by source and branch.
- Automated detailed weekly metric calculations for marketing operations meetings.
- Introduced the solution into regular marketing workflows.
How it works
The daily summary reports new lead counts and their distribution by source and city.
Decibel 10:00
Decibel The weekly summary compares the previous two complete weeks: leads, booking and purchase conversion, and CPL based on allocated spend. It highlights branches that need attention and includes detailed Excel calculations and methodology.
Decibel 09:00
Project results
- The marketing team receives daily lead counts and trends in its work chat.
- The manager receives regular metric calculations for the weekly operations meeting.
04
AI assistant for marketing task creation
An AI bot that clarifies marketing requests and creates tasks in Bitrix24
Built a Python bot that clarifies employee requests for the marketing team, suggests an assignee and creates a Bitrix24 task after confirmation. Tested and introduced it into marketing workflows.
Context
Employees needed a clear way to submit requests to marketing. The assistant helped clarify the work required, the expected result and the appropriate assignee.
Objective
Create a workflow from an employee’s initial message to a Bitrix24 task, with user confirmation before task creation.
My contribution
- Built the bot’s server-side logic in Python.
- Set up a server database for logs, conversations and marketing team information.
- Connected a hosted AI agent through an API to the service deployed on Timeweb infrastructure.
- Separated predefined workflows from AI-generated responses.
- Optimised token usage and response time.
- Tested the solution within the department and across the company, then independently introduced it into marketing workflows.
How it works
An employee sends a request to the bot in Bitrix24. The bot asks follow-up questions, drafts a task based on company and marketing team rules, suggests an assignee and creates the task once the user confirms.
The bot application and database are hosted on a server.
The AI is accessed through API requests.
Marketing assistant
Employee
Hi! Please assign someone in marketing a task to find an outdoor advertising location.
Decibel
Hello! Could you clarify:
1. Which city or branch is the advertising for?
2. Do you have a preferred advertising format?
3. Is there a set budget?
4. When should the campaign start?
5. Do you have any location preferences?
Employee
Moscow, transit advertising. The budget is RUB 20,000. We want to start tomorrow on routes near the branch.
Decibel
I suggest assigning this to the outdoor advertising specialist. Task: find routes near the branch, request prices and check whether a launch tomorrow is possible within RUB 20,000. If not, suggest the earliest available date. Do you confirm?
Employee
Yes, I confirm.
Decibel
Done! The task has been assigned to Natalia, the outdoor advertising specialist.
Task created
- Objective
- Find transit advertising in Moscow
- Assignee
- Outdoor advertising specialist
- Result
- Route options, prices and available launch dates. Budget: up to RUB 20,000.
- Deadline
- Tomorrow: check launch availability
Project results
- A working bot-based intake flow with requirements clarification and user confirmation.
- Adoption in the marketing workflow after testing.
05
Advertising campaign analysis
Marketing analytics for a monthly budget of over RUB 4 million
Analysed Yandex Direct campaign performance with monthly budgets exceeding RUB 4 million.
Objective
Use advertising platform and Yandex Metrica data to evaluate performance and recommend campaign improvements.
My contribution
- Managed and optimised Yandex Direct campaigns.
- Analysed marketing metrics and user behaviour: leads, conversions, acquisition costs and average time to the target action.
- Used analytical findings to guide campaign optimisation.
How it works
Combined advertising statistics with lead data and subsequent customer funnel stages.
Made analytics part of ongoing campaign management, rather than a separate end-of-period report.
Project results
- Data-informed campaign management with a monthly budget of over RUB 4 million.
- Campaign optimisation reduced CPL from RUB 3,458 to RUB 1,238.
BI / DEMO Dashboard BI report demo · synthetic data Open dashboard Close dashboard
BI report demonstration
April–June 2026 · all branches
Lead monitoring
Leads 16,673
Revenue RUB 121,377,840
Customers with a booking 9,806
Customers with a meeting 6,629
Leads 16,673 100%
Customers with a booking 9,806 58.8%
Customers with a meeting 6,629 39.8%
Buyers 2,065 12.4%
Conversion from all leads By source
- Outdoor advertising 1,389
- Radio 1,019
- Partner network 5,036
- Yandex Direct 3,182
- VK Ads 3,824
- TV 2,223
| By source | Leads | Customers with a booking | Customers with a meeting | Buyers | Revenue | Acquisition spend | Lead-to-purchase rate | Blended CPL |
|---|---|---|---|---|---|---|---|---|
| Outdoor advertising | 1,389 | 874 | 593 | 191 | RUB 11,424,764 | RUB 240,623 | 13.8% | RUB 173 |
| Radio | 1,019 | 640 | 432 | 134 | RUB 8,126,726 | RUB 176,458 | 13.2% | RUB 173 |
| Partner network | 5,036 | 3,527 | 2,394 | 875 | RUB 49,780,457 | RUB 356,997 | 17.4% | RUB 71 |
| Yandex Direct | 3,182 | 1,717 | 1,160 | 324 | RUB 18,918,457 | RUB 5,218,292 | 10.2% | RUB 1,640 |
| VK Ads | 3,824 | 1,641 | 1,097 | 239 | RUB 14,838,661 | RUB 4,731,026 | 6.3% | RUB 1,237 |
| TV | 2,223 | 1,407 | 953 | 302 | RUB 18,288,775 | RUB 384,997 | 13.6% | RUB 173 |
| Total | 16,673 | 9,806 | 6,629 | 2,065 | RUB 121,377,840 | RUB 11,108,393 | 12.4% | RUB 666 |
By branch
- Moscow 3,542
- Saint Petersburg 4,093
- Yekaterinburg 4,382
- Novosibirsk 2,793
- Vladivostok 1,863
| By branch | Leads | Customers with a booking | Customers with a meeting | Buyers | Revenue | Acquisition spend | Lead-to-purchase rate | Blended CPL |
|---|---|---|---|---|---|---|---|---|
| Moscow | 3,542 | 2,118 | 1,586 | 501 | RUB 28,039,557 | RUB 2,347,070 | 14.1% | RUB 663 |
| Saint Petersburg | 4,093 | 2,400 | 1,633 | 512 | RUB 31,282,242 | RUB 2,744,462 | 12.5% | RUB 671 |
| Yekaterinburg | 4,382 | 2,567 | 1,327 | 410 | RUB 21,620,944 | RUB 2,919,318 | 9.4% | RUB 666 |
| Novosibirsk | 2,793 | 1,631 | 1,247 | 384 | RUB 24,214,343 | RUB 1,858,526 | 13.7% | RUB 665 |
| Vladivostok | 1,863 | 1,090 | 836 | 258 | RUB 16,220,754 | RUB 1,239,017 | 13.8% | RUB 665 |
| Total | 16,673 | 9,806 | 6,629 | 2,065 | RUB 121,377,840 | RUB 11,108,393 | 12.4% | RUB 666 |
Leads by status
- Not booked 6,867
- Booked, not attended 3,177
- Attended, no purchase 4,564
- Buyer 2,065
Lead trends
4,916
April 2026 6,667
May 2026 5,090
June 2026 Age distribution
16,673
- Under 25 6.9%
- 25–44 12.8%
- 45–60 21.4%
- 61–70 21.6%
- 71–80 25.5%
- 81+ 11.8%
Gender distribution
16,673
- Female 61.5%
- Male 38.5%
Yandex Direct
Impressions 1,799,771
Clicks 75,377
Spend RUB 5,218,292
Leads 3,182
CPC, RUB RUB 69
CTR 4.2%
CPL, RUB RUB 1,640
Lead conversion 4.2%
Leads by campaign
- Brand campaign 540
- Yandex Advertising Network 1,626
- Retargeting 378
- Direct Search 638
| By campaign | Impressions | Clicks | Spend | Leads | CPC, RUB | CTR | CPL, RUB | Lead conversion |
|---|---|---|---|---|---|---|---|---|
| Brand campaign | 295,859 | 12,831 | RUB 1,043,660 | 540 | RUB 81 | 4.3% | RUB 1,933 | 4.2% |
| Yandex Advertising Network | 907,658 | 37,068 | RUB 2,191,681 | 1,626 | RUB 59 | 4.1% | RUB 1,348 | 4.4% |
| Retargeting | 239,527 | 10,004 | RUB 782,743 | 378 | RUB 78 | 4.2% | RUB 2,071 | 3.8% |
| Direct Search | 356,727 | 15,474 | RUB 1,200,208 | 638 | RUB 78 | 4.3% | RUB 1,881 | 4.1% |
| Total | 1,799,771 | 75,377 | RUB 5,218,292 | 3,182 | RUB 69 | 4.2% | RUB 1,640 | 4.2% |
Spend by branch
- Moscow RUB 1,107,539
- Saint Petersburg RUB 1,312,294
- Yekaterinburg RUB 1,348,014
- Novosibirsk RUB 870,267
- Vladivostok RUB 580,178
| By branch | Impressions | Clicks | Spend | Leads | CPC, RUB | CTR | CPL, RUB | Lead conversion |
|---|---|---|---|---|---|---|---|---|
| Moscow | 396,529 | 16,571 | RUB 1,107,539 | 670 | RUB 67 | 4.2% | RUB 1,653 | 4% |
| Saint Petersburg | 451,038 | 18,731 | RUB 1,312,294 | 795 | RUB 70 | 4.2% | RUB 1,651 | 4.2% |
| Yekaterinburg | 452,133 | 18,883 | RUB 1,348,014 | 830 | RUB 71 | 4.2% | RUB 1,624 | 4.4% |
| Novosibirsk | 291,951 | 12,368 | RUB 870,267 | 532 | RUB 70 | 4.2% | RUB 1,636 | 4.3% |
| Vladivostok | 208,120 | 8,824 | RUB 580,178 | 355 | RUB 66 | 4.2% | RUB 1,634 | 4% |
| Total | 1,799,771 | 75,377 | RUB 5,218,292 | 3,182 | RUB 69 | 4.2% | RUB 1,640 | 4.2% |
Lead forecast and target achievement · Yandex Direct
As of · Days remaining: 13
Leads by month-end 2,879
Leads at snapshot date 640
Expected additional leads 2,239
Last month’s leads 1,234
- Budget for selected dimensions
- RUB 4,000,000
- Spend at snapshot date
- RUB 930,000
- Remaining budget
- RUB 3,070,000
- Last month’s CPL
- RUB 1,371
- Required daily spend
- RUB 236,154
- Versus last month
- 133.3%
Target / reference, leads 2,500
Achieved at snapshot 25.6%
Forecast achievement 115.1%
Meetings
Scheduled meetings 11,524
Completed meetings 7,378
Meetings with a sale 2,187
Scheduled → held 64%
Held → sale 29.6%
Scheduled → sale 19%
Scheduled meetings · by manager
- Evgeny 1,218
- Sergey 1,274
- Tatiana 1,391
- Victoria 1,431
- Nadezhda 1,474
- Viktor 1,545
- Alexander 1,916
- Ksenia 1,275
| By manager | Scheduled meetings | Completed meetings | Meetings with a sale | Scheduled → held | Held → sale | Scheduled → sale |
|---|---|---|---|---|---|---|
| Evgeny | 1,218 | 865 | 263 | 71% | 30.4% | 21.6% |
| Sergey | 1,274 | 903 | 270 | 70.9% | 29.9% | 21.2% |
| Tatiana | 1,391 | 894 | 269 | 64.3% | 30.1% | 19.3% |
| Victoria | 1,431 | 926 | 276 | 64.7% | 29.8% | 19.3% |
| Nadezhda | 1,474 | 723 | 214 | 49.1% | 29.6% | 14.5% |
| Viktor | 1,545 | 752 | 219 | 48.7% | 29.1% | 14.2% |
| Alexander | 1,916 | 1,388 | 407 | 72.4% | 29.3% | 21.2% |
| Ksenia | 1,275 | 927 | 269 | 72.7% | 29% | 21.1% |
| Total | 11,524 | 7,378 | 2,187 | 64% | 29.6% | 19% |
Scheduled meetings · by branch
- Moscow 2,492
- Saint Petersburg 2,822
- Yekaterinburg 3,019
- Novosibirsk 1,916
- Vladivostok 1,275
| By branch | Scheduled meetings | Completed meetings | Meetings with a sale | Scheduled → held | Held → sale | Scheduled → sale |
|---|---|---|---|---|---|---|
| Moscow | 2,492 | 1,768 | 533 | 70.9% | 30.1% | 21.4% |
| Saint Petersburg | 2,822 | 1,820 | 545 | 64.5% | 29.9% | 19.3% |
| Yekaterinburg | 3,019 | 1,475 | 433 | 48.9% | 29.4% | 14.3% |
| Novosibirsk | 1,916 | 1,388 | 407 | 72.4% | 29.3% | 21.2% |
| Vladivostok | 1,275 | 927 | 269 | 72.7% | 29% | 21.1% |
| Total | 11,524 | 7,378 | 2,187 | 64% | 29.6% | 19% |
Trends
Leads · customers · Blended CPL
RUB 678
April 2026 RUB 553
May 2026 RUB 804
June 2026 | Months | Value |
|---|---|
| April 2026 | RUB 678 |
| May 2026 | RUB 553 |
| June 2026 | RUB 804 |
Work experience
Core employment, project roles and experience in marketing.
Some periods overlap because projects ran in parallel.
Jul 2024 — present Core role
Healthcare services network
Data Analyst
A corporate BI environment connecting source systems and a data warehouse to marketing, sales and customer funnel reporting.
Responsibilities and scope
- Initiated the BI project and contributed to a shared analytics environment for teams and management.
- Worked on Apache Airflow ETL processes, API integrations with CRM, 1C, advertising and telephony systems, warehouse layers and data marts.
- Wrote SQL queries for customer, marketing and sales analysis: the first 30 days after enquiry, branch comparisons and conversion from enquiry to purchase.
- Built Power BI and DataLens reports for funnels, channels, branches and customer segments; analysed revenue, CAC, CPA, CPL, ROMI and LTV.
- Automated recurring summaries and contributed to the rollout of an AI assistant for task creation.
Jun 2024 — Jun 2026 Project role
Customer analytics project
Data Analyst
Customer segmentation, repeat purchases and marketing channel performance.
Responsibilities and scope
- Analysed sales, customers and advertising campaigns; segmented the customer base by purchasing behaviour.
- Performed RFM and cohort analysis, investigating repeat purchases and the customer lifecycle.
- Calculated LTV, CAC, CPA, CPL and ROMI; assessed marketing channels and website user behaviour.
- Prepared analytical reports and recommendations based on the findings.
Jan 2024 — Mar 2025 Project role
Transport and logistics company
Data Analyst
Orders, marketing and operational performance: delivery times, vehicle utilisation and demand patterns.
Responsibilities and scope
- Retrieved and processed ERP, CRM and advertising data through APIs.
- Analysed customer orders, seasonality, geography and demand patterns.
- Calculated and visualised average delivery time, vehicle utilisation and deliveries per vehicle.
- Analysed marketing spend, acquisition channels and web data; built DataLens dashboards.
- Supported automation pilots and prepared analysis for leadership and operational managers.
Apr 2023 — May 2025 Project role
Medical centre
Data Analyst
The patient journey from initial enquiry to booking and visit, with reporting for management and customer service.
Responsibilities and scope
- Analysed customers, enquiries, bookings and visits; calculated conversion between funnel stages.
- Performed cohort analysis, calculated segment LTV and assessed patient acquisition channels.
- Developed management BI reports and monitoring of customer and operational metrics.
- Prepared findings and recommendations for marketing and customer service.
Sep 2023 — May 2024 Marketing role
Packaging and consumables company
Digital Marketer
Advertising campaigns, analytical dashboards and automation of recurring marketing tasks.
Responsibilities and scope
- Launched and managed Yandex Direct campaigns and analysed their performance.
- Collected marketing data and built DataLens dashboards.
- Administered the company website using HTML, CSS, JavaScript and Bitrix CMS.
- Developed and introduced tools to automate routine marketing operations.
Jan 2023 — Sep 2023 Early career
Digital agency
Paid Traffic Manager
Hands-on advertising experience provided business context for my subsequent move into data analytics.
Responsibilities and scope
- Set up and managed campaigns in Yandex Direct and VK Ads.
- Built DataLens reports and analysed campaign performance.
- Prepared advertising optimisation recommendations, communicated with clients and maintained reporting.
Jan 2024 — Jul 2026 Additional project
Own business analytics and automation project
Co-founder / Data Analyst
Additional experience in gathering requirements and designing analytical solutions.
Responsibilities and scope
- Identified requirements and expectations, analysed business processes and developed solution concepts.
- Designed architecture and implemented ETL, warehouses, layers and marts; wrote SQL and Python scripts and developed BI reports.
- Prepared demonstrations and presentations; agreed on scope, timelines and collaboration.
A little about me
I want to join a team at a company with well-established processes, contribute to shared goals, exchange knowledge with colleagues and grow as an analyst.
I organise my own work: setting priorities, planning tasks and following them through to completion.
I learn quickly and am ready to invest time in new tools and approaches. I enjoy analytics: finding connections in data, understanding why metrics change and answering business questions.
Professional development
Completed courses
SQL, Python, data visualisation, statistics and marketing analytics.
2025
-
Interactive SQL course
SQL Academy
-
Python for data analysis
Yandex Practicum
-
Mathematics for data analysis
Yandex Practicum
2024
-
SQL simulator
karpov.courses
-
DataLens: data analysis and visualisation
Yandex Practicum
2022
-
Marketing analyst
Skillfactory
-
Introduction to Data Science and machine learning
stepik.org
-
Statistics fundamentals
stepik.org
Contact
I am considering in-house Data Analyst and BI Analyst roles. Contact me about an open position or to request my CV.
Moscow · On-site, hybrid or remote