Skip to content

Instantly share code, notes, and snippets.

@norisk-marketing

norisk-marketing/norisk_smx-2019_negative_automation.ipynb

Created April 10, 2019 21:14
Show Gist options
  • Save norisk-marketing/07d0e44da75818c367374475c160eb47 to your computer and use it in GitHub Desktop.
Save norisk-marketing/07d0e44da75818c367374475c160eb47 to your computer and use it in GitHub Desktop.
Download ZIP
norisk_SMX-2019_Negative_Automation.ipynb
Display the source blob
Display the rendered blob
Raw
norisk_smx-2019_negative_automation.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"name": "norisk_SMX-2019_Negative_Automation.ipynb",
"version": "0.3.2",
"provenance": [],
"collapsed_sections": [
"mgdj0ug-hp7l"
],
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/norisk-marketing/07d0e44da75818c367374475c160eb47/norisk_smx-2019_negative_automation.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"metadata": {
"id": "Ftl_U-chfVC2",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"![SMX logo](https://user-images.githubusercontent.com/11938030/53285379-e4b50480-375f-11e9-8636-ce01060b3d08.png)\n",
"\n",
"\n",
"# Dear SMX Munich 2019 Attendants, \n",
"\n",
"Thank for your listening to my talk \"*The End Of Exact Match? Negative Automation for 2019*\" at SMX Munich 2019. We hope you enjoyed the presentation. \n",
"\n",
"Welcome to the norisk **code notebook** to copy & paste the mentioned ads scripts to your accounts. \n",
"\n",
"The slide deck can be found at: [https://slideshare.net/norisk](https://slideshare.net/norisk). \n",
"\n",
"Feel free to post any content with hashtag **#SMX19**.\n",
"\n",
"\n",
"\n",
"NOTE: the javascript code can NOT be run directly in this notebook, only the SQL commands in chapter 4.\n",
"\n",
"\n",
"---\n",
"---\n",
"---\n",
"\n",
"\n",
"![norisk Group](https://user-images.githubusercontent.com/11938030/51073029-4ebf9180-166b-11e9-9d9e-5bfc6b2e0bf5.png)  ![Chris](https://user-images.githubusercontent.com/6991865/51346860-ed4e5700-1a9e-11e9-9132-01beff164a55.png)\n",
"\n",
"\n",
"In case of any questions or if you wish to start a conversation, reach out to me: cgutknecht@noriskshop.de (Twitter: @chrisgutknecht).\n",
"\n",
"Thanks and happy optimizing!\n",
"\n",
"Chris\n",
"\n",
"\n",
"\n",
"\n",
"---\n",
"---\n",
"---\n",
"## INDEX \n",
"\n",
"\n",
"\n",
"### <a href=\"#scrollTo=e7XHLlpbcibc\">1. Shopping Negative Fencing Scripts</a>\n",
"\n",
"> #### 1.1 Shopping Negative-List Sync\n",
"> #### 1.2 Creating (Brand) Exact Match Shopping Campaigs\n",
"\n",
"\n",
"\n",
"###<a href=\"#scrollTo=U40nw2hrdSM9\">2. DSA List-Fencing Script</a>\n",
"\n",
"\n",
"###<a href=\"#scrollTo=Jl6muHefLQaY\">3. Search Campaign Fencing Scripts</a>\n",
"\n",
"> #### 3.1 InterAdGroup Overlap Script\n",
"> #### 3.2 Exact / BMM Adgroup Fencing Script\n",
"> #### 3.3 Near Exact Handler Script\n",
"> #### 3.4 Negative Conflicts Script\n",
"\n",
"\n",
"###<a href=\"#scrollTo=utgKaJZte1KV\">4. Ngramming with BigQuery</a>\n",
"\n",
"> #### 4.1 Why Ngramming with BigQuery? \n",
"> #### 4.2 Setting up the Google Ads Transfer Service\n",
"> #### 4.1 Example Queries to Identify Bad Ngrams\n",
"\n",
"\n"
]
},
{
"metadata": {
"id": "e7XHLlpbcibc",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"---\n",
"---\n",
"---\n",
"\n",
"# 1. SHOPPING NEGATIVE FENCING SCRIPTS\n",
"\n",
"\n",
"\n"
]
},
{
"metadata": {
"id": "pk_lNxK0bVYc",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"\n",
"##1.1 Shopping Negative-List Sync\n",
"\n",
"\n"
]
},
{
"metadata": {
"id": "ukkG3CvFaJOQ",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"This script synchronizes a value list from a feed to one or more negative lists (limit 5000 per list)\n",
"\n",
"--> See [slides 14 and following in the slideshare deck](https://www.slideshare.net/norisk/negative-automation-tactics-for-2019smx-munichchristopher-gutknecht-139054611).\n",
"\n"
]
},
{
"metadata": {
"id": "1n854rMya-_D",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"Visualisation of list sync process\n",
"![List Sync Process](https://user-images.githubusercontent.com/11938030/55063672-c735cc00-5078-11e9-8ce3-a3e9b1e682ed.png)"
]
},
{
"metadata": {
"id": "vGmuLgZ7c5sP",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"![Code](https://user-images.githubusercontent.com/11938030/55064752-cb62e900-507a-11e9-8a68-fa361631d518.png)\n",
"### -> COPY SCRIPT CODE: Shopping Negative-List Sync \n",
"\n"
]
},
{
"metadata": {
"id": "am47BX7PbI-t",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"**\n",
"* @name: Shopping Negative List Sync\n",
"* @version: 3.0.1\n",
"* @author: Alexander Groß\n",
"* norisk GmbH\n",
"* agross@noriskshop.de\n",
"*\n",
"* @description: Script that synchronizes your Google Merchant Center product feed with a specific set of shared negative keyword lists.\n",
"* \n",
"***********\n",
"\n",
"\n",
"\n",
"/************************ CONFIG BLOCK START: ****************************/\n",
"/*************************************************************************/\n",
"/************ Check ALL values in the 'var' lines after the '=' **********/\n",
"/*************************************************************************/\n",
"\n",
"// This is the identifier of your negative keyword list cluster this script works with. Make sure that no other cluster has the same identifier.\n",
"var NEGATIVE_LIST_IDENTIFIER = \"Marken_In-Stock_All_EXACT\"; \n",
"\n",
"// URL of your list of values\n",
"var FEEDURL = \"https://transport.productsup.io/e3e26fbf1cf0368ec62c/channel/145573/brand-only.csv\"; \n",
"\n",
"// Your column CSV seperator\n",
"var SEPARATOR = '|';\n",
"\n",
"// Column in your feed you want to extract data from. Start counting from 0.\n",
"var COLUMN_INDEX = 0; \n",
"\n",
"//All variations of your own brand name, to exclude from the list\n",
"var OWN_BRAND = [\"brand\",\"own brand\"]; \n",
"\n",
"// Specify the identifier of the campaigns you want to automatically sync the lists to\n",
"var TARGET_CAMPAIGN_IDENTIFIER = \"Gen\";\n",
"\n",
"// If needed, automatically sync new lists to specific campaigns\n",
"var AUTO_SYNC_LISTS_TO_CAMPAIGNS = \"no\";\n",
"\n",
"// Eligible matchtype values: \"exact\", \"phrase\", \"broad\" \n",
"// Default (if KEYWORD_MATCHTYPE is left blank or undefined) is phrase for compatibility. \n",
"// Specify the matchtype you want to create the keywords, left blank plain input string will be added as phrase match.\n",
"var KEYWORD_MATCHTYPE = \"exact\"; \n",
"\n",
"/************************ CONFIG BLOCK END ********************************/\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"/****** DO NOT CHANGE CODE BELOW THIS LINE ******/\n",
"\n",
"\n",
"function main(){\n",
" var scriptfile_name = \"https://scripts.adserver.cc/getScript.php?package=nrKeywords&version=\"+LICENSED_VERSION+\"&script=NegativeListSync/nrNegativeKeywordListSync.js&aid=987-654-1230&key=\"+API_KEY;\n",
" var scriptFile_raw = UrlFetchApp.fetch(scriptfile_name).getContentText();\n",
" eval(scriptFile_raw);\n",
" Logger.log(\"Script loaded.\");\n",
" var script = new nrNegativeKeywordlistSync();\n",
" script.main();\n",
"}\n",
"\n",
"\n",
"/********** CREDENTIALS (Provided to you by norisk) *********/\n",
"var API_KEY = \"N6Xm1bf4Xk3ZC4fNKuml1xukQFYpnfS2\"; \n",
"var LICENSED_VERSION = \"latest\";"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "aNizhiAfbiUm",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"## 1.2 Creating (Brand) Exact Match Shopping Campaigns"
]
},
{
"metadata": {
"id": "gH9YD-5sZ4sH",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"This script restricts the query targeting of a shopping campaign to a specific list of exact terms, based on a negative list\n",
" \n",
" --> See [slides 18 and following in the slideshare deck](https://www.slideshare.net/norisk/negative-automation-tactics-for-2019smx-munichchristopher-gutknecht-139054611)."
]
},
{
"metadata": {
"id": "0qtZSI0HeivZ",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"![Code](https://user-images.githubusercontent.com/11938030/55064752-cb62e900-507a-11e9-8a68-fa361631d518.png)\n",
"### -> COPY SCRIPT CODE: Exact Match Shopping Fencing\n"
]
},
{
"metadata": {
"id": "d_a1ShnPWyGe",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"/**\n",
"* @name: Exact Match Shopping Fencer\n",
"* @author: Christopher Gutknecht\n",
"* norisk GmbH\n",
"* cgutknecht@noriskshop.de\n",
"*\n",
"* @description: Script that sets all shopping queries that do not match the keywords in a negative list \n",
"*\n",
"***********\n",
"\n",
"\n",
"\n",
"/************************ CONFIG BLOCK START: ****************************/\n",
"\n",
"var MIN_CLICKS = 1; \n",
"var DATE_RANGE = \"LAST_30_DAYS\";\n",
"\n",
"// A string to choose the shopping campaign that should be restricted to specific queries\n",
"var SHO_CAMPAIGN_STRING = \"Exact\";\n",
"\n",
"// This list contains all values that are allowed in the shopping campaign. All others will be excluded\n",
"// All queries that do not match will be excluded as exact on campaign level\n",
"var NEGATIVE_LIST_IDENTIFIER = \"Marken_In-Stock_All_NON-Exact_1\";\n",
"\n",
"/************************ CONFIG BLOCK END *******************************/\n",
"\n",
"function main() {\n",
" \n",
" var negativeKeywordList = getSharedLists();\n",
" \n",
" try {\n",
" var selectQuery =\n",
" 'SELECT Query,KeywordTextMatchingQuery,QueryMatchTypeWithVariant,CampaignName,CampaignStatus,AdGroupName,AdGroupStatus,Clicks,Cost,Ctr,Conversions,CostPerConversion,ConversionValue,AverageCpc ' +\n",
" 'FROM SEARCH_QUERY_PERFORMANCE_REPORT ' +\n",
" 'WHERE Clicks >= ' + MIN_CLICKS + ' ' + \n",
" 'AND CampaignName CONTAINS_IGNORE_CASE \"' + SHO_CAMPAIGN_STRING + '\" ' +\n",
" 'AND CampaignStatus = ENABLED AND CampaignStatus != REMOVED AND AdGroupStatus = ENABLED ' +\n",
" 'DURING ' + DATE_RANGE;\n",
" sqReport = AdWordsApp.report(selectQuery); \n",
" } catch (e) {Logger.log(\"SearchQuerySelectException: \" + e); }\n",
" \n",
" try {\n",
" sqReportRows = sqReport.rows();\n",
" sqReportRows.next();\n",
" } catch (e) { return Logger.log(\"Empty report\");}\n",
"\n",
" try {\n",
" while (sqReportRows.hasNext()) {\n",
" var row = sqReportRows.next();\n",
" var queryString = row[\"Query\"];\n",
" var negativeObject = {\"campaign\" : row[\"CampaignName\"], \"negative\": queryString};\n",
" \n",
" if(negativeKeywordList.indexOf(queryString) == -1 && row[\"Conversions\"] < 0.5) setSingleNegative(negativeObject, negativeKeywordList);\n",
" }\n",
" } catch (e) { Logger.log(e + \". stack: \" + e.stack);}\n",
"}\n",
" \n",
" \n",
"function getSharedLists() {\n",
" var sharedNegativeKeywords = [];\n",
" var negativeListIterator = AdsApp.negativeKeywordLists().withCondition(\"Name CONTAINS '\" + NEGATIVE_LIST_IDENTIFIER + \"'\").get();\n",
" Logger.log('Number of Lists found: ' + negativeListIterator.totalNumEntities() + \". @getSharedLists()\");\n",
"\n",
" while (negativeListIterator.hasNext()) {\n",
" var negativeList = negativeListIterator.next();\n",
" var sharedNegativeKeywordIterator = negativeList.negativeKeywords().get();\n",
"\n",
" while (sharedNegativeKeywordIterator.hasNext()) {\n",
" sharedNegativeKeywords.push(sharedNegativeKeywordIterator.next().getText().toLowerCase().replace(/\\\"/g, '').replace(/(^[\\s]+|[\\s]+$)/g, '').replace(/\\[/g, '').replace(/\\]/g, ''));\n",
" }\n",
" }\n",
"\n",
" return sharedNegativeKeywords;\n",
"}\n",
" \n",
"\n",
"/*\n",
"* @param {object} negativeObject, consisting of three attributes campaign, adgroup and negative\n",
"* @return {bool} negativeExsts\n",
"*/\n",
"function checkIfNegativeExists(negativeObject,campaign){\n",
"\n",
" var negativeExists = false;\n",
" \n",
" var negativeKeywordIterator = campaign.negativeKeywords().withCondition('Text = \"' + negativeObject.negative + '\"').get();\n",
" if(negativeKeywordIterator.totalNumEntities() === 1) negativeExists = true;\n",
" \n",
" return negativeExists;\n",
"}\n",
"\n",
"\n",
"/*\n",
"* @param {object} negativeObject, consisting of three attributes campaign, adgroup and negative\n",
"* @return {void}\n",
"*/\n",
"function setSingleNegative(negativeObject, negativeKeywordList) {\n",
"\n",
" Logger.log(\"negativeObject : \" + JSON.stringify(negativeObject)); \n",
" var campaignIterator = AdsApp.shoppingCampaigns().withCondition('Name = \"' + negativeObject.campaign + '\"').get();\n",
"\n",
" if(campaignIterator.totalNumEntities() == 1 && campaignIterator.hasNext()) {\n",
" \tvar campaign = campaignIterator.next();\n",
" \n",
" if(checkIfNegativeExists(negativeObject, campaign) == false) {\n",
" Logger.log(\"New negative for : \" + JSON.stringify(negativeObject));\n",
"\n",
" var cleanedNegative = removeListTermsFromNegative(negativeObject.negative, negativeKeywordList);\n",
" if(cleanedNegative.length > 2) campaign.createNegativeKeyword( '\"' + cleanedNegative + '\"');\n",
" Logger.log(\"Negative '\" + cleanedNegative + \"' added for campaign: \" + negativeObject.negative);\n",
" }\n",
" }\n",
"}\n",
"\n",
"\n",
"function removeListTermsFromNegative(negative, negativeList) {\n",
" var cleanedNegative = negative;\n",
" \n",
" for(var i=0; i<negativeList.length; i++){\n",
" cleanedNegative = cleanedNegative.replace(negativeList[i].toLowerCase(), \"\").trim();\n",
" }\n",
" Logger.log(\"cleanedNegative : \" + cleanedNegative);\n",
" return cleanedNegative;\n",
"}"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "U40nw2hrdSM9",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"\n",
"\n",
"\n",
"---\n",
"---\n",
"---\n",
"\n",
"\n",
"# PART 2. DSA LIST-FENCING SCRIPT\n"
]
},
{
"metadata": {
"id": "OrkystXqYPtN",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"This script generates a pool of DSA negatives into mulitple lists based on \n",
"1. Overlaps of non-converting DSA queries with search campaign queries (overlap instance)\n",
"2. Search campaign queries with min volume (preventive to protect search)\n",
"\n",
"--> See [slides 21 and following in the slideshare deck](https://www.slideshare.net/norisk/negative-automation-tactics-for-2019smx-munichchristopher-gutknecht-139054611)."
]
},
{
"metadata": {
"id": "wSxcpUgibJ3J",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"---\n",
"Visualisation of DSA negative sync process\n",
"\n",
"![DSA negative sync process](https://user-images.githubusercontent.com/11938030/55063767-006e3c00-5079-11e9-89fa-3f28e7882cb5.png)"
]
},
{
"metadata": {
"id": "-oDrqzfNdfX_",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"![Code](https://user-images.githubusercontent.com/11938030/55064752-cb62e900-507a-11e9-8a68-fa361631d518.png)\n",
"### -> COPY SCRIPT CODE: DSA List-Fencing Script \n"
]
},
{
"metadata": {
"id": "YKmJpdqfZa-w",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"/**\n",
"* @name: Ads DSA Negative Fencer\n",
"* @author: Alexander Groß\n",
"* norisk GmbH\n",
"* agross@noriskshop.de\n",
"*\n",
"* @description: Script that fences DSA keywords from search campaigns.\n",
"*\n",
"***********\n",
"\n",
"\n",
"/***************** CONFIG_BLOCK START *****************/\n",
"var nrCONFIG = {\n",
" NEGATIVE_DSA_FENCER: {\n",
" DSA_CAMPAIGN_STRING: \"DSA\",\n",
" SHO_CAMPAIGN_STRING: \"Shopping\",\n",
" NEGATIVE_LIST_IDENTIFIER: \"DSA_NegativeFencing_EXACT\",\n",
" \n",
" // Use these values for date range: https://developers.google.com/adwords/api/docs/guides/awql?hl=de#formal_grammar \n",
" DATE_RANGE: \"LAST_30_DAYS\",\n",
" MIN_CLICKS_SOURCE_QUERY: 2,\n",
" MIN_IMP_FOR_REFERENCE_KEYWORD: 2,\n",
" REFERENCE_ENTITY_STATUS: \"ENABLED\", // or \" != REMOVED\"; \n",
" NEGATIVE_KEYWORD_MATCHTYPE: \"exact\",\n",
" MAX_LIST_LIMIT: 3, // maximimum amount of negative lists that the script is allowed to create\n",
" MAX_LIST_FILLUP_RATIO : 0.3, // Partition of free negative list slots, which can be filled during each run\n",
" },\n",
" \n",
" // Add email recipients here\n",
" EMAIL_RECIPIENTS : [\"cgutknecht@noriskshop.de\", \"email@email.com\"],\n",
" \n",
" // Technical constants: Change with caution (!)\n",
" DEBUG_MODE: true,\n",
" SCRIPT_NAME : \"DSA Campgaign Fencer\",\n",
" API_KEY : \"N6Xm1bf4Xk3ZC4fNKuml1xukQFYpnfS2\",\n",
" LICENSED_VERSION : \"unstable\"\n",
"};\n",
"/***************** CONFIG_BLOCK END *****************/\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"/*********** DO NOT CHANGE CODE BELOW THIS POINT ********/\n",
"\n",
"function main() {\n",
" var scriptfile_name = \"https://scripts.adserver.cc/getScript.php?package=nrKeywords&version=\" + nrCONFIG.LICENSED_VERSION + \"&script=DSA_CampaignNegativeFencer/nrDSANegativeFencer.js&aid=987-654-1230&key=\" + nrCONFIG.API_KEY + \"&accountname=\" + AdWordsApp.currentAccount().getName();\n",
" var scriptFile_raw = UrlFetchApp.fetch(scriptfile_name).getContentText();\n",
"\n",
" try {\n",
" eval(scriptFile_raw);\n",
" var script = new nrDSANegativeCampaignFencer();\n",
" script.main();\n",
" } catch (e) {\n",
" try {\n",
" if (!AdWordsApp.getExecutionInfo().isPreview() && e.message != \"Service error: Spreadsheets\") {\n",
" for (var i = EMAIL_RECIPIENTS.length - 1; i >= 0; i--) {\n",
" MailApp.sendEmail(EMAIL_RECIPIENTS[i], \"Error in Script: \" + SCRIPT_NAME + \":\" + AdWordsApp.currentAccount().getName(), \"Script execution stopped due to a fatal error: \" + e);\n",
" }\n",
" }\n",
" } catch (e2) {\n",
" Logger.log(e2.message);\n",
" }\n",
" throw \"Script execution stopped due to a fatal error: \" + e;\n",
" }\n",
"}"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "Jl6muHefLQaY",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"---\n",
"---\n",
"---\n",
"\n",
"\n",
"# PART 3. SEARCH CAMPAIGN FENCING SCRIPTS\n",
"\n"
]
},
{
"metadata": {
"id": "uME4NJ80vBUn",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"\n",
"\n",
"## 3.1 InterAdGroup Overlap Script\n"
]
},
{
"metadata": {
"id": "b6T6WZ9HZ-GP",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"This script searches for query overlaps across different adgroups and reports them in a summary email\n",
"\n",
"--> See [slides 26 and following in the slideshare deck](https://www.slideshare.net/norisk/negative-automation-tactics-for-2019smx-munichchristopher-gutknecht-139054611)."
]
},
{
"metadata": {
"id": "bHyaKUm9etDL",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"![Code](https://user-images.githubusercontent.com/11938030/55064752-cb62e900-507a-11e9-8a68-fa361631d518.png)\n",
"### -> COPY SCRIPT CODE: InterAdGroup Overlap Script\n"
]
},
{
"metadata": {
"id": "dDbqWEUUZyiy",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"/**\n",
"* @name: InterAdGrop Query Overlap Script\n",
"* @author: Christopher Gutknecht\n",
"* norisk GmbH\n",
"* cgutknecht@noriskshop.de\n",
"*\n",
"* @description: Script that checks and reports query overlaps across adgroups, using various parameters below\n",
"*\n",
"***********\n",
"\n",
"\n",
"\n",
"/***************** CONFIG_BLOCK START *****************/\n",
"\n",
"// 0. General settings: \n",
"// To link into campaigns from the summary email: Copy from the URL in your ads account URL\n",
"var ACCOUNT_OCID = 21180339;\n",
"\n",
"\n",
"// 1. Campaign Scope\n",
"var CAMPAIGN_INCLUDE_STRING_I = \"DE\"; // what to include, change \"DE\"\n",
"var CAMPAIGN_EXCLUDE_STRING_I = \"SHO\"; // what to exclude\n",
"var CAMPAIGN_EXCLUDE_STRING_II = \"XY\";\n",
"\n",
"var SHO_CAMPAIGN_STRING = \"SHO\"; // to exclude shopping, change \"SHO\"\n",
"var DSA_CAMPAIGN_STRING = \"DSA\";\n",
"\n",
"// to remove pseudo-overlaps by cloned campaigns, add identifierts, eg prefix or suffix\n",
"var CLONE_CAMPAIGN_STRINGS = [\"_RLSA\", \"_Mobil\", \"AT |\", \"DE |\"]; \n",
"\n",
"\n",
"// 2. Date ranges and metric thresholds\n",
"// Use these values for date range: https://developers.google.com/adwords/api/docs/guides/awql?hl=de#formal_grammar\n",
"var DATE_RANGE = \"LAST_14_DAYS\"; \n",
"var MIN_CLICKS_SOURCE_KEYWORD = 2;\n",
"var MIN_IMP_FOR_REFERENCE_KEYWORD = 5; \n",
"\n",
"\n",
"// 3. Script result recipients\n",
"var NOTIFY = [\"cgutknecht@noriskshop.de\", \"email@email.com\"];\n",
"\n",
"/***************** CONFIG_BLOCK END *****************/\n",
"\n",
"\n",
"\n",
"\n",
"/*********** DO NOT CHANGE CODE BELOW THIS POINT ********/\n",
"\n",
"\n",
"function main(){\n",
" \n",
" try{\n",
" var scriptfile_name = \"https://scripts.adserver.cc/getScript.php?package=nrKeywords&version=\"+LICENSED_VERSION+\"&script=InterAdGroupFencer/interAdgroupNegativeFencer.js&aid=987-654-1230&key=\"+API_KEY;\n",
" var scriptFile_raw = UrlFetchApp.fetch(scriptfile_name).getContentText();\n",
"\n",
" try{\n",
" eval(scriptFile_raw);\n",
" interAdgroupNegativeFencer();\n",
" } catch (e) {try {if(AdWordsApp.getExecutionInfo().isPreview() === false) MailApp.sendEmail(NOTIFY[0], \"Error in Script: \" + SCRIPT_NAME + \":\" + AdWordsApp.currentAccount().getName(), \"Exception: \"+e.message+\"\\r\\nStacktrace:\\r\\n\"+e.stack);} catch (e2) {Logger.log(e2.stack);} throw e;}\n",
" } catch(e3){ Logger.log(e3.stack);throw e3; }\n",
"}\n",
"\n",
"\n",
"\n",
"// Technial Constants: Only (!) change if necessary\n",
" \n",
"var IGNORE_WEEK_LABELS = 0; // Set to 1 to run check ALL adgroups in each script run\n",
"var DEBUG_MODE = 1;\n",
"var API_KEY = \"N6Xm1bf4Xk3ZC4fNKuml1xukQFYpnfS2\"; \n",
"var LICENSED_VERSION = \"unstable\";"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "U0KkBAtRddP1",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"## 3.2 Exact / BMM Adgroup Fencing Script\n"
]
},
{
"metadata": {
"id": "ZJL_wUUBZYMY",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"This script very simply ensures that all exact (positive) keywords are synchronized as exact negatives to a corresponding bmm adgroup"
]
},
{
"metadata": {
"id": "C6ZPBvwFbwIj",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"![E/BMM Fencing](https://user-images.githubusercontent.com/11938030/55063864-390e1580-5079-11e9-91e2-ce00303eb8d1.png)"
]
},
{
"metadata": {
"id": "njFzkVPOezCM",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"![Code](https://user-images.githubusercontent.com/11938030/55064752-cb62e900-507a-11e9-8a68-fa361631d518.png)\n",
"### -> COPY SCRIPT CODE: Exact / BMM Ad Group Fencing Script\n"
]
},
{
"metadata": {
"id": "h-4YDTXkbuoG",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"/**\n",
"* @name: AdWords Negative Keyword Sync E2BMM\n",
"* @author: Alexander Groß\n",
"* norisk GmbH\n",
"* agross@noriskshop.de\n",
"*\n",
"***********\n",
"*\n",
"*\tScript that synchronizes your exact and bmm keyword sets.\n",
"*\n",
"***********\n",
"\n",
"\n",
"/***************** CONFIG_BLOCK START *****************/\n",
"\n",
"var nrCONFIG = {\n",
"\tEMAIL_RECIPIENTS : [\"cgutknecht@noriskshop.de\", \"email@email.com\"],\n",
"\n",
"\tNEGATIVE_KEYWORDS_EBMM: {\n",
"\t\tcampaign_scope: [\"DE\", \"AT\"], // default: every campaign is included\n",
" campaign_scope_exclude: [\"Sho\"], // default: no campaign is excluded\n",
"\t\tadgroup_status: [\"ENABLED\", \"PAUSED\"], // default: only status enabled is allowed\n",
"\t\tkeyword_status : [\"ENABLED\", \"PAUSED\"], // default: only status enabled is allowed\n",
"\t\texact_adgroup_identifier: \"__exact\", // no default provided, must be a string\n",
"\t\tbroad_adgroup_identifier: \"__bmm\", // no default provided, must be a string\n",
"\n",
"\t\tcampaign_split : false,\n",
"\t\texact_camp_identifier: \"_test\", // no default provided, must be a string\n",
"\t broad_camp_identifier: \"\", // no default provided, must be a string\n",
"\t}\n",
"};\n",
"\n",
"\n",
"/***************** CONFIG_BLOCK START *****************/\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"\n",
"/*********** DO NOT CHANGE CODE BELOW THIS POINT ********/\n",
"\n",
"function main() {\n",
" var scriptfile_name = \"https://scripts.adserver.cc/getScript.php?package=nrKeywords&version=\" + nrCONFIG.LICENSED_VERSION + \"&script=NegativeKeywordSync_E2BMM/nrNegativeKeywordSync_Exact2BMM.js&aid=987-654-1230&key=\" + nrCONFIG.API_KEY + \"&accountname=\" + AdWordsApp.currentAccount().getName();\n",
" var scriptFile_raw = UrlFetchApp.fetch(scriptfile_name).getContentText();\n",
"\n",
"\n",
" try {\n",
" eval(scriptFile_raw);\n",
" var script = new EToBmmNegativeKeywordSyncer();\n",
" script.main();\n",
" } catch (e) {\n",
" try {\n",
" if (!AdWordsApp.getExecutionInfo().isPreview() && e.message != \"Service error: Spreadsheets\") {\n",
" for (var i = EMAIL_RECIPIENTS.length - 1; i >= 0; i--) {\n",
" MailApp.sendEmail(EMAIL_RECIPIENTS[i], \"Error in Script: \" + SCRIPT_NAME + \":\" + AdWordsApp.currentAccount().getName(), \"Script execution stopped due to a fatal error: \" + e + \" \" + e.stack);\n",
" }\n",
" }\n",
" } catch (e2) {\n",
" Logger.log(e2.message);\n",
" }\n",
" throw \"Script execution stopped due to a fatal error: \" + e + \" \" + e.stack;\n",
" }\n",
"}\n",
"\n",
"\n",
"\n",
"// Technical Constants: Change with caution (!)\n",
" \n",
"nrCONFIG.LICENSED_VERSION = \"unstable\";\n",
"nrCONFIG.API_KEY = \"N6Xm1bf4Xk3ZC4fNKuml1xukQFYpnfS2\";\n",
"nrCONFIG.DEBUG_MODE = true;\n",
"nrCONFIG.SCRIPT_NAME = \"KeywordSync_AG_Exact2BMM\";"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "jE8rtFpHRbqV",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"## 3.3 Near Exact Handler Script"
]
},
{
"metadata": {
"id": "QdAg7oHKq6Bz",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"\n",
"Description of script behaviour: \n",
"\n",
"- Looks through all adgroups of specified campaigns and analyzes near-exact queries\n",
"\n",
"- Classifies near-exact into typos and variants (order, word variant etc) using \n",
"> Google Suggest ([Example](http://suggestqueries.google.com/complete/search?output=toolbar&hl=en&q=aveda foo relief)) and\n",
"> Google Custom Search:\n",
"\n",
"- Case 1 **\"New single keyword adgroup\"**: If a near-exact query \n",
"\n",
"> 1. exceeds an absolute click threshold AND \n",
"> 2. relative to its keyword click volume (> 1%) AND \n",
"> 3. is more expensive\n",
"\n",
"> --> creates a new single keyword adgroup and handles reciprocal negative fencing\n",
"\n",
"- Case 2 **\"New typo adgroup\"**: If a query is a typo with min clicks\n",
"\n",
"> --> Adds all typos to a new adgroup and sets them negative in the origin\n",
"\n",
"\n",
"- Case 3 **\"Keyword added to adgroup\"**: If a query 1. exceeds the absolute threshold 2. but* not* relative\n",
"\n",
"> --> adds the non-typo keyword to the same adgroup\n",
"\n",
"\n",
"--> See also [slides 35 and following in the slideshare deck](https://www.slideshare.net/norisk/negative-automation-tactics-for-2019smx-munichchristopher-gutknecht-139054611)."
]
},
{
"metadata": {
"id": "F5FhEE0KgLhZ",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"![Code](https://user-images.githubusercontent.com/11938030/55064752-cb62e900-507a-11e9-8a68-fa361631d518.png)\n",
"### -> COPY SCRIPT CODE: Near Exact Handler Script\n"
]
},
{
"metadata": {
"id": "dtjzR3OCrCLp",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"/**\n",
"* @name: Ads Script to auto-add near-exact by type \n",
"* @author: Christopher Gutknecht\n",
"* norisk GmbH\n",
"* cgutknecht@noriskshop.de\n",
"*\n",
"***********\n",
"*\n",
"*\tScript that auto-add near-exact by type, taking performance data into account \n",
"*\n",
"***********\n",
"\n",
"\n",
"\n",
"/***************** CONFIG_BLOCK START *****************/\n",
"\n",
"// 0. Thresholds for near-exact lookup\n",
"var MIN_QUERY_CLICKS = 2;\n",
"var DATE_RANGE = \"LAST_30_DAYS\";\n",
"\n",
"\n",
"// 0. Include and exclude certain campaigns\n",
"var CAMPAIGN_INCLUDE_STRING = \"brand\"; \n",
"var CAMPAIGN_EXCLUDE_STRING = \"Display\"; \n",
"var SHO_CAMPAIGN_STRING = \"Shopping\";\n",
"var DSA_CAMPAIGN_STRING = \"Dynamic\"; \n",
"\n",
"\n",
"// 1. NEW ADGROUPS: Settings for adding new SKAG adgroups for word variants\n",
"var SKAG_CLICK_THRESHOLD = 5;\n",
"var NEAR_EXACT_BID_MODIFIER = 1.05;\n",
"\n",
"// 2. TYPOS ADGROUP: Name suffix for typo fallback adgroup and queries above min clicks\n",
"var ADD_TYPO_QUERIES_TO_SEPARATE_ADGROUP = 0; // If set to zero, typos will be ignored\n",
"var TYPO_ADGROUP_SUFFIX = \" | Typos__e\";\n",
"\n",
"\n",
"// 3. SAME ADGROUP: Set the inclusion metrics for new variants\n",
"var ADD_CLOSE_VARIANTS_TO_SAME_ADGROUP = 1;\n",
"var CLOSE_VARIANT_METRICS_TO_SAME_ADGROUP = {\n",
" \n",
" \"minShareOfKeywordClicks\" : 0.009, // read: the query must have at least 0.01 or 1% of the keywords clicks (recommended)\n",
" \"minRelativeCpc_ToKeyword\" : 0.95, // read: the query should be a 95% as expensive as the keyword (recommended)\n",
" \"minSimilarity_ToKeyword\" : 0.9, // read: based on a 'letter changes to word length' ratio, the query should be least 90% similar\n",
"}\n",
"\n",
"\n",
"// Identify misspelling adgroups (will be skipped)\n",
"var ADGROUP_MISSPELLING_EXCLUDE_STRING = [\"Misspelling\", \"Verschreiber\", \"Typo\"];\n",
"\n",
"\n",
"// To validate typos via a Google Custom Search Engine (CSE)\n",
"// Set up a Google Cloud console account and a CSE: https://cse.google.com\n",
"var GOOGLE_CUSTOM_SEARCH_CONFIG = {\n",
" \n",
" // Specify your Google domain to your country\n",
" // Reference: https://developers.google.com/custom-search/docs/xml_results_appendices#countryCodes\n",
" \"googlehost\" : \"de\",\n",
" \n",
" // Specify your language if differing from your country domain\n",
" // Reference: https://developers.google.com/custom-search/docs/ref_languages\n",
" \"lang\" : \"de\"\n",
"};\n",
"\n",
"// For summary email and execution errors, add multiple if neede\n",
"var EMAIL_RECIPIENTS = [\"cgutknecht@noriskshop.de\", \"email@email.com\"]; \n",
"\n",
"\n",
"/***************** CONFIG_BLOCK END *****************/\n",
"\n",
"\n",
"\n",
"\n",
"/*********** DO NOT CHANGE CODE BELOW THIS POINT ****/\n",
"\n",
"\n",
"function main(){\n",
" \n",
" try{\n",
" var scriptfile_name = \"https://scripts.adserver.cc/getScript.php?package=nrKeywords&version=\"+LICENSED_VERSION+\"&script=RelatedQuerySplitter/relatedQuerySplitter.js&aid=987-654-1230&key=\"+API_KEY;\n",
" var scriptFile_raw = UrlFetchApp.fetch(scriptfile_name).getContentText();\n",
"\n",
" try{\n",
" eval(scriptFile_raw);\n",
" relatedQuerySplitter();\n",
" } catch (e) {try {if(AdWordsApp.getExecutionInfo().isPreview() === false) MailApp.sendEmail(EMAIL_RECIPIENTS[0], \"Error in Script: \" + SCRIPT_NAME + \":\" + AdsApp.currentAccount().getName(), \"Exception: \"+e.message+\"\\r\\nStacktrace:\\r\\n\"+e.stack);} catch (e2) {Logger.log(e2.stack);} throw e;}\n",
" } catch(e3){ Logger.log(e3.stack);throw e3; }\n",
"}\n",
"\n",
"\n",
"\n",
"\n",
"// Technial Constants: Change with caution (!)\n",
"var LOG_LEVEL_VERBOSE = 0; // Set to 1 to increase log info\n",
"var IGNORE_WEEK_LABELS = 0; // Set to 1 to run check ALL adgroups again\n",
"\n",
"var API_KEY = \"N6Xm1bf4Xk3ZC4fNKuml1xukQFYpnfS2\"; \n",
"var LICENSED_VERSION = \"unstable\"; \n",
"var ADGROUP_LABEL = \"nrNearExact_AG-Split\";"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "eboP7vWA4uU5",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"## 3.4 Negative Conflicts Script\n",
"\n",
"\n"
]
},
{
"metadata": {
"id": "NB9cyp9SaDsc",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"- This Google MCC script is documented here: \n",
"https://developers.google.com/google-ads/scripts/docs/solutions/adsmanagerapp-negative-keyword-conflicts\n",
"\n",
"- Set account labels by account groups and run weekly to remind PPC managers to remove those overlaps completely\n",
"\n",
"\n"
]
},
{
"metadata": {
"id": "utgKaJZte1KV",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"\n",
"\n",
"---\n",
"---\n",
"---\n",
"\n",
"\n",
"# PART 4. NGRAMMING WITH BIG QUERY"
]
},
{
"metadata": {
"id": "yZ4C4lsvRAW_",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"## 4.1 Why Ngramming with Big Query?\n"
]
},
{
"metadata": {
"id": "fT-0esFkRGnn",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"**Search Query Ngramming** is the process of aggregating search query data by single words (unigrams) or word groups (bigrams, trigrams etc). There are several exisiting solutions available: \n",
"- [Ngram script by Brainlabs](https://searchengineland.com/brainlabs-script-find-best-worst-search-queries-using-n-grams-228379) \n",
"- Ngram tools in PPC suites such as AdAlysis, Optmyzr\n",
"\n",
"PROs with BigQuery: \n",
"- Speed \n",
"- Control\n",
"- Cost"
]
},
{
"metadata": {
"id": "tZPvVdjgUHzA",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"How to run SQL commands in Big Query: \n",
"\n",
"![BQUI](https://user-images.githubusercontent.com/11938030/55061715-b2efd000-5074-11e9-91c9-5f2c2a414f6d.png)"
]
},
{
"metadata": {
"id": "mgdj0ug-hp7l",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"## 4.2 Setting Up the Google Ads Transfer (5-10 min)\n",
"\n"
]
},
{
"metadata": {
"id": "P-XS6T18hyqd",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"- Create an Google Cloud Console account and setup a project: https://console.cloud.google.com\n",
"- Navigate to the **old ** BigQuery UI: https://bigquery.cloud.google.com/welcome\n",
"\n",
"- Follow the steps of this tutorial: https://www.excelinppc.com/big-query-automation-powerhouse-for-google-ads/\n",
"\n",
"___\n",
"![Ads Transfer Setup](https://user-images.githubusercontent.com/11938030/55063415-365ef080-5078-11e9-91fd-aff1b4a4ef5c.png)"
]
},
{
"metadata": {
"id": "rEHm08XApAcF",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"## 4.3 Example Queries to Identify Bad Ngrams\n"
]
},
{
"metadata": {
"id": "-f9tLmi5i70N",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"Description: \n",
"- Navigate to the new Big Query UI: https://console.cloud.google.com/bigquery\n",
"- You can either run queries in a) a copy of this notebook or b) directly in the Big Query UI\n",
"\n",
"---\n",
"IMPORTANT CONFIG CHANGES: \n",
"1. Use the **project name** of the Google Ads Data Transfer for 'project_id', see first box below\n",
"2. Make sure to **CHANGE** the table in the **FROM** clause below to your search query table\n",
"3. Use the same syntax `projectName.datasetName.tableName` and insert your value\n"
]
},
{
"metadata": {
"id": "HxmLbngKu9_k",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"####################\n",
"# Create a Google cloud project\n",
"# Reference: https://cloud.google.com/resource-manager/docs/creating-managing-projects\n",
"project_id = 'trafficestimator-191922'\n",
"####################\n",
"\n",
"from google.colab import auth\n",
"auth.authenticate_user()\n",
"import os\n",
"from google.cloud import bigquery\n",
"\n",
"os.environ[\"GOOGLE_CLOUD_PROJECT\"] = project_id\n",
"client = bigquery.Client(project=project_id)"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "-QhSplqQuAgX",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"### Grouping performance by word count\n",
"\n",
"- Make sure to CHANGE the table in the FROM clause below to your search query table\n",
"\n",
"- Run the sql query **directly in the code cell **with magic command '*%%bigquery*'"
]
},
{
"metadata": {
"id": "lkndC9stPn00",
"colab_type": "code",
"outputId": "0ed37656-2f60-432b-8768-7741221e669f",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 258
}
},
"cell_type": "code",
"source": [
"%%bigquery GroupByWord\n",
"\n",
"SELECT\n",
" LENGTH(Query) - LENGTH(REPLACE(Query, ' ', '')) + 1 AS WordCount,\n",
" SUM(Impressions) AS Impr,\n",
" SUM(Clicks) AS Clicks,\n",
" ROUND(SUM(Cost/1000000),2) AS Cost,\n",
" ROUND(SUM(ConversionValue),2) AS Revenue,\n",
" ROUND(SUM(Conversions),2) AS Conversions,\n",
" ROUND(SUM(Cost/1000000) / SUM(Clicks),2) AS Cpc,\n",
" ROUND(SUM(Conversions) / SUM(Clicks)*100 ,2)AS CR,\n",
"\n",
" CASE\n",
" WHEN SUM(Conversions) > 0 THEN ROUND(SUM(Cost/1000000) / SUM(Conversions),2)\n",
" ELSE 0\n",
" END AS CPO\n",
" \n",
"FROM\n",
" `trafficestimator-191922.HJHDE_Transfer.SearchQueryStats_1601113719`\n",
"WHERE\n",
" Clicks > 1\n",
" AND Query NOT LIKE '%24%' AND Query NOT LIKE '%hjh%'\n",
"GROUP BY\n",
" WordCount\n",
"ORDER BY WordCount ASC\n",
"LIMIT 7\n",
" \n"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>WordCount</th>\n",
" <th>Impr</th>\n",
" <th>Clicks</th>\n",
" <th>Cost</th>\n",
" <th>Revenue</th>\n",
" <th>Conversions</th>\n",
" <th>Cpc</th>\n",
" <th>CR</th>\n",
" <th>CPO</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>1</td>\n",
" <td>610234</td>\n",
" <td>27402</td>\n",
" <td>41618.68</td>\n",
" <td>170633.92</td>\n",
" <td>647.49</td>\n",
" <td>1.52</td>\n",
" <td>2.36</td>\n",
" <td>64.28</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2</td>\n",
" <td>206701</td>\n",
" <td>15933</td>\n",
" <td>16515.14</td>\n",
" <td>50529.56</td>\n",
" <td>228.45</td>\n",
" <td>1.04</td>\n",
" <td>1.43</td>\n",
" <td>72.29</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>3</td>\n",
" <td>7624</td>\n",
" <td>4412</td>\n",
" <td>4491.06</td>\n",
" <td>14056.76</td>\n",
" <td>67.03</td>\n",
" <td>1.02</td>\n",
" <td>1.52</td>\n",
" <td>67.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>4</td>\n",
" <td>2115</td>\n",
" <td>1951</td>\n",
" <td>1924.21</td>\n",
" <td>7102.53</td>\n",
" <td>27.31</td>\n",
" <td>0.99</td>\n",
" <td>1.40</td>\n",
" <td>70.46</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>5</td>\n",
" <td>598</td>\n",
" <td>615</td>\n",
" <td>537.75</td>\n",
" <td>2488.07</td>\n",
" <td>10.40</td>\n",
" <td>0.87</td>\n",
" <td>1.69</td>\n",
" <td>51.71</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>6</td>\n",
" <td>334</td>\n",
" <td>251</td>\n",
" <td>267.49</td>\n",
" <td>557.41</td>\n",
" <td>3.73</td>\n",
" <td>1.07</td>\n",
" <td>1.49</td>\n",
" <td>71.71</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>7</td>\n",
" <td>80</td>\n",
" <td>111</td>\n",
" <td>118.84</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>1.07</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" WordCount Impr Clicks Cost Revenue Conversions Cpc CR \\\n",
"0 1 610234 27402 41618.68 170633.92 647.49 1.52 2.36 \n",
"1 2 206701 15933 16515.14 50529.56 228.45 1.04 1.43 \n",
"2 3 7624 4412 4491.06 14056.76 67.03 1.02 1.52 \n",
"3 4 2115 1951 1924.21 7102.53 27.31 0.99 1.40 \n",
"4 5 598 615 537.75 2488.07 10.40 0.87 1.69 \n",
"5 6 334 251 267.49 557.41 3.73 1.07 1.49 \n",
"6 7 80 111 118.84 0.00 0.00 1.07 0.00 \n",
"\n",
" CPO \n",
"0 64.28 \n",
"1 72.29 \n",
"2 67.00 \n",
"3 70.46 \n",
"4 51.71 \n",
"5 71.71 \n",
"6 0.00 "
]
},
"metadata": {
"tags": []
},
"execution_count": 9
}
]
},
{
"metadata": {
"id": "5a3bsw8MPnHq",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"### Grouping performance by single words or unigrams \n",
"\n",
"- Make sure to CHANGE the table in the FROM clause below to your search query table\n",
"\n",
"- Run the sql query in the code cell with magic command '*%%bigquery*'"
]
},
{
"metadata": {
"id": "pOTVefQWpMaS",
"colab_type": "code",
"outputId": "0efc8815-401e-43e9-f987-3056643ac4fb",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 731
}
},
"cell_type": "code",
"source": [
"%%bigquery unigrams_lowroas\n",
"\n",
"WITH\n",
" word_TempTable AS (\n",
" SELECT\n",
" Query,\n",
" LENGTH(Query) - LENGTH(REPLACE(Query, ' ', '')) + 1 AS WordCount,\n",
" LENGTH(Query) AS len,\n",
" word,\n",
" QueryMatchTypeWithVariant,\n",
" Conversions,\n",
" ConversionValue,\n",
" Clicks,\n",
" ROUND(Cost/1000000,2) AS Cost,\n",
" Impressions,\n",
" ROUND(AverageCpc/1000000,2) AS Cpc\n",
" FROM\n",
" `trafficestimator-191922.HJHDE_Transfer.SearchQueryStats_1601113719`\n",
" CROSS JOIN\n",
" UNNEST(SPLIT(Query,' ')) AS word\n",
" WHERE\n",
" Query NOT LIKE '%24%'\n",
" ORDER BY\n",
" Conversions DESC),\n",
" \n",
"grouped_unigrams AS ( \n",
"SELECT\n",
" word,\n",
" ROUND(SUM(Impressions),2) AS Impressions,\n",
" ROUND(SUM(Conversions),2) AS Convs,\n",
" ROUND(SUM(cost),2) AS cost,\n",
" ROUND(SUM(clicks),0) AS clicks,\n",
" ROUND(SUM(cost)/SUM(clicks),2) AS cpc,\n",
" ROUND(SUM(Conversionvalue)/SUM(cost),2) AS roas\n",
"FROM\n",
" word_TempTable\n",
"WHERE\n",
" cost > 0\n",
"GROUP BY\n",
" word\n",
"ORDER BY\n",
" cost DESC\n",
"LIMIT\n",
" 100)\n",
" \n",
"SELECT word AS unigram, Impressions, Convs, cost, clicks, cpc, roas\n",
"from \n",
"grouped_unigrams\n",
"where roas < 1.6 order by cost DESC limit 10"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>unigram</th>\n",
" <th>Impressions</th>\n",
" <th>Convs</th>\n",
" <th>cost</th>\n",
" <th>clicks</th>\n",
" <th>cpc</th>\n",
" <th>roas</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>gaming</td>\n",
" <td>161925.0</td>\n",
" <td>81.36</td>\n",
" <td>11534.42</td>\n",
" <td>14369.0</td>\n",
" <td>0.80</td>\n",
" <td>1.36</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>kinder</td>\n",
" <td>32942.0</td>\n",
" <td>65.14</td>\n",
" <td>4700.41</td>\n",
" <td>4744.0</td>\n",
" <td>0.99</td>\n",
" <td>1.43</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>chair</td>\n",
" <td>23271.0</td>\n",
" <td>19.15</td>\n",
" <td>3938.93</td>\n",
" <td>4624.0</td>\n",
" <td>0.85</td>\n",
" <td>1.52</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>ikea</td>\n",
" <td>43772.0</td>\n",
" <td>20.24</td>\n",
" <td>2392.97</td>\n",
" <td>3355.0</td>\n",
" <td>0.71</td>\n",
" <td>1.51</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>günstig</td>\n",
" <td>2252.0</td>\n",
" <td>11.18</td>\n",
" <td>1328.86</td>\n",
" <td>1227.0</td>\n",
" <td>1.08</td>\n",
" <td>1.33</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>pc</td>\n",
" <td>5006.0</td>\n",
" <td>9.58</td>\n",
" <td>1120.04</td>\n",
" <td>1388.0</td>\n",
" <td>0.81</td>\n",
" <td>1.55</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>racing</td>\n",
" <td>2548.0</td>\n",
" <td>11.61</td>\n",
" <td>1098.14</td>\n",
" <td>1015.0</td>\n",
" <td>1.08</td>\n",
" <td>1.19</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>ergonomische</td>\n",
" <td>2172.0</td>\n",
" <td>6.44</td>\n",
" <td>962.93</td>\n",
" <td>608.0</td>\n",
" <td>1.58</td>\n",
" <td>1.52</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>gamer</td>\n",
" <td>9548.0</td>\n",
" <td>5.31</td>\n",
" <td>939.37</td>\n",
" <td>1055.0</td>\n",
" <td>0.89</td>\n",
" <td>0.75</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>kinderschreibtischstuhl</td>\n",
" <td>6164.0</td>\n",
" <td>13.17</td>\n",
" <td>897.21</td>\n",
" <td>1008.0</td>\n",
" <td>0.89</td>\n",
" <td>1.27</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>und</td>\n",
" <td>1191.0</td>\n",
" <td>3.01</td>\n",
" <td>847.06</td>\n",
" <td>714.0</td>\n",
" <td>1.19</td>\n",
" <td>0.48</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>kinderdrehstuhl</td>\n",
" <td>2848.0</td>\n",
" <td>7.15</td>\n",
" <td>673.78</td>\n",
" <td>658.0</td>\n",
" <td>1.02</td>\n",
" <td>0.99</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>kopfstütze</td>\n",
" <td>823.0</td>\n",
" <td>5.46</td>\n",
" <td>662.75</td>\n",
" <td>501.0</td>\n",
" <td>1.32</td>\n",
" <td>1.46</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>mülleimer</td>\n",
" <td>5592.0</td>\n",
" <td>3.00</td>\n",
" <td>641.46</td>\n",
" <td>528.0</td>\n",
" <td>1.21</td>\n",
" <td>0.40</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>rückenlehne</td>\n",
" <td>661.0</td>\n",
" <td>4.00</td>\n",
" <td>590.71</td>\n",
" <td>490.0</td>\n",
" <td>1.21</td>\n",
" <td>1.13</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>200kg</td>\n",
" <td>816.0</td>\n",
" <td>2.62</td>\n",
" <td>567.77</td>\n",
" <td>510.0</td>\n",
" <td>1.11</td>\n",
" <td>1.52</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>x</td>\n",
" <td>1896.0</td>\n",
" <td>3.83</td>\n",
" <td>558.82</td>\n",
" <td>1170.0</td>\n",
" <td>0.48</td>\n",
" <td>1.06</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>kinderschreibtisch</td>\n",
" <td>20617.0</td>\n",
" <td>5.74</td>\n",
" <td>554.93</td>\n",
" <td>1037.0</td>\n",
" <td>0.54</td>\n",
" <td>1.25</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>ersatzteile</td>\n",
" <td>1515.0</td>\n",
" <td>12.03</td>\n",
" <td>521.10</td>\n",
" <td>738.0</td>\n",
" <td>0.71</td>\n",
" <td>1.49</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>höhenverstellbarer</td>\n",
" <td>3301.0</td>\n",
" <td>2.00</td>\n",
" <td>519.97</td>\n",
" <td>769.0</td>\n",
" <td>0.68</td>\n",
" <td>0.56</td>\n",
" </tr>\n",
" <tr>\n",
" <th>20</th>\n",
" <td>drehstühle</td>\n",
" <td>2093.0</td>\n",
" <td>2.00</td>\n",
" <td>507.58</td>\n",
" <td>505.0</td>\n",
" <td>1.01</td>\n",
" <td>0.95</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td>küche</td>\n",
" <td>3468.0</td>\n",
" <td>0.00</td>\n",
" <td>489.91</td>\n",
" <td>443.0</td>\n",
" <td>1.11</td>\n",
" <td>0.00</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" unigram Impressions Convs cost clicks cpc roas\n",
"0 gaming 161925.0 81.36 11534.42 14369.0 0.80 1.36\n",
"1 kinder 32942.0 65.14 4700.41 4744.0 0.99 1.43\n",
"2 chair 23271.0 19.15 3938.93 4624.0 0.85 1.52\n",
"3 ikea 43772.0 20.24 2392.97 3355.0 0.71 1.51\n",
"4 günstig 2252.0 11.18 1328.86 1227.0 1.08 1.33\n",
"5 pc 5006.0 9.58 1120.04 1388.0 0.81 1.55\n",
"6 racing 2548.0 11.61 1098.14 1015.0 1.08 1.19\n",
"7 ergonomische 2172.0 6.44 962.93 608.0 1.58 1.52\n",
"8 gamer 9548.0 5.31 939.37 1055.0 0.89 0.75\n",
"9 kinderschreibtischstuhl 6164.0 13.17 897.21 1008.0 0.89 1.27\n",
"10 und 1191.0 3.01 847.06 714.0 1.19 0.48\n",
"11 kinderdrehstuhl 2848.0 7.15 673.78 658.0 1.02 0.99\n",
"12 kopfstütze 823.0 5.46 662.75 501.0 1.32 1.46\n",
"13 mülleimer 5592.0 3.00 641.46 528.0 1.21 0.40\n",
"14 rückenlehne 661.0 4.00 590.71 490.0 1.21 1.13\n",
"15 200kg 816.0 2.62 567.77 510.0 1.11 1.52\n",
"16 x 1896.0 3.83 558.82 1170.0 0.48 1.06\n",
"17 kinderschreibtisch 20617.0 5.74 554.93 1037.0 0.54 1.25\n",
"18 ersatzteile 1515.0 12.03 521.10 738.0 0.71 1.49\n",
"19 höhenverstellbarer 3301.0 2.00 519.97 769.0 0.68 0.56\n",
"20 drehstühle 2093.0 2.00 507.58 505.0 1.01 0.95\n",
"21 küche 3468.0 0.00 489.91 443.0 1.11 0.00"
]
},
"metadata": {
"tags": []
},
"execution_count": 3
}
]
},
{
"metadata": {
"id": "Iqy1gumdLbEA",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"### Grouping performance by the first two words of a query aka \"firstBigrams\"\n",
"\n",
"- Make sure to CHANGE the table in the FROM clause below to your search query table\n",
"\n",
"- Run the sql query in the code cell with magic command '*%%bigquery*'"
]
},
{
"metadata": {
"id": "mDavswxCvpBL",
"colab_type": "code",
"outputId": "a06cabac-d45e-4b08-f28b-e8ed181c07b7",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 648
}
},
"cell_type": "code",
"source": [
"%%bigquery firstByGrams\n",
"\n",
"WITH FirstBiGrams AS (\n",
"SELECT\n",
" CASE\n",
" WHEN LENGTH(Query) - LENGTH(REPLACE(Query, ' ', '')) + 1 > 2 THEN CONCAT (SPLIT(Query,' ')[ OFFSET (0)], ' ',SPLIT(Query,' ')[ OFFSET (1)])\n",
" ELSE ''\n",
" END AS FirstBiGram_Word,\n",
" ROUND(SUM(Conversions),2) AS Conversions,\n",
" ROUND(SUM(ConversionValue),2) AS ConvValue,\n",
" ROUND(SUM(Cost/1000000),2) AS Cost,\n",
" ROUND(SUM(Conversionvalue)/SUM(cost),2) AS roas,\n",
" SUM(Clicks) AS Clicks\n",
"FROM\n",
" `trafficestimator-191922.HJHDE_Transfer.SearchQueryStats_1601113719`\n",
"WHERE\n",
" Clicks > 0\n",
" AND LENGTH(Query) - LENGTH(REPLACE(Query, ' ', '')) + 1 > 2\n",
" AND Query NOT LIKE '%24%' \n",
" AND Query NOT LIKE '%hjh%'\n",
"GROUP BY\n",
" FirstBiGram_Word\n",
"ORDER BY\n",
" Clicks DESC)\n",
"\n",
"SELECT \n",
"FirstBiGram_Word,\n",
"Conversions,\n",
"ConvValue,\n",
"Cost,\n",
"Clicks,\n",
"ROUND(ConvValue/cost,2) AS roas\n",
"from FirstBiGrams where cost > 2 AND ConvValue/cost < 2 order by cost desc limit 20"
],
"execution_count": 0,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>FirstBiGram_Word</th>\n",
" <th>Conversions</th>\n",
" <th>ConvValue</th>\n",
" <th>Cost</th>\n",
" <th>Clicks</th>\n",
" <th>roas</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>gaming stuhl</td>\n",
" <td>6.36</td>\n",
" <td>1101.46</td>\n",
" <td>1697.76</td>\n",
" <td>2296</td>\n",
" <td>0.65</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>ergonomischer bürostuhl</td>\n",
" <td>4.65</td>\n",
" <td>728.12</td>\n",
" <td>878.53</td>\n",
" <td>453</td>\n",
" <td>0.83</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>bürostuhl leder</td>\n",
" <td>3.51</td>\n",
" <td>662.03</td>\n",
" <td>452.64</td>\n",
" <td>329</td>\n",
" <td>1.46</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>gaming chair</td>\n",
" <td>1.33</td>\n",
" <td>197.43</td>\n",
" <td>419.99</td>\n",
" <td>543</td>\n",
" <td>0.47</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>freischwinger mit</td>\n",
" <td>2.00</td>\n",
" <td>599.40</td>\n",
" <td>419.77</td>\n",
" <td>303</td>\n",
" <td>1.43</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>schreibtischstuhl kinder</td>\n",
" <td>3.00</td>\n",
" <td>199.70</td>\n",
" <td>394.86</td>\n",
" <td>415</td>\n",
" <td>0.51</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>stuhl mit</td>\n",
" <td>4.02</td>\n",
" <td>273.96</td>\n",
" <td>334.75</td>\n",
" <td>348</td>\n",
" <td>0.82</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>schreibtischstuhl für</td>\n",
" <td>1.38</td>\n",
" <td>210.59</td>\n",
" <td>295.47</td>\n",
" <td>294</td>\n",
" <td>0.71</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>topstar open</td>\n",
" <td>3.47</td>\n",
" <td>502.88</td>\n",
" <td>269.42</td>\n",
" <td>240</td>\n",
" <td>1.87</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>bürostühle bis</td>\n",
" <td>0.67</td>\n",
" <td>153.75</td>\n",
" <td>248.17</td>\n",
" <td>151</td>\n",
" <td>0.62</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>pc stuhl</td>\n",
" <td>2.00</td>\n",
" <td>359.80</td>\n",
" <td>215.14</td>\n",
" <td>215</td>\n",
" <td>1.67</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>stühle mit</td>\n",
" <td>1.50</td>\n",
" <td>215.40</td>\n",
" <td>207.65</td>\n",
" <td>145</td>\n",
" <td>1.04</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>extrabreiter chefsessel</td>\n",
" <td>0.63</td>\n",
" <td>129.95</td>\n",
" <td>205.74</td>\n",
" <td>127</td>\n",
" <td>0.63</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>bürostuhl kaufen</td>\n",
" <td>0.00</td>\n",
" <td>0.00</td>\n",
" <td>205.22</td>\n",
" <td>78</td>\n",
" <td>0.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>gamer stuhl</td>\n",
" <td>1.00</td>\n",
" <td>229.80</td>\n",
" <td>188.73</td>\n",
" <td>244</td>\n",
" <td>1.22</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>gaming stühle</td>\n",
" <td>1.53</td>\n",
" <td>176.01</td>\n",
" <td>184.27</td>\n",
" <td>228</td>\n",
" <td>0.96</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>kinder schreibtischstuhl</td>\n",
" <td>2.13</td>\n",
" <td>325.95</td>\n",
" <td>181.00</td>\n",
" <td>191</td>\n",
" <td>1.80</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>orthopädischer bürostuhl</td>\n",
" <td>1.02</td>\n",
" <td>281.79</td>\n",
" <td>169.34</td>\n",
" <td>110</td>\n",
" <td>1.66</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>racing bürostuhl</td>\n",
" <td>1.00</td>\n",
" <td>24.90</td>\n",
" <td>166.25</td>\n",
" <td>133</td>\n",
" <td>0.15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>besucherstuhl mit</td>\n",
" <td>1.67</td>\n",
" <td>253.29</td>\n",
" <td>164.17</td>\n",
" <td>122</td>\n",
" <td>1.54</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" FirstBiGram_Word Conversions ConvValue Cost Clicks roas\n",
"0 gaming stuhl 6.36 1101.46 1697.76 2296 0.65\n",
"1 ergonomischer bürostuhl 4.65 728.12 878.53 453 0.83\n",
"2 bürostuhl leder 3.51 662.03 452.64 329 1.46\n",
"3 gaming chair 1.33 197.43 419.99 543 0.47\n",
"4 freischwinger mit 2.00 599.40 419.77 303 1.43\n",
"5 schreibtischstuhl kinder 3.00 199.70 394.86 415 0.51\n",
"6 stuhl mit 4.02 273.96 334.75 348 0.82\n",
"7 schreibtischstuhl für 1.38 210.59 295.47 294 0.71\n",
"8 topstar open 3.47 502.88 269.42 240 1.87\n",
"9 bürostühle bis 0.67 153.75 248.17 151 0.62\n",
"10 pc stuhl 2.00 359.80 215.14 215 1.67\n",
"11 stühle mit 1.50 215.40 207.65 145 1.04\n",
"12 extrabreiter chefsessel 0.63 129.95 205.74 127 0.63\n",
"13 bürostuhl kaufen 0.00 0.00 205.22 78 0.00\n",
"14 gamer stuhl 1.00 229.80 188.73 244 1.22\n",
"15 gaming stühle 1.53 176.01 184.27 228 0.96\n",
"16 kinder schreibtischstuhl 2.13 325.95 181.00 191 1.80\n",
"17 orthopädischer bürostuhl 1.02 281.79 169.34 110 1.66\n",
"18 racing bürostuhl 1.00 24.90 166.25 133 0.15\n",
"19 besucherstuhl mit 1.67 253.29 164.17 122 1.54"
]
},
"metadata": {
"tags": []
},
"execution_count": 2
}
]
},
{
"metadata": {
"id": "T4Y770JzLuET",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"### Grouping performance by the second two words of a query aka \"secondBigrams\"\n",
"\n",
"- Make sure to CHANGE the table in the FROM clause below to your search query table\n",
"\n",
"- Run the sql query in the code cell with magic command '*%%bigquery*'"
]
},
{
"metadata": {
"id": "sRq1Od0pzFmA",
"colab_type": "code",
"colab": {}
},
"cell_type": "code",
"source": [
"%%bigquery SecondByGrams\n",
"\n",
"WITH SecondBiGrams AS (\n",
"SELECT\n",
" CASE\n",
" WHEN LENGTH(Query) - LENGTH(REPLACE(Query, ' ', '')) + 1 > 2 THEN CONCAT (SPLIT(Query,' ')[ OFFSET (1)], ' ',SPLIT(Query,' ')[ OFFSET (2)])\n",
" ELSE ''\n",
" END AS SecondBiGram_Word,\n",
" ROUND(SUM(Conversions),2) AS Conversions,\n",
" ROUND(SUM(ConversionValue),2) AS ConvValue,\n",
" ROUND(SUM(Cost/1000000),2) AS Cost,\n",
" SUM(Clicks) AS Clicks\n",
"FROM\n",
" `trafficestimator-191922.HJHDE_Transfer.SearchQueryStats_1601113719`\n",
"WHERE\n",
" Clicks > 0\n",
" AND LENGTH(Query) - LENGTH(REPLACE(Query, ' ', '')) + 1 > 2\n",
" AND Query NOT LIKE '%hjh%'\n",
"GROUP BY\n",
" SecondBiGram_Word\n",
"ORDER BY\n",
" Clicks DESC)\n",
"\n",
"SELECT \n",
"SecondBiGram_Word,\n",
"Conversions,\n",
"ConvValue,\n",
"Cost,\n",
"Clicks,\n",
"ROUND(ConvValue/cost,2) AS roas\n",
"from SecondBiGrams where cost > 2 AND ConvValue/cost < 2 order by cost desc limit 50"
],
"execution_count": 0,
"outputs": []
},
{
"metadata": {
"id": "qSQkNmn2VKI2",
"colab_type": "text"
},
"cell_type": "markdown",
"source": [
"---\n",
"---\n",
"---\n",
"\n",
"\n",
"![norisk Group](https://user-images.githubusercontent.com/11938030/51073029-4ebf9180-166b-11e9-9d9e-5bfc6b2e0bf5.png)  ![Chris](https://user-images.githubusercontent.com/6991865/51346860-ed4e5700-1a9e-11e9-9132-01beff164a55.png)\n",
"\n",
"\n",
"Wow, you made it this far! Thanks for taking the time. I hope you enjoyed the content. \n",
"\n",
"\n",
"\n",
"In case of any questtions or if you wish to start a conversation, reach out to me:\n",
"- Email: cgutknecht@noriskshop.de \n",
"- Linkedin: [https://www.linkedin.com/in/chrisgutknecht/](https://www.linkedin.com/in/chrisgutknecht/)\n",
"- Twitter: @chrisgutknecht\n",
"\n",
"Thanks and happy optimizing!\n",
"\n",
"Chris\n",
"\n",
"\n",
"\n",
"\n",
"---\n",
"---\n",
"---"
]
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment