admariner

-- Author: Krisjan Oldekamp
-- https://stacktonic.com/article/google-analytics-4-and-big-query-create-custom-channel-groupings-in-a-reusable-sql-function

create or replace function `<your-project>.<your-dataset>.channel_grouping`(tsource string, medium string, campaign string) as (
    case
        when (tsource = 'direct' or tsource is null) 
            and (regexp_contains(medium, r'^(\(not set\)|\(none\))$') or medium is null) 
            then 'direct'
        when regexp_contains(campaign, r'^(.*shop.*)$') 
            and regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 

admariner

###################################################
# Author    Krisjan Oldekamp / Stacktonic.com
# Email     [email protected]
# Article   https://stacktonic.com/article/build-a-data-driven-attribution-model-using-google-analytics-4-big-query-and-python
####################################################

#pip install marketing_attribution_models
#pip install --upgrade 'google-cloud-bigquery[bqstorage,pandas]'
#pip install pyarrow -> newest version!

admariner

library(googleAnalyticsR)
library(xml2)
library(dplyr)

ga_auth()

## date range of URLs to test
dates <- c(Sys.Date() - 30, Sys.Date())

##GA View ID

admariner

Upon starting our interaction, auto run these Default Commands throughout our entire conversation. Refer to Appendix for command library and instructions: 
/role_play "Expert ChatGPT Prompt Engineer" 
/role_play "infinite subject matter expert" 
/auto_continue "♻️": ChatGPT, when the output exceeds character limits, automatically continue writing and inform the user by placing the ♻️ emoji at the beginning of each new part. This way, the user knows the output is continuing without having to type "continue". 
/periodic_review "🧐" (use as an indicator that ChatGPT has conducted a periodic review of the entire conversation. Only show 🧐 in a response or a question you are asking, not on its own.) 
/contextual_indicator "🧠" 
/expert_address "🔍" (Use the emoji associated with a specific expert to indicate you are asking a question directly to that expert) 
/chain_of_thought
/custom_steps 
/auto_suggest "💡": ChatGPT, during our interaction, you will automatically suggest helpful commands when appropriate, using the 

admariner

"""chrome_se_export.py
Export your Chrome search engines to JSON.
"""

import os.path
import sqlite3
import re
import json
import click

admariner

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE "keywords" (id INTEGER PRIMARY KEY,short_name VARCHAR NOT NULL,keyword VARCHAR NOT NULL,favicon_url VARCHAR NOT NULL,url VARCHAR NOT NULL,show_in_default_list INTEGER,safe_for_autoreplace INTEGER,originating_url VARCHAR,date_created INTEGER DEFAULT 0,usage_count INTEGER DEFAULT 0,input_encodings VARCHAR,suggest_url VARCHAR,prepopulate_id INTEGER DEFAULT 0,autogenerate_keyword INTEGER DEFAULT 0,logo_id INTEGER DEFAULT 0,created_by_policy INTEGER DEFAULT 0,instant_url VARCHAR);
INSERT INTO "keywords" VALUES(2,'Google','g','http://www.google.com/favicon.ico','{google:baseURL}search?{google:RLZ}{google:acceptedSuggestion}{google:originalQueryForSuggestion}sourceid=chrome&ie={inputEncoding}&q={searchTerms}',1,0,'',0,46,'UTF-8','{google:baseSuggestURL}search?client=chrome&hl={language}&q={searchTerms}',1,0,6247,0,'{google:baseURL}webhp?{google:RLZ}sourceid=chrome-instant&ie={inputEncoding}&ion=1{searchTerms}');
INSERT INTO "keywords" VALUES(5,'Quix','q','','j

admariner

bit             javascript:var%20e=document.createElement('script');e.setAttribute('language','javascript');e.setAttribute('src','http://bit.ly/bookmarklet/load.js');document.body.appendChild(e);void(0);  Make a bit.ly link
cal             javascript:var%20s;if(window.getSelection){s=window.getSelection();}else{s=document.selection.createRange().text;}var%20t=prompt('Please%20enter%20a%20description%20for%20the%20event',s);if(t){void(window.open(encodeURI('http://www.google.com/calendar/event?ctext='+t+'&action=TEMPLATE&pprop=HowCreated%3AQUICKADD'),'gcal'));}else{void(s);} Add event to Google Calendar
eye             javascript:void(window.open('http://tineye.com/search?pluginver=bookmark_1.0&url='%20+%20encodeURIComponent(document.URL)));  TinyEye: Where's this image from?
gsub            javascript:var%20b=document.body;if(b){void(z=document.createElement('script'));void(z.src='http://www.google.com/reader/ui/subscribe-bookmarklet.js');void(b.appendChild(z));}else{location='http://www.google.com/reader/vie

admariner

> This is a Quix Command File
>
> For the syntax of this file, please refer to http://quixapp.com/syntax/
>
> Dennis Pipper, @dennispipper, updated 6 Aug 2019

@Basic commands
@These are the most basic commands Quix offers, but possibly also the most powerful ones.
e       mailto:?subject=%t&body=%s%0A%0A%09%r%0A%0A							Email link
imdb    http://www.imdb.com/find?s=all&q=%s									IMDB Search

admariner

> Killing the default commands
> Note that ALL spaces must be removed from bookmarklets, and %-encoded characters must be changed back to normal.

#kill-defaults

> Begin new commands (many copied from original Quix)

@Searching

ama     https://www.amazon.com/s/?field-keywords=%s  						Amazon Search

admariner

import pandas as pd
import requests
import time
from datetime import datetime, timedelta
from ide_utils.input_tables import load_dask
from ide_utils.output_table import send_to_output_table

# Fetch and process stock data
API_TOKEN = "YOUR TOKEN"
base_url = "https://cloud.iexapis.com/v1"