Monitoring user account registrations in the portal
Implementation: 2016
Project goal
Designing a system that enables monitoring the dynamics of user account registrations in the online portal, as well as analyzing the sources of acquisition, preferences, and potential value over time.
Challenge
At the time of implementation, the project had to rely on data from a huge database (over 100 tables and 15 GB of data) fetched from MySQL directly into Excel 2016 with the PowerPivot add-in. The key problem was consolidating data from many different sources and keeping it up to date without the need for manual processing.
A solution was needed that would enable:
- monitoring daily, weekly, and monthly registrations,
- analyzing age structure and platforms (PC, Xbox, PlayStation),
- tracking activity of social media accounts (Facebook, PSN, Xbox Live),
- modeling user growth over time (growth forecasting),
- analyzing user acquisition costs and their estimated value (LTV).
Solution
An analytical system based on Excel Power Pivot and DAX was built, which:
- automatically fetches data from MySQL views optimized for analysis,
- enables the analysis of daily, weekly, and monthly registrations,
- allows segmentation by age, platform, and registration source,
- supports analysis of account quality (e.g., whether a birthdate was provided, whether the account was linked with FB, PSN, Xbox),
- provides capability for modeling user growth and acquisition costs.
Effect
- Full control over the user database in real time.
- Ability to model campaigns for acquiring new players.
- Identification of valuable users and potential VIP segments.
- A foundation for further analysis of LTV, margin, and revenue forecasts from the secondary market.
Automatic data refresh from the MySQL database
The screen above shows the source worksheet, which retrieves data directly from the relational MySQL database—without manual export or IT involvement.
Key elements:
- User table view containing, among other things, email addresses and the dates of account registration and activation.
- ” Data Refresh Progress” panel showing that data has been successfully updated—fetched directly from the SQL view.
- Possibility to instantly refresh the entire Power Pivot model with one click, enabling work on always up-to-date data.
Effect:
Instead of manually copying data from reports, analysts can work on a live database—analyzing registrations, account activations, and user segments in real time.
Dynamic analysis of registrations and account activations
The main analytical dashboard on the screen enables tracking changes in the number of user registrations and account activations over time.
Key elements:
- The top chart shows the daily number of new accounts, with options to filter data by age, platform, or social account status.
- The bottom chart aggregates data monthly, allowing quick identification of seasonal trends and assessment of campaign effectiveness.
- On the left, slicers enable rapid segmentation: for example, only accounts linked to PSN, users aged 25–34, etc.
Effect:
The user can analyze historical and current data without any knowledge of SQL—just a few clicks are enough to check the effectiveness of marketing activities, registration campaigns, or a specific traffic acquisition source.
Detailed chart of daily user registrations
The screen displays a single bar chart presenting the daily number of user registrations over several months.
Key elements:
- The chart helps identify registration peaks that may correspond to specific campaigns, game launches, or promotional activities.
- The timeline and bar values are fully dynamic—they can be filtered using slicers (available in other views).
- The bars include labels showing the exact number of registered accounts, enabling immediate analysis of effectiveness on a given day.
Effect:
This tool is ideal for daily monitoring of marketing effectiveness and for responding to changes in user activity in real time.
User age structure and data quality
The above screen shows a demographic analysis of portal users, divided into age groups and the completeness of provided data.
Key elements:
- Horizontal chart illustrates the number of accounts in individual age groups (e.g., 15–24, 25–34).
- The “NO BIRTHDAY PROVIDED” category is also visible, allowing assessment of collected data quality.
- Use of a slicer on the left enables analysis of age structure in the context of other criteria—e.g., only users with a social account, or registered in a given year.
Effect:
This view allows you to:
- better understand who is actually using the portal,
- segment marketing communication by age,
- assess which age groups are worth activating or retargeting.
User account links with social media platforms
This screen shows a summary of user demographic data depending on whether their account has been linked with external platforms: Facebook, PSN, Xbox Live.
Key elements:
1. A summary presented as a pivot table that combines age data with account linkage status.
2. For each age group, the number of users is visible as:
- with a linked account (CONNECTED),
- without linkage (NOT CONNECTED),
- or with no data provided (NO BIRTHDAY PROVIDED).
3. The tables allow rapid assessment of which age segments are more inclined to integrate with external platforms—which is important for retargeting campaigns and data integration.
Effect:
Thanks to this analysis, it is possible to:
- identify the most engaged users,
- design campaigns for groups with high conversion potential (e.g., users with a PSN account aged 25–34),
- plan activities based on the user’s cross-platform presence.
Comparison of registration and activation trends over time
On the screen, there is a summary of two line charts, allowing deeper analysis of user behavior trends—not only regarding the number of registrations, but also their activations and further engagement.
Key elements:
- The top chart presents the variation in new accounts over time—broken down by segments such as registration source or type of platform.
- The bottom chart shows more aggregated values, e.g., monthly or quarterly increases—ideal for planning and strategic comparisons.
- You can dynamically switch between views and compare different sets of data over time.
Effect:
The user can not only observe current results, but also:
- detect decreases and increases in registrations and their causes,
- compare different user acquisition sources and their effectiveness over a longer period,
- use data for forecasting and planning future marketing activities.