System Structure

CiviCRM handles the data and consists of a Contacts file which includes Individuals and Organizations. Organizations can be Campaigns, Affiliates, and Regions. There are records for each. WordPress handles the web front end. There is documentation on CiviCRM at https://civicrm.org/.

Periodic Tasks

Daily – new inquiries – check for duplicates and update or add the record. Check CiviMail for new emails to be sent.

Weekly – Backup files.

Twice a Month – 1) Within 5 days of sending the previous statewide email blast, clone affiliate meetups for the next month starting from the date of the next email blast.  2) On the 5th and 20th, send email to affiliates and county contacts asking for other events and corrections to listed events. Include dates and deadlines. Send the same email to the board of directors. Post it of Facebook. 3) Assemble and send statewide email blast on the 14th and the day before the 1st.

Monthly – A week before the board meeting, send an email to all board members reminding them that their reports, and committee reports, are due next Wednesday. On or before the Friday before the board meeting, post board reports, agenda, and previous minutes to lpedia.org with links from the Board Records page.

Monthly – On the first of the month, prepare a spreadsheet of Membership statistics for the Membership Director.

1)  Set up a spreadsheet with all Counties in column A. Name this MembershipStats_yymmdd.xlsx.
2) Search Builder for National Members.
Contact | State | Primary | = | Colorado
Individual | National Membership Type | = | Lifetime
Or
Contact | State | Primary | = | Colorado
Individual | National Membership Type | # | Lifetime
Individual | National Membership End Date | >= | Beginning End Date
3) Export using Colorado Mail Phone Format, save as CO_NatlMbrs_yymmdd.csv
4) Search Builder for Volunteers
Contact | State | Primary | = | Colorado
Individual | Volunteer | = | Yes
5) Export using Colorado Mail Phone Format, save as CO_Volunteers_yymmdd.csv
6) Advanced Search for Run For Office
Activities – Uncheck With, Type = Run For Office
Run For Office – Date = This Calendar Year
7) Export using Colorado Mail Phone Format, save as CO_RFO_yymmdd.csv
8) for each of the saved sheets,
a) sort by County, Last Name, First Name
b) Data | Outline | Subtotal | Change County | Count | County
c) add second sheet to the exported sheet, paste county list in column A
d) column B is Lookup with formula =concat(a2,” Count”)
e) column C is =IFERROR(VLOOKUP(B2,SheetName!K$2:L$1000,2,FALSE),0)
f) copy column C
g) paste Values (ctrl-alt-v, Values) from Column C into  a new column on the Membership Stats spreadsheet.
9. Send MembershipStats spreadsheet to Membership Director.

Archives

Most of the archives are over on Lpedia.org, more specifically, http://lpedia.org/Libertarian_Party_of_Colorado. That includes the Constitution, Bylaws, and Platform. You should archive those pages there before you make changes to them. If you open those pages in the WP Editor instead of Elementor, you can copy the html source code of the entire document, save it as a txt file, change the file type to .html, open the page, copy it, and paste it into the lpedia editor. Then all you have to do is change Articles and Sections to Bold and Italic. The monthly board documents – agenda, board reports, minutes, and youtube links – are also on lpedia. The lpedia.org username is lpcodbm and is linked to the databasemanager@lpcolorado.org email.

Events

There are two types of events, Paid and Non-Paid/Free. Both need to be entered in the Events application on the Sidebar Menu. Only Paid events are ALSO entered on the CiviCRM Events at the top of the CiviCRM menu AFTER you enter them on the Sidebar Event app. This is for payment processing and other technical reasons.

If you want to clone an event just once, you do that from the All Events page. Hover over the event and a menu will appear below the event name. One of the choices should be “Clone”. It duplicates the event and refreshes the list and will appear with “-2” at the end of the url and “Draft” in the entry. Edit the draft event and change the date.

Venues can be edited from the Venues program on the Events sub-menu, but you cannot change the name. If you have two venues of the same name, for example “Denny’s”, you should add the City or Neighborhood to the name, “Denny’s Capitol Hill”, to make it unique.

We send out a statewide email blast twice a month, on the 1st and the 15th. It lists all the events for the next 30 days, plus recent news events (posts). On the 5th and the 20th, we send an email to all affiliate contacts (Active and Developing) asking them to check their events and send us any one-time things like road cleanup, fairs, campaign events, etc.

Duplicate Records

Duplicate records usually come from imports of new records. They can be found with the CiviCRM > Contacts > Find Duplicates program. Use the Name and Address option and copy the names, look them up in Advanced Search, and look for conditions that should not be merged. Records should not be merged if they are Parent and Child/Father and Son, or two records with different voter ids. Those should be flagged as not duplicates.

1. CiviCRM | Contacts | Find and Merge Duplicates
2. Name and Address (reserved), select Use Rule
3. Select Group CO, click Continue
4. do not merge from the list.
5. copy several entries to notepad
6. use Advanced Search to search by Name
7. select both records, in Actions, click Merge
8. put record with voter info on right (flip)
9. check to see if duplicates (Address, Phone)
10. check to see if NOT duplicate (voter id, External Id, birthdate (both must have entry), Father/Son)
11. if voter id, external id, or birthdate are NOT same, DO NOT MERGE, flag as not duplicate if certain
12. check fields to copy, generally choose to add to info already there.
13. click MERGE button
14. if external ids do not match, send both to robert.kraus@lp.org with name to find out if they are the same, and which one is right.
15. if you can’t figure out if these are dups, send to LPCO Membership for research.

Media

The Media section contains all photos, graphic files, pdfs, spreadsheets, etc. Prefix them with “CO_”.

Newsletter Mailings

The newsletter gets mailed to recent donors. You can select these people with Advanced Search using the Contributions section and a date range and possibly an amount range. I believe the amount range applies to total contributions and not individual ones. Export them to a spreadsheet using the default fields.

Data Sharing with Affiliates and Activists

See the article on Data Sharing.

Directors

People on the state board need to be assigned their appropriate Relationship and this Relationship should be terminated when they leave the board. The Board History web page also needs to be updated. Board members may receive emails from National based on these Relationships. The From Email Addresses need to be changed when a board member changes. Ask the IT Director or National to do this. The proper Relationships for board members are:

Chair – Chair of
Vice-Chair – Vice Chair of
Treasurer – Treasurer of
Fundraising – Finance Director of
Campaigns – Political Director of
Outreach – Outreach Director of
Affiliates – Field Director of
Legislative – Legislative Director of
Membership – Membership Director of
Communications – Communications Director of
Secretary – Secretary

When a Director leave his position, or a new Director is chosen, the following tasks need to be performed.
1. Update the relationship in CiviCRM. Find the contact and go to the Relationship tab.
2. Change the Leadership page.
3. Update the lpedia main Colorado page (https://lpedia.org/wiki/Libertarian_Party_of_Colorado#Board_of_Directors), the past officials page (https://lpedia.org/wiki/Libertarian_Party_of_Colorado_Board_Past_Officials), and the individual’s page.
4. New directors need to become users in CiviCRM. They should fill out the user request form at https://my.lp.org/user-request/ and you should process that request. See the menu at the top starting with More > State Admin > Users > User Requests.
5. Update the From Email Addresses.

Assigning Directors to Local Affiliates

To assign contacts and officers of a local affiliate, select the local affiliate in Advanced Search, click View, select the Relationships tab, then select the office (Membership Director is), and then search for the contact who is in that position. Enter the start date. If you have a change in officers, terminate the old one by entering the End Date and add the new one. The County Contact is considered the Chair of the Affiliate and has a Relationship to that effect.

You can also search for the individual and assign the relationship there. The Chair of the county should match the contact on the Affiliate record, unless there is a different person assigned to that position.

Campaigns

In CiviCRM, Campaigns are stored as Organizations with the name of candidate (first last) as the organization name. You can find them on the Relationships tab on the Individual. Candidate Campaigns can exist in the database without being associated with an Individual record, but they should be with a Relationship.

To find Candidate Campaigns, go to CiviCRM, under Search, select Simple Contacts, select Candidate Campaign under Contact Type, and hit Search. After you’ve listed all the candidates, you can change the search to select the Individual associated with that campaign and repeat the search to get to the Individual record.

Candidates can be removed from the list of candidates by editing the Campaign record and changing the Campaign Status to “Declined”.

When a candidate is entered into the system,  an Individual record is created, unless an Individual record can be found, and the Run for Office Activity is added to that person.  This means that there may be two Individual records if no match is found. You need to check every new candidate and merge the two Individual records if necessary. At some point during the process of vetting the candidate, the Candidate Campaign record needs to be created. See the Campaigns Director’s Continuity Binder on the google drive.

Maps

Maps are maintained with the Premium HTML5 Maps application at the bottom of the Left Sidebar Menu.

Footer Menu

The Footer Menu is maintained with Elementor. When you are logged in, go to Pages, View the About page in a new tab, and at the top there’s a menu item “Edit with Elementor”. Below that (hover), you will see “Footer”.

Footer Menu Message

Right above the Footer Menu are fields for anyone to send a message. This email goes to info@lpcolorado.org. It does not get added to our contacts file.

Top Menu

The Top Menu is maintained under Appearance (Left Menu) and Menus.

Pages vs Posts

Pages are for relatively static information. Posts should be used for everything under Articles (footer menu) except videos.

Users

Volunteers begin the process of becoming a user here: https://my.lp.org/user-request/. The IT Director / Database Manager will receive a Pending New User email with links to documentation when they complete the form.

When you create a new user, you may need to merge it with their regular record. Best way to do this is to add something to their last name like “(dir)” so you can tell them apart. Select the records using Advanced Search. Put the user record with “(dir)” on the left. You may have to have to flip them. Their email, if different from their personal one, should go into “Other”.

When someone leaves the board, you will need to downgrade them from State Leader to something else, including “None”. You will also need to terminate their Relationship with their position by entering an End Date on the Relationship. New directors need to be added to the Relationship with their position.

When you add County Leaders and Volunteers, you need to 1) set up the Relationship on the Contact record between them and the county; and 2) choose their Access Group on the User record. If you do not choose an Access Group, CiviCRM will not appear on the sidebar menu. When someone leaves that role, be sure to add an End Date to the Relationship.

Authors for posts need to be entered as WordPress Users and each should have a short bio. Set their privilege level to None.

Bad Addresses

You should periodically check for bad addresses with Search Builder. Use the following parameters to look for people not in Colorado with a Colorado Postal Code. The State and County will probably need to be changed. You can do zip lookups on usps.com. For County, do an internet search using City and State.

Contacts>State>Primary>#>Colorado
Contacts>Postal Code>Primary>Like>80% also 81% – this gets zip codes beginning with 80 and 81. Zip codes in Colorado are in the range of 80000-81999.

To find people with missing county, use Search Builder with these parameters:

Contacts | State | Primary | = | Colorado
Contacts | County | Primary | Is Null
Contacts | City | Primary | Not Null
Contacts | Contact Type | = | Individual

Invalid Resident of County Relationships

Occasionally a Resident of County Relationship will become Inactive without an Active Relationship taking its place. We don’t know why. You can find these in Advanced Search. In Address, select your state and county. In Relationships, select Type = Resident of County, Status is Inactive, and in Target Contact, put the same county. Some of the selected records will be ok because the person moved from the county in question to a different county. The invalid ones will just have an Inactive Resident of County Relationship and no Active one. To fix this, edit and save an Address, and then edit and save the Inactive Relationship.

Emails

Before sending emails, send a test email to yourself and check to see that any links in the email are correct. See also the article on Email Guidelines. Contacts have several fields related to emails spread over 3 sections. There’s Email Addresses, Communication Subscriptions, and Communication Preferences (Privacy, Preferred). The No Bulk Emails flag is under Privacy.

State Admin can send emails via either CiviMail or SendMail. All other users may only send email via SendMail which limits them to 50 or fewer emails. However, all other users may set up a CiviMail mailing to any number of people. It is the State Admin’s job to check CiviMail daily for emails from other people that need to be sent.

Bounced Emails PostCards

Sending emails can result in bounced emails, because people change their email addresses all the time. Here’s how to find them and send them a postcard asking for a new email address.

Bounced Email Process
1. go to Civicrm > Mailings > Mailing Reports
2. select Mail Bounces
3. Add columns Contact Id, Email, Mailing Subject, Bounce Type
4. Add Filters
Bounce Date, Bounce Type = Invalid
5. Export results to csv, save as BouncedEmails_YYMMDD.xlsx
6. Open latest backup file.
7. Add these columns to Bounce file
FirstName =VLOOKUP($B2,[CW_Backup_200719.xlsx]All!$A$2:$BZ$75000,7,FALSE)
LastName =VLOOKUP($B2,[CW_Backup_200719.xlsx]All!$A$2:$BZ$75000,9,FALSE)
Address =VLOOKUP($B2,[CW_Backup_200719.xlsx]All!$A$2:$BZ$75000,58,FALSE)
City =VLOOKUP($B2,[CW_Backup_200719.xlsx]All!$A$2:$BZ$75000,59,FALSE)
State =VLOOKUP($B2,[CW_Backup_200719.xlsx]All!$A$2:$BZ$75000,61,FALSE)
Zip =VLOOKUP($B2,[CW_Backup_200719.xlsx]All!$A$2:$BZ$75000,62,FALSE)
Zip+4 =VLOOKUP($B2,[CW_Backup_200719.xlsx]All!$A$2:$BZ$75000,63,FALSE)
Name =CONCAT(F2,” “,G2)
Address =(H2)
CSZ =CONCAT(I2,”, “,J2,” “,K2,IF(L2<>”0”,CONCAT(“-“,L2)))
8. Print out enough BouncedEmailsPostCard on 100 lb cardstock.
Divide number of entries in Bounced Spreadsheet by 4, plus 1.
Because of the paper weight, you may have to print them one at a time.
9. Put the printed cards back in the printer so you can print on the other side.
10. Open the PostCardReturnAddress word document and save a temp version.
11. Copy 4 lines of Name, Address, and CSZ to a notepad item.
12. Format each line into an address block
with enough spaces preceding each line to position it properly on the card (about 40).
13. Replace 3 blank lines on each card with an address.
Because paste adds a carriage return line feed,
you may have to delete the following line to keep the 2nd postcard
and subsequent ones vertically aligned.
Reopen the temp version if necessary.
14. Print the page.
15. Repeat steps 11-14 until finished.

Local Districts

The CRM has fields to store the County Commissioner, City Council, and School Districts. These need to be acquired from the  individual counties because the SOS will not provide them. I collect them in a spreadsheet called CountyPrecinctSplit. The Split is a part of a precinct and there can be multiple splits in a precinct. This spreadsheet needs to be built before processing the SOS voter file for import. VLookups are used to extract information from this spreadsheet.

A complete list of Precincts and Splits can be obtained from the SOS Voter file by copying County, Precinct, and Split from the Voter file to a new spreadsheet, combining those fields, and deleting duplicates. The combined field is then separated into separate columns.

Denver may be the easiest county to get local districts from because they have easy to read precinct maps of City Council, School, and RTD districts.

Conventions

At the convention, you will get a new board. Each old board member needs to have their board/leadership Relationship terminated by entering the End Date. New board members should start a Relationship with a Start Date. Relationship Types do not exactly align with our board positions, so use the Relationship Type from the previous person in that job.

Backups

Backup all the Individual Records weekly with the following procedure. This will give you a file that you can use to check for duplicates and recover lost data if someone makes a mistake.

For Individuals, in Advanced Search, set Contact Type = Individual and Group = CO State. Select all records. Select Export. Choose Export Map CO_AllFields_2023. Select Download. Save it in the Download folder. Rename it from .csv to .txt. Open it in Excel. Change the following fields to Text: Contact Id, First Name, Middle Name, Last Name, Postal Code, Postal Code Suffix, External Id, Voter Id. Save it in the Backup Folder as CW_Backup_yymmdd.xlsx.

The AllFields map for exporting does not save Notes or Volunteer Notes because the process of exporting the rest of the file introduces carriage return/line feeds in multi-line note fields, which causes extra lines in the spreadsheet. So we need to export those separately. In Search Builder, select Contacts > Notes: Body Only > Not Null plus another set of criteria Individual > Volunteer: Volunteer Note > Not Null .

1. select All Records
2. Action: Export Contacts
3. Select fields for export
4. Use Saved Field Mapping Default_Notes
5. Continue
6. Export
7. Save File (do NOT open the file in Excel)
8. Open Windows Explorer
9. Select downloads folder
10. Rename file to CW Notes yymmdd.csv
11. On CiviCRM page, select Done.
12. Copy the file to the Backup folder.
13. Upload both backup files to the Amazon drive.

Contributions Backup
CiviCRM > Contributions > Contribution Reports >
View All Reports > Contribution Report Templates > Contribution Detail Report

In late 2022, all of the contributions from day 1 of the National LP were imported into CiviCRM. The historical contributions were backed up on 12/4/2022 as 2 files, CW_Contributions_19700101_20001231.csv, and CW_Contributions_20010101_20191231.csv. So the only transactions you have to back up on a regular basis are those beginning 1/1/2020.

Columns>Contact ID
Filters>Date Received, From: 1/1/2020, To: Today
Group = CO State
Contributions or Soft Credits: Both
Export Report
Rename as CW Contributions yymmdd

You should also use Advanced Search to do monthly backups of 1) Campaigns (Default_Candidates); 2) County, Regional, and Multi-County Affiliates (Default_Affiliates)  (change County to Organization Name, add Upcoming Events). Use the Contact Type to select the records.

Backup the Board Members and volunteers.

Report CO_BoardMembers and Volunteers
Change Filters to
Contact Type B – Add County Affiliate, Multi-County Affiliate, Regional Affiliate
Contact B – Remove all but “colorado”
Export as CW_BoardMembers_yymmdd

Import the SOS Voter file

See a more current version here.