loading...

Getting Google Search Keywords into Adobe Analytics

frederikwerner profile image Frederik Originally published at fullstackanalyst.io on ・11 min read

While Adobe Analytics is a much more mature solution compared to Google Analytics, the latter always had an advantage when it comes to Search Keywords. It shouldn’t surprise us that the company who offers both the search engine and the analytics tool has some integration between them. While it was easy to get search keywords from the target URL in the past, those times are gone for years now. Ever since then, business were struggling to know what their visitors were initially looking for when they came to their webpage. This article outlines a couple of ways on how to achieve this in Adobe Analytics.

For this post we will take a look at the integration Adobe offers to Analytics Prime customers called Advertising Analytics. Right after that we are going to build our own integration based on the same method to get some insight into Google Ads performance. To round things off, we will build a way to get natural search performance data into Analytics as well.

The Adobe way: Advertising Analytics

If we were to ask Adobe on how to get Keywords in Analytics, the obvious answer would be Advertising Analytics. It’s part of the Prime subscription layer and offers a way to get your Paid Search performance for your website. It imports campaign data like Ad Groups or Campaigns into some dedicated dimensions together with some specialized metrics like Cost or Impressions.

The setup is documented quite well and not very complicated. Basically we only need to login to Google Ads from Adobe Analytics to connect our Ads account to a Report Suite. This will enable the extra Dimensions and Metrics within Analytics. To tell Analytics which Campaign was used on a campaign clickthrough, Analytics will also set up or edit the Tracking Template in Google Ads.

This is the interesting part with this integration path. A Tracking Template is used whenever we want to pass campaign information from Google Ads to the landingpage of you campaign. Let’s have a look at the way Analytics extends the Tracking Template. It adds this to your URLs:

s_kwcid=AL!ID!3!{creative}!{matchtype}!{placement}!{network}!{product_partition_id}!{keyword}

With this part in the URL we will have an URL parameter s_kwcid. The value will be a long string containing different campaign information like the Creative, Network, and our desired Keywords separated through exclamation marks. If a tracking event occurs, Analytics will take that information from the page URL, take it apart and receive data from Google Ads for the IDs included in the URL. Based on the URL parameter and the data from Ads, Analytics classifies the tracked ID called AMO ID.

On a technical level, Analytics uses Classifications and a Data Source to import campaign information and metrics. With the AMO ID, an export from Google Ads and some processing, we would be able to basically build this ourselves. But even if we don’t need all the dimensions and metrics that Advertising Analytics offers we can use some of the same methods.

Paid Search Keywords in Adobe Analytics

So, let’s get our hands dirty! Our goal is to get some insight into the Keywords that were used when someone clicked on a paid link on Google. First, prepare your Report Suite with a custom prop or eVar to capture the Keywords. As always, give it a descriptive name and define, how long the value should be persisted if you opt for an eVar. A lot of people choose a Visit expiration (or even longer) to give credit for the onsite actions to that keyword.

Next, we head to Google Ads and change our Tracking Template to include the Keywords in a parameter creatively called “keyword”, or any other parameter you like, so it includes this:

keyword={keyword}

There are two ways to get that keyword data into Analytics. If you can’t or don’t want to set this up in Adobe Launch, you could use Processing Rules to take the URL parameter and write it to the prop or eVar you prepared. This is independent from your Devs and can be done quite quick. An example processing rule would look like this:

While this is possible to do, we could also use a Data Element within Adobe Launch to capture the Query Parameter. This would also allow us to use it for other purposes outside of Analytics. This is how we would use Launch to capture our Keywords based on the Tracking Template:

Done! Believe it or not, but that is all we need to do to know which keywords brought a visitor to our website. We could extend this concept to rebuild the Advertising Analytics integration all on our own! All we would need are some IDs from Google Ads, an export script and a data source to import the data. Look at the next part below for detailed information on how to do this.

With this type of integration we not only have access to the keywords used for our Paid Campaigns but also connect them to other onsite activities. For example, you are able to look at sales or newsletter signups in relation to the search keywords used before landing on your site. You could also build segments for keyword clusters or look at product views depending on what your users where looking for. Keywords are just another Analytics dimensions now, so get creative!

But we won’t do this in detail for this article because there still is some more to talk about. Paid Search keywords are nice, but what about our visitors who did not need an ad to find our website? Let’s look at how we can get some information about those as well!

Natural Search Keywords in Adobe Analytics

Google offers a way to know how your website is performing in it’s search. That feature is called Google Search Console and completely free! In there you will find the Clicks and Impressions of your website together with the average position in the results for Organic Keywords. It can do that for certain pages of your websites or in total. Of course it also tells you which Keywords have been used in total and per page URL. That gives us some interesting options to integrate that data into Analytics. Keep in mind: Those imported values will not be connected to your onsite activity measured with Analytics, so you cannot break them down by other dimensions except those included in the import!

The first option is to just import Search Clicks and Impressions over time that we exported from the Search Console. If you don’t look at search performance at all, this will already be a huge improvement. In addition to that, we can have our export contain Keywords as well. Finally, we can include both Keywords and the landing page. But how would that help us?

Let me have a wild guess: You will have one or more dimensions in Analytics to capture the URL of the pages on your website already. It’s very common to track this together with the page name or even link tracking events. With that information already available, we can use a Data Source to give us the keywords for a certain page! Let’s build it!

Method 1: Search Performance Summary Metrics

We will start with the most simple way. For this first method, we are going to fetch total Clicks and Impressions from the Google Search Console API with Python and import it to Analytics using a Data Source.

To get data from the Search Console API, follow the tutorial from the Google API documentation. At the end of that tutorial, you should have successfully queried the API and got your data in some different formats. Change the search_analytics_api_sample.py to include a query like this, which gives the total search performance over time and prints it to the console:

  request = {
      'startDate': flags.start_date,
      'endDate': flags.end_date,
      'dimensions': ['date']
  }
  response = execute_request(service, flags.property_uri, request)
  for element in response["rows"]:
      print (element)

For a small demosite, this gives me and output like this:

Awesome! That’s all the data we need for this first method. Now head over to Analytics and configure three success events (or four, if you don’t want to calculate the Click Through Rate yourself). Since those numbers are the total performance over time, name them appropriately. Mine look like this:

To hold the type of search data, create an eVar to hold the type of data. This is mine:

Now we head over to the Data Sources section in the Analytics Admin Interface. Create a new Data Source, using “Generic” as the Category and “Summary Data Only” as the Type. I name the Data Source “Search Summary Data Demo”, this is my Metric Naming and Mapping:

Complete the wizard and map your data type variable to the eVar created before. Take note of the FTP connection details. If you want, you can also download and inspect the File Template to know how the files will look like we will upload.

Next we need to bring our Search Console data into the format Adobe Analytics requires for our Data Source. In the file we used before, we will iterate through the result and write it to a tab separated file. We only need the csv module from python for this. Change the filename if you like, but be sure to put the number of your Search Type eVar and Success Events in instead of the placeholders:

import csv

...

request = {
      'startDate': flags.start_date,
      'endDate': flags.end_date,
      'dimensions': ['date']
  }
  response = execute_request(service, flags.property_uri, request)
  outputFile = open('summary.txt', 'w')
  writer = csv.writer(outputFile, delimiter='\t', quoting=csv.QUOTE_NONE, lineterminator="\n")
  writer.writerow(["Date","Evar [number of your type eVar]","Event [number of your clicks event]","Event [number of your impressions event]","Event [number of your position event]"])

  for element in response["rows"]:
    writer.writerow([element["keys"][0][5:7]+"/"+element["keys"][0][8:10]+"/"+element["keys"][0][0:4]+"/00/00/00","summary",element["clicks"],element["impressions"],element["position"]])

This will go trough our received data and write a line for each day of data to our output file. Try and upload this file to Adobe Analytics using the FTP connection details we noted before. Remember to also upload an empty file with the same name and the .fin extension (so “summary.fin” in our case) to let Adobe know the file has been uploaded completely. After some time of waiting, we will see something like this in Analytics with my random demo data:

Done! If you like, extend the script to also do the FTP upload for you. Be careful to not upload the same day twice, as that will double your metrics. But don’t worry if you do, you can just upload some negative values to correct it.

Method 2: Search Performance Keyword Metrics

Now that we can pull aggregate performance data to Analytics, let’s extend it to also include the daily keyword performance. First, we need to modify (or extend) our Python script. All we need to add is the “query” dimension, so our request looks like this:

  request = {
      'startDate': flags.start_date,
      'endDate': flags.end_date,
      'dimensions': ['date','query'],
      'rowLimit': 25000
  }

That’s all we need to do for the request. Repeat the steps outlined above to create a new “keyword” eVar in Analytics to hold the search keyword information, if you don’t already have one. For this example we will also use three new events, so we can have both total and per-keyword level metrics. Don’t worry, we can reuse the same Data Source without modification. Now we need to slightly modify our script to also include the keywords in the output files. Replace the placeholders like before:

  outputFile = open('keywords.txt', 'w')
  writer = csv.writer(outputFile, delimiter='\t', quoting=csv.QUOTE_NONE, lineterminator="\n")
  writer.writerow(["Date","Evar [number of your type eVar]","Evar [number of your keyword eVar]","Event [number of your keyword clicks event]","Event [number of your keyword impressions event]","Event [number of your keyword position event]"])

  for element in response["rows"]:
      writer.writerow([element["keys"][0][5:7]+"/"+element["keys"][0][8:10]+"/"+element["keys"][0][0:4]+"/00/00/00","keywords",element["keys"][1],element["clicks"],element["impressions"],element["position"]])

Repeat the steps from the previous method to upload that new keywords.txt file to Analytics. After some processing, it will give you some nice data like this:

That’s an amazing achievement already! Now we can see how certain keywords perform over time for our website. But there still is more.

Method 3: Search Performance Keyword and URL Metrics

For the last method, we are going to use one of the actual onsite dimension. We will use the URL dimension I mentioned above together with the keywords. By combining both, we will see which pages on our website perform best for which keywords. That information can then be used for segmenting on the entry URL.

If you like, you can again add three new success events in Analytics to hold our Search performance broken down by keyword and URL. Since this is the most granular level we will go into, you could only add those three events and aggregate them by keyword or date without the need for dedicated events for totals.

Once again we modify our script to also include the landing page URL. As always, don’t forget to change the placeholders to your eVars and events:

  request = {
      'startDate': flags.start_date,
      'endDate': flags.end_date,
      'dimensions': ['date','query','page'],
      'rowLimit': 25000
  }
  response = execute_request(service, flags.property_uri, request)
  outputFile = open('url_keywords.txt', 'w')
  writer = csv.writer(outputFile, delimiter='\t', quoting=csv.QUOTE_NONE, lineterminator="\n")
  writer.writerow(["Date","Evar [number of your type eVar]","Evar [number of your keyword eVar]","Evar [number of your URL eVar]","Event [number of your keyword+url clicks event]","Event [number of your keyword+url impressions event]","Event [number of your keyword+url position event]"])

  for element in response["rows"]:
      writer.writerow([element["keys"][0][5:7]+"/"+element["keys"][0][8:10]+"/"+element["keys"][0][0:4]+"/00/00/00","url+keywords",element["keys"][1],element["keys"][2],element["clicks"],element["impressions"],element["position"]])

Upload the resulting url_keywords.txt to Analytics like before, using the connection details and a .fin file. Wait till Analytics has finished processing and take a look at what we have now in Analytics:

Looks awesome! Now we know which keywords are used for certain landing pages. We can trend them over time and optimize our pages for certain search queries. Optimization ahead!

Conclusion

In this tutorial we learned how to integrate Google Ads and Search Console data into Adobe Analytics. We used the Search Console API in Python and a Data Source in Analytics. The resulting data allows us to optimize our website to better suit the most viewed search terms.

With the process we used here, we can integrate all kinds of marketing tools or offline data sources in the same way. I’m curious what you can build on your own with the methods outlined above 😊

The post Getting Google Search Keywords into Adobe Analytics appeared first on The Full Stack Analyst 📈.

Posted on May 16 by:

frederikwerner profile

Frederik

@frederikwerner

Data Scientist roaming the realms of Web Analytics and Online Marketing Tech

Discussion

markdown guide