Consumer first-party
data acquisition and
analysis

Context

This project was made during an internship experience I had during a double degree exchange program at the University of Grenoble INP in France. The report was then used as a thesis for my graduation project at the University of São Paulo. The internship was undertaken at a consumers goods multinational, with a specific emphasis on exploring the interplay between data analysis and digital marketing in the contemporary landscape marked by the enforcement of new digital marketing compliance regulations such as GDPR. The internship comprised two primary projects: the acquisition and activation of consumer first-party data, as well as the exploration of retail data through the utilization of Amazon Marketing Cloud.

Objectives

Sub-project 1: Grooming first-party data acquisition

The main goal of the first project was to quantify the number of potential grooming consumers that were not identified as such in the databases owned by the company, across five different countries in Europe: Italy, Spain, UK, France, and Germany. This involved gathering and utilizing consumer first-party data for the company's grooming brands, aligning with the company's fiscal year objectives. Additionally, the discussion covers changes in data collection methods and types in digital marketing, particularly addressing the impact of new data regulations currently in effect in Europe.

Sub-project 2: Consumer data analysis from Amazon's website

The aim of the second project was to utilize the Amazon Marketing Cloud digital marketing platform for gathering and analyzing grooming consumers data. The objective was to tackle the company's pre-defined business questions, utilizing Python for data analysis. The Amazon Marketing Cloud (AMC) platform functions as a cloud-based clean room containing information about sales and consumer engagement on Amazon's website, accessible via SQL queries.

Used technologies

• Jupyter Notebooks and Python libraries (pydata_google_auth, pandas, numpy, matplotlib, google.cloud, openpyxl)
• Amazon Marketing Cloud
• Google Cloud Platform's tools BigQuery and Vertex AI Workbench

Sub-project 1: Grooming first-party data acquisition

System requirements

Functional requirements

• The system has to authenticate with GCP’s BigQuery to access all databases
• The system has to query all the consumers inside the selected databases that are potential grooming consumers for three different grooming brands
• The system has to query all the traits inside the selected databases that could be related to grooming products
• The system has to query all the events inside the selected databases that could be related to grooming products
• The system has to query all the data sources inside the selected databases that could be related to grooming consumers
• The system has to analyze all data collected related to traits, consumers, events, and data sources to get insights based on the definition of a potential grooming consumer
• The system has to generate charts related to each analysis result
• The system has to do all the querying and analysis activity for five different countries (Italy, France, Germany, Spain, and United Kingdom) based on the marketing program number
• The system must only query consumers with age and gender information
• The system must not query duplicated consumers, traits, sources, nor events
• The system must not query consumers that are present on the grooming database
• The system must not query consumers that are not active in the past 12 months
• The system must only query consumers that have grooming golden traits
• The system must only query consumers that agreed with the email communication regulation defined by GDPR
• The user has to be able to input the country selected for analysis and get as output the data related to that country analyzed, in chart and table formats

Non-functional requirements

• The system has to use Python programming language to treat the collected data;
• The system has to use SQL to recover data from the relational databases provided;
• The system has to use Google Cloud Platform’s cloud services, such as databases and cloud computing environments;
• The system has to execute the analysis using Jupyter Notebooks and has to generate the reports directly from them;
• The connection with GCP’s databases has to be done internally, not being able to connect to external networks;
• All data management has to comply with GDPR's regulations;
• The system has to be able to perform analysis for five different countries: Italy, France, Germany, Spain, and United Kingdom;
• The system has to be able to be reused for future analysis on different countries.

Project implementation

The main objective of this sub-project was to find on the company's predefined databases consumers that could be potential grooming product buyers. These consumers must comply to some contrains defined on the system requirements. To find these consumers, some queries were constructed and the final result was displayed on a table for all five countries in analysis.

Selecting Marketing Program Numbers

This analysis encompassed five distinct countries: Italy, France, Germany, Spain, and the United Kingdom. Each country and brand is associated with its unique Marketing Program Number (MPN) within the selected databases. An MPN is an exclusive identifier assigned to a marketing plan devised by the company, encompassing all tasks aimed at boosting sales for a specific brand or a group of brands, categorized by country. Each of the selected grooming brands possess its dedicated MPN corresponding to the country under examination. To retrieve the necessary Marketing Program Numbers, a query was executed directly from BigQuery’s interface.

Defining queries' constraits

To ensure compliance with system requirements needing gender and age information for all consumers, the verification process must be integrated into the queries. The database contains various potential gender values. Therefore, before selecting consumers based on this data, it is crucial to identify and consolidate all available gender values within the database. Consequently, the only acceptable values for gender become 'm' or 'f'.

Another constraint defined on the system requirements is that a consumer must be active on the past 12 months. To select the active consumers, the events that the consumer participates that are not considered as active need to be defined. Then, the last time the consumer has been active needs to be verified on all tables related to events. After that, the events considered as not active were excluded from this verification, resulting on a list of userId’s that are active on the past 12 months.

The next constraint is regarding the email marketing communications. The consumers that have agreed to receive email marketing communications need to be selected by verifying the database column that contains that information.

One final constraint needs to be added to the analysis, which is related to the analysis done in Italy. In Italy, the databases have been through a cleaning regarding GDPR’s regulations, which is related to the consent given by the user to allow the usage of their data. This information is on a column present on the databases, which needs to be verified on all Italian databases analysis.

Queries created

Consumer traits query

The first query created had the intent of looking for consumer traits that have grooming related terms on its name. A consumer trait is a user attribute such as name, gender, city, but also computed traits, like the answers of a survey. Regular expression functions were used to search for grooming related traits. The grooming traits and all the unique userId’s related to them that are present on the chosen databases were selected. After selecting them, there was an exclusion of: the userId’s that were already present on any of the grooming databases; the traits that have less then 10,000 userId’s associated to them. Finally, all the constrains mentioned before were applied to get the final results containing the number of unique consumers related to each consumer trait.

Consumer sources query

The next query created was regarding the sources from where the consumer data has come from. The first thing done was getting a list of all of the sources of information available. With the sources selected, the next step is to select the ones that are related to grooming and get all of the consumers that have them as a source. To verify if the source is grooming related, regular expresions were used on the description and name of the source by verifying if there were any grooming terms on them. As it was done for the traits query, the constraints defined before were also applied to get the final result containing the number of userId's for each source.

Consumer events query

The events query was done based on the events selected as meaningful for grooming brands. User events are user activity (clicks, views, orders) on a touchpoint. All of the selected consumers are unique, meaning that their userId appears only once at the results of the analysis. The events selected on the queries were: email events, coupon events, receipt events and cashback events. After selecting all consumers related to these events, the contraints defined before were applied resulting on a list of events and the number of unique consumers that are related to those events.

All consumers query

The query includes the merge of all of the sources, traits and events SQL queries, as well as the constraints that were defined, resulting on a final number of unique users that could be potential grooming consumers.

Results

The results for each country are shown on the table below. Total Users is the number of the users that have gender and age information and that are related to at least one grooming golden trait , event or source. Opt-in users are the Total Users with the restriction of having agreed to receiving email marketing communications. Active users are the Total Users with the restriction of being active on the past 12 months. Active and Opt-in Users are the number of users with both restrictions. The percentage of Male and Female consumers with both restrictions is also quantified on the table below.

Sub-project 2: Consumer data analysis from Amazon's website

The objective of this project was to analyze Amazon Marketing Cloud's consumer data to answer the business questions provided by the company. The implementation and results were divided in five topics, each one answering one of the questions. There were two grooming brands in analysis, which will be defined as GB1 (Grooming Brand 1) and GB2 (Grooming Brand 2) for legal reasons.

Business questions

1. What are the optimal marketing campaign settings related to the number of times an advertisement is shown to the customer (frequency cap) and the times when most sales occur (day parting) for Display Advertisements?
2. How do different types of advertising placements on the Amazon website work together (Display Ads, Search Ads, and Share of Voice)?
3. What are the paths followed by the customer on the Amazon website until the purchase, and which types of advertisements are displayed?
4. What is the time elapsed between the first viewing of an advertisement and the purchase made by a consumer related to that ad (time lag)?
5. Which consumer audiences generate the most sales? Are there age differences?

System requirements

Functional requirements

• The system must query the Display Advertisements information to generate reports related to the day parting of them.
• The system has to define what is the optimal frequency cap for Display Advertisements in a period of a month;
• The system has to define what is the period of the day in which most of the sales related to a marketing campaign;
• The system must use the purchase rate, ROAS, number of impressions, and number of users as KPIs for evaluating what is the best frequency cap for Display Advertisements;
• The system must divide the result for frequency cap and day parting by the selected grooming brands in analysis;
• The system must generate charts related to day parting, dividing the results by the hour of the day;
• The system must generate charts for frequency caps comparing the number of users that have seen an advertisement, the number of times the advertisement has been seen by the same users, with the purchase and ROAS rate related to them;
• The system must analyze how the different types of media vehicles work together and separately;
• The system must compare the media vehicles analysis results using the following parameters: the number of impressions, the number of users that have purchased a product, the purchase amount for the campaign in Euros, the total cost of the advertisements, and the unique reach;
• The system must use ROAS and purchase rate as KPIs for the media vehicles analysis;
• The system must generate charts and tables with the results for the media vehicles analysis divided by the period of operation of the advertisement and by the brand in analysis;
• The system must compare the performance of the different media vehicles in peak periods and non-peak periods;
• The system must compare the different campaign paths that occur on Amazon’s website and generate tables with all the results;
• The system must analyze how the consumers interact with advertisements after their first impression until their last impression on a predefined period of time;
• The system must generate charts for the user impressions analysis using as a parameter the time passed after the impression, the purchases made up until that moment, and the actions made by the consumer related to that advertisement. All this must be done for each brand in analysis;
• The system must analyze what are the best-performing consumer audiences on Amazon for each brand in analysis and generate tables with this comparison on a predefined period of time;

Non-functional requirements

• The system has to use Python programming language to treat the collected data;
• The system has to use SQL to recover data from the relational databases provided on the Amazon Marketing Cloud website, generating Excel files with the results;
• The system has to use Jupyter Notebooks to treat the data and generate insights related to the business questions;
• The system has to use Google Cloud Platform’s cloud services for treating the data, specifically AI Vertex;
• The connection with GCP’s databases has to be done internally, not being able to connect to external networks;
• All data management has to comply with GDPR’s regulations;
• The analysis has to be adaptable to generate insights for other countries than the one in analysis, which is France.

Implementation and results

Frequency cap and day parting

The objective of this part of the analysis was to understand, for Display Advertisements, what is the division of advertisements displayed during the day (day parting) and what is the optimal frequency cap to be set for displaying them, in other words, how many advertisements they should be displayed to a consumer for each marketing campaign set up on a period of 30 days to get the best results in terms of purchases. This is important to know because Amazon charges their advertisements per impression and per clicks (how many times the advertisement was clicked), meaning that a maximum value of times an advertisement is displayed need to be set up so that the ratio between the money spent on a campaign and the return in sales is the best.

Two queries were used on AMC to get the information needed for the analysis, one for day parting and one for frequency cap. The queries were executed per month, meaning the date range selected on AMC was of a month, and also per brand.

For day parting, the main KPI used to understand how the campaign has performed is the purchase rate, which was calculated for each hour of the day. After calculating the purchase rate per hour, a chart was plot for each marketing campaign in analysis. On this chart, besides the purchase rate per hour, the percentage of the total daily impressions a campaign has received is displayed too, also divided by hour of the day. One of the results of this analysis for GB1 is shown on the chart below. It is possible to see that the time of the day that this specific campaign receives the most impressions is during the night, but the best purchase rate happens during the morning period.

Regarding the frequency cap, the objective was to determine the optimal number of advertisements to display to a consumer for optimal results. After obtaining the query results, the key performance indicators (KPIs) of purchase rate and return on advertising spend (ROAS) were utilized to identify the optimal values. The analysis unfolded in two stages: initially, the number of impressions was aggregated into groups of 5. For instance, if a user viewed an ad once within a 30-day period, the user would fall into the 1 to 5 ads group. Subsequently, the analysis was refined by adjusting the query to capture more detailed values. Instead of using frequency groups, individual frequencies were treated as distinct groups. In the given example, the user would now be categorized in the 1 ad group. For each of these groups, the percentage of users falling into them was computed. Two separate charts were generated for each campaign under examination. One chart compared the purchase rate and the number of users in each group, while the other chart compared the purchase rate for each group with the corresponding ROAS.

Figures 2 and 3 illustrate the outcomes of the initial phase of the analysis for GB1 shavers campaigns, while Figures 4 and 5 depict the second phase of the analysis for the same campaigns. Notably, the first part of the analysis yields results that lack granularity, preventing the identification of precise optimal frequency values. In the first phase, the selected campaign exhibits its optimal frequency cap value between 6 to 10 advertisements per month, as indicated by the highest ROAS and purchase rate within that range. Conversely, the second part introduces more detailed frequency values. For the specific campaign depicted in the charts, the optimal frequency cap is determined to be 10 advertisements per month, aligning with the peak ROAS and purchase rate.

Media vehicles

In this segment of the analysis, the aim was to understand the collective and individual effectiveness of different types of advertisements. The exposure groups, or media types, chosen for comparison included Sponsored Advertisements (Search Ads), Display Advertisements, and Share of Voice packages. The objective was to assess the performance of these three advertisement types during peak periods (Black Friday and Prime Day) and off-peak periods, determining if their utilization justifies the expenditure. The analysis involved querying AMC for all the specified months relevant to the investigation.

In the initial analysis, the focus was on comparing Sponsored Advertisements and Display Advertisements, with Share of Voice packages earmarked for future examination. Each comparison was conducted on a brand basis, with the selected brands being GB1 and GB2. Upon retrieving the query results for the chosen months, key performance indicators (KPIs) used to gauge success, including ROAS and purchase rate, along with other values relevant for comparison, such as the percentage of total impressions and percentage of total sales attributed to an exposure group for that period, were calculated. These KPIs were computed individually for each brand and exposure group. Subsequently, a chart was generated to visually represent the ROAS for each brand in the selected month and exposure group. The outcomes are depicted in Figures 6 and 7, covering the period from August 2021 to June 2022.

Regarding GB1, it's evident that, for the majority of months, employing both Display and Search advertisements proves advantageous in terms of ROAS. Display Advertisements consistently exhibit lower ROAS values when used independently, primarily due to their high impression numbers, as they are predominantly employed for maximizing reach. In the case of GB2, the effectiveness of utilizing both Search and Display Advertisements is observed only in particular months. There are instances when Display Advertisements are not utilized at all, corresponding to months where the ROAS chart displays zero values for Display.

To get a general picture of the use of both types of advertisements, the comparison values were summed for all the months in analysis, and the results were put in a table for each brand. For both GB1 and GB2, most part of the impressions come from Display Advertisements, but the ROAS and purchase rate for this exposure group are very low. For GB1, as shown on Table 2, using the combination of both types of advertisements gives the best results. For GB2, observing the results on Table 3, the purchase rate is better when overlapping the advertisements, but the ROAS is better when using only Search ads.

As a conclusion, it is noticeable that for GB1, the use of both advertisements is important. For GB2, the recommendation is that the overlap of media vehicles should be done, but not on all months. The focus should be on using both of the them on peak period. For all tables, M stands for thousand and MM for million.

As a second phase of this analysis, it is imperative to explore the impact of introducing Share of Voice (SoV) as a new exposure group on sales performance. To achieve this, understanding the specific periods during which the company procured SoV packages for the selected brands is crucial, given that this type of advertisement is typically seasonal and procured during peak periods. Among the analyzed brands, GB1 is the only one that utilized this type of advertisement. Consequently, two peak months, July 2022 (Prime Day) and October 2022 (second Prime Day), were chosen to incorporate this new variable.

New queries were executed on AMC, incorporating the new exposure group for each of the selected months. The outcomes are presented in Tables 4 and 5. It is discernible that the utilization of SoV packages proves advantageous during peak periods, exhibiting higher ROAS and Purchase Rates, particularly when combined with Search ads. Additionally, favorable results are observed when there is a triple overlap among the three exposure groups.

Campaign paths

The analysis focused on understanding various campaign paths leading to a purchase, considering the sequence and frequency of Search and Display Ads. Key metrics for comparison included the starting and ending types of advertisements, path occurrences, purchase rate, ROAS, average path length, and the percentage of total occurrences for each path type. Tables 6 and 7 reveal that paths starting and ending with Display Ads ads have the highest occurrences but exhibit the lowest purchase rate. Conversely, paths starting with Search and ending with Display Ads ads yield the best results in terms of ROAS and purchase rate.

Time lag for purchase

In this analysis, consumer interactions with advertisements within 15 days of exposure were examined. The study focused on three main actions: Purchase, Add to Shopping Cart, and See Detail Page after viewing an advertisement. The data, spanning 12 months and divided into 15-day intervals, revealed insights such as the time elapsed after ad exposure, user actions over time, and the percentage of total purchases within the 15-day period. Analyzing GB1 and GB2 from June 2021 to April 2022, Figures 8 and 9 illustrated similar consumer behavior, emphasizing a significant conversion peak two days after ad exposure, contributing to over 50% of total sales during that period.

In the analysis of consumer audiences on Amazon Marketing Cloud, the focus was on identifying high-performing segments for each brand, GB1 and GB2. Consumer audiences represent groups with similar purchasing patterns, enabling targeted advertising based on interests and improving overall performance compared to generic targeting. For each brand, the analysis involved displaying total product sales in euros, calculating ROAS, purchase rate, and the percentage of sales attributed to new-to-brand (NTB) users. NTB users are defined as those who haven't purchased any products from a brand in the last 12 months. The analysis covered the period from August 2021 to June 2022.

Table 8 displays GB1's top-performing segments, while Table 9 presents GB2's. Notably, the first two segments in both tables are broad and general, with the more specific descriptions in subsequent segments proving to be more crucial for the brands. Additionally, GB1 exhibits a higher percentage of consumers who are new to the brand compared to GB2. This suggests that GB2's consumers show a higher inclination towards repurchasing products.

In the next phase of the analysis, the focus was on identifying the most lucrative age groups for each brand in terms of sales. This involved utilizing AMC segments that categorize consumers based on age. Additionally, the analysis explored the performance of different devices (Personal Computer, Phone, Tablet) for each age group. The results, presented in Table 10 for GB1 and Table 11 for GB2, highlight that age groups 35 to 44 and 45 to 54 contribute significantly to sales for both brands. Phones are the primary sales device for GB1, while Personal Computers (PCs) dominate for GB2. Notably, GB2's consumer base exhibits a higher proportion of repeat purchasers. An interesting observation is that Tablets, despite contributing less to sales, demonstrate the highest return on advertising spend (ROAS) across most groups.