How To Track The Effectiveness Of Advertising Campaigns In Relation To Sales Of Specific Product And Manufacturer
eye

Лена Б 1080.png

Head of Digital Analytics department

Helen

THE CLIENT: DEKA

OUR GOAL: To increase the effectiveness of advertising campaigns in the context of certain brands and products (brand + product category segments).


OUR TASK: To find out which channels are more effective for the promotion of certain brands and products, taking into account several user’s interactions with different sources on their way to conversion.


THE PROBLEM: Google Analytics interface doesn’t allow us to combine the information about multi-channel sequences and product information in one report. This, in turn, makes it impossible to track the effectiveness of advertising campaigns in relation to the sales of a specific product made by a particular manufacturer.


THE SOLUTION.

  1. To separately unload:
  • The product’s data.
  • The data on paths to purchase.
  • The data about the channels’ effectiveness in the context of various attribution models.

2) To combine the data into a single system.

3) To visualize the data in a dashboard.


THE TOOLS. Google Sheets, Google App Script, Google Analytics Spreadsheet Add-on, Google Data Studio.


THE RESULT. Convenient interactive dashboard containing the information on the effectiveness of different sources and channels. The dashboard is updated automatically. The report includes filters that allow choosing separate brands or products, after which dashboard automatically displays the data according to the parameters you specified. There’s also an opportunity to calculate ROI in the necessary context and in conjunction with a website segment with an advertising source.

The number of transactions for the chosen period.png

The Task

As we know, in most cases, a client makes more than one interaction with your website before making a purchase.

That’s why it’s important to analyze which channels interact with each other, how they do that, and how they affect sales, to optimize marketing activities. The easiest but, unfortunately, not the most accurate way to do that is to analyze the reports on multi-channel conversion (paths to the conversions, the comparison of attribution models). To get this data, you have to install the Google Analytics tracking code and set up Ecommerce or, at least, a goal.

However, in some cases, a business is interested in finding out which channels are effective for selling specific brands and products.

Setting up Enhanced Ecommerce allows seeing which brand or product is purchased and which traffic source is the last on a user’s path to purchasing a certain amount of that product.

x2_skrin.width-1110.png.pagespeed.ic.zxy_UGQXLh.png When we wrote this article (in November 2018), the Google Analytics interface for Multi-Channel Funnels and Attribution reports didn’t allow us to pick a product or a transaction identifier as an additional parameter.

How Did We Solve The Task?

We developed the following plan.

STEP 1. We identified the metrics and indicators we needed to solve the client’s business tasks.

STEP 2. We identified those required indicators and metrics that were interconnected.

STEP 3. We started looking for ways to collect and combine the data we needed.

STEP 4. We visualized the collected data into a convenient dashboard.

Now let’s focus on each step separately and tell you in detail what exactly we did.

Step 1 and Step 2

We identify which metrics and indicators we need to solve the client’s business tasks. We also identify how they are connected.

To implement the first and the second steps, we:

  • Interviewed the DEKA representative to identify the needs.
  • Formulated an analytical task after receiving a business task and additional entry data.
  • Created a preliminary data scheme that included:

— A list of required parameters and metrics.

— A map of connections between parameters and metrics

Step 3

Looking for ways to collect and combine the required data.

As known, Google Analytics has an API to work with your data without using the web interface of the service.

Google provides 6 types of APIs for work with different types of data.

Screenshot_7.png

To solve our task, we need to use 2 types of API:

  1. Core Reporting API: it allows us to get parameters and indicators for report creation. You can use it to get the Ecommerce data, the data on user behavior, traffic, and many other types of data. However, you cannot use it to get the data on multi-channel sequences.
  2. Multi-Channel Funnels Reporting API: it provides data to build reports on multi-channel sequences to analyze complicated paths to transactions and conversions.

One of the easy ways for non-programmers is to get data with the help of Google Analytics API and use Google Analytics Spreadsheet Add-on. This add-on enables us to get data from the APIs that interest us.

There are so many good articles on how to use Google Analytics Spreadsheet Add-on in the context of Core Reporting API; we are planning to release an article on how to use Multi-Channel Funnels Reporting API soon. That’s why in this article, we don’t focus on how to work with this add-on.

Let’s get back to our task.

We got the data we needed with the help of three queries. Then we used the Google Spreadsheets functions to merge the data.

An example of data scheme #Ecommerce Data Parameters_.png
An example of queries created with the help of Google Analytics Spreadsheet Add-on x5_skrin.width-1110.png.pagespeed.ic.ESzxBX1Eel.png

Below you can see a short description of configuration parameters we used to solve our task:

Report Name — the name of the report and the sheet where the information will be stored.

View ID — the identifier of the presentation from which we’ll get the data.

Start Date and End Date — date of the beginning and the end of the period for which we have to get the data.

Metrics — indicators that we have to unload.

Dimensions — parameters that we have to unload.

Filters — data filter.

Limit — the maximum number of rows in upload (there are limits).

Report Type — a type of API used in a query. It has two types: core and mcf. If the field is empty, the core type is used by default.

Then we create a spreadsheet where we specify the details for every transaction.

Дизайн без названия (2).png
Лена Б 1080.png

Head of Digital Analytics department

Helen

There’s one feature that you have to keep in mind when you work with Multi-Channel Funnels Reporting API: you can work only with a 30-day retrospective view window. Respectively, such a way to receive data won’t work for you if it takes more than 30 days for your clients to make a purchase decision, and all the transactions that go beyond this period are important to you.

Wherein, when you unload the data on last indirect click with the help of Core Reporting API, the previous indirect click will display correctly even if it was made more than 30 days before conversion.

That’s why some transactions contain sources different from direct ones in their ‘last non-direct con’ unload, while in the MFC unload, this conversion will contain only direct transitions. However, the following situation is more common: when ‘last non-direct con’ upload contains a source/a channel listed as the last indirect one, while in MFC unload, the same channel is listed as either associated or first/last one. To not lose any sources, we have to pick all the ‘transaction identifier — source/channel’ unique pairs from two spreadsheets: MFC and ‘last non-direct con.’

As we have to select the data on a transaction and all sources that took part in it without any duplicates, we use the following formula:

=UNIQUE(QUERY({'last non-direct con'!B16:C;MFC!B16:C}; "Select * where Col2!=''"))

  • UNIQUE() — this function returns unique rows in the select range, removing all duplicates.
  • QUERY(data; query) — this function runs queries based on Google API visualization query language. This is an SQL similar language.
  • {Source 1; Source 2} — the data for queries from several sources.
  • "Select * where Col2!=''" — a query that selects all the data rows under streaks condition.

After we receive the unique parameter pairs, we’ll have to fill in the significators for each one of these pairs.

Let’s take the obtained significators for the last indirect click as an example. To do this, we use the formula that does the following:

For the C2 cell: This formula works with all non-empty rows and checks if there’s a match of pair of parameters in the ‘last non-direct con’ unload. If there’s a match, we have to add several transactions for the last indirect click for this parameter pair; if there’s no match, we have to list ‘zero.’

Here's how the formula looks:

=ARRAYFORMULA(IF(A2:A<>"";IFERROR(ARRAYFORMULA(VLOOKUP(A2:A&B2:B;{'last non-direct con'!B:B&'last non-direct con'!C:C\'last non-direct con'!D:D};2;0));0);""))

  • ARRAYFORMULA() — it allows you to use arrays in functions that aren’t intended for this.
  • IF(source; compliance_value; non-compliance_value) — it allows us to set up the conditions for formula usage.
  • IFERROR(value; [error_value]) — error handling formula.
  • VLOOKUP(query; range; index; [sorted]) — runs a search for the first column of the range and returns the value from the found cell.

Let’s take a closer look at the part of this formula:

ARRAYFORMULA(VLOOKUP(A2:A&B2:B;{'last non-direct con'!B:B&'last non-direct con'!C:C\'last non-direct con'!D:D};2;0))

  • As the VLOOKUP function can run a search only for one value, we combine the parameters in final and source spreadsheets into one:

A2:A&B2:B

'last non-direct con'!B:B&'last non-direct con'!C:C

  • We have to return the value of the D column under the given conditions. To do this, we create a data array and group a combined parameter in it with a column from the spreadsheet:

{'last non-direct con'!B:B&'last non-direct con'!C:C\'last non-direct con'!D:D}

  • 2 - indicates which column from the group should be returned on the match.
  • 0 - indicates that the parameter match should be exact.

This is similar for other values.

As a result, we obtain the following table:

x7_skrin.width-1110.png.pagespeed.ic.1Cw4sSJrWa.png

Then we use the script to combine a transaction with its products and their parameters and save it to the archive. Using transaction 100602 as an example, we’ll get the following as a result of the script:

x8_skrin.width-1110.png.pagespeed.ic.M6Ju2ROocI.png

The final spreadsheet might look quite weird as it should be used for visualization in Google Data Studio.

Our final step when setting up the data collection for the report is to set up the timers for the script and data upload from Google Analytics.

Step 4

The final step is the visualization of the data with the help of Google Data Studio

The number of transactions for the chosen period.png

The visualization process requires you to be attentive when selecting an aggregation type. For instance, for ‘the number of transactions for the selected period’ chart. We take the transaction identifier as an indicator and count its quantity without duplicates, as the transaction identifier is unique.

x9_skrin.width-1110.png.pagespeed.ic.Z2gNXWTsCT.png

The Conclusion

Some might ask why cannot we use the combined data in Data Studio? The data combination service is a recent addition to Google Data Studio and still has several limitations. For instance, it’s not always convenient to use the combine data, because technically a combined data source is a product of ‘left outer join’ operation (which is familiar to those who work with SQL). Furthermore, it was easier and quicker for our team to write a script.

However, if you don’t have any experience in scriptwriting, you can create combined data using Data Studio. This tool will eliminate the need to manage the data first, saving your time and efforts.

In conclusion, we’ll tell you about the pros and cons of our setup.

The Pros:

  • The reports are generated automatically.
  • You can see the data in a new context.
  • It allows us to save person-hours and eliminates the mistakes that could be caused by the human factor.

The Cons:

  • This implementation suits those businesses with a decision-making period of fewer than 30 days; or those businesses who are willing to sacrifice the data and statistics for a more extended period.
  • Google Spreadsheets enable you to store up to 5 million cells in one document; therefore, you need to calculate how long your data storing spreadsheet will function. Maybe, it’ll last for years, perhaps, only for a couple of months.

As you can see, Google Sheets, Google App Script, Google Analytics Spreadsheet Add-on, and Google Data Studio allow you to set up a base for solving non-trivial tasks.