Wanted - Script for MilePoint Kiva team to assist with team accounting

Discussion in 'Travel Technology' started by iolaire, May 9, 2012.  |  Print Topic

  1. iolaire
    Original Member

    iolaire Gold Member

    Messages:
    3,510
    Likes Received:
    5,767
    Status Points:
    4,170
    Do you have scripting skills and a bit of free time? If so please volunteer help fellow MilePoint members by automating our team accounting.

    Background, Milepoint’s kiva team is very active, we are consistently near the top for new loans, new members, total money invested etc…, yet we are constantly seeking to increase our team size and promote increased participation.

    Kiva has a nice API that returns .json or .xlm data. We would benefit from a few scripts that would provide us the following:
    Over period of time:
    1. New members
    2. Members who make a Kiva loan
    3. Number of invites sent by Members

    We hope to do some sort of promotion in June, so it would be ideal to find someone who could write the code over the next few weeks. Ideally the code would be shared with the MilePoint community in case someone needs to support it later on.

    Links:
    http://build.kiva.org/
    http://developers.wiki.kiva.org/w/page/14405528/FrontPage

    Our team’s loans, page #2 as xml:
    http://api.kivaws.org/v1/teams/18077/loans.xml?page=2

    Sample code:
    PHP:
    http://www.taborsky.cz/kiva/src.php
    http://www.taborsky.cz/kiva/

    Google apps (python):
    http://kivarecruit.appspot.com/displayTeamStats
    https://github.com/jdfoote/Kiva-Team-Tools

    Google spreadsheets:
    http://en.freedonne.org/kiva-team-k...h-statistics/how-was-the-statistics-page-made

    There are many other code samples available. From what I can see the ruby examples/libraries do not address team stats.

    Please reply to this thread and volunteer your skills!
     
    miles and smiles likes this.
  2. iolaire
    Original Member

    iolaire Gold Member

    Messages:
    3,510
    Likes Received:
    5,767
    Status Points:
    4,170
    Update May 14th I'm moving forward on this and found that http://kiva.rubyforge.org/ (thats not right I'm using: https://github.com/simplificator/ruby-kiva ) works fine for team data. I'm most familiar with Ruby on Rails so I'm setting up some batch scripts to load data into MySQL, right now all stats are via manual MySQL queries.

    If anyone wants to help or look at my code let me know. (Code is in a private SVN repository.)

    Later I post some sample stats, to date I've learned that 10% of or Kiva team members have made zero Kiva loans.
     
  3. freqflyercoll

    freqflyercoll Silver Member

    Messages:
    24
    Likes Received:
    72
    Status Points:
    240
    I've been a bit busy of late, but I'd be interested in helping out on this where I can.
     
    iolaire likes this.
  4. freqflyercoll

    freqflyercoll Silver Member

    Messages:
    24
    Likes Received:
    72
    Status Points:
    240
    I'm mostly familiar with PHP, but I don't mind picking up Ruby to help out
     
    canucklehead and iolaire like this.
  5. Wandering Aramean
    Original Member

    Wandering Aramean Gold Member

    Messages:
    28,201
    Likes Received:
    61,716
    Status Points:
    20,020
    I would be happy to produce this for you, though I definitely won't be writing it in PHP or Ruby. That said, I'm pretty decent at parsing XML/JSON as can be evidenced by my existing fare/award tools site. And I should be able to get it done within the time frame you've described, though I'd need to confirm exact specs to commit to that.
     
    canucklehead and iolaire like this.
  6. iolaire
    Original Member

    iolaire Gold Member

    Messages:
    3,510
    Likes Received:
    5,767
    Status Points:
    4,170
    Thank you both for offering help. Wandering-aramean since your working in a MS environment I don't know how much I could help other than providing specs, but if you would like to taker over I would be happy. Otherwise I should be able to get the nesasary data parsed into MySQL regularly and could use the assistance on a UI.

    Here is an update as to where I am and what I think needed to be done.

    Keep in mind at this point the specs are mine. I've mentioned them in some PM's with various parties who are trying to get a new promotion going and people think it will help, but at this point I'm the driving force. So if you help, it's helping my efforts (to promote our kiva team), not a sanctioned forum promotion.

    I can now get a snapshot of our team members, that tells us information like the average number of loans (not just attributed to or team), our number of invites sent per member, plus when each member joined the team. This should help if someone is running a promotion for new members, or for existing members sending invites, or to a member that made any loan during a time period. I need to add historical stats tracking so I can see the change on the member level over time.

    I can pull all loans for a user, or all users. Since I can not exactly pull when a member makes a loan and credits it to the team, but we do have team loans, I need to merge team and member loan lists to identify where there is a overlap. This will allow us to insure members receive credit only for loans attributed to our team. I think this is needed because many members may credit loans to muliple teams.

    Right now most stats will be MySQL queries to save time, it would be good to have a UI that anyone could use. Also we will have vast insight into our teams actions, so there are all sorts of map and visualization opportunist. Such as proving member country loaned to lists, and then suggesting active loans to fill gaps.

    Thanks
     
    canucklehead likes this.
  7. iolaire
    Original Member

    iolaire Gold Member

    Messages:
    3,510
    Likes Received:
    5,767
    Status Points:
    4,170
    canucklehead likes this.
  8. iolaire
    Original Member

    iolaire Gold Member

    Messages:
    3,510
    Likes Received:
    5,767
    Status Points:
    4,170
    Here is the existing table structure, note that people = our team members, placements = our loans. I've not yet built the historical tables to keep things like people.uid, people.invitee_count, and people.run_date etc..


    Code:
    CREATE TABLE `peoples` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `uid` varchar(255) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      `loan_count` int(11) DEFAULT NULL,
      `invitee_count` int(11) DEFAULT NULL,
      `country_code` varchar(255) DEFAULT NULL,
      `member_since` datetime DEFAULT NULL,
      `team_join_date` datetime DEFAULT NULL,
      `loan_because` text,
      `whereabouts` varchar(255) DEFAULT NULL,
      `occupation` varchar(255) DEFAULT NULL,
      `occupational_info` text,
      `personal_url` varchar(255) DEFAULT NULL,
      `image_url` varchar(255) DEFAULT NULL,
      `created_at` datetime DEFAULT NULL,
      `updated_at` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=727 DEFAULT CHARSET=latin1;
    Note: The API gives various terms data points, the library I'm using breaks them out but for now I was just concatenating them into the terms column.
    Code:
    CREATE TABLE `placements` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `loan_id` int(11) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      `activity` varchar(255) DEFAULT NULL,
      `use` text,
      `borrower_count` int(11) DEFAULT NULL,
      `partner_id` int(11) DEFAULT NULL,
      `status` varchar(255) DEFAULT NULL,
      `description` text,
      `loan_amount` int(11) DEFAULT NULL,
      `funded_amount` int(11) DEFAULT NULL,
      `paid_amount` int(11) DEFAULT NULL,
      `basket_amount` int(11) DEFAULT NULL,
      `sector` varchar(255) DEFAULT NULL,
      `delinquent` varchar(255) DEFAULT NULL,
      `journal_totals` int(11) DEFAULT NULL,
      `terms` varchar(255) DEFAULT NULL,
      `funded_date` datetime DEFAULT NULL,
      `posted_date` datetime DEFAULT NULL,
      `paid_date` datetime DEFAULT NULL,
      `location` varchar(255) DEFAULT NULL,
      `image` varchar(255) DEFAULT NULL,
      `video` varchar(255) DEFAULT NULL,
      `created_at` datetime DEFAULT NULL,
      `updated_at` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=630 DEFAULT CHARSET=latin1;
    Code:
    CREATE TABLE `people_placements` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `placement_id` int(11) DEFAULT NULL,
      `people_id` int(11) DEFAULT NULL,
      `created_at` datetime DEFAULT NULL,
      `updated_at` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=630 DEFAULT CHARSET=latin1;
     

Share This Page