Database Manager

  1. Home
  2. Knowledge Base
  3. System Management
  4. Database Manager
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.

Periodic Tasks

Daily – new inquiries – check for duplicates and update or add the record.

Weekly – Assign new signups to a Phone Call Activity to both the Welcome Committee and the Membership Director, 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.

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.

Elementor

Bylaws, Constitution, and Platform use Elementor, an editing tool. Under Elementor in the Left Menu is a link to their Knowledge Base. You should read Build Your First Page.

You have sections, columns, and widgets in Elementor. Sections contain Columns and Columns contain Widgets. The Section Edit button is at the center top of the section. The Column Edit button is in the upper left corner. The Widget Edit button is in the upper right corner.

You can move widgets by grabbing the widget edit button and moving it to a different location in the column. The main widget for the BC&P pages is a “Toggle”, which allows you to show/hide detail text. The best way to add a new widget to a document if you already have one in the document is to duplicate it, which copies all the properties of the old one. Right click the widget edit button and click “Duplicate”.

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. Edit the 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.

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.

Assigning Directors to Local Affiliates

To assign contacts a 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 and add the new one.

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, both a Candidate record and an Individual record are created, unless an Individual record can be found. 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.

Maps

Maps are maintained with the Premium HTML5 Maps application at the bottom of the Left Sidebar 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”.

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.

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.

Users

when you create a new user, you 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.

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

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

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.

Volunteers

Every Sunday I use Advanced Search to find new individuals for the previous week (Sunday thru Saturday). Select Contact Type = Individual and Change Log = Add, plus a date range through Saturday. Export these records with the CO_AllFields custom format. Check each record for duplicates by using Advanced Search on Last Name, First Name. Check for missing county. Merge any duplicates. Add a new Column B for Contact Info and use the formula from the previous spreadsheet.

Choose Add Activity from the Search Action dropdown. This will allow you to send an email to Welcome, Chair, and Membership and will also add an activity to the selected people. Use the default date, specify phone call, put “new signups” in the subject.

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.

Using Search Builder, select each Congressional District separately with the following parameters:
Individual>Voter Registration Congressional District>=>1-7

Then select people with a Congressional District greater than 7
Individual>Voter Registration Congressional District> > >7

Then select people with no Congressional District with the following parameters:
Individual>Voter Registration Congressional District>IsNull
For each CD,
1. select All Records
2. Action: Export Contacts
3. Select fields for export
4. Use Saved Field Mapping CO_AllFields
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 Backup CDn yymmdd.txt (change file-type to txt)
11. On CiviCRM page, select Done.
12. Repeat 1-11 for each CD segment.

For each Backup txt file in the downloads folder, open it in Excel. During the setup dialog, choose 1) Delimited; 2) comma; 3) change External Identifier, First Name, Middle Name, and Last Name to Text fields. Save the first segment as CW Backup yymmdd.xlsx in the Backup folder. Append other segments to it.

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 CO_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>Contribution Detail
Columns>Contact ID
Filters>Date Received
From: 1/1/16
To: Today
Export Report
Rename as CW Contributions yymmdd

You should also use Advanced Search to do monthly backups of 1) Campaigns (CO_Candidates); and 2) County and Regional Affiliates (CO_Affiliates). Use the Contact Type to select the records.

Import the SOS Voter file

This process creates 3 import files to be loaded by National’s Affiliate Support. The files are New, Update, and FormerLib.

Using the voter file from the Secretary of State, unzip it to your hard drive. It’s about 1.2 GB in size and comes in about 8 parts. The LBR records are spread throughout the parts and need to be combined into one csv file with a header.

Import the .txt files into Excel. During the import process, change the Last Name, First Name, and Middle Name to a Text field (not General).  Open a second blank spreadsheet. Copy the first line of the part file (headings) to the new spreadsheet. Save it as a xlsx file with the name SOS_yyyy_mm, where yyyy is the year and mm is the month.

A very few records may be screwed up because of some weird character in an address. You can find these by sorting the part by Last Name. If there is one, you will get a popup saying that it is trying to sort numbers in the Last Name. You may have to open the part file in Notepad to find it. Fix the record in Notepad, save it, and reopen it in Excel.

For each of the part files, sort it by the Party field and last and first name.
Scroll down through the records until you get to the first line with LBR in the Party field. Use the Find feature and look for LBR with “match entire cell contents” checked.
Select the entire line.
Scroll down through the LBR records until you come to the last one. Or use the Find feature and look for REP with “match entire cell contents” checked. REP comes right after LBR.
Select all the records from the first LBR record through the last.
Copy and paste them into the new spreadsheet after the preceding group.
Do this for each part file. Save the spreadsheet as a xlsx spreadsheet. Close the spreadsheet.

Open the backup file before opening the SOS file.

1. Add column to Backup file at end called Contact Id. Copy 1st column to it.

2. on SOS sheet, Add column before Voter_id called ContactId
3. Add this formula to all rows.
=vlookup(b2,[CW_Backup_190324.xlsx]All!$d$2:$cz$65000,102,false)
this puts the contact id if it finds a voter id in the backup file. Change 65000 to max rows in backup.
4. sort Backup file by Voter Id and Last name. save it.
5. sort SOS by ContactID, Last Name, First Name
look up last name on lines with no contact id with
=VLOOKUP(Exxx,[CW_Backup_190324.xlsx]All!$I$nnn:$CZ$65000,1,FALSE)
where nnn is first row in backup of names without a voter id and xxx is first line of names with no contact id.
If N/a, clear cell, if last name, validate first and middle names, and maybe phone. if not found, clear.
if found, copy/paste Contact Id from backup to SOS.

6. Sort SOS by Contact Id, Last Name, First Name. Then move everybody with no Contact Id to a new spreadsheet called SOS_yyyy_mm_New. Copy the column headings, too.

7. Add column to SOS, look up Old Precinct with this formula. You will have to adjust the backup date and possibly the column on this and the next two formulas.
=VLOOKUP(B2,[CW_Backup_190818.xlsx]All!$D$2:$DA$70000,96,FALSE)
8. Add column to SOS, look up Old Status with this formula
=VLOOKUP(B2,[CW_Backup_190818.xlsx]All!$D$2:$DA$70000,66,FALSE)
9. Add column to SOS, look up Old Affiliation with this formula
=VLOOKUP(B2,[CW_Backup_190818.xlsx]All!$D$2:$DA$70000,58,FALSE)
10. Add test column to compare precinct, status, and affiliation. If you get the #NAME? error for some reason, you may have to retype this formula into the cell. Replicate down through all columns with a Contact Id.
=IF(BC2<>”Libertarian”,”rereg”,IF(AF2=BA2,IF(AB2=BB2,”ok”,”sbad”),”pbad”))

11. Create a new spreadsheet called SOS_yyyy_mm_Update. Copy the SOS column headings to it.

12. Sort SOS spreadsheet by Test, Last Name, First Name. Move everybody with test column not equal “ok” to it.

On both the Update and New spreadsheets
1. remove foreign mail addresses. Sort by each of the Mailing columns to find which columns have info in them.
2. remove inactive and undeliverable campus mail addresses (e.g. Bradford Hall)
3. combine mail addr 1 and 2 for active voters.
4. remove “Congressional “, “State Senate “, and “State House ”
5. Change Party from LBR to L.
6. sort by Mailing State & City, review for errors.
7. delete following columns
ID_REQUIRED
PERMANENT_MAIL_IN_VOTER
SPL_ID
MAILING_COUNTRY
MAIL_ADDR2
MAIL_ADDR3
PREFERENCE
VOTER_STATUS_ID
SPLIT
STATUS_REASON
EFFECTIVE_DATE
UNIT_NUM
UNIT_TYPE
POST_DIR
STREET_TYPE
STREET_NAME
PRE_DIR
HOUSE_SUFFIX
HOUSE_NUM
ADDRESS_LIBRARY_ID
PRECINCT_NAME
STATUS_CODE
VOTER_NAME
COUNTY_CODE

8. add column at end called SOS Import Date. Enter sos file effective date (2019-03 = 2/28/19)
9.1 on New sheet only, delete the Contact ID column.
10. Save the spreadsheet.
11. Save the spreadsheet as csv file.

To create the FormerLibs file:

1. open both backup and SOS (all libs)
2. on backup, sort by Registered Party Affiliation, Last, first, middle names
3. insert column after voter id (D), call it New voter id (E)
4. in the cells on rows where Registered Party Affiliation = Libertarian,
use this formula:
=VLOOKUP(Dnnn,[SOS_2019_03.xlsx]Sheet1!$A$2:$A$50000,1,FALSE) where nnn is the first row in step 4.
5. sort backup by Registered Party Affiliation, New Voter Id, last, first, middle name.
6. create new spreadsheet
7. copy the column headings from the 1st row of backup to it
8. at the bottom of the Libertarians, there will be 2-3,000 rows with #N/A in the New Voter Id column. These are people
who are no longer registered libertarians.
Copy these rows to the new spreadsheet.
9. on the new spreadsheet, delete the following columns:
Person ID
Legacy CRM ID
New voter id
External Identifier
Individual Prefix
Individual Suffix and all columns following
10. Add SOS Import Date column after Last Name and Enter effective month end date
11. Save file with name SOS_yyyy_mm_FormerLibs.xlsx
12. Save as csv

Upload the 3 csv files to the Data folder under CO Nationbuilder Migration directory.

Send the following message to affiliates@lp.org, changing the filenames to the appropriate dates:

“There are 3 SOS Voter files uploaded to the Data folder of the CO Nationbuilder Migration directory.

SOS_2019_03_FormerLibs.csv. Change the Registered Party Affiliation to null or Former Lib, add Party Change Activity. I guess you can use the SOS Import Date as the Activity Date since we don’t know the exact date. The SOS Import Date should update the SOS Import Date, SOS Last Updated, and the Party Affiliation Date.

SOS_2019_03_New.csv contains voters who are completely new to us.
Put the Residence Address in the Voter Address field. The SOS Import Date should update the SOS Import Date, and SOS Last Updated

SOS_2019_03_Update.csv contains voters who are already on our file
but their information has changed in 1 or more of several ways.
Their voter info should be treated as new.
Put the Residence Address in the Voter Address field. The SOS Import Date should update the SOS Import Date and SOS Last Updated.
Update ALL of the voter info.
1. they moved.
2. they became inactive.
3. they came to us from an outreach booth and then registered libertarian.
4. they went from libertarian to something else and then changed back to libertarian.
This file contains the CiviCRM Contact Id”

Was this article helpful?

Related Articles

Scroll to Top