<commit_before>{
  "script":{
    "released":"2020-08-13",
    "license":"Licensed under the Apache License, Version 2.0",
    "copyright":"Copyright 2020 Google LLC",
    "icon":"donut_large",
    "title":"CM360 Segmentology",
    "description":"CM360 funnel analysis using Census data.",
    "image":"https://github.com/google/starthinker/raw/master/tutorials/images/segmentology_dv.png",
    "sample":"https://datastudio.google.com/c/u/0/reporting/3673497b-f36f-4448-8fb9-3e05ea51842f/",
    "from":["cm360", "census"],
    "to":["bigquery", "datastudio"],
    "pitches":[
      "Optimize creatives and landing pages for engaged demographics.",
      "Examine CM360 funnel in terms of demographics.",
      "Discover missed marketing opportunities.",
      "Run continously to measure impact of funnel changes."
    ],
    "impacts":{
      "spend optimization":100,
      "spend growth":100,
      "time savings":80,
      "account health":90,
      "csat improvement":100
    },
    "instructions":[
      "Wait for <b>BigQuery->{field:recipe_project}->{field:recipe_slug}->Census_Join</b> to be created.",
      "Join the <a hre='https://groups.google.com/d/forum/starthinker-assets' target='_blank'>StarThinker Assets Group</a> to access the following assets",
      "Copy <a href='https://datastudio.google.com/c/u/0/reporting/3673497b-f36f-4448-8fb9-3e05ea51842f/' target='_blank'>CM360 Segmentology Sample</a>. Leave the Data Source as is, you will change it in the next step.",
      "Click Edit Connection, and change to <b>BigQuery->{field:recipe_project}->{field:recipe_slug}->Census_Join</b>.",
      "Or give these intructions to the client."
    ],
    "authors":["<EMAIL>"]
  },
  "tasks":[
    { "dataset":{
      "description":"Create a dataset for bigquery tables.",
      "hour":[4],
      "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Credentials used for writing data." }},
      "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "description":"Place where tables will be created in BigQuery." }}
    }},
    { "bigquery":{
      "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Credentials used for writing function." }},
      "function":"Pearson Significance Test",
      "to":{
        "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }}
      }
    }},
    { "google_api": {
      "auth":"user",
      "api": "dfareporting",
      "version": "v3.4",
      "function": "accounts.get",
      "kwargs":{
        "id":{"field":{ "name":"account", "kind":"integer", "order":5, "default":"", "description":"Campaign Manager Account ID" }},
        "fields":"id,name"
      },
      "results": {
        "bigquery": {
          "auth":"service",
          "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }},
          "table": "CM360_Account"
        }
      }
    }},
    { "dcm":{
      "auth":{"field":{ "name":"auth_read", "kind":"authentication", "order":0, "default":"user", "description":"Credentials used for reading data." }},
      "report": {
        "filters": {
           "dfa:advertiser": {
             "values": {"field":{ "name":"advertisers", "kind":"integer_list", "order":6, "default":[], "description":"Comma delimited list of CM360 advertiser ids." }}
           }
        },
        "account":{"field":{ "name":"account", "kind":"string", "order":5, "default":"", "description":"Campaign Manager Account ID" }},
        "body":{
          "name": {"field":{ "name":"recipe_name", "kind":"string",  "prefix":"Segmentology ", "description":"The report name.", "default":"" }},
          "criteria": {
            "dateRange": {"kind": "dfareporting#dateRange", "relativeDateRange": "LAST_90_DAYS"},
            "dimensions": [
              { "kind": "dfareporting#sortedDimension", "name": "dfa:advertiserId" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:advertiser" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:campaignId" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:campaign" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:placementId" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:placement" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:postalCode" }
            ],
            "metricNames": [
              "dfa:impressions",
              "dfa:clicks",
              "dfa:totalConversions"
            ]
          },
          "type": "STANDARD",
          "delivery": {"emailOwner": false},
          "format": "CSV"
        }
      }
    }},
    { "dcm":{
      "auth":{"field":{ "name":"auth_read", "kind":"authentication", "order":0, "default":"user", "description":"Credentials used for reading data." }},
      "report":{
        "account":{"field":{ "name":"account", "kind":"string", "default":"" }},
        "name":{"field":{ "name":"recipe_name", "kind":"string", "order":3, "prefix":"Segmentology ", "default":"", "description":"Name of report, not needed if ID used." }}
      },
      "out":{
        "bigquery":{
          "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Authorization used for writing data." }},
          "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }},
          "table":"CM360_KPI",
          "header":true
        }
      }
    }},
    { "bigquery": {
      "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Authorization used for writing data." }},
      "from": {
        "query":"SELECT
           Id AS Partner_Id,
           Name AS Partner,
           Advertiser_Id,
           Advertiser,
           Campaign_Id,
           Campaign,
           Zip_Postal_Code AS Zip,
           SAFE_DIVIDE(Impressions, SUM(Impressions) OVER(PARTITION BY Advertiser_Id)) AS Impression_Percent,
           SAFE_DIVIDE(Clicks, Impressions) AS Click_Percent,
           SAFE_DIVIDE(Total_Conversions, Impressions) AS Conversion_Percent,
           Impressions AS Impressions
         FROM `{dataset}.CM360_KPI`
         CROSS JOIN `{dataset}.CM360_Account`
       ",
       "parameters":{
         "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "description":"Place where tables will be created in BigQuery." }}
       },
       "legacy":false
     },
     "to":{
        "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "description":"Place where tables will be written in BigQuery." }},
        "view":"CM360_KPI_Normalized"
      }
    }},
    { "census": {
      "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Authorization used for writing data." }},
      "normalize": {
        "census_geography": "zip_codes",
        "census_year": "2018",
        "census_span": "5yr"
      },
      "to": {
        "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }},
        "type": "view"
      }
    }},
    { "census": {
      "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Authorization used for writing data." }},
      "correlate": {
        "join": "Zip",
        "pass": ["Partner_Id", "Partner", "Advertiser_Id", "Advertiser", "Campaign_Id", "Campaign"],
        "sum": ["Impressions"],
        "correlate": ["Impression_Percent", "Click_Percent", "Conversion_Percent"],
        "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }},
        "table": "CM360_KPI_Normalized",
        "significance": 80
      },
      "to": {
        "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }},
        "type": "view"
      }
    }}
  ]
}
<commit_msg>Fix product field name change.<commit_after>{
  "script":{
    "released":"2020-08-13",
    "license":"Licensed under the Apache License, Version 2.0",
    "copyright":"Copyright 2020 Google LLC",
    "icon":"donut_large",
    "title":"CM360 Segmentology",
    "description":"CM360 funnel analysis using Census data.",
    "image":"https://github.com/google/starthinker/raw/master/tutorials/images/segmentology_dv.png",
    "sample":"https://datastudio.google.com/c/u/0/reporting/3673497b-f36f-4448-8fb9-3e05ea51842f/",
    "from":["cm360", "census"],
    "to":["bigquery", "datastudio"],
    "pitches":[
      "Optimize creatives and landing pages for engaged demographics.",
      "Examine CM360 funnel in terms of demographics.",
      "Discover missed marketing opportunities.",
      "Run continously to measure impact of funnel changes."
    ],
    "impacts":{
      "spend optimization":100,
      "spend growth":100,
      "time savings":80,
      "account health":90,
      "csat improvement":100
    },
    "instructions":[
      "Wait for <b>BigQuery->{field:recipe_project}->{field:recipe_slug}->Census_Join</b> to be created.",
      "Join the <a hre='https://groups.google.com/d/forum/starthinker-assets' target='_blank'>StarThinker Assets Group</a> to access the following assets",
      "Copy <a href='https://datastudio.google.com/c/u/0/reporting/3673497b-f36f-4448-8fb9-3e05ea51842f/' target='_blank'>CM360 Segmentology Sample</a>. Leave the Data Source as is, you will change it in the next step.",
      "Click Edit Connection, and change to <b>BigQuery->{field:recipe_project}->{field:recipe_slug}->Census_Join</b>.",
      "Or give these intructions to the client."
    ],
    "authors":["<EMAIL>"]
  },
  "tasks":[
    { "dataset":{
      "description":"Create a dataset for bigquery tables.",
      "hour":[4],
      "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Credentials used for writing data." }},
      "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "description":"Place where tables will be created in BigQuery." }}
    }},
    { "bigquery":{
      "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Credentials used for writing function." }},
      "function":"Pearson Significance Test",
      "to":{
        "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }}
      }
    }},
    { "google_api": {
      "auth":"user",
      "api": "dfareporting",
      "version": "v3.4",
      "function": "accounts.get",
      "kwargs":{
        "id":{"field":{ "name":"account", "kind":"integer", "order":5, "default":"", "description":"Campaign Manager Account ID" }},
        "fields":"id,name"
      },
      "results": {
        "bigquery": {
          "auth":"service",
          "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }},
          "table": "CM360_Account"
        }
      }
    }},
    { "dcm":{
      "auth":{"field":{ "name":"auth_read", "kind":"authentication", "order":0, "default":"user", "description":"Credentials used for reading data." }},
      "report": {
        "filters": {
           "dfa:advertiser": {
             "values": {"field":{ "name":"advertisers", "kind":"integer_list", "order":6, "default":[], "description":"Comma delimited list of CM360 advertiser ids." }}
           }
        },
        "account":{"field":{ "name":"account", "kind":"string", "order":5, "default":"", "description":"Campaign Manager Account ID" }},
        "body":{
          "name": {"field":{ "name":"recipe_name", "kind":"string",  "prefix":"Segmentology ", "description":"The report name.", "default":"" }},
          "criteria": {
            "dateRange": {"kind": "dfareporting#dateRange", "relativeDateRange": "LAST_90_DAYS"},
            "dimensions": [
              { "kind": "dfareporting#sortedDimension", "name": "dfa:advertiserId" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:advertiser" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:campaignId" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:campaign" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:placementId" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:placement" },
              { "kind": "dfareporting#sortedDimension", "name": "dfa:zipCode" }
            ],
            "metricNames": [
              "dfa:impressions",
              "dfa:clicks",
              "dfa:totalConversions"
            ]
          },
          "type": "STANDARD",
          "delivery": {"emailOwner": false},
          "format": "CSV"
        }
      }
    }},
    { "dcm":{
      "auth":{"field":{ "name":"auth_read", "kind":"authentication", "order":0, "default":"user", "description":"Credentials used for reading data." }},
      "report":{
        "account":{"field":{ "name":"account", "kind":"string", "default":"" }},
        "name":{"field":{ "name":"recipe_name", "kind":"string", "order":3, "prefix":"Segmentology ", "default":"", "description":"Name of report, not needed if ID used." }}
      },
      "out":{
        "bigquery":{
          "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Authorization used for writing data." }},
          "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }},
          "table":"CM360_KPI",
          "header":true
        }
      }
    }},
    { "bigquery": {
      "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Authorization used for writing data." }},
      "from": {
        "query":"SELECT
           Id AS Partner_Id,
           Name AS Partner,
           Advertiser_Id,
           Advertiser,
           Campaign_Id,
           Campaign,
           Zip_Postal_Code AS Zip,
           SAFE_DIVIDE(Impressions, SUM(Impressions) OVER(PARTITION BY Advertiser_Id)) AS Impression_Percent,
           SAFE_DIVIDE(Clicks, Impressions) AS Click_Percent,
           SAFE_DIVIDE(Total_Conversions, Impressions) AS Conversion_Percent,
           Impressions AS Impressions
         FROM `{dataset}.CM360_KPI`
         CROSS JOIN `{dataset}.CM360_Account`
       ",
       "parameters":{
         "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "description":"Place where tables will be created in BigQuery." }}
       },
       "legacy":false
     },
     "to":{
        "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "description":"Place where tables will be written in BigQuery." }},
        "view":"CM360_KPI_Normalized"
      }
    }},
    { "census": {
      "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Authorization used for writing data." }},
      "normalize": {
        "census_geography": "zip_codes",
        "census_year": "2018",
        "census_span": "5yr"
      },
      "to": {
        "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }},
        "type": "view"
      }
    }},
    { "census": {
      "auth":{"field":{ "name":"auth_write", "kind":"authentication", "order":1, "default":"service", "description":"Authorization used for writing data." }},
      "correlate": {
        "join": "Zip",
        "pass": ["Partner_Id", "Partner", "Advertiser_Id", "Advertiser", "Campaign_Id", "Campaign"],
        "sum": ["Impressions"],
        "correlate": ["Impression_Percent", "Click_Percent", "Conversion_Percent"],
        "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }},
        "table": "CM360_KPI_Normalized",
        "significance": 80
      },
      "to": {
        "dataset":{"field":{ "name":"recipe_slug", "kind":"string", "order":4, "default":"", "description":"Name of Google BigQuery dataset to create." }},
        "type": "view"
      }
    }}
  ]
}

{'fraction_non_alphanumeric': 0.2008240773916159, 'fraction_numerical': 0.01683984235041204, 'mean_word_length': 3.244044602128738, 'pattern_counts': {'":': 502, '<': 21, '<?xml version=': 0, '>': 33, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2112951', 'n_tokens_mistral': 5362, 'n_tokens_neox': 5033, 'n_words': 1062}