Tetyana Hordiichuk
Private message
Offer work
Kyiv,
Ukraine
1 year back
available for hire
age 36 years
on the service 1 year
Rating
Successful projects
Average rating
Language proficiency level
English: upper-intermediate
Skills and abilities
Programming
Services
Portfolio
-
27 USD SQL query
Databases & SQL
This SQL query analyzes the monthly income of game users, identifies new paying users, and assesses income dynamics. It calculates metrics such as income expansion or contraction, user loss, and their return after churn. This allows for a deeper understanding of user behavior and income dynamics.
The SQL query uses the following tools and functions:
CTE (Common Table Expressions) - for structuring the query into logical parts (monthly_revenue, revenue_lag_lead_month, revenue_metrics).
… Aggregation functions:
SUM() - for calculating the total income of a user per month.
COUNT(DISTINCT user_id) - for counting unique paying users.
Window functions:
LAG() and LEAD() - for obtaining income values from the previous and next months for comparison.
PARTITION BY - for partitioning data by each user to evaluate data separately for each.
Conditional expressions CASE - for calculating different types of income, such as new income, expansion, contraction, return after user loss, etc.
The function date_trunc() - for truncating the date to the month level ('month'), which simplifies grouping payments by months.
-
23 USD Analysis of advertising campaigns
Databases & SQL
The project involved analyzing the effectiveness of advertising campaigns by assessing the return on marketing investment (ROMI) from November 2020 to October 2022. Data on advertising expenditures, cost per click (CPC), cost per thousand impressions (CPM), click-through rate (CTR), and profitability for various campaigns such as "Expansion," "Lookalike," "Electronics," "Wholesale," among others, were analyzed. This analysis helped to understand which campaigns had the highest effectiveness and to optimize advertising expenditures in the future.
-
23 USD Revenue Metrics
Databases & SQL
This project involves the creation of an informational dashboard for tracking and analyzing key revenue indicators. The dashboard contains five charts highlighting new paying users, revenue (MRR), the number of users who have unsubscribed (Churned Users), and indicators of revenue expansion and contraction. Interactive filters for user language and age allow for a more detailed analysis of revenue dynamics over different periods.
The analysis included studying the number of new paying users, the level of monthly recurring revenue (MRR), user churn dynamics (Churned Users), and the impact of expansions and contractions on revenue. Key findings showed an increase in new paying users in the middle of the year, which positively affected MRR. However, at the end of the year, there was an increase in user churn, which reduced overall revenue. A significant impact of revenue expansions in certain months was also found, which compensated for losses from contractions. This information will help product managers make strategic decisions regarding user retention and revenue growth.