--- title: "Optimizing ad campaigns using assisted conversions" canonical: "https://www.owox.com/blog/success-stories/optimize-advertising-campaigns-with-assisted-conversions" updated: "2026-10-12" --- # How to optimize advertising campaigns with assisted conversions in Google BigQuery Updated April 14, 2017 · [Alyona Samovar](/team/alyona-samovar) · 5 min read · Retail 80% · of buyers touch multiple channels ![How to optimize advertising campaigns with assisted conversions in Google BigQuery](https://cdn.owox.ai/www/webflow/6794460065388db00eb5d10b_39632.png/public) The more advertising channels the businesses develop, the more customers could be acquired by different campaigns. Accordingly, the revenue generated will grow considerably if the business users get valuable data insights to improve the marketing performance. In this case, we describe the solution provided by the OWOX team for a large consumer electronics and home appliances retail chain that had challenges with optimizing advertising campaigns for different user segments over different platforms. ## Goal The company was looking to optimize advertising spendings while taking into account a number of factors: the contribution of multiple advertising channels to the customer’s purchase journey, a business group of categories (BGC), and differences in user behavior among various regions. A business group of categories (BGC) is a group of similar product categories. For example, _Audio Equipment_ is a common name for MP3 players, earphones, etc. Segmentation by BGC is driven by the internal structure of the retailer: each department is responsible for one BGC. The behavior of customers from different cities or regions differs due to economic factors. That’s why the Media&CRM department segments orders by geography. ## Challenge According to the retailer’s experience, 80% of online and offline customers interact with multiple advertising channels before making a purchase. The Assisted Conversions report in Google Analytics allows for analyzing the effect of online channels on conversion paths. However, the report doesn’t allow for segmentation by region and BGC. This makes it impossible to fully assess the contribution of each channel. In addition, the company was looking to make better decisions, backed by full user behavior data. Google Analytics applies sampling if the volume of data exceeds 500 K sessions (100 M sessions in Google Analytics 360) for the reporting period. Also, it wasn’t possible to see the full picture of the conversion paths in the Multi-Channel Funnel reports, since the number of conversions each month exceeded 1 million. Given the scale of operations, such measurement errors drastically affected the quality of the retailer’s decisions. ## The Turning Point OWOX Data Marts See your first report built in real time. _15 minutes._ 1. Connect your data warehouse 2. Pick your metrics 3. Get a live Google Sheets report In the time it takes to write a ticket. Then imagine never writing that ticket again. [Book a Demo](/book-a-call) We'll use your actual use case ## Solution To compare the performance of advertising channels in the different _region—BGC_ segments, it was decided to collect user behavior data in a big data warehouse. OWOX suggested using Google BigQuery, as this service ensures [security](https://cloud.google.com/security/compliance), flexibility, and rapid processing of data. ### Step 1. Collect full data in Google BigQuery Website visitors’ behavior data is automatically imported to Google BigQuery from Google Analytics, thanks to the Google BigQuery Export feature. This feature is only available for Google Analytics 360 clients. AdWords campaign performance data is automatically imported to Google Analytics. [OWOX Pipeline](/product/data-connectors) helps import cost data for advertising campaigns to Google Analytics and also collects data on all the advertising costs in Google BigQuery. The internal system (ERP) stores data about the relationships between product categories and BGC. For example, _Microwave Ovens_ belong to the _Small Domestic Appliances (SDA)_ group, and _Laptops_ belong to the _Computers_ group. The analysts copy the data from the internal system to Google Sheets, and then import it to Google BigQuery using the [OWOX BigQuery Reports](/product/spreadsheets) add-on. The data collection flowchart is given below: ![Diagram: advertising services, AdWords and ERP data flow through OWOX into BigQuery, then Data Studio and Sheets.](https://cdn.owox.ai/www/webflow/6794463a9d94df0a770ddbb7_39625.png/public) ### Step 2. Process the obtained data The data required for the attribution model is stored in two tables in Google BigQuery. The first table stores data about purchases, user behavior, and advertising spending from Google Analytics. The other one stores data about relationships between product categories and BGC from Google Sheets. OWOX experts merged the data from these two tables by the Product Category ID, using the JOIN operation. The data was combined as follows: ![A data model joining product ID to product category ID and a business group of categories.](https://cdn.owox.ai/www/webflow/6794463b4e84f471f663feda_40641.png/public) The company wanted to see what channels users interact with the most often and in what sequence. The channels which most frequently bring the first two sessions in the conversion path would perform best at the upper stages of the funnel, as they help attract users to the website. The channels which most commonly bring the final two sessions before purchase would perform better at the lower funnel: they help buyers make the decisions. OWOX analysts suggested segmenting orders by the number of sessions to the transaction (1, 2, 3, 4, and 5+ sessions). Long conversion paths (5+ sessions) are treated similarly to the short ones: the main focus is placed on the first two and the last two sessions. These are the sessions during which a user learns about a product, and makes a buying decision. The sessions in between contribute much less, therefore they are analyzed all together. ### Step 3. Create reports The OWOX team set up automatic import of segmentation results to Google Sheets using the [OWOX BigQuery Reports](/product/spreadsheets) add-on and created a report. The report shows how advertising channels perform for different locations and different BGCs. For example, now it can be seen how often users visit the website by clicking Google ads at different stages of the funnel in long (5+ sessions) conversion paths. The company’s specialists can compare the performance of ads in Google and Criteo and see which of the channels would perform better at the upper, middle, and lower funnel, for each region-BGC segment. ![A Google Sheets pivot of business groups by path length, with session counts.](https://cdn.owox.ai/www/webflow/6794463abab6c1a69ec5ccc7_39629.png/public) To facilitate the work of the managers and marketing specialists, OWOX experts visualized the data in form of interactive [dashboards](/product/dashboards). Google Data Studio was chosen as a dashboarding solution, for the following reasons: * Dashboards are convenient to work with: data can be easily filtered by date or selected dimensions. * [Data sources](/data-sources) for reporting can be connected in just a few clicks. * Unlimited custom reports and dashboards can be created at no cost. As a result, the dashboard was received, displaying how purchases are segmented by ad source, region, a business group of categories (BGC), and the path length before the purchase. ![A dashboard of pie charts by source, region, business group and path length, above a top sources table.](https://cdn.owox.ai/www/webflow/6794463acd073ae4e67895cc_39631.png/public) ## Results As a result of collecting and processing data in Google BigQuery using Google Analytics 360 and OWOX, the company could evaluate the performance of advertising channels for different regions and BGC, and also visualized the results in Google Data Studio. This helped answer such questions as: * Which channels perform better at the upper funnel, middle funnel, and lower funnel? * Which channels perform better in a particular «region—BGC» segment? * Which «region-BGC» segment gets the most purchases? Now the plans are to review and reallocate the performance marketing budget in the second half of 2017 based on the test results. ## Your New Normal Turn your data into decisions. Governed data marts give you the clean foundation ML needs to actually work. * No AI hallucinations * Analyst-governed definitions * Every number traces to SQL [Get started free](https://www.owox.com/app-signup) ## Who wrote this ![Alyona Samovar](https://cdn.owox.ai/www/webflow/6799099d337aa8a997b03b56_Alyona-Samovar.png/public) [Alyona Samovar](/team/alyona-samovar) · Senior Digital Analyst Alyona Samovar is a Senior Digital Analyst who spent years at OWOX helping enterprise clients design analytics systems, build BigQuery reporting pipelines, and optimize marketing measurement. She specializes in SQL-based analytics, data modeling, and turning raw marketing data into actionable insights. Alyona writes about practical analytics workflows, BigQuery best practices, and data-driven decision-making. [Case Studies](/blog/topics/case-study) [Marketing Analytics](/blog/topics/marketing-analytics) [OWOX](/blog/topics/owox-bi) ## More stories [Avrora: Avrora: analytics transformation with OWOX · May 30, 2024](/blog/success-stories/avrora) [Citrus: Competitors’ prices impact sales: Citrus success · Mar 1, 2018](/blog/success-stories/citrus) [COMFY: Set up marketing analytics · Dec 12, 2018](/blog/success-stories/comfy) ## What users are saying ## What our clients say Real things real customers said — each quote pinned to a specific claim, straight from the quotes database. A9 · re: one source of truth ![Mark Simmons](https://cdn.owox.ai/www/webflow/6a7b298b2b9daab0ade631d7_mark-headshot.png/public) Mark Simmons · CMO, Pürblack® > _“We had six or seven different channels and no single source of truth — it was almost impossible, a lot of guesswork.”_ C7 · re: getting time back ![Nodari Rizun](https://cdn.owox.ai/www/webflow/6a7b298b2ca5055443187566_nodari-headshot.png/public) Nodari Rizun · Founder & CEO, Pürblack® > _“We regained time. And time is the one resource that never comes back.”_ E3 · re: golden data ![Nodari Rizun](https://cdn.owox.ai/www/webflow/6a7b298b2ca5055443187566_nodari-headshot.png/public) Nodari Rizun · Founder & CEO, Pürblack® > _“Golden data is clean data — the measure of reality, true and not false, that you can trust based on its source. If you have that, you're good.”_ ## Book a call You are booked. The invitation is on its way to , with the calendar invite and the meeting link. [Reschedule](#) [Cancel](#) ## Links Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link. - [Alyona Samovar](https://www.owox.com/team/alyona-samovar) — /team/alyona-samovar.md - [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md - [OWOX Pipeline](https://www.owox.com/product/data-connectors) — /product/data-connectors.md - [OWOX BigQuery Reports](https://www.owox.com/product/spreadsheets) — /product/spreadsheets.md - [dashboards](https://www.owox.com/product/dashboards) — /product/dashboards.md - [Data sources](https://www.owox.com/data-sources) — /data-sources.md - [Get started free](https://www.owox.com/app-signup) - [Case Studies](https://www.owox.com/blog/topics/case-study) — /blog/topics/case-study.md - [Marketing Analytics](https://www.owox.com/blog/topics/marketing-analytics) — /blog/topics/marketing-analytics.md - [OWOX](https://www.owox.com/blog/topics/owox-bi) — /blog/topics/owox-bi.md - [Avrora: Avrora: analytics transformation with OWOX · May 30, 2024](https://www.owox.com/blog/success-stories/avrora) — /blog/success-stories/avrora.md - [Citrus: Competitors’ prices impact sales: Citrus success · Mar 1, 2018](https://www.owox.com/blog/success-stories/citrus) — /blog/success-stories/citrus.md - [COMFY: Set up marketing analytics · Dec 12, 2018](https://www.owox.com/blog/success-stories/comfy) — /blog/success-stories/comfy.md