Skip to content

Instantly share code, notes, and snippets.

doryokujin doryokujin

Block or report user

Report or block doryokujin

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View slemma_uniques.sql
SELECT
TD_TIME_FORMAT(time,'yyyy-MM-dd') AS d,
COUNT(1) AS pv,
COUNT(DISTINCT td_client_id) AS uu
FROM
pageviews
GROUP BY
TD_TIME_FORMAT(time,'yyyy-MM-dd')
ORDER BY
d
View td_ec_goods_sales_monthly.csv
month member_id goods_id sub_category category amount sales
2015-02-01 2084822 593901 Musical Instruments Movies and Music and Games 1 2839
2015-02-01 1400295 594055 Baby Clothing and Shoes and Jewelry 1 1000
2015-02-01 2084837 586278 Books Books and Audible 1 2839
2015-02-01 111442 593933 Wedding Registry Home and Garden and Tools 1 2848
2016-04-01 2068068 668145 Car/Vehicle Electronics and GPS Automotive and Industrial 2 2000
2016-04-01 591009 664872 Video Games Movies and Music and Games 1 2839
2015-12-01 1932731 646845 Trade In Your Electronics Electronics and Computers 1 1000
2015-12-01 905489 645071 Power and Hand Tools Home and Garden and Tools 1 2362
2015-12-01 745362 621351 Digital Games Movies and Music and Games 1 3791
View td_js_log_monthly.csv
We can make this file beautiful and searchable if this error is corrected: It looks like row 7 should actually have 8 columns, instead of 3. in line 6.
month,td_client_id,td_url,td_title,td_referrer,td_browser,td_language,cnt
2016-08-01,831ca1,http://get.treasuredata.com/JP-Download-Corporate-Overview.html,Treasure Data - データ分析をクラウドで、シンプルに。 - Treasure Data,,IE,ja-jp,2
2016-08-01,09240e,https://www.treasuredata.com/jp/partnerships,パートナー企業 - Treasure Data,https://www.treasuredata.com/jp/,IE,ja-jp,1
2016-08-01,a435df,https://www.treasuredata.com/jp/jp_download_corporate_overview_str/,TREASURE DMPの概要 - Treasure Data,https://www.facebook.com,Firefox,ja,1
2016-08-01,58df61,http://get.treasuredata.com/JP-Download-Corporate-Overview.html,Treasure Data - データ分析をクラウドで、シンプルに。 - Treasure Data,http://nt-lsoxap.housefoods.co.jp/mail/MailPreView.do,IE,ja-jp,2
2016-08-01,8f4fd4,https://www.treasuredata.com/jp/press_release/20160407_private_dmp_solution_treasure_dmp,プライベートDMPソリューション”TREASURE(トレジャー) DMP(ディーエムピー)”を 4月より提供開始 - プレスリリース - Treasure Data,https://www.treasuredata.com/jp/about,IE,ja-jp,1
2016-08-01,4edea6,http://get.treasuredata.com/JP-Download-Corporate-Overview.html
View dc_client_id.sql
+---------------------------+------------------+-------------------------+----------------+----------------+
| date_hour:timestamp | page_path:string | client_id:string | pageviews:long | view_id:string |
+---------------------------+------------------+-------------------------+----------------+----------------+
| "2016-08-19 07:00:00 UTC" | "/" | "1108439470.1471592890" | 4 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/" | "1224794560.1471584000" | 1 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/" | "1369494787.1462782000" | 1 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/" | "1389102700.1471592860" | 3 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/" | "1521310483.1469457390" | 1 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/" | "1690037376.1461813950" | 2 | "128049838" |
| "2016-08-19 07:00:00 UTC" | "/"
View GA to TD-18f4c65b19b3.json
{
"type": "service_account",
"project_id": "ga-to-td",
"private_key_id": "18f4c65b19b3*****",
"private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvg*****",
"client_email": "treasure-data@ga-to-td.iam.gserviceaccount.com",
"client_id": "11147*****",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://accounts.google.com/o/oauth2/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
View dc_timestamp_issue.bash
$ td connector:issue config.yml --database ga_reorts --table master_td_data_82405714 --auto-create-table
Job 84575054 is queued.
Use 'td job:show 84575054' to show the status.
$ td job:show 84570522
JobID : 84570522
Status : success
Type : bulkload
Database : ga_reorts
Config :
View dc_timestamp2.yml
in:
type: google_analytics
json_key_content: |
{
// omit
}
view_id: *****
start_time: "2001-01-01"
end_time: "2016-09-01"
time_series: "ga:dateHour"
View a.sql
+---------------------------+------------------------------+-------------------------+-------------------+----------------+----------------+---------------------------+
| date_hour:timestamp | page_path:string | dimension1:string | dimension2:string | pageviews:long | view_id:string | time2:timestamp |
+---------------------------+------------------------------+-------------------------+-------------------+----------------+----------------+---------------------------+
| "2016-08-22 02:00:00 UTC" | "/" | "1646047626.1464089685" | "1471831722644" | 1 | "128049838" | "1927-06-22 21:35:16 UTC" |
| "2016-08-22 02:00:00 UTC" | "/entry/20101102/1288651712" | "1192170867.1468485627" | "1471833441226" | 1 | "128049838" | "1927-07-12 18:58:18 UTC" |
| "2016-08-22 02:00:00 UTC" | "/entry/20101116/1289860948" | "781104774.1469012530" | "1471833732132" | 1 | "128049838" | "1927-07-16 03:46:44 UTC" |
| "2016-08
View dc_timestamp.yml
dimensions:
- "ga:pagePath"
- "ga:dimension1"
- "ga:dimension2"
metrics:
- "ga:pageviews"
View dc_timestamp.sql
+---------------------------+------------------------------+-------------------------+-------------------+----------------+----------------+
| date_hour:timestamp | page_path:string | dimension1:string | dimension2:string | pageviews:long | view_id:string |
+---------------------------+------------------------------+-------------------------+-------------------+----------------+----------------+
| "2016-08-22 02:00:00 UTC" | "/" | "1646047626.1464089680" | "1471831722" | 1 | "128049838" |
| "2016-08-22 02:00:00 UTC" | "/entry/20101102/1288651712" | "1192170867.1468485620" | "1471833441" | 1 | "128049838" |
| "2016-08-22 02:00:00 UTC" | "/entry/20101116/1289860948" | "781104774.14690125300" | "1471833732" | 1 | "128049838" |
| "2016-08-22 02:00:00 UTC" | "/entry/2014/02/04/145027" | "318470974.14633835430" | "1471833680" | 1 | "128049838" |
| "2016-08-22 02:00:00 UTC" | "/entry
You can’t perform that action at this time.