Origami Effect GT Mailing

Email Segmentation and Precision Marketing Campaigns

Implemented in 2016

Project Goal

To build a tool for creating effective marketing campaigns based on real user data from a web portal database. The system was to enable instant segmentation of email addresses and their use in advertising platforms such as Facebook Custom Audiences, Google Ads, and Mailchimp.

Challenge

The project was developed around 2015, when most companies did not yet have access to advanced BI systems or CDP (Customer Data Platforms), and marketing automation tools were expensive and inflexible. As a result, many companies operated “blindly,” without segmentation, relying solely on generic email campaigns.

Modern marketing campaigns, however, require not just mass mailings but above all precise targeting. Communication that is too broad wastes budget and leads to low conversion rates.

Another challenge was the size and complexity of the database – over 15 GB of data and more than 100 tables in a relational MySQL database, containing both declarative data and data integrated from external sources (e.g., Facebook, PSN, Xbox accounts).

The tool needed to:

  • enable rapid creation of targeted audience groups,
  • pull data directly from MySQL into Excel,
  • allow personalization of marketing communication,
  • support export of ready-to-use databases for advertising platforms,
  • work without the need for expensive SaaS licenses.

Solution

A BI system was created in Excel using Power Pivot and DAX, which connected to previously prepared and optimized SQL views.

Features:

  • Direct data import and update from the MySQL database
  • User segmentation
  • Creation of marketing lists for selected channels

Results

Campaign creation time reduced from hours to minutes

Improved campaign effectiveness – better tailored messages led to higher Open Rate and CTR

Ability to react dynamically to changes – for example, preparing a campaign for a specific age group or platform in just a few clicks

Ready data export to advertising platforms without the need to involve the IT department

The system did not require a SaaS subscription – it was independent, efficient, and 100% owned by the client

Available Data Segmentation Using Excel Slicers:

  • User age
  • Platform (PlayStation, Xbox, PC)
  • Email address and domain
  • Residential address
  • Bank identifier code
  • Links to social media accounts (Facebook, PSN, Xbox Live)
  • Account creation year
  • Campaign generator exporting data to:
  • Mailchimp (email campaigns)
  • Facebook Custom Audiences (remarketing)
  • Google Ads Custom Audience (audience segmentation)

Relational Database View of the Portal

The above screenshot presents the data model view of the web portal imported into Power Pivot.

The database contained over 100 tables and was approximately 15 GB in size, which required:

  • analyzing relationships between tables,
  • identifying key columns for segmentation and targeting,
Baza danych

Data Mapping and Preparation

To achieve full model functionality:

  • Mapped columns from various tables into unified entities (e.g., users, accounts, activity, segments),
  • Filtered input data to avoid duplicates and logical errors,
  • Created database views in MySQL optimized for Power Pivot queries,
  • which shortened loading time and enabled work on live data.

Result

Thanks to proper structure preparation:

  • the data model could be refreshed directly from within Excel,
  • the end user did not need to know the database structure or SQL – ready-made tables and segments were available for analytics and campaign work,
  • the whole solution provided a solid foundation for further dashboards and analyses, including additional BI modules.

DAX Formulas in Power Pivot

Creating a functional and dynamic analytical model required not only importing data but also transforming and enriching it.

For this purpose, DAX formulas (Data Analysis Expressions) were used, which are the foundation of how Power Pivot works in Excel.

GT Mailing Power Pivot

What Was Done?

Advanced DAX formulas were applied to:

  • calculate dynamic marketing and operational metrics (e.g., user age, account lifetime, time-based segments),
  • create calculated fields useful for segmentation (e.g., age categories, user type, platform assignment),
  • filter data for advertising campaigns according to defined rules,
  • define KPI indicators to assess the effectiveness of marketing activities (open rate, CTR, campaign reach per segment).

Result

Thanks to the use of DAX:

  • The model became a tool not only for analysis but also for creating campaign strategies,
  • The end user could continuously modify the rules without IT support,
  • Any change in filters or input data automatically updated the entire analytical structure.

Why DAX?

  • It allowed independence from changes in the database – transformations were made directly in the Excel model.
  • Enabled building flexible, dynamic segments without coding on the database side.
  • Was the foundation for creating personalized reports and exports – ready for integration with advertising platforms.

User Data Segmentation

The created system enables instant segmentation of portal users according to selected criteria.

Thanks to the use of Excel slicers, the end user can create recipient lists for marketing campaigns without knowledge of SQL or pivot tables.

Selector

Available Segmentation Criteria:

  • User age ranges,
  • Gaming platform (PlayStation, Xbox, PC),
  • Email domain (e.g., gmail.com, wp.pl),
  • Bank account identifier,
  • Location (city),
  • Connection with external services (Facebook, PSN, Xbox Live),
  • Account creation year in the service.

Result

The user can prepare a recipient database perfectly tailored to the campaign objectives in seconds:

  • mailing only to PS4 gamers aged 25–35,
  • remarketing to users with a Facebook account who have not been active for 6 months,
  • testing communications on segments based on age, registration source, and activity.

As a result, each campaign can be fitted like a glove, leading to higher CTR, lower ad costs, and better conversion.

Volume Analysis and Message Planning

This view is designed for practical campaign planning—it allows you to quickly estimate how many people are in a given segment and how many marketing materials need to be prepared.

Thanks to dynamic filters (slicers), the user can instantly:

  • determine the number of accounts in a specific segment (e.g., women aged 25–34 on PS4),
  • calculate the need for unique marketing messages (e.g., how many banner versions, how many emails),
  • prepare input data for Google Ads, Facebook Ads, Mailchimp—with ready numbers and criteria.
Volumeny

Example Applications:

  • Estimating campaign costs based on the number of creatives and audience groups,
  • Analyzing which groups have the greatest reach potential,
  • Comparing the effectiveness of different segments after a campaign thanks to results integration.

Result

Instead of choosing campaign materials “by feel,” you can precisely plan communication, costs, and required resources. This tool combines BI with operational marketing.