Megaphone Commons

# Exporting Nationbuilder for CiviCRM

Nationbuilder notes:

Nationbuilder lets you download your data in Postgres 9 format.

**Settings menu » Database**

Click **Create Database Snapshot**, wait several minutes

### Configuring Postgres on Ubuntu

~~~ shell

sudo apt install postgresql postgresql-contrib

# Optional: Postres GUI

sudo apt install pgadmin3

sudo -iu postgres

createuser --interactive nbuild

# superuser is "yes"

createdb nbuild

psql nbuild

ALTER USER nbuild WITH PASSWORD '1234';

\q

psql -d nbuild -U nbuild -h 127.0.0.1 --password -c "CREATE SCHEMA nbuild_africans; CREATE SCHEMA shared_extensions; CREATE EXTENSION hstore WITH schema shared_extensions; CREATE EXTENSION dblink WITH schema shared_extensions; CREATE EXTENSION citext WITH schema shared_extensions; CREATE EXTENSION pg_trgm WITH schema shared_extensions;"

psql -c "CREATE SCHEMA nbuild_africans; CREATE SCHEMA shared_extensions; CREATE EXTENSION hstore WITH schema shared_extensions; CREATE EXTENSION dblink WITH schema shared_extensions; CREATE EXTENSION citext WITH schema shared_extensions; CREATE EXTENSION pg_trgm WITH schema shared_extensions;"

# as your regular Unix user

pg_restore -d nbuild --password --format=c -U nbuild -h 127.0.0.1 --schema="nbuild_africans" --verbose --clean --no-acl --no-owner /path/to/backupfile

~~~

To reimport again later, just the last line (the `pg_restore`) is necessary.  The password is whatever you set in the `ALTER USER` command above.

### Contacts

`signups` table is the "civicrm_contact" equivalent

`signups.signup_type` is equivalent to Civi `contact_type`. 0 for Individual, 1 for Organization.

Households aren't a type of signup; they're in `households` and are connected to signups via `household_signups`.

### Email

Email is stored in both `signups` and `email_addresses`.  They appear to have the same data, though if it's possible to how more than 4 emails, you'll only find them in `signups`.

is_bad and is_invalid are both variations on "on hold" - `is_bad` is a manually set option to mark an email bad on a per-email basis.  `is_invalid` is based on bounce processing.

Email location types just aren't a thing in NB.

### Addresses

Postal addresses are in `signups`, but they're already normalized in `addresses`.  While `addresses.signup_id` exists, it's a red herring.  You use the following fields from signups to connect them: address_id, mailing_address_id, registered_address_id, work_address_id, billing_address_id, twitter_address_id(?), facebook_address_id, meetup_address_id, user_submitted_address_id, primary_address_id.

It would seem like this means that an address can have multiple location types, but that seems not to be the case.  This makes mapping relatively easy.

As far as I can tell, there are addresses not attached to any record.  This is an artifact of not being able to use foreign keys with their db structure.

Cool features of Nationbuilder:

* "User submitted" as a location type - better as a custom field IMO, but still cool

* Getting address data from Twitter etc.

* A field called "geocode accuracy" to indicate that an address isn't accurate enough

Sources: http://nationbuilder.com/edit_primary_address

### Contribution pages

The equivalent table is `donation_pages`.  Note that donation pages are embedded in regular pages, so you'll need to join to the `pages` table on page_id (and next_page_id, if you want thank-you values).

`donation_tracking_codes` maps to `civicrm_financial_type`.  See the "migrating website" section.

### Basic pages

Page info in `pages`, content in `basic_pages`.

### Social Capital

I haven't tested, but it seems very similar to the [CiviPoints](https://github.com/futurefirst/uk.org.futurefirst.networks.civipoints) extension.

### Events

Events are found in the `event_pages` table - there's also an `events` table, so maybe there's another type of event I'm not familiar with.

Times are in UTC; there's a time_zone field to indicate the offset.  When Civi implements CRM-17618, the data structure will be comparable; until then, you must manually offset the date based on timezone.

In Nationbuilder, find events from the "Website" top nav, then find the "Calendar" page.  Events will be a subpage of that.

### Participants

Found in `event_rsvps`.

When in Nationbuilder viewing an event, you'll find the data on the "RSVPs" subtab.