Building an Analytics Solution for Ad campaigns -[Part 1 -Data Modelling]

In this series of Blog posts we will be building an analytics solution for a growing food ordering app, we understand the pivotal role that effective ad campaigns play in reaching and engaging target audiences. The marketing team have been running an ad campaign through Google ads to amplify their online presence. The campaign is aimed to extend their reach to a wider audience, drive user engagement, and boost conversions.

While the campaign has generated a lot of interactions and engagements, we’re acutely aware that these interactions are more than mere numbers — they hold the keys to optimizing our app’s future . To harness this potential, we’ve outlined specific objectives that will guide our analysis:

1. Cost Management: We seek to understand the allocation of our budget across various ad channels and the associated costs of different campaigns. This insight will enable us to optimize our spending for maximum impact.
2. Audience Targeting: Delving into our campaign data will reveal valuable information about the demographics, locations, and interests of our engaged audience. Armed with this knowledge, we can refine our targeting strategies to resonate with the right users.
3. Keyword Effectiveness: Uncovering the effectiveness of keywords employed in our ads is paramount. By identifying high-performing keywords, we can fine-tune our content to enhance relevance and engagement.
4. ROI Calculation: Ultimately, we aim to measure the success of our campaign through Return on Investment (ROI) calculations. This involves assessing the revenue generated against the incurred costs. Such a calculation will provide us with a clear understanding of the campaign’s financial impact.

Why Data Pipeline and Warehousing Matter ?

While Google Ads provides valuable data on campaign performance, the data it offers is raw and it’s very difficult to get insights from it directly. Building a dedicated analytics solution involving data pipelines, warehousing, and visualization holds several key advantages:

1. Data Integration and Centralization: Google Ads data is just one piece of the puzzle. By integrating it with other data sources, like user behavior or sales data, we gain a comprehensive view of our business. Centralizing data in a warehouse ensures a unified source of truth.
2. Data Cleansing and Enrichment: Raw data is often riddled with inconsistencies, inaccuracies, and missing values. Data pipelines allow us to clean and enrich the data before analysis, ensuring the accuracy of insights.
3. Historical Analysis: Google Ads’ built-in interface offers limited historical data. A warehouse allows us to retain historical records, enabling us to analyze long-term trends and make informed strategic decisions.
4. Complex Analysis: To understand the nuances of campaigns, we often need to perform complex calculations, aggregations, and correlations. Data warehouses enable such analyses at scale, which Google Ads’ interface might not provide.

Why Not Rely Solely on Google Ads Data:

It’s important to note that while Google Ads data is a valuable starting point, it has its limitations:

1. Contextual Constraints: Google Ads provides data within its own ecosystem. To gain a broader context, such as user behavior before and after engaging with ads, we need to combine it with external data sources.
2. Customization and Flexibility: Google Ads’ reporting tools offer standardized metrics. However, our business might require tailored KPIs that necessitate custom data transformations, which a dedicated solution can provide.
3. Granularity and Drill-Down: Detailed analysis often requires drilling down to specific attributes or dimensions that Google Ads data might not readily support.

End Users of our Solution:

Our analytics solution will cater to a range of stakeholders:

1. Marketing Teams: They’ll gain insights into the performance of different ad campaigns, enabling them to refine strategies and allocate budgets more effectively.
2. Management and Decision-makers: Clear visualizations and aggregated reports provide them with a bird’s-eye view of campaign outcomes, helping in informed decision-making.
3. Data Analysts: They can leverage the cleaned, transformed data for in-depth exploratory analysis, uncovering hidden patterns and insights.
4. Finance Teams: ROI calculations and cost breakdowns will aid them in evaluating campaign profitability.

By building this solution, we bridge the gap between raw data and actionable insights, empowering these stakeholders to make data-informed decisions that drive the success of our food ordering app.

Building the Data Model: A Star Schema Approach

In our journey to obtain insights from the Google Ads campaign data, we’ve adopted a robust data modelling strategy known as the Star Schema.

source : https://uncledata.medium.com/

Significance and Benefits:

The Star Schema architecture empowers us with unparalleled advantages:

1. Query Performance: With data organized into dimension and fact tables, complex queries become efficient. The dimensional hierarchy optimizes query execution, enabling swift analysis.
2. Hierarchical Insights: The relationships between dimension and fact tables foster hierarchical insights. We can explore performance at various levels, from campaigns down to keywords and even individual ads.
3. Flexible Analysis: Star Schema accommodates diverse analysis scenarios. We can analyze performance by date, device, location, and more, making informed decisions at a granular level.

This approach involves structuring our data into fact and dimension tables, optimizing query performance and facilitating insightful analysis. Let’s see how we transform the source data into this architecture:

Dimension Tables:

1. Campaigns: Campaign ID, Campaign Name, Start Date, End Date, and Status are directly extracted from the source Google Ads data. Each campaign becomes a distinct entry, ensuring comprehensive campaign-level insights.
2. Ad Groups: Ad Group ID, Ad Group Name, Campaign ID, and Status are derived from the raw data. The foreign key relationship with the Campaigns table establishes a connection, enabling campaign grouping.
3. Ads: Ad ID, Ad Type, Headline, Description, and Ad Group ID are sourced from the Google Ads data. The link to the Ad Groups table maintains a hierarchical relationship, aiding in ad-level analysis.
4. Keywords: Keyword ID and Keyword Text are extracted from the source. The relationship with the Ad Groups table associates keywords with specific ad groups, essential for keyword effectiveness evaluation.
5. Locations: Location ID, Country, City, and Region details are collected from user interactions and campaign settings. The Locations table allows geographical analysis for better targeting.
6. Devices: Device ID and Device Type (Desktop, Mobile, Tablet) attributes are captured. This dimension enriches the analysis by considering user device preferences.
7. Date: The Date dimension table contains Date Key, Date, Day of the Week, Month, Quarter, and Year attributes. These temporal details enable time-based analysis and trend identification.

Fact Table:

Our central hub of analytics resides in the CampaignPerformance fact table. This table aggregates performance metrics at a granular level, offering a comprehensive view of campaign effectiveness.

1. The CampaignPerformance table connects to various dimension tables through foreign keys. This relationship links campaigns, ad groups, ads, keywords, locations, devices, and dates. This interconnected structure allows for multidimensional analysis and drilling down into specific attributes.
2. Performance metrics such as Clicks, Impressions, Conversions, Conversion Value, and Cost are stored within the fact table. These metrics, consolidated with dimension attributes, provide a holistic understanding of campaign performance.

How this data model full fills our objectives ?

1. Cost Management:

The CampaignPerformance fact table within our data model contains cost-related data, including Cost, Clicks, and Impressions. By analyzing these metrics across campaigns, ad groups, and individual ads, we gain insights into cost allocation effectiveness. We can identify which campaigns or ads are generating the most clicks and conversions relative to their costs. This knowledge allows us to optimize our budget allocation, pausing under performing campaigns or reallocating resources to high-impact areas.

2. Audience Targeting:

The combination of dimension tables such as Locations, Devices, and Keywords empowers us to fine-tune our audience targeting strategies. By analyzing which geographic locations and devices yield the highest conversions and engagement rates, we can tailor our campaigns to resonate with these specific segments. Additionally, the Target Audience and Bid Strategy attributes provide a window into audience preferences and behaviours, further enhancing our targeting precision.

3. Keyword Effectiveness:

Keywords are a fundamental component of our campaigns. Through our data model, we can evaluate the performance of keywords by tracking Clicks, Impressions, Conversion Rates, and more. This insight enables us to identify high-performing keywords that drive engagement and conversions. Conversely, it highlights keywords that underperform, allowing us to refine our content strategy and bid management for better keyword effectiveness.

4. ROI Calculation:

Return on Investment (ROI) is a critical metric for assessing campaign success. Our data model provides the necessary components for ROI calculation. By combining Conversion Value and Cost from the CampaignPerformance table, we can ascertain the revenue generated relative to the expenses incurred. This insight provides a clear understanding of campaign profitability, guiding us in decisions regarding resource allocation and strategic direction.

Thanks for Reading ❤️
If you Like this post and wish to read more such content in future then join Data Engineering Daily group on Linkedin.