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
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
The innovator’s Mindset - Leading Through an Era of Exponential Change - McGill University 2025
jdejongh
PRO
1
400
Designing for humans not robots
tammielis
254
26k
Building the Perfect Custom Keyboard
takai
2
960
Save Time (by Creating Custom Rails Generators)
garrettdimon
PRO
33
5.8k
Producing Creativity
orderedlist
PRO
348
41k
Testing 201, or: Great Expectations
jmmastey
46
8.4k
Leading Effective Engineering Teams in the AI Era
addyosmani
9
2.9k
Tell your own story through comics
letsgokoyo
1
1.1k
Context Engineering - Making Every Token Count
addyosmani
9
1.3k
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
31
3.4k
Money Talks: Using Revenue to Get Sh*t Done
nikkihalliwell
0
560
16th Malabo Montpellier Forum Presentation
akademiya2063
PRO
0
470