LPUS Database-archive
From Libertarian Wiki
This page was added by Bonnie to archive out the old "issues" section of the main [LPUS_Database] page after the May 5, 2005 phone conference with the state DBAs. The issues section was started fresh with a consolidated, five-tier list of prioritized open items. If you feel something was incorrectly archived and not carried forward onto the new open list, please either bring it up on the lp-database list, or post it to the Bug Tracker, which was graciously set up by Steven Kuck, a Nidus collaborator.
[edit] The issues section as it was on May 5, 2005
archived off and updated by Bonnie
[edit] Issues
The following is a summary of issues expressed by at least one person, and status as reported by LPUS if any. Resolved issues are listed first. Unresolved issues are listed second.
[edit] Bug Tracker
The Libertarian Party Fix-it List PHP Bug Tracker is now available at http://phpbt.lpf.org/, courtesy of Steven Kuck.
[edit] Issues Resolved
The following are issues that are generally considered resolved.
[edit] Zip+4
- RE does indeed store Zip+4 and it is entered when we are given that information.
[edit] A single ID field with unique permanent values
--Each data dump file that you receive contains the unique Member ID as the first field on the file. This is a unique number and does not change. Some of the IDs may have "P" or "M" in front of them, but that was the way the data was converted into Raiser's Edge from the prior system. All new, non-converted entries into RE do not have the letters "P" or "M".
[edit] put Last Name and First Name in separate fields
This should have been fixed.
[edit] Only active members were included in the data dumps
Everyone should now be getting the Lapsed members. (And inquiries.)
[edit] last updated field
A field containing the date of the last change to the record helps determine which records have changed and need to be processed into the affiliates database. Without this, every pair of records must be compared.
In the "Monthly Dumps" e-mail in early 2004 Jan, Jennifer N. Villarreal stated that 'In keeping with my promise to give at least 3 weeks notice of any format/layout changes, next month's data dumps will have 1 minor modification to them. The 'Date Changed' field will be added to the files. This is basically the equivalent of 'Last Updated' that a lot of you have been requesting. So look for this field on next month's files. It will be the last field on the files.'
[edit] Issues Unresolved
The following are issues that are generally considered unresolved. Most are things that the old database dumps provided, but the new Raiser's Edge (RE) dumps do not [yet] provide.
[edit] Dump File Formats Other Than Excel
There are several different requests for the monthly files to be sent in Excel, TXT, CSV, or DBF format.
Supposedly RE can export the data dumps in pretty much any format desired. However an earlier dump from LPUS in CSV format was unusable, mainly because fields were truncated to 9 characters.
- This is my most important need. I can't do anything until I can read the files. - DWR@LPMA.
[edit] using old inquiry ID numbers
The inquiry IDs that appeared in the ID_NO field do not appear in the new dumps. These IDs were used by many states to match records. Without these numbers matching records is probibitively labor intensive.
- This is my second most important need. - DWR@LPMA.
[edit] provide data in a single file instead of 3 files
This is how RE exports the data. Unfortunately, there is really nothing that we can do about this.
[edit] Ability to view transaction (ie donation) history
This is possible via RE:Anywhere, but the state-level security is still being defined, so I cannot give a definite answer.
[edit] Ability to view change history
While this is available through regular PC-based Raiser's Edge, change history is not visible via RE:Anywhere.
[edit] Inclusion of "total contributions in last year" field in dumps
For now we are making no field changes to the data dumps.
[edit] Ability to view CC info for bonded state-level users
Credit Card information will probably be hidden for security purposes, especially since RE:Anywhere is a web-based system.
[edit] Voter Registration Data
Data included should be:
1) congress,
2) county/city/town jurisdiction (in some states two of the three),
3) voter street address--the rest can be figured out from this
4) state legislative districts
5) voting precinct
6) local council/board districts
7) regional election districts (such as soil & water boards, etc)
RE does store Congressional District, but that is all.
In FoxPro, this was the CONG and CONGDIST fields, and Chuck Eby charles@eby.name was reporting problems with the accuracy of those under the FoxPro system. We also purchased Congressinal district data once for the "Write to Congress" feature of www.lp.org.
We might be able to buy a separate database that tells us district information about our members and inquiries.
Marc Montoni writes on the lp-database list: "The nationwide matching file would be $9500. This gets us four updated tables a year (zip codes change and are created, etc.
"I also asked them whether they had done any studies of their tables' accuracy, and he said he knew the last audit they had done showed their matching tables had a 99.2% accuracy rate.
"The file also contains state legislative districts and counties/cities."
He adds, "One of the things we've used them for in Virginia is doing a quick email blast within a day or two of a Special Election being announced to see if we have any members within that district who are willing to run for the open spot."
California has also used voter data to identify races for their members to run in. They got the libertarian enrollment from their state. With the Help America Vote Act, this information more centralized and, for example, in New York, we might be able to get one file with one format of voter data for our state in the future, instead of it being in a different format for each county (58 formats, because the NYC boroughs are combined).
The remaining problem is #3, from above. We need a place somewhere to store the voter's voting address, if it's different from the normal mailing address, so that we can do this district matching with an external DB and program. Unfortunately, I don't see any fields for alternate addresses in the current RE layout (see: #New_Database_Dumps)
BJS: Presumably, if we purchased this data, we could also re-purpose it and get the 'Write to Congress' feature on www.lp.org working again. (It's hidden now.)
[Marc Montoni]: Actually the "Attributes" screen of RE is entirely user-customizable and could contain fields for residential addresses. Not a difficult change to make, despite the protests.
[edit] Script for doing data dumps automatically in state's chosen formats
add function to combine tables into one for states that so desire.
With RE:Anywhere the states will be able to create whatever kind of data dump they wish.
[edit] Add "record creation date" field to member data tables
We will be making no additions to RE delivered tables. (see my explanation below)
[edit] Add the "InquiryID" number to member tables to facilitate de-duping
We will be making no additions to RE delivered tables. (see my explanation below)
[edit] Expiration Date and Join Date have 2-digit years in CSV files
RE stores years as 4-digits always. So there should be a way to convert all for digits into CSV files.
[edit] The Member Type codes have changed
This is also due to the fact that RE is not a custom system. It has its own defined member types that we must use.
In this case, the meaning of the RE member type should be explained.
[edit] Other Missing Fields Or Values
Deactivated IS NOT MISSING. The equivalent is
> CnMem_1_01_Standing > Allowed values are: > Active > Deceased > Dropped > Lapsed
DUPLICATE, ONE_ADD (for combining household mail), and Bad Address Info do seem to be missing, at least from the dumps that the states get.
[edit] Place emails in their own persistent field; phones in their own persistent fields
RE does not have separate Email and Phone fields. Instead, it has Contact1, Contact2, and Contact3 fields. A person could have 2 phone #s and 1 email or 3 phone #s or 2 emails and 1 phone #. Therefore, we cannot dedicate each field to be 1 or the other. That would limit us in storing whatever contact information we have obtained.
[edit] Correct your "BAD ADDRESS" notation system
We will be making no additions to RE delivered tables. (see my explanation below)
[edit] Allow state DB operators to enter corrections in real time
In the beginning at least, RE:Anywhere will provide query and reporting capability to the states. Either that or RE:API or the Raisers Edge interface are needed to maintain data integrity when updating the database.
BJS: The lack of real-time access is a step backwards from state-developed systems, which allow authentication down to the county level and real-time updates, cached locally. E.g., Nidus, from Greg Dirasian of MI. (Nidus is currently available again.)
The states can use an alternate "presentation layer" for their data management needs, and forward along changes for national to integrate at their leisure.
Automated imports ARE needed for national. We know Raisers Edge supports that.
"Data integrity issues" should be ensured with nightly backups, which I hope are being done.
[edit] database change files instead of complete dump files
Many states maintain their own DB. Is there anyway we can setup a system for you to send changes to us and vice-versa? Basically forgo the idea of "dumps" and just send update transactions back and forth?
[edit] allow states to make edits/updates to the RE database
For now, if you have updates that need to be made then they should be sent them to Jennifer in Excel format.
[edit] Need to back-fill inquiry dates prior to 9/10/04
Dates of leads in the inquiry table are listed as 9/10/04 if they were in the system prior to the migration to RE, which presumably took place on 9/10/04. These data need to be backfilled from the FoxPro system.
[edit] Life members not included when calculating UMP Payments
THIS SHOULD BE REOLVED NOW: WAITING FOR CONFIRMATION WITH FEB UMP CHECKS
30 Dec 2004: December UMP payment to Florida dropped $250.
Payment for life members over 12 months, dropped from 5 dollars per month to zero.
FLORIDA: 98 lifetime on member list 60 removed from UMP payment list 38 on UMP payment list
December Payment for 38
3x1.00=3.00 8x2.00=16.00 5x3.00=15.00 8x4.00=32.00 14x5.00=70.00
38-members $136.00
98x5.00=490.00
Short $354.00
Jack Tanner, Treasurer@LPF.org
--
Jan 2005: NY's UMP has also dropped dramatically, from $1025 to $845, presumably because of this same problem.
--
Feb 2005: Response from Jennifer:
When I was first instructed to create the UMP calculations from RE, Dixon gave me explicit directions to only pay UMP on active members who had given $$ in the past 12 months. Therefore, when I wrote the query to extract the data from RE it did not include life members who hadn't given $$ in the past 12 months. I communicated with Dixon that the UMP payments would be decreased because these individuals were not being included in the calculation. This was fine with both Dixon and Nelson at the time. Just this past week I was instructed by Dixon to go back and now to add the life members to the calculation regardless of whether they have given $$ any time recently.
Question: can we assume that the missing payments for life members will be made up? It sounds like it, but doesn't exactly say that.
[edit] explain the RE database layout
"They are working on it." See #New_Database_Dumps for the list of fields.
Greg Dirasian writes Feb 17, 2005:
If we have official answers to any of these, somebody please let me know.
1. I would like to see the list of tables and fields for each table in the RE database. I may choose to implement the same tables in my database or I may choose to make some changes.
2. I would like to see each field in the new database documented. This includes size, type, possible values, required or not, and how those fields are used. I can figure out that CnBio_First_Name is the first name field, but other things like CnAttrCat_3_01_Description are less obvious -- it has values like RM and SM or is blank.
3. There seem to be some new member types. What is the difference between an "Honorary Basic Member" and a "Basic Member"?
4. How are the id numbers created and what do they mean?
5. There is a field called "CnAttrCat_2_01_Description" which has a two digit number in it. What is it? What do the numbers mean?
6. If we have two people at the same address and they only want one mailing, how is that represented in the database?
7. In Michigan (and I imagine this problem extends to other states), we have a county called "St. Clair", however, sometimes RE refers to it as "Saint Clair" and sometimes as "St. Clair". Why don't we have consistent spelling.
8. There is no "San Francisco" county in Michigan, however, "San Francisco" appears in the county field for one of our members who lives in "Washtenaw" county.
9. How do we distinquish between donors/subscribers, and members?
10. When an inquiry becomes a member, does the member id change?
11. We have several id numbers that begin with "O-". I assume these are from the FoxPro organizations table. These records are incomplete, more specifically, they lack a name.
12. What is the process for entering new members in the database? In the FoxPro system, sometimes national would create an inquiry record before creating a member record, sometimes not. Sometimes we would have two ids for a person sometimes not. Understanding the process will help me design my database better.
13. I previously asked how we send updates to national and I was told I should ask Sam New for the "spreadsheet". Could somebody please send me the spreadsheet or upload it to the "files" section of this yahoo group. I have no confidence in getting a response from national.
14. I am more than a little concerned about a database that puts email addresses in a field called CnAdrPrfPh_1_01_Phone_number. I just want email addresses to be a little more than an afterthought.
15. How are bad addresses handled?
After I get answers to these, I'm sure I'll have more. I reiterate, if we have official answers to any of these questions, please let me know.
Thanks, Greg
[edit] are web inquiries and 1-800 number inquiries going into Raisers Edge?
And if not, what is happening to them?
Terry Floyd, MCSE, MCDBA, and local CA LP leader writes:
I used to get inquiries forwarded to me automatically whenever someone living in my region called the 1-800 number or provided their contact info on the LP.ORG website or the Advocates for Self-Government website. These used to be automagically forwarded to me by a script written by (I presume) Joe Dehn [Note: for California]. I used to get four or five of these every month
Since the rollout of RE, I have not received any inquiries in this manner. [Note: presumably, because it got broken in the transition, and there was no communication about how to fix it] The only inquiries I've gotten since last April 2004 have been manually generated by Matthew Dailey or Sam New, and they broadcast these to every member of the California LP Executive Committee for anyone living in California, regardless of whether they were residents of my region or not.
I used to get four or five of these every month, but since the new database was instituted, I have only gotten four or five inquiries in about nine months. And this was during a Presidential Election! In the 1996 and the 2000 general election, I was getting a dozen or more inquiries every month leading up to November. In 2004, I didn't get more than 15 inquires for the entire 12 months.
John Clifton, State Chair of NY, who recently changed email addresses because mail was bouncing at his old address, writes:
I have not received leads from national in quite a while (a few months). Sam New mentioned he would straighten out getting volunteers and other notices to my current address a few weeks ago.
Bonnie Scott, LPNY Secretary, writes:
I used to have access to update NY-related info at www.lp.org, and that same interface gave me the ability to download a file with all of the volunteers at that website that listed NY as their state--i.e., the web leads that were also forwarded to our chair. I maintain the 'candidate volunteers' spreadsheet for our political director and hope to start up a volunteer/project matching system, so I have a need for this data, and I had been making corrections there, fixing duplicates, etc, while I had access. John Clifton is not getting these leads at his new email address, so they are just going into the bit bucket and we have no way to retrieve past volunteers (my candidate volunteer archive them ends at January 2004). I haven't been able to access that admin interface for a while, and would like to. I heard a rumor that some of the leads in that database from the Kia era were lost in the transition. (So we'd only have the volunteers in that DB since the transition from Kia.) Is that true? I hope not.
Steve Healey, Monroe County (NY) LP leader, writes:
I never received inquiries directly from National; they were relayed by various NYers. I have not seen one in many months.
Jak Karako, NYC LP leader, writes:
Yes I still get them. Though very few (averaging one a month)
[edit] Phone numbers missing
We seem to have lost a lot of phone numbers in the conversion process. I have over 200 members in my county and keep my database about 80% accurate. The dump from national doesn't even have my phone number in it -- and I know they call me for money.
Less than 10% of our current members have phone numbers. Greg Dirasian
Chris Farris writes:
The three fungible fields we get for phone/email only cover the "Work Phone" "Home Phone" and "Email" fields in RE. There are some 20 other different phones (Home-Fax, Home1 Home-Voice) where phone info sometimes appears.
We need to either move all those numbers into the fields we get in our dump OR get more fields in our dump.
[edit] Spaces in file names
For those of us on Unix-based systems, it requires special handling. Can they be avoided for the data dumps?
[edit] Expiration dates wrong for life members
My expiration date is set to 12-13-01, but I am a life member. My member type is appropriately marked as a life member, but the expiration date shows me as expired.
This is stupid because now I need to check two different fields to see if somebody is a member.
Greg
[edit] How many user-customizable fields do we get?
Is there are limit on the number of user-customizable fields we get? Are they global (need to be agreed on by all the states and National), or are they per-state?
[edit] What is the process to inform national of member renewals and new members?
Under UMP II, the states get the 'credit' for getting a new membership. What is the process for transmitting this information (as well as the money) to national? Greg Dirasian has a member who renewed locally in September and is still not in the national database.
We need defined and documented processes. It would be nice if these were Internet based processes, but really, anything would do.
[edit] LPN subscribers turned into Benefactor Life Members
In the Vermont data, I have four cases of LP News subscribers being turned into Benefactor Life Members.
[edit] The 'specs' section as it was on May 5, 2005
[edit] What we need, where we are
by Bonnie Scott, (NY)
Call it KM (Knowledge Management), call it working smarter-- whatever you call it, we should be doing it better, given that we're the most computer-oriented political party.
This is a brief description of my view of the Big Picture.
My Big Question is: What do we, as a political party (from the local to the national level), need to track in order to be effective?
I don't really include the accounting system proper here--it was updated in 2002/2003 by Jason Gray, who transitioned HQ to QuickBooks (QB). Raisers Edge (RE) is not designed to manage individual transactions (other than donations) like an accounting package can. (Although we could buy its convention module, etc.)
It was promised that QB would just feed the relevant donor data into RE. Obviously, that's a critical link for maintaining donor history and membership info. Getting the 'import' of donation data in RE within $8K of prior balances during the transition was a nice feat, although we paid for it with other data not focussed on.
I see five main categories of data for us: People; Candidates; Money; Projects; Allied Groups.
- People
Members, inquiries, candididates--anyone who is Libertarian or has shown some interest. Needs to include their skills and their 'get off the couch' issues, like the volunteer DB behind the scenes at http://www.lp.org did.
- Candidates
Like on our Kia-built website, this covers data not directly overlapping with the 'People' tables, but it can be joined with them. E.g., who's a candidate for which office; campaign info; election results; voter data
- Money
This is what Raiser's Edge is designed for, and why it was desired. This should cover tracking fundraising appeals for effectiveness, monthly pledges, all donations from people. We've got it; it will do this nicely if data are entered properly.
- Projects
We get volunteers who want to help. We get people with good ideas. Yup, just think of what we could accomplish if we could efficiently match them up by region and interest (e.g., by issue and type of work).
- Allied Groups
Includes affiliates and college chapters, as well as other groups that agree with us on at least one issue.
Usage Note: Things like 'Legislative Watch' can be shoehorned into my paradigm as a Project, keyed by affected region and related issue(s), which then link you to the Allied Groups we can work with and People who share the interest in doing something about it, and so on.
coming next: some of our legacy sources of this data and code that can manage them
[edit] Some random notes that need to be pulled together
[edit] member renewal process
Greg writes, re: the member renewal process, and for new memberships initiating with the states that we need a defined process.
"We need an audit trail and FEEDBACK from national so that we know when tasks are completed.
Communicating with national seems to be a one way process."
How can we fix this?
[edit] Voter/Rep info
In summary, if some creates a system for the LP nationally, then it should contain a contact log along with fields to track where the legislator stands on specific issues (sponsor, in favor, slightly in favor, neutral, slightly opposed, opposed). -- HM
