How To Uplift Data Based On Cookie Opt-In Rates in Data Studio

How To Uplift Data Based On Cookie Opt-In Rates in Google Data Studio

Written by Conor Ross

Conor is a Digital Analytics Specialist, having previously studied Psychology and an MSc in Research Methods at the University of Dundee, followed by a Marketing MSc at Queen’s University Belfast. He works particularly closely with the Google Data Studio and Google Optimise platforms.
January 29, 2021

 

The topic of cookies and opt-in rates is becoming increasingly important in the world of digital analytics as we navigate 2021 and beyond, with Google Analytics tracking often not considered a “Strictly Necessary” cookie, and as such falls into the opt-in requirement. If a user doesn’t opt-in to these cookies, it can result in a significant proportion of your traffic not being tracked in Google Analytics.

This cookie drop-off is certainly causing some concern within the industry, but we’re here to help with a simple method of projecting and modelling your data through Google Data Studio that can help alleviate some of these headaches. Essentially, this is a form of manual sampling using a multiplier field, and while not perfect, definitely helps to produce a better representation of your website’s performance.

It’s also important to note that adjustments and amendments to your data using Data Studio are only surface level changes, so they won’t affect your underlying data source and the raw data found within. So, if you change your mind or make a mistake, there’s nothing to worry about!

 

Uplifting Your Data, But By How Much?

 

The following method of implementing cookie uplift first involves taking your unweighted data from Google Analytics, as you would normally do so for a Data Studio dashboard around your GA data. Once this data source is connected to Data Studio, we can get to work. As cookie opt-in rates change on your website over time, the proportion of the total website traffic that is represented and recorded in Google Analytics changes. At one stage your opt-in may have been close to 100%, but with changes to policies and browsers, this has fallen to 80%, 60%, or lower.

The key to this cookie uplift in Data Studio is knowing what your opt-in rate is, and knowing how that changes over time. If my opt-in rate for December 2020 has dropped to 50%, then I need a multiplier of 2 to uplift my data to a more accurate representation of the true website performance. If my opt-in increased to 60% the following month, I’d need a multiplier of 1.667, and so on. You can calculate your required multiplier by dividing 100 by your opt-in rate; it’s as simple as 100 / X.

There are multiple ways you can find this information, with the most accurate source being a cookie consent management system like OneTrust, CookiePro, or CookieBot, but an estimation based on other factors and observations from your analytics is also suitable, although less accurate. A daily rate would offer the most accurate multiplier, but practically speaking this probably isn’t necessary given that opt-in is unlikely to fluctuate significantly on a daily basis. We recommend recording the opt-in monthly, but you’re free to do this weekly, quarterly, even yearly. The general method doesn’t change, but the amount of manual work required will increase if you want a more detailed opt-in. Whatever you’ve decided, we can begin to formulate our uplift multiplier in Data Studio with this opt-in rate data.

 

Modelling Your Missing Data in Data Studio

 

There are three steps to putting together your multiplier in Data Studio:-

  1. Creating a custom field for a variable opt-in rate
  2. Using your variable opt-in rate to create an uplift multiplier
  3. Creating uplifted metrics for your Data Studio dashboards

Strictly speaking, you could combine the first and second steps into a single field, but this way you can display your opt-in rate clearly for any users of your dashboard as well as the resultant multiplier value, and it doesn’t require any additional work when it comes to updating your formula on an ongoing basis.

This method allows you to account for variability over time in your opt-in rate, which is a really useful foundation to have in place, even if it may not seem important now.

 

1. Creating A Custom Field For A Variable Opt-In Rate

 

We’re going to create a CASE statement that applies an opt-in rate to a designated time period – in this case, monthly. This statement will essentially say, if the month is Y, then the opt-in rate is X. Let’s call this new field Cookie Opt-In Rate. In this example we’ll use December 2020 as our month, from the Month Of Year dimension that’s included with a Google Analytics connected data source, and use a cookie opt-in rate of 50%.

CASE

WHEN CONTAINS_TEXT(Month of Year, "202012") THEN 0.50

END

We can expand on this statement to include additional months and information, as well as project and model future data, until we have a more definitive opt-in rate for those time periods. Let’s say that we had this 50% opt-in for the last three months of the year, and before that was a 60% opt-in. And for 2021 we anticipate our opt-in to remain at 50%. We can quickly add lines to this statement to account for this:

CASE

WHEN CONTAINS_TEXT(Month of Year, "2021") THEN 0.50

WHEN CONTAINS_TEXT(Month of Year, "202012") THEN 0.50

WHEN CONTAINS_TEXT(Month of Year, "202011") THEN 0.50

WHEN CONTAINS_TEXT(Month of Year, "202010") THEN 0.50

WHEN CONTAINS_TEXT(Month of Year, "202009") THEN 0.60

END

Now we’ve created a formula to populate our custom field, Cookie Opt-In Rate, and this allows us to quickly add in our data for cookie opt-in rates, both historically and for the future. We are yet to use this to create any uplift or weighted metrics, however. We’re going to use this custom formula to populate our multiplier. This step is important as it’s easier to update on an ongoing basis, and also allows us to show the opt-in rate as a percentage within the report, which may be critical to helping users see the cookie acceptance over time, as well as understand the process of creating the cookie uplift multiplier.

 

Variable Cookie Opt-In Rate Displayed Over Time

Variable Cookie Opt-In Rate displayed over time

 

 

2. Using Your Variable Opt-In Rate To Create An Uplift Multiplier

 

The next formula is a much more simple one, and shouldn’t require any manual updates once it’s been created. Make sure you remember what you’ve named your custom field from the previous step, in this example, it’s Cookie Opt-In Rate. Create a new field, that contains the formula:-

AVG((100 / Cookie Opt-In Rate ) / 100)

In terms of the calculation itself, the inclusion of AVG() here isn’t important, the real formula is ((100 / Cookie Opt-In Rate) / 100), but Data Studio will create a dimension here by default rather than a metric, which we’ll need to create our uplifted metrics later on. You can use a range of mathematical terms here, as long as the custom field results in a metric rather than a dimension. You can check this in your data source by looking at the colour of your new field: Dimensions are highlighted in green, and metrics highlighted in blue.

Let’s look at our example again of December 2020. We’ve already indicated that the cookie opt-in rate was 50%, so we should need a multiplier of 2 to uplift this data to a modelled 100%.

(100 / 0.5) / 100          =          200/100          =          2

 

Cookie Multiplier Calculated Using Your Opt-In Rate

Cookie Uplift Multiplier calculated using your Cookie Opt-In Rate

 

As you can see, the value we’ve entered as our Cookie Opt-In Rate is then used to to calculate our multiplier.  You shouldn’t need to make any changes to this formula, but don’t forget to name it something suitable, like Cookie Uplift Multiplier. This multiplier will work dynamically based on the dates attributed to the data you’ve selected, so can apply different multiplier values across different dates within your dataset. That’s what makes this stand out from a simple constant value multiplication.

 

3. Creating Uplifted Metrics For Your Data Studio Dashboards

 

Our last step is to use the Cookie Uplift Multiplier to create our new weighted metrics. You’ll need to create a formula for each metric unfortunately, but this is really simple. Let’s use Sessions as an example. You can create a new field at the widget level, but we recommend creating this at the data source level as this will save it for future use across the rest of your dashboard. Your new weighted metric will be calculated like this:-

Sessions * Cookie Uplift Multiplier

And it is as simple as that. Your new metric, let’s call it Sessions (Weighted) is now an adjusted measurement of your total sessions that accounts for your cookie opt-in rate and the missing data that comes with it. The functionality of this formula relies on both elements being classified as metrics by Data Studio, which is why we include the extra detail in the second step to ensure that’s the case. Let’s see how this looks over time:

 

Weighted Sessions Calculated Using Your Existing Data and a Multiplier

Weighted Sessions calculated using your Existing Data and your Cookie Uplift Multiplier

 

Comparison of Original Sessions metric and the uplifted Sessions based on cookie opt-in rates

Comparison of Original Sessions Metric and the Uplifted Sessions Based on Cookie Opt-in Rates

 

You’ll be using the same Cookie Uplift Multiplier field for all your weighted metrics, so you don’t need to worry about adjusting this each time. And don’t forget that you won’t need to uplift any ratio based metrics like Pages/Session or Conversion Rates because these won’t be affected by your opt-in rates.

 

Projected Data: A Step in the Right Direction

 

This method of uplifting your data based on cookie opt-in rates is a simple one to help you fill in the gaps within your existing dataset. It provides a more accurate representation of your site’s performance, but it’s not necessarily a full solution to the issues of cookie consent and a loss of tracking capabilities. However, we hope this certainly helps to reduce some of the noise created by any drop-off caused by changes to cookie policies and allowances, and in such a way that is very easy to amend and update, given that you’re not making any irreversible edits to the set-up of your accounts or tracking. You’re simply uplifting your existing data to create a more accurate picture of your site, and to help you make more informed decisions based on that data.

If you’d like to know more about creating a multiplier to account for cookie opt-in in Google Data Studio, or other information on creating dynamic data dashboards, please feel free to leave a comment or contact us at [email protected]. You can also follow us on Twitter @GlowMetrics for more tips, guides, and up to date news in the world of Digital Analytics and Marketing.

 

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Enjoyed reading this article? Find more like it below…