$30 off During Our Annual Pro Sale. View Details »

雑につくるKPIツールのススメ

TAKAHASHI Kazunari
March 28, 2015
380

 雑につくるKPIツールのススメ

TAKAHASHI Kazunari

March 28, 2015
Tweet

Transcript

  1. ࡶʹͭ͘Δ
 KPIπʔϧͷεεϝ 2015/03/28 Minami.rb ࠷ॳͰ࠷ޙͷLTେձ

  2. ࢲ͸…. • @1syo • Rails Programmer • Yokohama.rb • 2012

    Ruby kaja award winner • MinatoRubyKaig01 organizer • TDDBC Yokohama TA
  3. MinatoRubyKaigi02

  4. ͨͿΜ9݄, 10݄

  5. KPIπʔϧͱ͸ʁ • Ϣʔβʔ਺.. • ച্… • ೔࣍ɺ݄࣍ɺྦྷܭ.. • Έ͍ͨͳΞϨ..

  6. ։ൃͷ࣮৘… • ϦϦʔε௚ޙ͔Βඞཁ • ཁ͕݅;ΜΘΓ͍ͯ͠Δ • ޻ظͱ͔ͳ͍

  7. Ͳ͔͜ͰखΛൈ͔ͳ͍ͱਏ͍

  8. ਺ࣈͷਖ਼֬͞ > ࢖͍উख

  9. άϥϑ = ࢮ

  10. ͪΐͬͱલͷ΍ͭ

  11. #! /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`! ! from=no-reply@example.com! to=admin@example.com! 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'`!
  12. /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__
  13. ֓ཁ • mysqlίϚϯυͰSQLΛୟ͍ͯ • ݁ՌΛawkͰCSVʹͯ͠ • mailͷఴ෇ϑΝΠϧʹͯ͠ૹ৴

  14. ͋ͱ͸ΤΫηϧઌੜཔΉʂ

  15. ૂ͍ • SQLͰਖ਼֬ͳ਺ࣈΛग़͢ͷʹઐ೦͍ͨ͠ • ΈΜͳେ޷͖ΤΫηϧઌੜͷݞʹ৐Γ͍ͨ • ؒҧ͍Λࢦఠ͞ΕͨΒ௚͙ʹमਖ਼͍ͨ͠

  16. Α͍఺ • SQLͰΞΫηεͰ͖Ε͹େ఍ूܭͰ͖Δ • ৔߹ʹΑͬͯ͸shellΛۦ࢖ͯ͠ϑΝΠϧ΋ू ܭͰ͖Δ • େྔੜ࢈͠΍͍͢

  17. ѱ͍఺ • sendmail͕࢖͑ͳ͍ͱͳ͍ͱख٧·Γ • ʮʓʓ͞Μ΋ݟ͍ͨʯ͕݁ߏ໘౗ • ूܭ݁Ռ͕෼ࢄ

  18. ࠷ۙͷ΍ͭ

  19. 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
  20. ֓ཁ • msql2 ͰSQLୟ͍ͯ • ݁ՌΛRubyͰΰχϣΰχϣͯ͠ • GoogleDriveʹอଘ͢Δ

  21. ͋ͱ͸GoogleઌੜཔΉʂ

  22. ྑ͍఺ • sendmail͕͍Βͳ͍ • ݖݶ෇༩͕ΤϯυϢʔβʔͰ΋Ͱ͖Δ • ूܭ݁Ռͷू໿͕Ͱ͖Δ • APIܦ༝Ͱ࠶ར༻΋Ͱ͖Δ •

    GoogleDocsͷ͍͢͝ػೳͷԸܙΛड͚ΕΔ
  23. ѱ͍఺ • OAuthͷ։ൃ໘౗ • refresh_tokenʹ༗ޮظݶ͕͋Δ • ηϧͷܗࣜࢦఆʹบ͕͋Δ

  24. GoogleDocsͷ͍͢͝ػೳ

  25. QUERYؔ਺ =QUERY(A2:E6,"select count(A) where B > 5”) ूܭʹඞཁͳதؒσʔλΛGoogleDocsʹొ࿥ ͢Ε͹͋ͱ͸ˢͰରԠͰ͖ΔΑ͏ʹͳͬͨ

  26. DEMO

  27. ࡶʹߦ͜͏!