Google Sheets

1. From Analytics, the records in the search results can be exported to Google Sheets for more in depth analysis.
2. The Link to the LIVE Google Sheets is https://docs.google.com/spreadsheets/d/1Z-zh4D10ghuI9BrOOSd7pJUtHx_vMMR_WMSRFAXNMO8/edit#gid=1824517640
3. The link to the TEST Google Sheets is https://docs.google.com/spreadsheets/d/1_y_O9jJnG5VO9cjGWNonZX800IYHcEg106W5nSDNmpA/edit#gid=0
4. The URLs for accessing Google Sheets can also be found in the Admin comments for Participant ‘Child1 Smith.’
5. First use the analytics page within the WebApp to search for the records you want to analyze. There are various filters allow you to narrow down your search.
6. Once you have set all the filters you want, then SEARCH. A summary of all the records retrieved will be shown.
7. Use the ‘EXPORT to sheets’ function, and the data will be sent to Google Sheets (like excel, but in the Cloud)
8. Considering the five options at the top of the analytics page:
9. 
10. Each option has a counterpart TAB on Google Sheets. When you export the data to Google Sheets, the data on the counterpart tab will be completely updated with the data you are exporting
11. Various ‘pivot tables’ are created for reporting purposes. The pivot tables take their associated data from the primary sheet which contains the exported data.
12. Data changed in sheets DOES NOT change data in the CJ Web App system. The Google Sheets data is a copy, which can be refreshed by re-exporting the data from Analytics.
13. Guidance on how to use Google sheets is not provided in this User Guide, since there are extensive and excellent resources available publicly on the internet for anyone wishing to educate themselves on Google Sheets and its functionality. YouTube is an excellent place to start.

Some example reports

• • Payment transaction details report
    1. Go to the booking details and copy the Payment ID e.g. 315921605200
    2. Now go to Transactions and enter the payment id in the search
    3. You will see the basic details of the transaction
    4. Open Google Sheets (see Google Sheets entry in this User Guide for the Link) and then go to the ‘User Transactions Raw Data’ tab.  Here you can see all the data associated with the payment transaction, including the full return code from JCC and the time the transaction was created.
  • Transport options for Summer Camp report (or any other Service for which we have collected transport option information).
    1. In analytics, export the required bookings e.g. filter on 23W2 to get all bookings for Summer Camp  Week 2 2023.
    2. Export the results to Sheets and then open Open Google Sheets (see Google Sheets entry in this User Guide for the Link) and go to the ‘Bookings Raw Data’ tab.  
    3. Select the ‘Transport’ Column by clicking on the column header.
    4. Now in the Google Sheets menu select ‘data, sort sheet, sort by column A-Z’.  You will now see the bookings grouped by Transport option.
  • Birthdays report
    1. Export the appropriate Booking records to Google sheets through Analytics (as per above).
    2. Select the ‘Birthday’ Column by clicking on the column header.
    3. Select ‘data, sort sheet, sort by colum A-Z.
    4. You will now see the bookings sorted in order of the child’s birth date.
  • Health notes report
    1. Health note reports should always be created from the PARTICPANT data. This is because health notes can be created by the parent when they book a service but they are always added to the participant record, so we have ALL health notes in one place (on the participant record).
    2. In Analytics, search for the required participant records (e.g. filter on year or school).
    3. Export the records to Google sheets.
    4. In Google Sheets, go to the ‘Participant Raw data’ tab and check that the correct data is present.
    5. Use the Health Report tab to view the report.
• Custom Searches
  1. 1. Custom Search 1: Enter the first token partial match e.g. 24ME (pulls in all participants with that match)
        then enter a comma,
        Second partial token is to show the bookings which match the second partial token for all participants who have the first token match
        e.g. 24ME,24W will show for all participants who have 24 membership, what their camp booking token is, or blank if they don’t have a camp booking.
     2. Custom Search 2: Show any duplicates on the partial token.
        e.g. entering 23W will show anyone who has two tokens for summer camp 23.