On the Average…

ADVANCE is a powerful tool we use to generate data to analyze our various constituencies, but how are we able to leverage that data to determine averages on a particular data point?

For example, what is the average age of our alumni members? What is the average Engagement Index of recent attendees to an event?  What is the average age of our Computer Science alumni?

…you can use your Statistical Analysis format to help get the answers to these questions? All you need is your Ad-Hoc report and a blank Excel document.

Let’s walk through an example.  Imagine you were recently asked to pull the average Engagement Index (EI) of graduates from a particular department, but this Ad-Hoc report yields a total of over 5,000 alumni! The Statistical Analysis format has the “CSU – Engagement Index” data point, but this simply shows us the number of alumni for each score. So how can we calculate the average based on the way this data is presented?

To calculate the average:

1. Retrieve your report as a Statistical Analysis with “CSU – Engagement Index” selected.
2. Open a blank Excel worksheet.
3. Copy and paste your Statistical Analysis “CSU – Engagement Index” table into Excel and delete the column with percentages in Column C (we will re-purpose this column).
   • Column A will show all the various EI scores for all qualified alumni.
   • Column B will show the number of alumni per score.
4. Now, let’s calculate Column C. In cell C1, multiply A1*B1.

*Try this formula:  =SUM(A1*B1)

5. You can now carry this formula down the remainder of Column C using the cross icon in the lower right corner of that cell:

  (To learn more about this function, click here or search the web for “How to  copy a formula down a column”)

6. You should now have values in column C for all EI scores.
7. Calculate the SUM of Columns B and C (column B is going to be the total number of entities on your report).
8. To determine the average, divide the total of Column C by the total of Column B.

You can now easily see the average Engagement Index for our department graduates is 325.

We can track their EI score at this point in time and use it for comparison purposes in the future to determine exactly how our recent outreach initiatives may have impacted their engagement with CSU.

You easily can apply this process for determining the average age of a population as well.

Linda Paule – Systems Education & Analysis

Alumni Non-Graduates

We are so accustomed to communicating with our alumni, but how do we continue to engage those who may have taken a break from school or discontinued their attendance at CSU?

…these are our alumni “non-grads,” a population we’ve always had the ability to interact with on a broad level, but now have more specific data points in place to allow more targeted reporting of this population in CDIStiller.

You can include alumni non-grads at varying degrees of detail:

• Record type: This includes all alumni non-grads, regardless of where, when or what level they were last enrolled.
• Non-Grad Detail: This variable menu will allow you to report on varying levels of intended degree information, similar to what we have for our graduates (division, major, level, degree type, etc.). The majority of variable options are similar to those in the alumni Degree variable menu, with the exception of:
  • Terminal Date: The end date of an entity’s attendance. Most helpful if used with a range of dates.

Please keep in mind – you must use the Non-Grad Detail specific variables to accurately target this audience, as the general Degree table is limited to alumni only.

If you have any questions or need any assistance using this variable in your reports, please contact ADVRequest@colostate.edu.

Linda Paule

Time for some gift-focused reporting facts and functions!

With the kickoff of 2019 comes an uptick in fundraising initiatives. Whether you’re tracking your success of past years or building an engaged audience for the future, the following tips on giving-related reporting variables may provide clarity surrounding giving criteria to assist in carrying out your plan. These can be applied to either entity or giving based reports depending on your needs.

TRACKING PAST SUCCESS

…you can track your past solicitation project successes through your appeal code? While you can see this automatically calculated for you using tools such as the Mailing Log (for email appeals), you can also pull this information through a giving analysis. To build your Ad-Hoc report, you can select “Giving” from your Grouped Variables menu and enter your appeal code into the Giving – Appeal (Giving) variable. Remember to keep your alpha character capitalized!

REMINDER: Do not confuse this with the Appeal – Appeal Code variable, as this doesn’t look at who gave to an appeal, but rather who was tagged as having received that solicitation piece.

ONGOING PLEDGES

…ongoing pledges have a $0 dollar amount until they are fulfilled?  Keep in mind an ongoing payroll deduction also qualifies as an ongoing pledge. So if you’re trying to view ongoing or payroll deduction donors who may have not made a payment yet, plug “0” into your giving amount field and include pledges to ensure these donors make the list.

(Tip: If you don’t identify a dollar amount in your giving amount fields, it will automatically look for an amount of $.01+)

OTHER PRIVATE SUPPORT (OPS) TRANSACTIONS

…this category previously only captured Office of Sponsored Programs grants, contracts and agreements, but has been re-purposed to also track non-charitable Athletics seating transactions as well. If you’re including these transaction amounts when considering comprehensive giving totals, you’ll need to include these transactions in your reporting. You can tailor which of these are allowed in your reporting via the Giving – Transaction Type variable.

PREVIOUS FISCAL YEAR (TO DATE)

…this indicates a date range in the previous fiscal year up to today’s date in the previous fiscal year. For example, if I were to pull previous fiscal year (to date) on January 30, 2019, it would consider giving between 7/1/2017 – 1/30/2018.  This will not pull giving from the beginning of the last fiscal year through today’s date – that date range would need to be keyed in manually.

GIVING PURPOSE

…you can tailor your giving reports to only look at funds of a specific purpose? This may be useful when trying to isolate the type of giving or donor based on the types of funds they support. For example, you may want to see a list of donors that support scholarship related funds. You can do this by selecting Student Fin Aid/Endowed and/or Student Fin Aid/Operating from the Giving – Purpose field.

RECEIPT DATE VS. PROCESSED DATE

…we track two dates per transaction? The receipt date is the date your gift was received by the Foundation while the processed date is the date it was officially processed and posted in Advance. Generally, you can proceed with receipt date, however, for more time sensitive things dependent upon the date of processing (such as Acknowledgments or tracking transactions as they are posted in the system), processed date may be a more useful data point.

Whether you’re trying these ideas out for the first time or just want a second pair of eyes on your reports to confirm your approach was correct, we at ADV Request are here to help!  To set up a quick training session or for more information, please contact adv_request@mail.colostate.edu.

Author: Linda Paule