Donation totals by day

Options
Michael
Michael
Ancient Membership 100 Comments Facilitator Level 3 Inquirer Level 2
in Blackbaud Luminate Online® & Blackbaud TeamRaiser® Discussions

Does anyone else keep a record of totals by day? Not by campaign, but overall across all donation pages -- count, amount and average gift. In GetActive, the only way I found to do it was to write my own bot to login and strip each amount and count automagically from the daily report pages. It was hamfisted, but it only took about 5 minutes and I ended up with a nice TAB file with date, amount and total for each day of whatever month I wanted to look at.

In Convio, this doesn't seem possible. The closest thing I've been able to find is the Timeline report, but that breaks it out by Campaign or Form, and there doesn't seem to be any way to get true site-wide daily reports...

This is the proceedure I've come up with -- I'm less than pleased by it. I'd love to know if there is a better way to get this data that I haven't found yet:

Anyway, In IE -- not FF, because FF doesn't play with Excel as well:

  1. Data Management > Report Center

  2. In Reports Classic Tab, choose Fundraising > Fundraising Timeline Report

  3. Choose All Forms, Cumulative Gift Amount, Summarize by Day and a data range ("Last Month" for example, or pick your own)

  4. Click Submit Report

You'll get a very long table of data that shows you each Form's data by day, grouped by Form. The last two columns are running totals, the last line in each form's area shows the totals for the form for the time period selected. Now....

  1. Highlight the whole thing, headers included, but not the links -- just the table data.

  2. Control C -- then paste (ctrl v) into the A1 of an new excel sheet.

  3. If you're finicky, play with the borders or whatever.....

  4. Click the top left box to select the whole sheet, right click, pick Format Cells > Alignment and complete clear the Merge box, hit OK.

  5. Select the whole sheet again, sort by Donation Name (column A) Descending, delete all the form name rows.

  6. Resort by Donation Date again, Ascending.

  7. Subtotal at each change in Donation Date, Sum, Number of Gifts.

  8. Click the little "2" to collapse and show only the subtotal numbers.

  9. Select only the cells you see there and paste first into a plain text editor (to clear the other cells), then into the Donations by Day sheet.

  10. Repeat this process from step 7, but select Gift Amount instead.

Tagged:

Comments

  • Laura Vacco
    Laura Vacco
    Ancient Membership First Comment Facilitator Level 1

    You can use a similar but simpler process by using the Transaction Report, You will get a file of all the transactions in date order. Add a column next to the amount columns and put in an Excel formula to give the date without the time, then subtotal on the new column. ( =DATE(YEAR(P2),MONTH(P2),DAY(P2)) ). While it's still not wonderful, it's easier than the manipulations required to remove the unwanted campaign and form totals..

  • Michael
    Michael
    Ancient Membership 100 Comments Facilitator Level 3 Inquirer Level 2
    Laura Vacco:

    You can use a similar but simpler process by using the Transaction Report, You will get a file of all the transactions in date order. Add a column next to the amount columns and put in an Excel formula to give the date without the time, then subtotal on the new column. ( =DATE(YEAR(P2),MONTH(P2),DAY(P2)) ). While it's still not wonderful, it's easier than the manipulations required to remove the unwanted campaign and form totals..

    That is a lot easier. Still, I wish Convio could actually just do this sort of thing... Doesn't seem like such an outlandish report to want readily available afterall.

  • Andy G.
    Andy G.
    Ancient Membership First Comment Facilitator Level 1
    Michael :

    That is a lot easier. Still, I wish Convio could actually just do this sort of thing... Doesn't seem like such an outlandish report to want readily available afterall.

    hi Mich - I was in the Convio summit last November and they said they will have transaction report in the new report writer that might have the ability to do grouping, but I am not clear about the release date. Laura's idea was excellent, though. you can also use a round down function: assuming the date is in cell A2, the new date cell formula = ROUNDDOWN(A2,0) and change the new cell data format to datetime, it will get rid of the time data. later you can use pivot table to do your date grouping and use sum of donation amount. My experience with excel 2003, pivot table is good up to 20K records before it freezes the application. More that that, I recommend Access or ETL to SQL server. Good luck =)

  • Michael
    Michael
    Ancient Membership 100 Comments Facilitator Level 3 Inquirer Level 2
    Andy G.:

    hi Mich - I was in the Convio summit last November and they said they will have transaction report in the new report writer that might have the ability to do grouping, but I am not clear about the release date. Laura's idea was excellent, though. you can also use a round down function: assuming the date is in cell A2, the new date cell formula = ROUNDDOWN(A2,0) and change the new cell data format to datetime, it will get rid of the time data. later you can use pivot table to do your date grouping and use sum of donation amount. My experience with excel 2003, pivot table is good up to 20K records before it freezes the application. More that that, I recommend Access or ETL to SQL server. Good luck =)

    If I was that smart and/or that motivated, I wouldn't need Convio to do reporting at all

    heh heh

Categories