Reports¶

Entering Input Parameters¶

Some built-in reports require you to enter a page number or name or other information so the report can find the stats for that object. These user-entered values are called input parameters.

In most cases, you can enter a single value or a comma-separated lists, like a list of zip codes. However, if you wish to include a list of values with letters as well as numbers, like congressional districts (e.g. CA_04; CA_03), the values must be separated by ; or new lines.

Many reports that provide results for multilingual campaigns or that request the user to input page or mailing id parameters provide an autofill box where you can type in part of the page or campaign name or id and then click to select from a list of matches. For example, "click on page but no action", requires you to enter a page id or partially enter the name to find matches by page name or campaign name.

To use a parameter value in a query report, reference it within the report SQL, like:

select * from core_mailing where mailing_id in ( {{mailing_id}} )

The parameter name can include alphanumeric characters, underscores, and hyphens, and wrapped in {{ }}. The parameter name will be used to prompt the staff user for the value. In the query builder, you can leave the value of a filter blank, and the parameter name will be filled in automatically. In both SQL and the query builder, the query report will prompt you to enter a value.

Learn More: Input Parameters

Breakdown By Page Or Mailing¶

If you pick a campaign from the autofill choices ("campaign" is appended to the page name) or enter a list of mailings or pages, ActionKit will break the results down so you'll see a total for the campaign and a subtotal for each of the pages or mailings.

If you only want to see the total, uncheck the Break down results box. The choice you make will apply to CSV downloads for query reports as well.

This works for most query and dashboard reports, although there are a few exceptions (like the Event Report dashboard and the query reports it uses - for the technically minded reader we had to add some new Django tags to build this report and the breakdown feature doesn't work with those).

Download Actions¶

The Download Actions tool lets you easily download a CSV of the entries made on a survey page, or the entries in a custom action field on another page type. You can also use this to generate a list of actions taken on non-survey pages, whether or not the page has associated action fields using the short name option.

To generate the CSV:

1 Click Download Actions in the Reports tab tools menu.

2 Select a Page - Enter the short name for a survey or non-survey page.

3 Choose the options you'd like to enable.

If you don't make any selections, you'll get a CSV file with earliest responses at the top and with columns for the page name, action id, user id, answers to each survey question or action field, and the timestamp.

Or select any combination of the following options:

• Reverse Order - Download responses with most recent responses first.
• Optional Information -
  • Include respondent name, full address, district, phone - these fields are appended to each row with whatever values we have for the relevant user.
  • Include respondent email - email will be appended to each row.
  • Include action source and mailing ID - action source and the mailing ID recorded on the aciton will be appended to each row.
• Include custom fields - selected user fields will be appended to each row.
• Limit to date range - select from and to dates from the calendar that appears when you click in the box. The date range will be shown in the file name.

4 Click Download Actions to generate your CSV or click show SQL to display the query generated from your selections.

Compare Mailings¶

The Compare Mailings report allows you to quickly compare results from two or more mailings. Use this for mailing testing or to view recent performance or performance by tag.

To compare mailings:

1 Click Compare Mailing in the Reports tab tools menu.

2 Select the mailings you want to compare. Recent mailings can be selected from a dropdown in the top box. To include an older mailing, type a word from the subject or the mailing ID in the second box.

The dropdown includes mailings that stopped or died. These mailings sometimes have sent to a set of people before they died or were stopped so it can still be useful to see the results in a comparison.

3 Hit Compare.

You'll see various measures of performance for each mailing. In addition to the open, click and action stats available in the individual mailing report, you'll see the average gift size and the mailing sent date, plus unsub and bounce stats.

Scroll down to see information on the statistical significance of your test results. There's a table and definitions of terms to help you determine whether the performance difference between two or more mailings is statistically significant.

Compare Pages¶

The Compare Pages report allows you to quickly compare the performance of two or more pages.

To compare pages:

1 Click Compare Pages from the Reports tab tools menu.

2 Select the pages you want to compare. Recent pages can be selected from a dropdown in the top box. To include an older page, start typing in the second box. In either box, start typing any of the following to see matches: title, page id, page type (e.g. 'petition').

3 Hit Compare.

You'll see various measures of performance for each page, most importantly, conversion rate (actions/clicks). A higher conversion rate means more of the people who come to the page then take action on the page. The total numbers (actions, distinct users taking action, new members from this page) are interesting, but often less useful for comparing pages, because they often say more about the size of the audience (a page that was included in a full base email will generate more actions than a page that was included in an email to 10% of your list).

Database Structure¶

We've provided a database table reference with table names, field names, and brief descriptions. You'll need this information to write your own SQL. You may also find it useful for understanding what data is available and for more advanced uses of the query builder.

Additionally, we've provided a more detailed reference for some more complicated categories, particularly donations and subscriptions, under Data capture details by category.

You can also access this document through the Reports > Related Tools > Database Table Reference.

You can also reference the Sample queries for examples of how the different tables can be joined.

While creating a query report, you can click the Tables link above the SQL field to show a list of database tables and columns. Enter one or more words in the search form to focus on matching tables. Click a table or column name to insert it into your SQL.

Creating And Scheduling¶

If you want to write your own query reports, you can either use the Query Builder tool or write your own Custom SQL.

You can start from an existing query by selecting copy next to the report name from the Browse All listing.

Or to create a new query from scratch, click Add a Query in the Reports tab and enter the following information:

• Name - Title of the query, ideally short and descriptive (like "New Members by Week"). This is a required field.

• Short name - This is the name that you use to reference a query report in a dashboard report (see Creating a dashboard report) and is included in the URL for this report. This is a required field.

  The short name must use letters, numbers, and underscores without spaces or other characters. Although dash characters are allowed in short names, they are not supported by the syntax used to embed query reports in a dashboard report, so if you'd like to separate words for readability, we suggest using underscore instead.

• Description - You can provide more detail about the query here (like "New member count from Mon thru Sun of each week"). The description is equivalent to Notes on pages and mailings.

• Query Type - There are multiple query types. The default value is Custom SQL.

  To use the point-and-click Query Builder to generate your SQL, select any other query type from the dropdown.

  Less advanced users will be more comfortable using the Query Builder but note that there are some queries that it will not be able to create, and the SQL it generates may not be as efficient as one written by hand.

• SQL - The query written in SQL. If you use the query builder the SQL will be generated for you.

  You can use many Django template tags in your reports and also custom tags we've added for ActionKit. By combining if/else template tags with input parameters, you can create reports that vary their SQL based on input by the person running the report.

• Query Template - Select from various layouts for your report. You can add a new one by clicking the green plus symbol and writing the HTML for your layout. This is a required field. For more information, see Managing query templates.
• Categories - You can filter reports by the category selected here on the Browse All screen. Some categories cause reports to be available in other areas in the UI. See Managing report categories.
• Parameter help - Enter instructions for other staff users for reports that require input parameters. When the user runs this report, they'll see a screen prompting them to enter the variable value and these instructions will show on the page. For an example of how we've used it see the Recurring Donation Monthly Report. This is an optional field.

• Schedule and Emailing - To view the schedule and emailing options, click Show. Then select an option from the Run every dropdown.

Additional options will show based on your selection:

• Hour - Runs every hour. No additional options.
• Day - Runs daily at the time you select from the Time of Day dropdown. Times are in GMT with no daylight savings.
• Week - Runs weekly on the weekday you select under Day of Week at the time you select under Time of Day.
• Monthly - Runs monthly on the Day of Month you select at the Time of Day you select. Enter -1 if you want the report to run on the last day of each month.

You can set scheduled reports to send to a list of email addresses each time they run.

• To emails - Enter one or more email addresses for people who should receive a copy of the report at each scheduled run.
• Email style - Email the report as an attached CSV or as HTML by selecting one of the following options:

• Email results as HTML using display style your specified as the query template

• Email results as a CSV file.

  If your query returns more than 1,000 rows, it will always be sent as a CSV file.

  Your choice applies to both scheduled reports and reports sent by the form on the report-viewer page.

• Send if no rows - Check if you want email recipients to receive an email even if there are no results for scheduled reports. This option is activated by default.

Using Queries To Target A Mailing¶

You can use query reports to target recipients for your mailings. Any query report categorized mailer will display in the Query Library field on the Target screen in the mailer. Below, we list the sample queries we've included in your instance and outline how to add your own queries.

Creating And Scheduling Dashboard Reports¶

To create a dashboard report, click Add a Dashboard in the Reports tab and enter the following information:

1 Name - Title of the report, ideally short and descriptive (like "New Members Dashboard"). This is a required field.

2 Short name - This is the name that is included in the url for this report. The short name must be one word using letters, numbers, underscores and dashes, no spaces or other characters. This is a required field.

3 Description - You can provide more detail about the dashboard report here (like "New member from all sources for the last month").

4 HTML - The HTML for the dashboard. You can use standard HTML tags to format the output or reference a stylesheet. Django and JavaScript are also allowed in the dashboard.

Include query reports by inserting the following into your HTML at the desired location: If the dashbard is passing parameters to the query report through user input or a parameter in the dashboard, use {{ reports.query-short-name }}. If the query report doesn't need additional input to render, use {% report "query-short-name" %}.

If the query report you reference requires user input(s), you will need to define each parameter in your dashboard report. You can prompt for the user input or fill in the parameter directly in the dashboard:

<!--Prompt for user's pet-->
{% required_parameter "animal" %}
{% report "query-short-name" animal as user_pet %}

<!--Always use cat as user's pet-->
{% report "query-short-name" "cat" as user_pet %}

For detailed instructions, see input parameters.

You can also use Google charts to convert the output to a visual representation.

<div class="google-chart ColumnChart">
  {{ reports.clicks }}
</div>

In the example above, the class ColumnChart indicates that the chart will be a column chart. See other Chart Types. For detailed instructions, see Using Google Charts.

5 Categories - You can filter reports by the category selected here on the Browse All screen. Select homepage to list the report in the reports section on your home tab and on your reports tab. For more information, see the report categories reference.

6 Parameter help - Enter instructions for other staff users for reports that require input parameters. When the user runs this report, they'll see a screen prompting them to enter the variable value and these instructions will show on the page. This is an optional field.

7 Schedule and Emailing - To view the schedule and emailing options, click Show.

• Run every - You can schedule reports to run every hour, night, or week.
• To emails - You can enter one or more email addresses for people who should receive the report at each scheduled run.

Input Parameters¶

Input parameters allow you to prompt the person running the query report or dashboard to enter a value.

If your dashboard relies on query reports that require user input, then you will need to define each parameter in your dashboard report. You may also define parameters for query reports and dashboards if you want to include additional attributes, such as hint text or a default value.

{% required_parameter "date_start" label "Start Date" type "date" %}

{% required_parameter "max" label "Maximum" type "number" default "5" %}

{% required_parameter "tags" label "Pages With These Tags" order 1 hint "Leave blank to match all pages" %}

{% required_parameter "since" label "Join date" type "date" default "2018-01-01" %}

<h3>{{ campaign_name|campaign:"title" }} ({{ campaign_name }})</h3>
 {% if campaign_name|campaign:"starts_at" %}
 The default start time is {{ campaign_name|campaign:"starts_at" | date:"r" }}
 {% endif %}

{% report "[query_short_name]" with [parameter_name] as [name of parameter required in query report] %}.

{% report "[report_name]" with [parameter_1_name] as [p1] [parameter_2_name] as [p2] ... %}

Using Custom Template Tags¶

You can use Django tags in your Dashboard reports. We've also added some helpful filters and tags specific to ActionKit.

Read about Custom Filters and Tags for more details.

{% if page_type == 'donation' %}
  {% report 'amt_raised' with 'page_id' as page_id %}
{% else %}
  {% report 'page_actions' with 'page_id' as page_id %}
{% endif %}

Using Google Charts¶

You can use Google Chart API to format your dashboard output as charts for better visualization. You can display query report results using Google Charts to see line, bar, and other types of charts in your dashboard instead of tables. You can also display query report results as a Google Table which would add things like sortable headers.

To create a dashboard report suitable for display as a chart, the referenced query report can be whatever you want, but for it to be interesting you'll want it to return multiple columns for multiple rows. Also, most chart types will expect numeric values in all columns but the first.

Here is the SQL query for the built-in Count clicks by Page report with the short name of clicks that counts the number of clicks by page for the last month:

SELECT name, COUNT(*)
FROM core_click cc
JOIN core_clickurl ccu ON ccu.id=clickurl_id
JOIN core_page p ON p.id=page_id
GROUP BY 1;

To create a Dashboard Report that utilizes google charts for this query, wrap the query short name in the google-chart div class:

<div class="google-chart ColumnChart">
{{ reports.clicks }}
</div>

The google-chart div class tells ActionKit that it should try and find a report results table inside the div and make it into a chart. ColumnChart is the type of chart. See Chart types.

When ActionKit renders this Dashboard Report, the new dashboard report should show up as a column chart.

<div class="google-chart ColumnChart" style="height: 300px; width: 600px">
{{ reports.actions_by_page }}
</div>

<div class="google-chart ColumnChart" id="actions_by_page_chart">
{{ reports.actions_by_page }}
</div>

<script type="text/javascript">
reports.actions_by_page_chart = {
  options: {
    title: 'Actions By Page (last 7 days)',
    hAxis: { title: 'Month' },
    colors: ['#ffcc00'],
    height: 300,
    width: 400
  }
};
</script>

<div class="google-chart ColumnChart" getOptions="getByPageOptions">
{{ reports.actions_by_page }}
</div>

<script type="text/javascript">
function getByPageOptions() {
  return {
    title: 'Actions By Page (last 7 days)',
    colors: ['purple'],
    height: 300,
    width: 400
  };
}
</script>

{% required_parameter days %}
<div class="google-chart ColumnChart" getOptions="getByPageOptions">
{% report 'actions_date' with days as Days_for_Date_and_Time_Created_Updated %}
</div>

<script type="text/javascript">
function getByPageOptions() {
  return {
    title: 'Actives in last {{ days }} days',
height: 500,
    width: 700
  };
}
</script>

<div class="google-chart ColumnChart">
<table>
   <tr><th>Month</th><th>Donations</th></tr>
   {% for month in now|months_pastyr %}
   <tr>
     <td>{{month|month_year}}</td>
     <td>{% report "donations_by_month" with month as month %}</td>
   </tr>
   {% endfor %}
</table>
</div>

<style>body.reports div.some_chart { height: 200px, ... }</style>

Browse All¶

The Browse All button in the Reports tab brings up the full list of all reports (queries and dashboards). Alternatively, use the search box or the built in filters to view a list of specific reports on this screen.

From this listing of all reports, you can:

• Filter reports by category or type using the toolbar on the right. Available options for filtering by type are: All, Query, and Dashboard.
• Search by key word and sort the list by specifying Order By and Descending or Ascending and clicking Search.
• Show hidden or disabled reports by clicking Show Hidden.
• Remove a report by clicking Hide.
• Create a new report by coping an existing report.
• Bulk edit reports to show/hide reports or add/remove categories from reports.

Bulk Editing¶

For Reports, you can hide reports, show hidden reports, add and remove categories from reports in bulk.

Minidash reports are a special case - you can hide them, but since those reports display on your primary tabs, we use them whether or not they’re hidden.

To bulk edit:

1 From the Browse All screen (or from the Event List screen for events), click on the “Bulk Edit” link in the top left next to “Show Hidden”.

2 Choose the items that you want to apply the action to -- you can click to select specific items, or select all items visible on the screen.

3 Click the Save button, review the count of objects to be changed, and confirm.

You’ll then see a reportback with the number of changes actually made. This may differ from the preliminary count if, for example, an object you try to apply a tag to already has that tag.

Customizing the Admin Interface¶

You can control which reports are highlighted on the Reports Tab:

• Report tags - To control which reports show up in the "Key Reports" list on your Reports Tab, add the homepage tag to the report.

A few special reports control stats displayed in your admin interface:

• Your Progress - The results of this report display on your home page.

• Page mini-dashboard - This report generates the stats displayed on the Pages Tab. Under the available actions for each page (e.g. "Edit Copy View Hide"), you'll see a count of action takers, if you're using the default report. You can copy and edit this report to show a count of new users, total donated, action sources or whatever page-related info is most useful for your organization. You can even provide links to more detailed reports here. You may want to consider using different content per page type: Using Custom Template Tags.

• One-line mailing stats - This report generates the stats displayed on the Mailings Tab for Sent Mailings. The default shows a count and a % of opens and clicks. As with the report describe above, you can copy and edit this report to change what shows here.
• And some reports provide mailing statistics for recurring mailing series and are linked to from the Mailing Report screen of a recurring mailing. These are Recurring series statistics, Per-send recurring mailing statistics, and Recurring mailing clicks breakdown.

There are also a few special names you can give to your own reports to further customize your admin interface:

• Draft Mailing Dashboard Report - If you name a report draft_dash, it will be displayed on the draft mailing proof-and-send screen, receiving the current mailing's ID as a mailing_id parameter.
• Sent Mailing Dashboard Report - If you name a report mailing_dash, it will be displayed on the sent mailing report screen, receiving the current mailing's ID as a mailing_id parameter.
• User Dashboard Report - If you name a report user_dash, it will be displayed on the individual user record screen, receiving the current user's ID as a user_id parameter.
• User Stats Mini-Dashboard - If you name a report user_minidash, it will be used to generate the summary stats displayed on the Users Tab and the user listing screen, receiving the current user's ID as a user_id parameter.

• Event Mini-Dashboard - If you name a report event_minidash, it will be displayed for each event on the event listing screen, receiving the current event's ID as a event_id parameter.

Using Reports Related Tools¶

The Reports > Related Tools area provides the following:

• Edit categories
• Manage Query Templates
• Manage Jobs
• Link to information on accessing your SQL database, see SQL database access.
• Link to description of the ActionKit database tables and fields.