Skip to content

Instantly share code, notes, and snippets.

View doryokujin's full-sized avatar

doryokujin doryokujin

40 followers · 1 following
  • Treasure Data
View GitHub Profile
@doryokujin
doryokujin / dc_timestamp2.yml
Last active August 27, 2016 00:35
in:
type: google_analytics
json_key_content: |
{
// omit
}
view_id: *****
start_time: "2001-01-01"
end_time: "2016-09-01"
time_series: "ga:dateHour"
@doryokujin
doryokujin / dc_client_id.sql
Last active August 27, 2016 00:32
+---------------------------+------------------+-------------------------+----------------+----------------+
| 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" | "/"
@doryokujin
doryokujin / dc_client_id.yml
Last active August 27, 2016 00:29
in:
type: google_analytics
json_key_content: |
{
// omit
}
view_id: *****
start_time: "2001-01-01"
end_time: "2016-09-01"
time_series: "ga:dateHour"
@doryokujin
doryokujin / GA to TD-18f4c65b19b3.json
Created August 26, 2016 07:26
{
"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",
@doryokujin
doryokujin / dc_timestamp_issue.bash
Last active August 26, 2016 07:07
$ 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 :
@doryokujin
doryokujin / dc_timestamp.sql
Last active August 26, 2016 06:59
+---------------------------+------------------------------+-------------------------+-------------------+----------------+----------------+
| 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
@doryokujin
doryokujin / client_id_js.js
Last active August 26, 2016 06:57
<script>
(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,'script','https://www.google-analytics.com/analytics.js','ga');
ga('create', 'UA-XXXXXX-XX', 'auto');
// get client id
ga(function(tracker) {
@doryokujin
doryokujin / a.sql
Created August 26, 2016 06:37
+---------------------------+------------------------------+-------------------------+-------------------+----------------+----------------+---------------------------+
| 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
@doryokujin
doryokujin / dc_timestamp.yml
Created August 26, 2016 06:05
dimensions:
- "ga:pagePath"
- "ga:dimension1"
- "ga:dimension2"
metrics:
- "ga:pageviews"
@doryokujin
doryokujin / 30dayUsers.sql
Last active August 26, 2016 04:37
+---------------------------+-----------------+----------------+----------------+
| date:timestamp | 1day_users:long | pageviews:long | view_id:string |
+---------------------------+-----------------+----------------+----------------+
| "2016-08-18 07:00:00 UTC" | 5279 | 27240 | "104634715" |
| "2016-08-19 07:00:00 UTC" | 4313 | 17425 | "104634715" |
| "2016-08-20 07:00:00 UTC" | 3196 | 8372 | "104634715" |
| "2016-08-21 07:00:00 UTC" | 4224 | 17468 | "104634715" |
| "2016-08-22 07:00:00 UTC" | 5604 | 30809 | "104634715" |
| "2016-08-23 07:00:00 UTC" | 5512 | 29494 | "104634715" |
| "2016-08-24 07:00:00 UTC" | 5241 | 28102 | "104634715" |