Tableau Training

Constituent Profile Inspired by Google Analytics

Knowing when to combine data into a single data set and when to break it out and blend it in Tableau is an important technique and that's what this workbook explores. This workbook uses data blending across many fast data sources to provide nonprofits with a single pane of glass to key data points for a given constituent.

Constituent Profile Inspired by Google Analytics
  1. Data Source Data Source 1
  2. Marks Marks 2
  3. View View 3
  1. Data Source

    The screenshot above is the dashboard view and therefore doesn't show the data sources. Data sources are displayed on each individual worksheet.

    Each worksheet uses a single data source or a blended combination of two data sources. In cases where blending is taking place, the linking field is the Records ID. Because this value is an integer, it performs faster for larger data sets than using a text field like the name.

    Here are the data sources used in this workbook:

    Constituent Profile Address

    Constituent Profile Core

    Constituent Profile Notes

    Constituent Profile Proposal

    Here's a breakdown of what's in each data source.

    The first data source, Constituent Profile Address, is pretty much a straight pull of the primary constituent address. We also have a join to another table where we store the geocoded latitude and longitude for each address. We use a service called Geocodio to provide this data and we do a fresh pull each night to geocode any new addresses.

    Dimensions (Constituent Profile Address):

    Address Type

    Address1

    Address2

    City

    Country

    County

    Full Address

    Records ID

    Region

    State

    Suburb

    Text

    Zip

    Measures (Constituent Profile Address):

    Latitude

    Longitude

    The second data source, Constituent Profile Core, connects to 3 different tables to provide core info on our constituent - constituent info, gifts and constituency.

    Dimensions (Constituent Profile Core):

    Constituency (table):

    Constituency

    Constituent (table):

    Age

    Birth Date

    Constituent Is A Solicitor

    Deceased

    Deceased Date

    First Name

    Last Name

    Name

    Nickname

    Records ID

    Suffix

    Title

    Gifts (table):

    Gift Date

    Gift Records ID

    Gift Split ID

    Gift Subtype

    Gift Type

    Gifts ID

    Measures (Constituent Profile Core):

    Gift Amount

    Gift Split Amount

    Calculated Fields (Constituent Profile Core):

    Amount Largest Gift

    {FIXED [Records ID] : MAX([Gift Amount])}

    The largest gift is the maximum gift amount for a given constituent record. Note this may vary if you wish to include multiple gifts as part of the same gift for some users. So you would possibly need to adjust this calculation to do a sum across gifts or something more complex.

    Amount Recent Gift

    { FIXED [Records ID] : MIN(IF [Date of Most Recent Gift] = [Gift Date] THEN [Gift Amount] END) }

    Using the calculated field Date of Most Recent Gift, I get the corresponding amount associated with the gift from this date.

    Cohort

    'Year of ' + STR(datepart('year',{FIXED [Gift RecordsID] : MIN([Gift Date]) }))

    I ended up not using this calculated field, but it's available if you want to know which year this constituent started giving.

    Complete Address

    ATTR([Constituent Profile Address (Concourse)].[Address1]) + '

    ' + ATTR([Constituent Profile Address (Concourse)].[City]) + ', ' + ATTR([Constituent Profile Address (Concourse)].[State]) + ' ' + ATTR([Constituent Profile Address (Concourse)].[Zip])

    I should have put this calculated field with the Constituent Profile Address data source. But actually, I'm glad I did it 'wrong' here because then you can see that it's possible to create a calculated field that references values in another data source by putting in the additional bit with the name of the data source.

    Note that I force a line break by including an actual line break in the calculated field itself!

    Date of Largest Gift

    DATE({ FIXED [Records ID] : MIN(IF [Amount Largest Gift] = [Gift Amount] THEN [Gift Date] END) })

    The date of the largest gift first matches on the amount of the largest gift with date that gift occurred. Note that this would not work properly in cases where the a the largest amount was given on more than one occasion. Maybe I should use MAX instead of MIN here to at least pull the latest date!

    Date of Most Recent Gift

    DATE({ FIXED [Records ID] : MAX([Gift Date])})

    Simply gets the 'biggest' date associated with the latest gift.

    Total Gifts

    {FIXED [Records ID] : SUM([Gift Amount])}

    The sum of all gift amounts should give us the total amount given. If there are multiple rows for each gift, we would need to include Gifts ID in this calculation as well.

    Dimensions (Constituent Profile Notes):

    Added By

    Author

    Date Added

    Date Changed

    Description

    Last Changed By

    Note Type

    Notepad Date

    Notes

    Notes ID

    Records ID

    Title

    Calculated Fields (Constituent Profile Notes):

    Top Notes

    COUNTD([Notes Id])

    This calculated field simply counts how many notes there are by counting the associated distinct ID values.

    Dimensions (Constituent Profile Proposals):

    Proposals (table):

    Campaign

    Contact Name

    Contact Records ID

    Date Added

    Date Asked

    Date Funded

    Date Last Changed

    Date Rated

    Deadline

    Fund

    Gift Type

    Inactive

    Instrument

    Notes

    Original Date Asked

    Pipeline Date

    Proposal Name

    Proposals ID

    Prospect ID

    Purpose

    Rating

    Reason

    Status

    Prospects (table):

    Classification

    Prospects ID

    Records ID

    Researchpointurl

    Status

    Ratings Table

    Prospects ID

    Rating Date

    Rating Description

    Rating Source

    Ratings ID

    Measures (Constituent Profile Proposals):

    Proposals Table:

    Amount Asked

    Amount Funded

    Amount Pipeline

    Original Amount Asked

  2. Marks

    Similarly, we can't see the marks cards for each worksheet in the dashboard screenshot view above. Here is how the marks are configured on the 12 worksheets used in this dashboard.

    Giving (worksheet):

    ATTR([Total Gifts]) is placed on the Text mark.

    Giving History (worksheet):

    Dual axis chart where one mark type is a line and the other an area. Color for the line is dark blue and light blue for the area with 60% opacity.

    Name (worksheet):

    [Name] dimension is placed on the Text mark.

    Donations (worksheet):

    **CNTD([Gifts ID]) **is used count the number of distinct gifts and is placed on the Text mark.

    Donation History (worksheet):

    Dual axis chart where one mark type is a line and the other an area. Color for the line is dark blue and light blue for the area with 60% opacity.

    Map (worksheet):

    Circle mark type with [City] and [State] dimensions placed on the Tooltip mark.

    Constituencies (worksheet):

    Line mark type is used, but the mark type doesn't matter because the displayed items are contained in the row instead of the Text mark.

    Address (worksheet):

    [Complete Address] calculated field is placed on the Text mark.

    Largest (worksheet):

    SUM([Amount Largest Gift]) **and **[Date of Largest Gift] are placed on the Text mark.

    Most Recent (worksheet):

    SUM([Amount Recent Gift]) and [Date of Most Recent Gift] are placed on the Text mark.

    Notes (worksheet):

    [Description] is placed on the Text mark. [Note Type] **and **[Notes] are placed on the Tooltip mark.

    Proposals (worksheet):

    [Measure Values] is placed on the Text mark with the included values of:

    SUM([Amount Asked])

    SUM([Amount Pipeline])

    SUM([Amount Funded])

  3. View

    Here is what is included in the view in each of the 12 worksheets used in this dashboard.

    Giving (worksheet):

    Nothing to see. Everything is on the marks!

    Giving History (worksheet):

    Columns:

    DATETRUNC('quarter', [Gift Date])

    Rows:

    SUM([Gift Amount])

    SUM([Gift Amount])

    Select the right-most SUM([Gift Amount]) pill and select Dual Axis.

    Name (worksheet):

    Nothing to see. Everything is on the marks!

    Donations (worksheet):

    Nothing to see. Everything is on the marks!

    Donation History (worksheet):

    Columns:

    DATETRUNC('quarter', [Gift Date])

    Rows:

    COUNTD([Gifts ID])

    COUNTD([Gifts ID])

    Select the right-most COUNTD([Gifts ID]) pill and select Dual Axis.

    Map (worksheet):

    Columns:

    AVG([Constituent Profile Address (Concourse)].[Longitude])

    Rows:

    AVG([Constituent Profile Address (Concourse)].[Latitude])

    Constituencies (worksheet):

    Rows:

    [Constituency]

    Address (worksheet):

    Nothing to see. Everything is on the marks!

    Largest (worksheet):

    Nothing to see. Everything is on the marks!

    Most Recent (worksheet):

    Nothing to see. Everything is on the marks!

    Notes (worksheet):

    Rows:

    [Notepad Date]

    Proposals (worksheet):

    Columns:

    [Measure Names]

    Rows:

    [Proposal Name]

    [Pipeline Date]

The one drawback from this dashboard is that selecting a constituent is a two-step process for end users. First, the user finds the constituent by doing a search on the name. After the constituent is found, the user must then click on the corresponding Records ID to select it. The second filter of the Records ID is configured to show only "Relevant Values" thereby limiting it to just the one Records ID that the user simply has to click on (unless there is more than on person with the same name).

The reason for this behavior is that we want the user to be able to search on the name. However, if we use the name as the linking field between blended data sets, the performance may be slow depending upon how many constituents you have. Also, if there is more than one constituent with the same name, how would you be able to distinguish between the two? That's why the Records ID at least ensures uniqueness.

I tried to find a good workaround to this and concluded this was the best option given the dynamic nature of the underlying data set. Tableau won't auto-click the value for you. Other workarounds include making a parameter whose values you pre-populate with your actual constituent data. But this wouldn't be practical if the data needs to be refreshed often.

As for the dashboard formatting, I decided to use floating for all the worksheet elements. This allowed me to set the precise location for each element. It's more work, but it provides the most control.

Next steps

Whenever possible, you want to have a single data set for a given workbook. However, when the amount of data is so large that this is not practical, turn to data blending as way to achieve the same results by using smaller, faster data sets.

Use this workbook as a way to experiment with data blending. Data blending is a great tool because it allows you to have ultra-fast data refresh times on the backend because your data sets are flat.

If you have The Raiser's Edge or The Raiser's Edge NXT, you will be able to use this dashboard without a lot of changes. You'll need to create 4 exports - one for each of the data sources listed above. If you change the fields from those listed, you'll need to tweak the workbook to match.

Even if you don't use Blackbaud as your fundraising CRM, the same concepts should translate easily!

Book Tableau training
for your nonprofit or foundation

Message sent!

We will respond shortly