Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Sign up for free
Menu
Search
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Pricing
Search
Sign in
Sign up for free
雑につくるKPIツールのススメ
Search
Sponsored
·
Your Podcast. Everywhere. Effortlessly.
Share. Educate. Inspire. Entertain. You do you. We'll handle the rest.
→
TAKAHASHI Kazunari
March 28, 2015
540
0
Share
Embed
Copy iframe code
Copy JS code
Copy link
Start on current slide
雑につくるKPIツールのススメ
TAKAHASHI Kazunari
March 28, 2015
Transcript
27 slides
ࡶʹͭ͘Δ KPIπʔϧͷεεϝ 2015/03/28 Minami.rb ࠷ॳͰ࠷ޙͷLTେձ
ࢲ…. • @1syo • Rails Programmer • Yokohama.rb • 2012
Ruby kaja award winner • MinatoRubyKaig01 organizer • TDDBC Yokohama TA
MinatoRubyKaigi02
ͨͿΜ9݄, 10݄
KPIπʔϧͱʁ • Ϣʔβʔ.. • ച্… • ࣍ɺ݄࣍ɺྦྷܭ.. • Έ͍ͨͳΞϨ..
։ൃͷ࣮… • ϦϦʔεޙ͔Βඞཁ • ཁ͕݅;ΜΘΓ͍ͯ͠Δ • ظͱ͔ͳ͍
Ͳ͔͜ͰखΛൈ͔ͳ͍ͱਏ͍
ࣈͷਖ਼֬͞ > ͍উख
άϥϑ = ࢮ
ͪΐͬͱલͷͭ
#! /bin/sh! host=localhost! database=db! user=dbuser! password=password! ! from_date=`date --date '1
days ago' +\%Y-\%m-\%d`! to_date=`date +\%Y-\%m-\%d`! !
[email protected]
!
[email protected]
! subject=counter! bound=`date +"%Y%m%d%H%M%S"`! filename="filename_$from_date.csv"! ! sql="SELECT * FROM users WHERE created_at BETWEEN $from_date AND $to_date"! result=`mysql -u$user -p$password -h$host $database - e"$sql" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\r\n//g'`!
/usr/sbin/sendmail -t -i <<__MAIL__! From: "$from" ! To: "$to"! Subject:
"$subject"! MIME-Version: 1.0! Content-Type: multipart/mixed; boundary=\"---------"$bound"\"! ! -----------"$bound"! Content-Type: Text/Plain; charset=ISO-2022-JP! Content-Transfer-Encoding: 7bit! ! Now: "$from_date"! ! --! `hostname`! "$from"! ! -----------"$bound"! Content-Type: application/octet-stream! name="$filename"! Content-Transfer-Encoding: base64! Content-Disposition: attachment;! filename="$filename"! ! `echo -e "$result" | base64`! ! -----------"$bound"--! __MAIL__
֓ཁ • mysqlίϚϯυͰSQLΛୟ͍ͯ • ݁ՌΛawkͰCSVʹͯ͠ • mailͷఴϑΝΠϧʹͯ͠ૹ৴
͋ͱΤΫηϧઌੜཔΉʂ
ૂ͍ • SQLͰਖ਼֬ͳࣈΛग़͢ͷʹઐ೦͍ͨ͠ • ΈΜͳେ͖ΤΫηϧઌੜͷݞʹΓ͍ͨ • ؒҧ͍Λࢦఠ͞ΕͨΒ͙ʹमਖ਼͍ͨ͠
Α͍ • SQLͰΞΫηεͰ͖ΕେूܭͰ͖Δ • ߹ʹΑͬͯshellΛۦͯ͠ϑΝΠϧू ܭͰ͖Δ • େྔੜ࢈͍͢͠
ѱ͍ • sendmail͕͑ͳ͍ͱͳ͍ͱख٧·Γ • ʮʓʓ͞Μݟ͍ͨʯ͕݁ߏ໘ • ूܭ݁Ռ͕ࢄ
࠷ۙͷͭ
module KPI! module Summary! class SalesReport! def client! @client ||=
Mysql2::Client.new(! host: Settings.kpi.database.host,! database: Settings.kpi.database.xxxxxxx,! username: config['username'],! password: config['password']! )! end! ! ! ### தུ ####! def collect! sql = <<-__SQL__! select *! from (#লུ...) salse_reports! where placed_at >= '#{start_at}'! and placed_at < '#{end_at}'! order by placed_at! __SQL__! client.query(sql)! end! ! def rows! @rows ||= collect.map { |row| row.values }! end! ! def save! spreadsheet = KPI::GoogleDrive::Collection.new.spreadsheet(@start_at)! sheet = spreadsheet.worksheet(:sales_report, rows)! sheet.clear! sheet.save! end! end! end! end
֓ཁ • msql2 ͰSQLୟ͍ͯ • ݁ՌΛRubyͰΰχϣΰχϣͯ͠ • GoogleDriveʹอଘ͢Δ
͋ͱGoogleઌੜཔΉʂ
ྑ͍ • sendmail͕͍Βͳ͍ • ݖݶ༩͕ΤϯυϢʔβʔͰͰ͖Δ • ूܭ݁Ռͷू͕Ͱ͖Δ • APIܦ༝Ͱ࠶ར༻Ͱ͖Δ •
GoogleDocsͷ͍͢͝ػೳͷԸܙΛड͚ΕΔ
ѱ͍ • OAuthͷ։ൃ໘ • refresh_tokenʹ༗ޮظݶ͕͋Δ • ηϧͷܗࣜࢦఆʹบ͕͋Δ
GoogleDocsͷ͍͢͝ػೳ
QUERYؔ =QUERY(A2:E6,"select count(A) where B > 5”) ूܭʹඞཁͳதؒσʔλΛGoogleDocsʹొ ͢Ε͋ͱˢͰରԠͰ͖ΔΑ͏ʹͳͬͨ
DEMO
ࡶʹߦ͜͏!
More Decks by TAKAHASHI Kazunari
See All by TAKAHASHI Kazunari
fat-settings-yml
1syo
0
920
みなとRuby会議02やりたい
1syo
1
370
Testing Wercker plugin with bats
1syo
0
600
私を変えた1冊の本
1syo
0
890
普通のCSVアップロードフォームを作りたい(改)
1syo
9
1.1k
普通のCSVアップロードフォームを作りたい
1syo
17
3k
kaja-2013
1syo
0
560
untestable production code
1syo
0
580
authorization-for-buktorg
1syo
0
390
Featured
See All Featured
GraphQLの誤解/rethinking-graphql
sonatard
75
12k
Why Our Code Smells
bkeepers
PRO
340
58k
The #1 spot is gone: here's how to win anyway
tamaranovitovic
4
1.2k
Neural Spatial Audio Processing for Sound Field Analysis and Control
skoyamalab
0
620
The Curse of the Amulet
leimatthew05
4
17k
My Coaching Mixtape
mlcsv
0
390
How to Talk to Developers About Accessibility
jct
2
620
What's in a price? How to price your products and services
michaelherold
247
13k
SERP Conf. Vienna - Web Accessibility: Optimizing for Inclusivity and SEO
sarafernandez
2
1.7k
Beyond borders and beyond the search box: How to win the global "messy middle" with AI-driven SEO
davidcarrasco
3
300
No one is an island. Learnings from fostering a developers community.
thoeni
21
3.9k
Jamie Indigo - Trashchat’s Guide to Black Boxes: Technical SEO Tactics for LLMs
techseoconnect
PRO
0
760