Santehnika-Online’s Success Story: How to Improve the Advertising-to-Sales Ratio by 10%
Santehnika-Online is a retailer for plumbing fixtures, water supply systems, heating and air conditioning equipment, finishing materials, and accessories for sanitary premises and kitchens. For over 9 years the company has grown from a small retail store to a large recognizable brand, becoming a leader in the segment.
Santehnika-Online offers its customers over 110,000 items from 600 leading manufacturers. Purchases can be made online at santehnika-online.ru, over the phone and in brick-and-mortar retail stores.
Santehnika-Online actively promotes products using Yandex.Market, Yandex.Direct, Facebook, Сriteo, Google AdWords and MyTarget. The company’s marketing specialists were looking to improve the efficiency of advertising, by evaluating advertising campaigns and optimizing the allocation of budget resources. At the same time, all the business processes were expected to be as automated as feasibly possible.
To achieve this goal, the company needed to:
- Automate the consolidation of data from different sources: advertising costs, website user behavior, orders made over the phone, and order fulfillment rates from the CRM system.
- Create attribution models based on the consolidated data, in order to accurately evaluate the performance of different advertising services.
- Automate the management of advertising campaigns and bids.
Before making a purchase, customers may interact with Santehnika-Online through a variety of different channels. For example, a customer may choose a product online, make an order over the phone, and complete the transaction in an offline store. In order to accurately evaluate the efficiency of advertising, the company needs to have the data combined across all touchpoints, both online and offline, while the data is originally collected and stored in different systems.
In order to create automatic bid management rules for advertising campaigns, the company needed to evaluate the effectiveness of keywords. To measure the performance of each keyword, it was necessary to merge all the available data about user actions, such as on-site interactions, phone calls, and offline purchases.
Santehnika-Online tracks customer interactions with the website using Google Analytics. However, Google Analytics is not the best solution when it comes to consolidating data and evaluating ad campaigns. Google Analytics doesn’t allow reprocessing data. In other words, it’s impossible to change the amount of a transaction or add transactions from previous periods. As a result, the following data may be inaccurate or missing:
- Orders placed via the call center.
- Canceled orders and purchase returns.
- Offline purchases in retail stores.
Google Analytics only allows uploading certain types of data. That’s why the company wasn’t able to send data to Google Analytics for a number of parameters they needed, including the data about offline transactions. Google Analytics also has limits for the number of dimensions and metrics in reports. To learn more about Google Analytics limitations and how you can overcome them, see our blogpost.
Santehnika-Online’s analysts decided to use the Google BigQuery cloud data warehouse. The reason for choosing Google BigQuery being that, the service guarantees the highest security of data and easily integrates with other services via the API. It’s also easy to upload data to Google BigQuery from files and import it from Google Sheets. Google BigQuery allows its users to create custom unsampled reports with no limitations to the number of dimensions and metrics in a report. What’s better, with OWOX BI Pipeline the company can send unsampled data to Google BigQuery in the structure that is convenient for the business.
The following data is sent to Google BigQuery via OWOX BI Pipeline:
- Advertising costs (automatically collected in Google Analytics also using OWOX BI Pipeline).
- Orders placed over the phone, from the Call touch call tracking system.
- Order completions from the CRM system.
The other tools used by Santehnika-Online are:
- Google Data Studio, to visualize advertising data.
- OWOX BI Attribution to assign value to advertising campaigns.
- Alytics, to automate bidding using the obtained data.
The company’s resulting data consolidation flowchart is as follows:
Now let’s take a closer look at each step Santehnika-Online took in order to improve the efficiency of advertising.
Step 1. Collect advertising cost data in Google Analytics and Google BigQuery
First, the company’s analysts collect cost data from advertising services in Google Analytics. Cost data from Google AdWords appears in Google Analytics automatically thanks to the native integration. Cost data from Facebook, Yandex.Direct, Yandex.Market, Criteo and MyTarget is automatically imported to Google Analytics via OWOX BI Pipeline. Next, cost data from all advertising services is automatically sent to Google BigQuery, also using OWOX BI Pipeline.
Step 2. Import data from CallTouch to Google BigQuery
Santehnika-Online’s specialists use the Calltouch call tracking system to collect data about orders placed in the call center. With OWOX BI Pipeline the data about call events is automatically sent to Google BigQuery immediately after each call. When sending the event, the call receives a tag with the phone number at which the call was made.
Collecting the data from Calltouch in Google BigQuery enables the company’s analysts to segment user audience down to a keyword and understand what keywords bring customers to the website before they make a phone call. First, an analyst queries the data in Google BigQuery to create a segment of completed orders that were placed over the phone. Next, this data is used by the OWOX BI Attribution model to evaluate the channels in the customer purchase path.
Step 3. Send data about order completion rates from the CRM system to Google BigQuery
The data about products, order statuses, and margins is collected in the 1С:CRM system. This data is automatically collected in Google BigQuery on a daily basis, thanks to the integration created by the company’s programmers. To evaluate the efficiency of advertising, the company merges the data about order completion rates, website user behavior, advertising costs, and orders placed in the call center.
Step 4. Combine the data in Google BigQuery
To get the full view of how the company’s advertising initiatives perform, the data from Calltouch, Google Analytics and 1C:CRM should be brought together. To facilitate this task, OWOX BI and Santehnika-Online’s analysts together created SQL queries for merging the data in one Google BigQuery table. Combining all the available data enables the company to track the full customer journey across all touchpoints, from visiting the website and making a call to the purchase.
Step 5. Visualize advertising performance data
OWOX BI analysts helped the company create an informative Google Data Studio dashboard based on the data about advertising costs combined in Google BigQuery. The dashboard looks as follows:
The dashboard makes it easier for Santehnika-Online’s marketing specialists to compare the advertising performance indicators: cost, revenue, number of sessions, CTR and ROAS, broken down by the source, region, and ad campaign. As a result, the marketers can easily identify discrepancies in campaign performance for the same types of campaigns in different regions. This makes it way easier to eliminate errors made during the setup, should there be any. Let’s say for example, a campaign targeted at the audience in Moscow was then duplicated for St. Petersburg. The new campaign appeared to be suspiciously less cost-efficient, with more money spent on the campaign and much less revenue generated. By checking the keywords through which users arrived to the website, marketers could see that in some ads, broad match keywords were used instead of phrase match and exact match keywords, which resulted in money wasted on irrelevant traffic.
Step 6. Evaluate advertising campaigns
To evaluate the performance of advertising, Santehnika-Online’s analysts used OWOX BI Attribution to create a funnel based attribution model based on the consolidated data from Google BigQuery. The main benefit of this attribution model is that it takes into account the contribution of each session before the order. The company’s purchase funnel looks as follows:
The chart demonstrates the step conversion rate The percentage of sessions in which customers moved on through to another step , probability The percentage of sessions in which customers navigated to the given step from another one and value for each user step in the purchase journey. The value of each session depends on the probability of the user moving on through from one step of the funnel to another. The lower the probability, the higher value is assigned to the session which helped the customer take the next step, and vice versa. In this example, the «Purchase» step is the most valuable one, and it’s also the most difficult for customers to take. The probability that a customer moves on through to this step is 6.9%. The model assigns value to all sessions that contributed to the purchase. For more information about the logic of calculations in the funnel based attribution model, please read our blogpost or watch the webinar replay.
Thanks to using the funnel based attribution model, Santehnika-Online’s marketing specialists were able to identify inefficient campaigns and optimize the allocation of advertising budget resources.
Step 7. Send calculation results from the attribution models to adjust bids in the Alytics platform
As a result of collaboration between OWOX BI and Santehnika-Online’s teams, the results calculated by the attribution model are automatically sent from Google BigQuery to Alytics. Santehnika-Online was among the first companies to participate in the testing of the integration between OWOX BI and Alytics. To learn more about the integration, see our Help Center. As a result, the OWOX BI team developed a custom solution for Santehnika-Online: the results of calculations from the attribution model are first sent to the access point using the Apps Script, and then imported to Alytics on a daily basis. The company’s analysts use Apps Script to enhance the data from the attribution model with the values from the AdContent dimension obtained from the OWOX BI Pipeline hit data tables in Google BigQuery. The AdContent dimension provides the first line of the ad campaign text.
The data imported from Google BigQuery into Alytics has the following structure:
Next, Santehnika-Online’s analysts determine rules to calculate bids in the Alytics interface. The formula is used to identify inefficient campaigns that, over the past 30 days, resulted in:
- More than or equal to X Google Analytics sessions, and
- The ROAS of less than or equal to Y%, and
- Less than or equal to Z phone calls in the Calltouch statistics.
The information about inefficient keywords is then passed on to the company’s contractors responsible for contextual advertising.
- Thanks to OWOX BI Pipeline, Santehnika-Online automated the process of consolidating the data needed for analysis. The data about website user actions, orders placed over the phone, advertising costs, margins and order completion rates is now collected in Google BigQuery.
- With the help of OWOX BI Attribution, the company was able to accurately evaluate advertising channels and optimize budget allocation.
- Thanks to the integration between OWOX BI and Alytics, Santehnika-Online can automatically apply the results calculated by the attribution model to manage bids in the Alytics platform.
- As a result, Santehnika-Online managed to increase paid advertising traffic to the website and improved the advertising-to-sales ratio Advertising-to-sales ratio is calculated by dividing total advertising expenses by sales revenue. This measures the effectiveness of advertising campaigns by more than 10%, as shown on the chart below:
The chart demonstrates that the average advertising-to-sales ratio for the period January—May 2017 is 11.72%. After the redistribution of the company’s advertising budget resources, the average advertising-to-sales ratio for June—October 2017 decreased by 10% as compared to the previous period, and accounted for 10.54%. At the same time, the amount of traffic from paid advertising sources for the time period increased more than threefold.
P.S. Any questions or thoughts? Let us know in the comments below. And if you enjoyed this post and you think your friends would find this useful, please share it with them.