SEMrush case study: Why collect raw cost data on Facebook Ads?

SEMrush is a service for managing the visibility of websites in search results. It includes platforms for SEO, PPC, campaign management, content marketing, and marketing research. SEMrush has more than 5 million users around the world and counts eBay, Quora, Booking. com, BNP Paribas, and other companies among its customers.

SEMrush audiences in each region have their own characteristics, and to attract users, it’s often not enough to track just clicks on advertising. Therefore, in order to correctly evaluate the effectiveness of advertising campaigns, SEMrush needs much more than statistics on clicks, expenses, and UTM tags.

In this article, SEMrush data analyst Konstantin Perevozchikov tells how his team uses raw data collected using the Facebook Ads → Google BigQuery pipeline from OWOX to analyze the effectiveness of advertising campaigns.

Why we started using the Facebook Ads → Google BigQuery pipeline

We used to collect cost data from Facebook ad accounts in two ways:

• Import cost data, clicks, impressions, and UTM tags with the help of the OWOX Facebook Ads → Google Analytics pipeline, then upload it into Google BigQuery by means of the Google Analytics → Google BigQuery pipeline.
• Use our own scripts written by the SEMrush team that upload certain data from a Facebook ad account to Google BigQuery.

This approach had several drawbacks.

First, we didn’t get all the data we needed. For example, we didn’t get ad region data and data needed to calculate the cost of user actions after ad views. In addition, we had to manually combine and monitor data from different accounts and for different dates.

Second, we needed to support and finalize our own solution so it solved all our data collection tasks and was ready for regular changes in the Facebook API. To do this, we needed to constantly pay programmers, spending time and money.

Therefore, we decided to try out the new Facebook Ads → Google BigQuery pipeline from OWOX. This pipeline collects all the data we need in a convenient format, monitors its relevance, and works out of the box, so we don’t need to do anything manually to merge data.

Lots of useful data

OWOX downloads about 200 fields with various data from Facebook ad accounts.

This is important because each SEMrush product has a separate development and marketing team, and each team has its own promotion budget.

You need to count the cost of not only the session but also individual actions within the session to evaluate the effectiveness of advertising investments for each team. And you need to attribute costs not for a session but for an action. This way we’ll know for sure that for conversion actions W, Y, and Z, we paid amount X, which is either acceptable for us or not. Then each team can calculate the cost per action (CPA), even if there were several key actions in the same session.

The data we get from Facebook with OWOX allows us to build deep analytics.

To do this, we combine cost data with data on the behavior of website users (downloaded by a separate OWOX pipeline) using 150 special scripts. And the more detailed our data from advertising accounts, the more accurate our assessment of user actions.

In addition, the Facebook Ads → Google BigQuery pipeline downloads the following data that’s important for our advertising:

Conversion goal — Thanks to this field, we see what goal the advertising campaign is set up for and understand what stage of the conversion funnel the user is at (for example, registration or trial).

Advertising link — Allows us to accurately associate a touchpoint and a specific advertisement.

Ad signatures on banners — In our work, we use the deeply embedded structure of advertising accounts. By signing ads on banners, we can measure and mark campaigns with maximum details. Then we can accurately determine the goals and associate campaigns with activities.

Many text fields (such as campaign name and ad set) — We wrote special scripts that download the values of such fields from BigQuery into Google Sheets. Then marketers use them to quickly and accurately mark up advertising accounts.

Cost statistics by country

SEMrush is an international product, and it’s important for us to analyze the effectiveness of advertising in different regions. For example, the cost of a click in the USA and in India is different. This means the final CPO is different by country.

One of the main advantages of the Facebook Ads → Google BigQuery pipeline for us is that the pipeline provides data from an advertising account across different countries.

Using raw data downloaded from Facebook, we can find out how much money we spend in a particular country on specific campaigns. Then we can attribute touchpoints to users and calculate the ROI of these campaigns.

We track how ads work depending on the geography of the audience and can fine-tune campaigns by country.

Results

The Facebook Ads → Google BigQuery pipeline greatly simplifies the process of analyzing the effectiveness of advertising campaigns.

• We collect all the data we need, and additional scripts are needed only for special cases like calculating the cost of attracting individual user actions.
• OWOX downloads a lot of important data for us, including crucial data such as region data that allows us to measure the geographical distribution of advertising costs.
• The pipeline automatically collects data from all our Facebook ad accounts into a single partitioned Google BigQuery table. This greatly simplifies cost analysis.
• The pipeline monitors the relevance of downloaded data. If the data changes retrospectively in the advertising account, OWOX updates it in BigQuery.
• Also, the OWOX team helped us download historical data for the previous three years from our Facebook ad accounts. Thus, we can evaluate campaign dynamics as well as track key changes in the past that significantly affect the results of current campaigns.
• We don’t need to involve programmers to set up data collection and consolidation, as the out-of-the-box OWOX pipeline does everything we need.

In the near future, we plan to abandon most of the scripts we wrote and collect Facebook Ads data (and also LinkedIn data) using only OWOX pipelines.