Built-In Reports Reference

This document includes reference information for the following report categories:

Built-In Query Builder Reports Reference

Active Events Last Month

Short name: active_events_last_month
Description: Which events (excluding deleted and cancelled) happened in the past month?
User input:
Associated with: None
../_images/active_events_last_month.png

Active Recurring Donors

Short name: currently_active_recurring_donors
Description: Which users are currently active recurring donors?
User input:
Associated with: None
../_images/currently_active_recurring_donors.png

At Least X Actions In Past Y Days

Short name: how_many_users_took_x_actions_in_past_y_days
Description: How many users took at least X actions in the past Y days?
User input: None
Associated with: None
../_images/how_many_users_took_x_actions_in_past_y_days.png

Campaign Event RSVPs

Short name: campaign_rsvps
Description: How many users RSVPed to events in X campaign?
User input: Campaign ID for Campaign ID
Associated with: None
../_images/campaign_rsvps.png

Custom Action Field Actions on X Page

Short name: custom_field_actions_on_page
Description: How many people filled in a value (eg checked a checkbox or filled in additional data about themselves) for a specific custom action field on a given page?
User input: Action Field Name, Page ID For Page
Associated with: None
../_images/custom_field_actions_on_page.png

Custom Action Fields on X Page

Short name: custom_action_fields_given_page
Description: Which custom action fields appeared on a given page?
User input: Page ID For Page
Associated with: None
../_images/custom_action_fields_given_page.png

Donations Exceeding N Amount in the Last Week

Short name: donations_exceeding_N_last_week
Description: Which donations exceeded N amount last week?
User input: Min For Amount In Us Dollars
Associated with: None
../_images/donations_exceeding_N_last_week.png

Donors' Cards Recently Failed

Short name: whose_donors_cards_recently_failed
Description: Which donors' cards recently failed? (Query Builder)
User input:
Associated with: None
../_images/whose_donors_cards_recently_failed.png

Failed Donations in the Last Week

Short name: donations_failed_last_week
Description: Which donations in the past week failed?
User input:
Associated with: None
../_images/donations_failed_last_week.png

Last Month's Donors

Short name: last_month_donors
Description: Which users donated in the last month? (Used to suppress solicitations)
User input:
Associated with: None
../_images/last_month_donors.png

Lifetime Donors Over N Amount

Short name: lifetime_donation_over_n_amount
Description: Which users have donated more than N amount for all time?
User input: Minimum Total
Associated with: None
../_images/lifetime_donation_over_n_amount.png

Mailable Users Grouped By State/Region/Country

Short name: mailable_users_stateregioncountry
Description: How many mailable users do we have, grouped by state/region/country?
User input: None
Associated with: None
../_images/mailable_users_stateregioncountry.png

Mailable Users by Congressional District

Short name: mailable_users_by_district
Description: How many mailable users do we have, grouped by congressional district?
User input: None
Associated with: None
../_images/mailable_users_by_district.png

Mailings Sent Today

Short name: all_mailings_sent_today
Description: Which mailings were sent in the last 24 hours?
User input:
Associated with: None
../_images/all_mailings_sent_today.png

Media Contacts near Zip Code

Short name: media_contacts_near_zip_code
Description: Who are the media contacts within 15 miles of X zip code?
User input: Zips For Geographic Coordinates Lat Lon Distance
Associated with: None
../_images/media_contacts_near_zip_code.png

New User Count from Pages Tagged X

Short name: new_users_from_pages_tagged
Description: How many new users joined from pages tagged X?
User input: Tag Ids For Page Tags
Associated with: None
../_images/new_users_from_pages_tagged.png

Open Rate by Domain for Given Mailing

Short name: open_rate_domain_given_mailing
Description: What was the open rate by domain, for a giving mailing?
User input: Ids For Mailing ID
Associated with: None
../_images/open_rate_domain_given_mailing.png

Range of Donations in the Last Year

Short name: range_of_donations_last_year
Description: What is the range of donations in the last year, grouped by amount?
User input:
Associated with: None
../_images/range_of_donations_last_year.png

Recurring Donations By Frequency

Short name: recurring_donations_by_frequency
Description: How many active recurring donations are there for each billing frequency? (This report will not be terribly interesting if your organization only uses monthly billing rather than weekly, quarterly, or annual cycles.)
User input:
Associated with: None
../_images/recurring_donations_by_frequency.png

SQL Search in Query Report

Short name: sql_search_query_report
Description: Which query reports contain this bit of SQL?
User input: Match For Content
Associated with: None
../_images/sql_search_query_report.png

Staff Accounts With Last Login

Short name: staff_accounts_last_login
Description: When did staff members last log in?
User input:
Associated with: None
../_images/staff_accounts_last_login.png

Successful Donations in the Last Week

Short name: successful_donations_last_week
Description: Which donations in the past week succeeded?
User input:
Associated with: None
../_images/successful_donations_last_week.png

Top 20 Action Sources in the Last Week

Short name: top_action_sources_this_week
Description: What were the top 20 "real" member action sources in the last week?
User input:
Associated with: None
../_images/top_action_sources_this_week.png

Top 20 Pages in the Last Week

Short name: top_pages_last_week
Description: Which pages (top 20) generated the most actions last week?
User input:
Associated with: None
../_images/top_pages_last_week.png

Top 20 Pages w/ New User Signups

Short name: new_user_signups_top_pages
Description: Which pages (top 20) generated the most new user signups last week?
User input:
Associated with: None
../_images/new_user_signups_top_pages.png

Top 20 Sources For a Page

Short name: top_sources_given_page
Description: For a given page, what were the top 20 source code values?
User input: Page ID For Page
Associated with: None
../_images/top_sources_given_page.png

Top 20 Sources for Pages in the Last Week

Short name: top_sources_pages_last_week
Description: What were the top 20 source code values for all pages in the last week?
User input:
Associated with: None
../_images/top_sources_pages_last_week.png

Top-Selling Products in the Last Year

Short name: top_selling_products_year
Description: What were the top-selling products this year?
User input:
Associated with: None
../_images/top_selling_products_year.png

Traffic Last Month From Mobile Devices

Short name: traffic_mobile_last_month
Description: What was the traffic last month from mobile devices?
User input:
Associated with: None
../_images/traffic_mobile_last_month.png

Unsubscribes Per Week in the Last Year

Short name: unsubscribe_actions_per_week_last_year
Description: How many unsubscribe actions did we get per week in the last year?
User input:
Associated with: None
../_images/unsubscribe_actions_per_week_last_year.png

Upcoming Event Host Contacts

Short name: upcoming_event_host_contacts
Description: What is the contact info for active event hosts organizing events in the next month?
User input: Campaign ID for Campaign ID
Associated with: None
../_images/upcoming_event_host_contacts.png

Users Subscribed by X Page

Short name: users_subscribed_by_x_page
Description: Which users were subscribed by X page?
User input:
Associated with: None
../_images/users_subscribed_by_x_page.png

Built-In SQL Query Reports Reference

# of Donations

Description: Displays total number of orders (donations, product orders, new recurring commitments) of any dollar amount received in the last week.
Short name: donations_count
User input: None
Associated with: List Stats
SQL:  
SELECT COUNT(*)
FROM core_order
WHERE status = 'completed'
  AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) <= created_at

(Mailing) Gifts

Description: Displays the total number of orders (donations, product orders), excluding recurring, processed through the specified page and coming from the specified mailing.
Short name: progress_mail_gifts
User input: Mailing id,
Page id
Associated with: Fundraising Campaigns: Your Progress Report
SQL:  
SELECT COUNT(distinct co.id)
FROM core_order co
JOIN core_action ca ON (co.action_id = ca.id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE co.status = 'completed'
  AND cor.order_id is null
  AND mailing_id = {mailing_id}
  AND page_id = {page_id};

(Mailing) Monthly Revenue

Description: Sum of payments toward current recurring commitments, processed through specified page and coming from specified mailing.
Short name: progress_mail_monthly
User input: Mailing id,
Page id
Associated with: None
SQL:  
SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_orderrecurring cor using (order_id)
JOIN core_action ca ON (ca.id = cor.action_id)
WHERE type = 'sale'
  AND success = 1
  AND cor.status = 'active'
  AND ca.mailing_id = {mailing_id}
  AND ca.page_id = {page_id}

(Mailing) Page Clicks

Description: Number of clicks on specified page that resulted from specified mailing.
Short name: progress_mail_page_clicks
User input: Mailing id,
Page id
Associated with: Fundraising Campaigns: Your Progress Report
SQL:  
SELECT COUNT(distinct user_id)
FROM core_click
JOIN core_clickurl ON (core_click.clickurl_id = core_clickurl.id)
WHERE mailing_id = {mailing_id}
  AND page_id = {page_id}

(Mailing) Pledges

Description: Count of active recurring profiles (not payments) for specified mailing and page.
Short name: progress_mail_pledges
User input: Mailing id,
Page id
Associated with: None
SQL:  
SELECT COUNT(distinct cor.id)
FROM core_orderrecurring cor
JOIN core_order co ON (cor.order_id = co.id)
JOIN core_action ca ON (cor.action_id = ca.id)
WHERE co.status = 'completed'
  AND cor.status = 'active'
  AND ca.status = 'complete'
  AND mailing_id = {mailing_id}
  AND page_id = {page_id}

(Mailing) Revenue

Description: Sum of successful orders (donations, product orders), excluding recurring and imported donations, from the specified page and mailing.
Short name: progress_mail_revenue
User input: Mailing id,
Page id
Associated with: Fundraising Campaigns: Your Progress Report
SQL:  
SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_order co ON (co.id = ct.order_id)
JOIN core_action ca ON (ca.id = co.action_id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE type = 'sale'
  AND success = 1
  AND co.status = 'completed'
  AND cor.order_id is null
  AND ca.mailing_id = {mailing_id}
  AND ca.page_id = {page_id}

(Monthly) Actions

Description: Number of completed user actions on call, letter, petition, or survey pages in the specified month. (Call actions are only completed if the user submits from the call screen.)
Short name: progress_actions
User input: Month
Associated with: None
SQL:  
SELECT COUNT(core_action.id)
FROM core_action
JOIN core_page ON (core_action.page_id = core_page.id)
WHERE core_action.created_at > {month}
  AND core_action.created_at < date_add({month},interval 1 month)
  AND core_page.real_actions
  AND core_action.status = 'complete'

(Monthly) Bounces

Description: Number of users unsubscribed because their address bounced in specified month.
Short name: progress_bounces
User input: Month
Associated with: List Growth: Your Monthly Progress Report
SQL:  
SELECT COUNT(distinct user_id)
FROM core_subscriptionhistory
WHERE change_id = 3
  AND created_at > {month}
  AND created_at < date_add({month},interval 1 month)

(Monthly) Emails

Description: Total individual emails sent to all users in specified month.
Short name: progress_emails
User input: Month
Associated with: List Growth: Your Monthly Progress Report
Action Rates: Your Monthly Progress Report
SQL:  
SELECT COUNT(id)
FROM core_usermailing
WHERE created_at > {month}
  AND created_at < date_add({month},interval 1 month)

(Monthly) List Size

Description: Number of users on any list in specified month.
Short name: progress_list
User input: Month
Associated with: List Growth: Your Monthly Progress Report
Action Rates: Your Monthly Progress Report
SQL:  
SELECT COUNT(distinct sh.user_id)
FROM (
  SELECT user_id, list_id, max(id) id
  FROM core_subscriptionhistory
  WHERE created_at < date_add({month}, interval 1 month)
  GROUP BY 1, 2) sh
JOIN core_subscriptionhistory using(id)
WHERE change_id IN (1,2,7)

(Monthly) Mailings

Description: Number of sent mailings in specified month.
Short name: progress_mailings
User input: Month
Associated with: Action Rates: Your Monthly Progress Report
SQL:  
SELECT COUNT(id)
FROM core_mailing
WHERE started_at > {month}
  AND started_at < date_add({month},interval 1 month)
  AND status = 'completed'

(Monthly) New Users

Description: Number of new users in specified month.
Short name: progress_new_users
User input: Month
Associated with: List Growth: Your Monthly Progress Report
SQL:  
SELECT COUNT(*)
FROM (
  SELECT user_id, min(id) id
  FROM core_subscriptionhistory
  WHERE change_id IN (1,2,3)
  GROUP BY 1) sh
JOIN core_subscriptionhistory using(id)
WHERE created_at > {month}
  AND created_at < date_add({month},interval 1 month)

(Monthly) One-Time Donations

Description: Sum of one-time donations or product orders in specified month.
Short name: progress_revenue_one
User input: Month
Associated with: Fundraising Campaigns: Your Progress Report
SQL:  
SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_order co ON (co.id = ct.order_id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE type = 'sale'
  AND success = 1
  AND co.status = 'completed'
  AND cor.order_id is null
  AND co.created_at > {month}
  AND co.created_at < date_add({month}, interval 1 month)

(Monthly) Unsubs

Description: Number of distinct users removed from your mailing list because the user unsubscribed, an admin unsubscribed the user, or the user marked your email as spam in specified month.
Short name: progress_unsubs
User input: Month
Associated with: List Growth: Your Monthly Progress Report
SQL:  
SELECT COUNT(distinct user_id)
FROM core_subscriptionhistory
WHERE change_id IN (4,5,6)
  AND created_at > {month}
  AND created_at < date_add({month},interval 1 month)

(Page) Gifts

Description: Number of orders (donations, product orders), excluding recurring, processed through specified page.
Short name: progress_page_gifts
User input: Page id
Associated with: Fundraising Campaigns: Your Progress Report
SQL:  
SELECT COUNT(distinct co.id)
FROM core_order co
JOIN core_action ca ON (co.action_id = ca.id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE co.status = 'completed'
  AND cor.order_id is null
  AND page_id = {page_id}

(Page) Gifts by Source

Description: Number of orders (donations, product orders), excluding recurring, processed through the specified page and coming from the specified action source.
Short name: progress_source_gifts
User input: Page id,
Source
Associated with: Fundraising Campaigns: Your Progress Report
SQL:  
SELECT COUNT(distinct co.id)
FROM core_order co
JOIN core_action ca ON (co.action_id = ca.id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE co.status = 'completed'
  AND cor.order_id is null
  AND ca.source = {source}
  AND ca.page_id = {page_id}

(Page) Monthly Revenue

Description: Sum of payments toward active recurring commitments created on the specified page.
Short name: progress_page_monthly
User input: Page id
Associated with: None
SQL:  
SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_orderrecurring cor using (order_id)
JOIN core_action ca ON (ca.id = cor.action_id)
WHERE type = 'sale'
  AND success = 1
  AND cor.status = 'active'
  AND ca.page_id = {page_id}

(Page) Monthly Revenue by Source

Description: Sum of payments toward active recurring commitments created on the specified page with the specified action source.
Short name: progress_source_revenue
User input: Page id,
Source
Associated with: None
SQL:  
SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_orderrecurring cor using (order_id)
JOIN core_action ca ON (ca.id = cor.action_id)
WHERE type = 'sale'
  AND success = 1
  AND cor.status = 'active'
  AND ca.source = {source}
  AND ca.page_id = {page_id}

(Page) Pledges

Description: Count of active recurring profiles created on specified page.
Short name: progress_page_pledges
User input: Page id
Associated with: None
SQL:  
SELECT COUNT(distinct cor.id)
FROM core_orderrecurring cor
JOIN core_order co ON (cor.order_id = co.id)
JOIN core_action ca ON (cor.action_id = ca.id)
WHERE co.status = 'completed'
  AND cor.status = 'active'
  AND ca.status = 'complete'
  AND page_id = {page_id}

(Page) Pledges by Source

Description: count of active recurring profiles with specified source on specified page.
Short name: progress_source_pledges
User input: Page id,
Source
Associated with: None
SQL:  
SELECT COUNT(distinct cor.id)
FROM core_orderrecurring cor
JOIN core_order co ON (cor.order_id = co.id)
JOIN core_action ca ON (cor.action_id = ca.id)
WHERE co.status = 'completed'
  AND cor.status = 'active'
  AND ca.status = 'complete'
  AND ca.source = {source}
  AND page_id = {page_id}

(Page) Revenue

Description: Sum of orders (donations, product orders), excluding imports, from specified page.
Short name: progress_page_revenue
User input: Page id
Associated with: Fundraising Campaigns: Your Progress Report
SQL:  
SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_order co ON (co.id = ct.order_id)
JOIN core_action ca ON (ca.id = co.action_id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE type = 'sale'
  AND success = 1
  AND co.status = 'completed'
  AND cor.order_id is null
  AND ca.page_id = {page_id}

(Page) Revenue by Source

Description: Sum of orders (donations, product orders), excluding imports, from specified page and source.
Short name: progress_source_revenue
User input: Page id
Source
Associated with: Fundraising Campaigns: Your Progress Report
SQL:  
SELECT coalesce(sum(ct.amount),0)
FROM core_transaction ct
JOIN core_order co ON (co.id = ct.order_id)
JOIN core_action ca ON (ca.id = co.action_id)
LEFT JOIN core_orderrecurring cor ON (co.id = cor.order_id)
WHERE type = 'sale'
  AND success = 1
  AND co.status = 'completed'
  AND cor.order_id is null
  AND ca.source = {source}
  AND ca.page_id = {page_id}

Action Details (All)

Description: For completed actions on pages with tags where the action has a source other than "import", shows counts of total actions, actions last week, distinct action takers, and new members, grouped by page title and showing tags.
Short name: actions_details_backup
User input: None
Associated with: None
SQL:  
SELECT group_concat(distinct core_tag.name separator ', ') AS 'Issue Tags',
  core_page.title AS 'Action Page', COUNT(distinct ca.id) AS 'Total Actions',
  COUNT(distinct IF(date_sub(current_timestamp(),interval 1 week) <= ca.created_at, ca.id,NULL)) AS 'Actions Last Week',
  COUNT(distinct ca.user_id) AS 'Total People',
  COUNT(distinct IF(ca.created_user = 1,ca.user_id,NULL)) AS 'New Members'
FROM core_tag
JOIN core_page_tags ON (core_tag.id = core_page_tags.tag_id)
RIGHT JOIN core_page ON (core_page_tags.page_id = core_page.id)
JOIN core_action AS ca ON (core_page.id = ca.page_id)
JOIN core_user ON (ca.user_id = core_user.id)
WHERE core_page.status = 'active' AND ca.status = 'complete'
  AND ca.source <> 'import'
GROUP BY core_page.title;

Action Details (Recent)

Description: Same as Action Details (All) but for the last week and including all pages whether or not they have tags associated.
Short name: actions_details
User input: None
Associated with: List Stats
SQL:  
SELECT group_concat(distinct core_tag.name separator ', ') AS 'Issue Tags',
  core_page.title AS 'Action Page',
  core_page.id AS page_id,
  'Total Actions',
  'Actions Last Week',
  'Total People',
  'New Members'
FROM (
  SELECT page_id, COUNT(distinct ca.id) AS 'Total Actions',
    COUNT(distinct IF(date_sub(current_timestamp(),interval 1 week) <= ca.created_at,
    ca.id,NULL)) AS 'Actions Last Week',
    COUNT(distinct ca.user_id) AS 'Total People',
    COUNT(distinct IF(ca.created_user = 1,ca.user_id,NULL)) AS 'New Members'
    FROM core_action ca
    WHERE ca.status = 'complete'
      AND ca.source <> 'import'
    GROUP BY 1
      HAVING 'Actions Last Week' > 0
    ) t
 JOIN core_page ON core_page.id=page_id
 LEFT JOIN core_page_tags ON (core_page.id = core_page_tags.page_id)
 LEFT JOIN core_tag ON (core_tag.id = core_page_tags.tag_id)
 GROUP BY core_page.id;

Action rate (weekly)

Description: Rate of actions, excluding unsubscribes, taken from mailings in the last week.
Short name: action_rate_weekly
User input: None
Associated with: None
SQL:  
SELECT IF(COUNT(um.id), ROUND( COUNT(a.mailing_id)
  COUNT(um.id) * 100, 1), 0.0)
FROM core_mailing m
JOIN core_usermailing um ON (m.id = um.mailing_id)
LEFT JOIN core_action a ON (um.mailing_id = a.mailing_id
  AND um.user_id = a.user_id)
LEFT JOIN core_unsubscribeaction u ON (u.action_ptr_id=a.id)
WHERE DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) <= m.started_at
  AND u.action_ptr_id IS NULL

Action takers for a page

Description: Number of new action takers with completed actions since last run for a specified page.
Short name: action_takers
User input: Page id
Associated with: Page mini-dashboard
SQL:  
{% if partial_run %}
SELECT COUNT(DISTINCT a.user_id)
FROM core_action a
LEFT join core_action old ON (old.page_id    = a.page_id
                          AND old.user_id    = a.user_id
                          AND old.created_at < a.created_at
                          AND old.status     = 'complete')
WHERE a.page_id  = {page_id}
AND a.created_at > {last_run}
AND a.status     = 'complete'
AND old.user_id  is null;
{% else %}
SELECT COUNT(DISTINCT user_id)
FROM core_action a
WHERE page_id = {page_id}
AND status    = 'complete';
{% endif %}

Action Takers by Jurisdiction for X Page

Description: Returns action taker totals for a given page broken down by Senate, House, State Senate or State House jurisdictions.
Short name: action_takers_jurisdiction_given_page
User input: Jurisdiction
page_id
Associated with:  
SQL:  
SET @Jurisdiction = (SELECT REPLACE(LOWER({Jurisdiction}),' ','_'));

{% if Jurisdiction|spaces_to_underscores == 'senate' %}
  SELECT
    t.seat AS 'Jurisdiction',
    CONCAT(t.title,' ',t.official_full) AS 'Name',
    a.count AS 'Action Takers'
  FROM core_target t
  JOIN (
    SELECT
      u.state,
      COUNT(DISTINCT u.id) AS count
    FROM core_action a
    JOIN core_user u ON u.id = a.user_id
    WHERE a.page_id = {page_id}
    AND a.status = 'complete'
    GROUP BY 1
    ORDER BY 1
  ) AS a ON t.state = a.state
  WHERE t.type = 'senate' AND t.hidden = 0
  ORDER BY 1;
{% else %}
  SELECT
    a.jurisdiction AS 'Jurisdiction',
    CONCAT(t.title,' ',t.first,' ',t.last) AS 'Name',
    a.count AS 'Action Takers'
  FROM core_target t
  JOIN (
    SELECT
    {% if Jurisdiction|spaces_to_underscores == 'state_house' %}
      l.us_state_district AS jurisdiction,
    {% elif Jurisdiction|spaces_to_underscores == 'state_senate' %}
      l.us_state_senate AS jurisdiction,
    {% else %}
      l.us_district AS jurisdiction,
    {% endif %}
      COUNT(DISTINCT a.user_id) AS count
    FROM core_action a
    JOIN core_location l ON a.user_id = l.user_id
    WHERE a.page_id = {page_id}
    AND a.status = 'complete'
    GROUP BY 1
    ORDER BY 1
  ) AS a ON a.jurisdiction =
    {% if Jurisdiction|spaces_to_underscores == 'state_senate' %}
      t.seat
    {% else %}
      t.us_district
    {% endif %}
  WHERE t.type = @Jurisdiction AND t.hidden = 0
  ORDER BY 1;
{% endif %}

Actions by Month Chart

Description: Number of completed actions taken in last year on call, petition, letter or survey pages, grouped by month.
Short name: progress_actions_chart
User input: None
Associated with: Action Rates: Your Monthly Progress Report
SQL:  
SELECT left(core_action.created_at,7), COUNT(*)
FROM core_action
JOIN core_page ON (core_page.id=core_action.page_id)
WHERE core_action.created_at >= date_sub(CONCAT(left(now(),7),'-01 00:00:00'), interval 1 year)
  AND core_page.real_actions
  AND core_action.status = 'complete'
GROUP BY 1

Actions taken

Description: Count of actions taken by users in the last week, excluding actions with an incomplete status and actions with a source of "import".
Short name: actions_week
User input: None
Associated with: List Stats,
list stats mini
SQL:  
SELECT COUNT(core_action.id)
FROM core_action
WHERE date_sub(current_timestamp(), interval 1 week) <= core_action.created_at
  AND status = 'complete'
  AND source <> 'import';

Actions taken (main list)

Description: Number of completed actions in the last week, on all pages including import pages, where the page adds users to list 1.
Short name: actions_week_main
User input: None
Associated with: list stats mini
SQL:  
SELECT COUNT(core_action.id)
FROM core_action
JOIN core_page ON (core_action.page_id = core_page.id)
WHERE date_sub(current_timestamp(), interval 1 week) <= core_action.created_at
  AND status = 'complete'
  core_page.list_id = 1;

Actions taken (weekly list)

Description: Same as Actions taken (main list) except for list 2.
Short name: actions_week_weekly
User input: None
Associated with: list stats mini
SQL:  
SELECT COUNT(core_action.id)
FROM core_action
JOIN core_page ON (core_action.page_id = core_page.id)
WHERE date_sub(current_timestamp(), interval 1 week) <= core_action.created_at
  AND status = 'complete'
  AND core_page.list_id = 2;

Actions taken, all-time

Description: Count of all actions since last ran where the source is not "import" regardless of action status.
Short name: actions_ever
User input: None
Associated with: Your Progress
SQL:  
SELECT COUNT(*)
FROM core_action
WHERE source <> 'import'
{% if partial_run %}
AND created_at BETWEEN {last_run} AND {now}
{% endif %}
;

Activity Rate by Last Previous Action

Description: Percentage of users taking action in the last month, broken down by the number of months since their last previous action.
Short name: activity_rate_last_previous
User input: None
Associated with: None
SQL:  
select
    previous_action_months_ago,
    number_of_users,
    took_action_this_month,
    format( 100 * took_action_this_month / number_of_users, 1 ) as action_pct
from (
    select
        floor( datediff( now(), ( select max(created_at) from core_action where core_action.user_id = core_user.id and created_at < now() - interval 30 day ) ) / 30 ) as previous_action_months_ago,
        count(*) as number_of_users,
        sum( if( exists ( select * from core_action where core_action.user_id = core_user.id and created_at >= now() - interval 30 day ), 1, 0 ) ) as took_action_this_month
    from core_user
    where core_user.subscription_status in ( 'subscribed' )
    group by 1
) as activity_rates

Amounts Raised by Source

Description: Stats for money raised by user source.
Short name: amounts_raised_source
User input: None
Associated with: Amounts Raised by Source (ROI) Dashboard
SQL:  
SELECT
  s.source AS 'Source',
  FORMAT(users,0) AS 'Users',
  FORMAT(payments,0) AS 'Payments',
  paid AS 'Total Paid',
  FORMAT(paid/users,2) AS '$/User',
  FORMAT(days_on_list/users,0) AS 'Avg Days on List',
  FORMAT((365/12)*paid/days_on_list,2) AS '$/User Months on List'
FROM (
  SELECT source,
  COUNT(*) AS users,
  SUM(DATEDIFF(CURDATE(), created_at)) AS days_on_list
  FROM core_user
  GROUP BY source
) s
LEFT JOIN (
  SELECT source,
  COUNT(DISTINCT t.id) AS payments,
  SUM(t.amount_converted) AS paid
  FROM core_user u
  JOIN core_order o ON o.user_id = u.id
  JOIN core_transaction t ON t.order_id = o.id AND t.success = 1 AND t.type = 'sale' AND t.status = 'completed'
  GROUP BY source
) p ON p.source = s.source
GROUP BY 1
ORDER BY 4 DESC, 5 DESC;

Attendees of upcoming events

Description: List of user_ids for all users signed up to attend an active event occurring at least 10 hours from now where the event is in the specified event campaign. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: upcoming_event_attendees
User input: Event name
Used in: None
SQL:  
SELECT distinct es.user_id
FROM events_event e
JOIN events_eventsignup es
  ON es.event_id=e.id
JOIN events_campaign c
  ON e.campaign_id = c.id
WHERE c.name = {campaign_name}
  AND e.status='active'
  AND es.status='active'
  AND es.role='attendee'
  AND host_is_confirmed=1
  AND e.starts_at >= now() - interval 10 hour;

Average Donation

Description: Amount of average order (donation, product order, new recurring commitment) in the last week, excluding orders that were not completed.
Short name: donations_average
User input: None
Used in: List Stats
SQL:  
SELECT ROUND(COALESCE(SUM(total),0) COUNT(*),2)
FROM core_order
WHERE status = 'completed'
  AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) <= created_at

Basic Product Report

Description: Report that displays combined sales information, grouped by product, for completed orders of specified product and with specified start and end dates.
Short name: product_report
User input: Start date,
End date,
Product id
Used in: None
SQL:  
SELECT core_product.name AS 'product name',
  core_product.price AS 'price',
  SUM(core_order_detail.quantity) AS 'qty',
  SUM(core_order_detail.amount) AS 'total',
  COUNT(DISTINCT core_order.user_id) AS 'users'
FROM core_order
JOIN core_order_detail
  ON core_order_detail.order_id=core_order.id
JOIN core_product
  ON core_product.id=core_order_detail.product_id
WHERE core_order.status = 'completed'
  AND DATE_FORMAT(core_order_detail.created_at, '%Y-%m-%d') >= {{ 1. start_date YYYY-MM-DD }}
  AND DATE_FORMAT(core_order_detail.created_at, '%Y-%m-%d') <= {{ 2. end_date YYYY-MM-DD }}
  AND core_order_detail.product_id={{ product_id }}
GROUP BY core_product.id;

Calls by State Chart

Description: Number of calls completed grouped by state for a specified page.
Short name: calls_by_state_chart
User input: Page id
Used in: Call Page Dashboard
SQL:  
SELECT ct.state, COUNT(distinct cat.callaction_id)
FROM core_action ca
JOIN core_callaction_targeted cat ON (ca.id = cat.callaction_id)
JOIN core_target ct ON (cat.target_id = ct.id)
WHERE page_id = {page_id}
  AND ca.status = 'complete'
GROUP BY ct.state

Calls Made by Target

Description: Count of calls with status "complete", broken down by target, for a specified page. Shows a count of the actions where the user submitted on the page as "actions" and those where the user checked the box to indicate which target was called as "checked".
Short name: calls_made_by_target
User input: Page id
Used in: Call Page Dashboard
SQL:  
SELECT
  CONCAT(ct.title,' ',ct.first,' ',ct.last) target, ct.type,
  IF(ct.us_district='',ct.seat,ct.us_district) seat,
    ct.title, ct.long_title, ct.fax, ct.email, ct.created_at,
  COUNT(distinct cat.callaction_id) actions,
  COUNT(distinct cac.callaction_id) checked
FROM core_action ca
JOIN core_callaction_targeted cat
  ON (ca.id = cat.callaction_id)
JOIN core_target ct
  ON (cat.target_id = ct.id)
LEFT JOIN core_callaction_checked cac
  ON (ca.id = cac.callaction_id AND ct.id = cac.target_id)
WHERE page_id = {page_id}
  AND ca.status = 'complete'
GROUP BY ct.id

Candidate Donors

Description: List of users, donation amount and occupation and employer (if entered) for successful donations to a specified candidate.
Short name: candidate_donors
User input: Candidate id
Used in: None
SQL:  
SELECT u.id, u.first_name, u.last_name,
  u.address1, u.address2,
  u.city, u.state, u.zip,
  COALESCE(occ.value) AS occupation,
  COALESCE(emp.value) AS employer,
  SUM(od.amount) AS total
FROM core_user u
JOIN core_order o ON (u.id = o.user_id)
JOIN core_order_detail od ON (o.id = od.order_id)
JOIN core_transaction t ON (t.order_id = od.order_id AND t.success = 1
  AND t.type = 'sale')
LEFT JOIN core_actionfield occ ON (o.action_id = occ.parent_id AND occ.name = 'occupation')
LEFT JOIN core_actionfield emp ON (o.action_id = emp.parent_id AND emp.name = 'employer')
WHERE o.status = 'completed' AND od.candidate_id = {{candidate_id}}
GROUP BY u.id

Click on Page but no Action

Description: List of users who followed a link from a mailing but did not submit on the page specified. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: click_no_action
User input: Page id
Used in: None
SQL:  
SELECT cc.user_id
FROM core_click cc
JOIN core_clickurl ccu ON (cc.clickurl_id=ccu.id)
LEFT JOIN core_action ca
  ON (cc.user_id=ca.user_id
  AND ccu.page_id=ca.page_id
  AND ca.status='complete')
WHERE ccu.page_id = {page_id}
  AND cc.user_id is not null
  AND ca.id is null

Click rate (weekly)

Description: Displays the weekly rate of users clicking on your pages.
Short name: click_rate_week
User input: None
Used in: None
SQL:  
SELECT IF(COUNT(um.id), ROUND( COUNT(DISTINCT c.user_id)
  COUNT(um.id) * 100, 1), 0.0)
FROM core_mailing m
JOIN core_usermailing um ON (m.id = um.mailing_id)
LEFT JOIN core_click c ON (um.mailing_id = c.mailing_id AND um.user_id = c.user_id)
WHERE DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) <= m.started_at

Clickers for a mailing

Description: Count of clicks originating from a specified mailing since last run, where the link was not to an ActionKit-hosted unsubscribe page.
Short name: mailing_clickers
User input: Mailing id
Used in: One-line Mailing stats
SQL:  
SELECT COUNT(*) FROM (
  SELECT click.user_id
  FROM core_click click
  JOIN core_clickurl url ON (click.clickurl_id = url.id)
  WHERE mailing_id = {mailing_id} AND
        NOT url.url LIKE '%%/cms/unsubscribe/%%'
  GROUP BY click.user_id
{% if partial_run %}
  HAVING MIN(click.created_at) > {last_run}
{% endif %}
) recent_clicks;

Comments by Page

Description: Enter the page_id for any page with an action field called "comments" (usually petition and letter pages) and you'll see a list of the user_ids with their comments. Users who took action on the page but didn't comment aren't listed.
Short name: page_comments
User input: Page id
Used in: None
SQL:  
SELECT ca.user_id, af.value
FROM core_actionfield af
JOIN core_action ca ON (ca.id=af.parent_id)
WHERE af.name='comment'
  AND length(af.value) > 0 AND page_id = {page_id}

Confirmation Mailing Clickers

Description: Targets users who clicked on a confirmation mailing for a page.
Short name: confirmation_mailing_clickers
User input: Page id
Used in: None
SQL:  
SELECT DISTINCT tms.user_id
FROM core_transactionalmailingsent tms
JOIN core_transactionalmailingclick tmc
  ON (tms.id = tmc.transactional_mailing_sent_id)
JOIN core_transactionalmailing tm
  ON (tm.id = tms.transactional_mailing_id)
WHERE tm.page_id = {{ page_id }} AND tm.type = 'confirmation' AND tms.user_id IS NOT NULL;

Confirmation Mailing Openers

Description: Targets users who opened a confirmation mailing for a page.
Short name: confirmation_mailing_openers
User input: Page id
Used in: None
SQL:  
SELECT tms.user_id
FROM core_transactionalmailingsent tms
JOIN core_transactionalmailingopen tmo
  ON (tms.id = tmo.transactional_mailing_sent_id)
JOIN core_transactionalmailing tm
  ON (tm.id = tms.transactional_mailing_id)
WHERE tm.page_id = {{ page_id }} AND tm.type = 'confirmation' AND tms.user_id IS NOT NULL;

Confirmation Mailing Performance

Description: This report shows performance for the confirmation mailings associated with a page. The count of opens and clicks are unique per-send so multiple opens or clicks by a recipient don't count here.
Short name: confirmation_mailing_performance
User input: Page id
Used in: None
SQL:  
SELECT subject,
       type,
       sent,
       opens,
       opens / sent * 100 as open_pct,
       clicks,
       clicks / opens * 100 as clicks_per_open,
       actions,
       actions / opens * 100 as actions_per_open,
       NTL as new_to_list
FROM (
        SELECT tm.subject,
           tm.type,
           COUNT(DISTINCT tms.id) as sent,
           COUNT(DISTINCT tmo.transactional_mailing_sent_id) as opens,
           COUNT(DISTINCT tmc.transactional_mailing_sent_id) as clicks,
           COUNT(DISTINCT tma.id) as actions,
           COUNT(DISTINCT(tma_ntl.id)) as NTL
        FROM core_transactionalmailing tm
        JOIN core_transactionalmailingsent tms
          ON (tm.id = tms.transactional_mailing_id)
        LEFT JOIN core_transactionalmailingopen tmo
          ON (tms.id = tmo.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingclick tmc
          ON (tms.id = tmc.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingaction tma
          ON (tms.id = tma.transactional_mailing_sent_id)
        LEFT JOIN core_action tma_ntl
          ON (tma_ntl.id = tma.action_id AND tma_ntl.subscribed_user = 1)

        WHERE tm.page_id = {{ page_id }} AND tm.type = 'confirmation' GROUP BY 1, 2
) stats;

Count clicks by page

Description: Number of clicks on a page coming from mailings, grouped by page name.
Short name: clicks
User input: None
Used in: None
SQL:  
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;

Custom Action Fields by Page and Usage

Short name: page_action_custom_field_count
Description: Which custom action fields appeared on a given page, and how frequently were values submitted for them?
User input: None
Associated with:
SQL:  
SELECT name AS custom_action_field, count(*) AS action_count
FROM core_actionfield
JOIN core_action ON core_actionfield.parent_id = core_action.id
WHERE page_id in ({ page_id })
GROUP BY 1

Dollars raised (all time)

Description: Total dollars raised from successful orders (donations, product orders), including imported orders. For recurring donations, only the first payment is included.
Short name: dollars_total
User input: None
Used in: Your Progress
SQL:  
SELECT COALESCE(SUM(total), 0)
FROM core_order
WHERE status = 'completed'
{% if partial_run %}
AND created_at BETWEEN {last_run} AND {now}
{% endif %}
;

Dollars raised (all time) (including dollars from recurring donations)

Description: Total dollars raised ever from all successful orders (donations, product orders, recurring contributions), except imported orders.
Short name: dollars_total_including_recurring_donation
User input: None
Used in: None
SQL:  
SELECT COALESCE(SUM(t.amount), 0)
FROM core_order o
JOIN  core_transaction t ON (o.id=t.order_id)
WHERE o.status = 'completed'
  AND t.type = 'sale'
  AND t.success = 1
{% if partial_run %}
AND t.created_at BETWEEN {last_run} AND {now}
{% endif %}

Dollars raised (weekly)

Description: Total dollars raised in the last week for one time transactions, product orders and first payments on new recurring profiles, including imported donations.
Short name: dollars_weekly
User input: None
Used in: None
SQL:  
SELECT COALESCE(SUM(total),0)
FROM core_order
WHERE status = 'completed'
 AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) <= created_at

Dollars raised (weekly) (including recurring donations)

Description: Total dollars raised in the last week from all orders (donations, product orders, recurring donations), except imported orders.
Short name: dollars_weekly_with_recurring
User input: None
Used in: None
SQL:  
SELECT COALESCE(SUM(amount),0)
FROM core_order o
JOIN core_transaction t ON (o.id=t.order_id)
WHERE t.type = 'sale'
  AND t.success = 1
  AND o.status = 'completed'
  AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) <= t.created_at

Donations by Candidate

Description: A breakdown of donations by candidate excluding imported donations.
Short name: donations_by_candidate
User input: None
Used in: None
SQL:  
SELECT c.id AS candidate_id, c.name AS candidate_name, SUM(od.amount) AS total
FROM core_order o
JOIN core_order_detail od ON (o.id = od.order_id)
JOIN core_candidate c ON (od.candidate_id = c.id)
JOIN core_transaction t ON (t.order_id = od.order_id AND t.success = 1 AND t.type = 'sale')
WHERE o.status = 'completed'
GROUP BY c.id
ORDER BY c.name

Donations Details

Description: Tags, page name, donation count, count for the last week, count of donors who are new to your lists, and total raised last week and ever, broken down by page title, for all completed orders (donations, product orders). For recurring donations, the first payment for each new profile is included. Includes import and donation pages.
Short name: donations_details
User input: None
Used in: List Stats
SQL:  
SELECT
(
  SELECT group_concat(distinct core_tag.name separator ', ') tags
  FROM core_page_tags
  JOIN core_tag ON (core_tag.id = core_page_tags.tag_id)
  WHERE page_id = core_page.id
  GROUP BY page_id
) AS 'Issue Tags',
  core_page.title AS 'Page Name',
  COUNT(distinct core_order.id) AS 'Total',
  COUNT(distinct IF(date_sub(current_timestamp(),interval 1 week) <= ca.created_at, core_order.id,NULL)) AS 'No. Last Week',
  COUNT(distinct IF(ca.created_user = 1,ca.user_id,NULL)) AS 'No. New to List',
  concat('$',coalesce(sum(if(date_sub(current_timestamp(),interval 1 week) <= ca.created_at, core_order.total,0)),0)) AS 'Raised this Week',
  concat('$',coalesce(sum(core_order.total),0)) AS 'Total Raised'
FROM core_page
JOIN core_action AS ca ON (core_page.id = ca.page_id)
JOIN core_order ON (ca.id = core_order.action_id)
JOIN core_user ON (core_order.user_id = core_user.id)
WHERE core_page.status = 'active'
  AND ca.status = 'complete'
  AND ca.source <> 'import'
  AND core_order.status = 'completed'
GROUP BY core_page.title;

Donors whose cards expire next month

Description: List of users having active recurring donations whose credit cards will expire next month. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: cards_about_to_expire
User input: None
Used in: None
SQL:  
SELECT user_id from core_orderrecurring
WHERE exp_date = date_format(now() + interval 1 month, '%m%y') AND status = 'active';

Donors whose cards recently failed

Description: Donors whose cards failed last month (who have not created a new recurring donation profile). This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: cards_recently_failed
User input: None
Used in: None
SQL:  
SELECT user_id from core_orderrecurring r
WHERE status='canceled_by_processor' AND
/* successful transaction in past month */
EXISTS (
    SELECT * from core_transaction
    WHERE
      order_id = r.order_id AND
      created_at > now() - interval 1 month AND
      success = 1
) AND
/* user doesn't have another active recurring order */
NOT EXISTS (
    SELECT * from core_orderrecurring
    WHERE
      user_id = r.user_id AND
      status = 'active'
);

Email Productivity By Week

Description: Number of emails sent and resulting actions, broken down by week for the last year.
Short name: email_productivity_week
User input: None
Used in: None
SQL:  
select
    mailing_week,
    mails_sent,
    mail_actions,
    format( 100 * mail_actions / mails_sent, 2 ) as action_rate
from (
    select concat( 'Week of ', date_sub( date( core_mailing.started_at ), INTERVAL ( dayofweek( core_mailing.started_at ) - 1 ) DAY ) ) as mailing_week,
         sum( ( select count(*) from core_usermailing where core_usermailing.mailing_id = core_mailing.id ) ) as mails_sent,
        sum( ( select count(*) from core_action left join core_unsubscribeaction on ( core_unsubscribeaction.action_ptr_id = core_action.id ) where core_action.mailing_id = core_mailing.id and core_action.status = 'complete' and core_unsubscribeaction.action_ptr_id is null  ) ) as mail_actions
    from core_mailing
    where core_mailing.started_at > date_sub( current_date(), INTERVAL ( 364 + dayofweek( current_date() ) - 1 ) DAY )
    group by 1
) as mailing_rates

Event Count by City

Description: Number of active events by cities in the United States for a specified campaign.
Short name: event_count_by_city
User input: Campaign name
Used in: Event Report
SQL:  
SELECT e.city City, COUNT(distinct e.id) Events
FROM events_event e
JOIN events_campaign c
  ON (e.campaign_id=c.id)
WHERE e.country = 'United States'
  AND e.city <> ''
  AND e.city is not null
  AND c.name = {campaign_name}
  AND e.status = 'active'
GROUP BY 1
ORDER BY e.city desc limit 50

Event Count by Date

Description: Number of events, signups, and attendance for a specified campaign grouped by date. Limits to active events and signups who haven't canceled or been removed.
Short name: event_count_by_date
User input: Campaign name
Used in: Event Report
SQL:  
SELECT date(ee.starts_at) 'date',
  COUNT(distinct ee.id) 'events',
  sum(ee.attendee_count) 'signup total',
  coalesce(sum(att.total),0) 'attendance'
FROM events_event ee
JOIN events_campaign ec ON (ee.campaign_id = ec.id)
LEFT JOIN (
  SELECT ees.event_id 'event_id', sum(ees.attended) 'total'
  FROM events_eventsignup ees
  WHERE ees.status = 'active'
  GROUP BY 1) att
  ON (ee.id = att.event_id)
WHERE ec.name = {campaign_name}
  AND ee.status = 'active'
GROUP BY 1

Event Count by Signups

Description: Number of active events by specified campaign and specified max and min number of people signed up.
Short name: event_count_by_signups
User input: Campaign name
max and min number of signups
Used in: Event Report
SQL:  
SELECT COUNT(distinct e.id)
FROM events_event e
JOIN events_campaign c ON (e.campaign_id = c.id)
WHERE c.name = {campaign_name}
  AND e.status = 'active'
  AND attendee_count between {min_signups} AND {max_signups};

Event Count by States

Description: Number of active events in each state for the specified campaign name.
Short name: event_count_by_states
User input: Campaign name
Used in: Event Report
SQL:  
SELECT state State, COUNT(*) Events
FROM events_event e
JOIN events_campaign c ON (e.campaign_id=c.id)
WHERE c.name = {campaign_name}
  AND e.status = 'active'
  GROUP BY 1;

Events: Events With Few RSVPs

Description: Returns a list of future events within a campaign that could use more recruitment.
Short name: events_events_with_few_rsvps
User input: Event Campaign
RSVP count less than (Return events with fewer RSVPs than this number)
Number of open spots left (Return events with open spots of at least this number)
Used in: None
SQL:  
SELECT e.id AS "ID",
e.title AS "Title",
e.starts_at AS "Date",
e.attendee_count AS "Attendees",
e.max_attendees AS "Max Allowed",
e.max_attendees - e.attendee_count AS "Available spots"
FROM events_event e
JOIN events_campaign c ON (c.id = e.campaign_id) and c.name IN ( {{ campaign }} )
WHERE e.attendee_count < {{ rsvps }}
AND e.max_attendees - e.attendee_count >= {{ num }}
AND e.status = 'active'
AND e.starts_at >= NOW()
ORDER BY 1;

{% required_parameter "campaign" label "Event Campaign" order "1" %}
{% required_parameter "rsvps" label "RSVP count less than" order "2" default "5" type "number" hint "Return events with fewer RSVPs than this number" %}
{% required_parameter "num" label "Number of open spots left" order "3" default "5" type "number" hint "Return events with open spots of at least this number" %}

Events: List of Almost Full Events

Description: Returns a list of future events within a campaign that are nearly full.
Short name: events_list_almost_full_events
User input: Event Campaign
Number of open spots left (Return events with open spots of at least this number)
Used in: None
SQL:  
SELECT e.id AS "ID",
e.title AS "Title",
e.starts_at AS "Date",
e.attendee_count AS "Attendees",
e.max_attendees AS "Max Allowed",
e.max_attendees - e.attendee_count AS "Available spots"
FROM events_event e
JOIN events_campaign c ON (c.id = e.campaign_id) and c.name IN ( {{ campaign }} )
WHERE e.max_attendees - e.attendee_count <= {{ num }}
AND e.status = 'active'
AND e.starts_at >= NOW()
ORDER BY 1;

{% required_parameter "campaign" label "Event Campaign" order "1" %}
{% required_parameter "num" label "Number of open spots left" default "5" type "number" hint "Return events with open spots of at least this number" %}

Expected Monthly Dollars from Recurring Donations

Description: Total dollars expected on a monthly basis from active recurring donations.
Short name: recurring_donations_dollars_promised
User input: None
Used in: None
SQL:  
SELECT COALESCE(SUM(amount * IF (period='weeks',4,1)), 0)
FROM core_orderrecurring recur
WHERE status = 'active'

Hosts of upcoming events

Description: List of users who have signed up to host an event for the specified campaign. Exludes hosts for events in the past or less than 10 hours in the future and those that have been deleted, canceled, where the host is unconfirmed, or those waiting for review. Includes events that are full or private. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: upcoming_event_hosts
User input: Campaign name
Used in: None
SQL  
SELECT distinct es.user_id
FROM events_event e
JOIN events_eventsignup es
  ON es.event_id=e.id
JOIN events_campaign c
  ON e.campaign_id = c.id
WHERE c.name = {campaign_name}
  AND e.status='active'
  AND es.status='active'
  AND es.role='host'
  AND host_is_confirmed=1
  AND e.starts_at >= now() - interval 10 hour;

Lost users bounces

Description: Total number of users that bounced last week.
Short name: users_bounced
User input: None
Used in: List Stats
list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscriptionhistory
  ON (core_user.id = core_subscriptionhistory.user_id)
WHERE change_id = 3
  AND date_sub(current_date(), interval 1 week) <= core_subscriptionhistory.created_at;

Lost users bounces Spec list

Description: Total number of users contained that bounced last week who were previously on list_id 2.
Short name: users_bounced_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscriptionhistory
  ON (core_user.id = core_subscriptionhistory.user_id)
WHERE list_id = 2 AND change_id = 3
  AND date_sub(current_date(), interval 1 week) <= core_subscriptionhistory.created_at;

Lost users bounces Std list

Description: Total number of users who bounced last week who were previously on list_id=1.
Short name: users_bounced_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscriptionhistory
  ON (core_user.id = core_subscriptionhistory.user_id)
WHERE list_id = 1 AND change_id = 3
  AND date_sub(current_date(), interval 1 week) <= core_subscriptionhistory.created_at;

LTE Basic: Average Letters per User

Description: Average number of letters per user for a specified LTE page.
Short name: lte_basic_average_letters_per_user
User input: LTE page id
Used in: LTE Basic Dashboard
SQL:  
SELECT AVG(n) AS 'Average letters per user'
FROM (
  SELECT ca.user_id, COUNT(*) AS 'n'
  FROM core_page AS 'cp'
  JOIN core_ltepage AS 'cle'
    ON (cp.id = cle.page_ptr_id)
  JOIN core_action AS 'ca'
    ON (cp.id = ca.page_id)
  JOIN core_lteaction AS 'cla'
    ON (ca.id = cla.action_ptr_id)
  JOIN core_user AS 'cu'
    ON (ca.user_id = cu.id)
  WHERE cp.id = {lte_page_id}
  GROUP BY ca.user_id
  ) AS 't';

LTE Basic: Letters Submitted by State

Description: The number of letters and users, by state for a specified LTE page.
Short name: lte_basic_average_letters_by_state
User input: LTE page id
Used in: LTE Basic Dashboard
SQL:  
SELECT cu.state, COUNT(ca.id) AS 'Letters per state', COUNT(DISTINCT cu.id) AS 'Users per state'
FROM core_page AS 'cp'
JOIN core_ltepage AS 'cle'
  ON (cp.id = cle.page_ptr_id)
JOIN core_action AS 'ca'
  ON (cp.id = ca.page_id)
JOIN core_lteaction AS 'cla'
  ON (ca.id = cla.action_ptr_id)
JOIN core_user AS 'cu'
  ON (ca.user_id = cu.id)
WHERE cp.id = {lte_page_id}
GROUP BY cu.state;

LTE Basic: Letters Submitted per Newspaper

Description: Number of letters submitted, per newspaper for a specified LTE page.
Short name: lte_basic_letters_submitted_per_newspaper
User input: LTE page id
Used in: LTE Basic Dashboard
SQL:  
SELECT cmt.id, cmt.name, COUNT(*) AS 'Letters per paper'
FROM core_page AS 'cp'
JOIN core_ltepage AS 'cle'
  ON (cp.id = cle.page_ptr_id)
JOIN core_action AS 'ca'
  ON (cp.id = ca.page_id)
JOIN core_lteaction AS 'cla'
  ON (ca.id = cla.action_ptr_id)
JOIN core_mediatarget AS 'cmt'
  ON (cla.target_id = cmt.id)
WHERE cp.id = {lte_page_id}
GROUP BY cmt.id;

LTE Basic: Letters Submitted by Newspaper Type

Description: Number of letters submitted, per newspaper type for a specified LTE page.
Short name: lte_basic_letters_submitted_by_newspaper_type
User input: LTE page id
Used in: LTE Basic Dashboard
SQL:  
SELECT cmt.levelcode, COUNT(*) AS 'Letters per paper type'
FROM core_page AS 'cp'
JOIN core_ltepage AS 'cle'
  ON (cp.id = cle.page_ptr_id)
JOIN core_action AS 'ca'
  ON (cp.id = ca.page_id)
JOIN core_lteaction AS 'cla'
  ON (ca.id = cla.action_ptr_id)
JOIN core_mediatarget AS 'cmt'
  ON (cla.target_id = cmt.id)
WHERE cp.id = {lte_page_id}
GROUP BY cmt.levelcode;

LTE Basic: Total Letters Submitted

Description: Total letters submitted in a single LTE campaign.
Short name: lte_basic_total_letters_submitted
User input: LTE page id
Used in: LTE Basic Dashboard
SQL:  
SELECT COUNT(DISTINCT ca.id) AS 'Total letters submitted'
FROM core_page AS 'cp'
JOIN core_ltepage AS 'cle'
  ON (cp.id = cle.page_ptr_id)
JOIN core_action AS 'ca'
  ON (cp.id = ca.page_id)
JOIN core_lteaction AS 'cla'
  ON (ca.id = cla.action_ptr_id)
JOIN core_mediatarget AS `cmt`
  ON (cla.target_id = cmt.id)
WHERE cp.id = {lte_page_id};

LTE Basic: Total Users Submitting Letters

Description: The total number of distinct users who submitted at least one letter on the specified LTE page.
Short name: lte_basic_total_users_submitting_letters
User input: LTE page id
Used in: LTE Basic Dashboard
SQL:  
SELECT COUNT(DISTINCT cu.id)
  AS 'Total distinct users'
FROM core_page AS 'cp'
JOIN core_ltepage
  AS 'cle'
  ON (cp.id = cle.page_ptr_id)
JOIN core_action
  AS 'ca'
  ON (cp.id = ca.page_id)
JOIN core_lteaction
  AS 'cla'
  ON (ca.id = cla.action_ptr_id)
JOIN core_user
  AS 'cu'
  ON (ca.user_id = cu.id)
WHERE cp.id = {lte_page_id};

Mailable US Users

Description: Total number of subscribed users with email addresses in the United States. Excludes users whose email addresses don't meet very basic validation criteria.
Short name: users_mail_US
User input: None
Used in: List Stats
list stats mini
SQL:  
SELECT COUNT(distinct email)
FROM core_user
JOIN core_subscription
  ON (core_user.id = core_subscription.user_id)
  WHERE country = 'United States';

Mailable US Users, Special List

Description: Total number of US users subscribed to list 2 with email addresses meeting basic validation criteria.
Short name: users_mail_US_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct email)
FROM core_user
JOIN core_subscription
  ON (core_user.id = core_subscription.user_id)
WHERE list_id = 1 AND country = 'United States';

Mailable US Users, Standard List

Description: Total number of US users subscribed to list 1 with email addresses meeting basic validation criteria.
Short name: users_mail_US_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct email)
FROM core_user
JOIN core_subscription
  ON (core_user.id = core_subscription.user_id)
WHERE country = 'United States';

Mailable Users

Description: Total number of subscribed users with email addresses meeting basic validation criteria.
Short name: users_mail
User input: None
Used in: list stats mini
List Stats
Your Progress
SQL:  
SELECT COUNT(DISTINCT core_user.id) FROM core_user JOIN core_subscription
ON (core_user.id = core_subscription.user_id)
{% if partial_run %}
WHERE core_user.created_at BETWEEN {last_run} AND {now}
{% endif %}
;

Mailable Users, Special List

Description: Number of users subscribed to list 2 with email addresses meeting basic validation criteria.
Short name: users_mail_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct email)
FROM core_user
JOIN core_subscription
  ON (core_user.id = core_subscription.user_id)
WHERE list_id = 2;

Mailable Users, Standard List

Description: Number of users subscribed to list 2 with email addresses meeting basic validation criteria.
Short name: users_mail_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct email)
FROM core_user
JOIN core_subscription
  ON (core_user.id = core_subscription.user_id)
WHERE list_id = 1;

Mailing openers

Description: Count of users who opened the specified mailing since the last run.
Short name: mailing_openers
User input: Mailing id
Used in: One-line Mailing stats
SQL:  
SELECT COUNT(*) FROM (
  SELECT user_id
  FROM core_open
  WHERE mailing_id = {mailing_id}
  GROUP BY user_id
{% if partial_run %}
  HAVING MIN(created_at) > {last_run}
{% endif %}
) recent_opens;

Mailing Response: Mailing IDs

Description: Provides mailing IDs for the charts used in the 24 Hour Mailing Response Dashboard.
Short name: mailing_response_mailing_ids
User input: Days Past
Time Zone (optional, defaults to 'US/Eastern')
Used in: 24 Hour Mailing Response Dashboard
SQL:  
SET @tz = {% if Time_Zone %}{Time_Zone}{% else %}'US/Eastern'{% endif %};

SELECT
  GROUP_CONCAT(m.id ORDER BY m.id)
FROM core_mailing m
WHERE m.progress > 0
  AND m.started_at >= DATE(CONVERT_TZ(NOW(),'GMT',@tz)) - INTERVAL {Days_Past} DAY
  AND recurring_schedule_id IS NULL;

Mailing Response: Mailing Rates

Description: Provides chart data for opens and actions in the 24 Hour Mailing Response Dashboard.
Short name: mailing_response_mailing_rates
User input: Days Past
Time Zone (optional, defaults to 'US/Eastern')
Type (opens or actions)
Used in: 24 Hour Mailing Response Dashboard
SQL:  
SET SQL_BIG_SELECTS=1;
SET SESSION group_concat_max_len = 100000;
SET @tz = {% if Time_Zone %}{ Time_Zone }{% else %}'US/Eastern'{% endif %};
SET @type = { Type };

SELECT
  GROUP_CONCAT(rate ORDER BY number SEPARATOR '|')
FROM (
  SELECT
    number,
    GROUP_CONCAT(rate ORDER BY id) AS rate
  FROM (
    SELECT
      m.id,
      n.number,
      IF(n.number = 0, 0.00, FORMAT(100*COUNT(DISTINCT c.user_id) / m.progress,2)) AS rate
    FROM numeric_9999 n
    JOIN core_mailing m ON m.progress > 0
        AND m.started_at >= DATE(CONVERT_TZ(NOW(),'GMT',@tz)) - INTERVAL {Days_Past} DAY
        AND recurring_schedule_id IS NULL
    {% if Type == 'opens' %}
      LEFT JOIN core_open c ON c.mailing_id = m.id AND c.created_at < m.started_at + INTERVAL 24 HOUR
          AND HOUR(TIMEDIFF(c.created_at, m.started_at)) < n.number
    {% else %}
      LEFT JOIN core_action c ON c.mailing_id = m.id AND c.created_at < m.started_at + INTERVAL 24 HOUR
          AND HOUR(TIMEDIFF(c.created_at, m.started_at)) < n.number
          AND c.page_id NOT IN (SELECT id FROM core_page WHERE real_actions = 0)
    {% endif %}
    WHERE n.number <= 24
    GROUP BY 1, 2
    ORDER BY 1, 2
  ) y
  GROUP BY number
  ORDER BY number
) z;

Mailing Response: Mailing Stats

Description: Returns sent counts, open rates, clicks/open, unsubs/open, actions, and payments for each mailings sent in the for the past X days.
Short name: mailing_response_mailing_stats
User input: Days Past
Hours Since Send
Time Zone (optional, defaults to 'US/Eastern')
Used in: 24 Hour Mailing Response Dashboard
SQL:  
SET @tz = {% if Time_Zone %}{Time_Zone}{% else %}'US/Eastern'{% endif %};

SELECT x.id_field AS 'ID',
  IFNULL(DATE_FORMAT(CONVERT_TZ(cm.started_at,'GMT',@tz),'%Y-%m-%d %h:%i %p'),'z') AS 'Sent At',
  IFNULL(ms.text,'') AS 'Subject',
  IFNULL(cm.notes,'') AS 'Notes',
  IFNULL((SELECT GROUP_CONCAT(t.name)
    FROM core_mailing_tags mt
    JOIN core_tag t ON t.id = mt.tag_id
    WHERE mt.mailing_id = cm.id),'') AS 'Tags',
  x.sent AS 'Sent',
  x.open_rate AS 'Open %',
  x.cpo AS 'Clicks / Open',
  x.upo AS 'Unsubs / Open',
  x.actions AS 'Action Takers',
  x.act_rate AS 'Act%',
  x.paid AS '$',
  x.paid_mailed AS '$ / 100 Mailed'
FROM (
  SELECT
    id,
    IFNULL(CONCAT('/mailings/reports/',id,' ',id),'TOTAL') AS 'id_field',
    FORMAT(SUM(sent),0) AS 'sent',
    FORMAT(100*SUM(opens)/SUM(sent),2) AS open_rate,
    IFNULL(FORMAT(100*SUM(clicks)/SUM(opens),2),'No opens') AS cpo,
    IFNULL(FORMAT(100*SUM(unsubs)/SUM(opens),2),'No opens') AS upo,
      FORMAT(SUM(actions), 0) AS actions,
      FORMAT(100 * SUM(actions) / SUM(sent), 2) AS act_rate,
      FORMAT(SUM(paid),2) AS paid,
      FORMAT(SUM(paid) / (SUM(sent)/100), 2) AS paid_mailed
  FROM (
    SELECT m.id, m.progress AS sent,
      IFNULL((SELECT GROUP_CONCAT(t.name)
        FROM core_mailing_tags mt
        JOIN core_tag t ON t.id = mt.tag_id
        WHERE mt.mailing_id = m.id),'') AS tags,
      COALESCE((SELECT COUNT(DISTINCT user_id)
        FROM core_open
        WHERE mailing_id = m.id
        AND created_at < m.started_at + INTERVAL {Hours_Since_Send} HOUR),0) AS opens,
      COALESCE((SELECT COUNT(DISTINCT c.user_id)
        FROM core_click c
        JOIN core_clickurl cu ON cu.id = c.clickurl_id
        JOIN core_page p on p.id = cu.page_id
        WHERE c.mailing_id = m.id
        AND p.real_actions = 1
        AND c.created_at < m.started_at + INTERVAL {Hours_Since_Send} HOUR),0) AS clicks,
      COALESCE((SELECT COUNT(DISTINCT a.user_id)
        FROM core_unsubscribeaction u
        JOIN core_action a ON a.id = u.action_ptr_id
        WHERE a.mailing_id = m.id
        AND a.created_at < m.started_at + INTERVAL {Hours_Since_Send} HOUR),0) AS unsubs,
      COALESCE((SELECT COUNT(DISTINCT a.user_id)
        FROM core_action a
        JOIN core_page p ON p.id = a.page_id
        WHERE a.mailing_id = m.id
        AND p.real_actions = 1
        AND a.created_at < m.started_at + INTERVAL {Hours_Since_Send} HOUR),0) AS actions,
      COALESCE((SELECT SUM(t.amount_converted) AS paid
            FROM core_order o
            JOIN core_action a ON a.id = o.action_id
            JOIN core_transaction t ON t.order_id = o.id AND t.type = 'sale' AND t.status = 'completed' AND t.success = 1
            JOIN core_page p ON p.id = a.page_id
            WHERE a.status = 'complete'
            AND a.mailing_id = m.id
        AND p.real_actions = 1
        AND t.created_at < m.started_at + INTERVAL {Hours_Since_Send} HOUR),0) AS paid
    FROM core_mailing m
    WHERE m.progress > 0
    AND m.started_at >= DATE(CONVERT_TZ(NOW(),'GMT',@tz)) - INTERVAL {Days_Past} DAY
    AND recurring_schedule_id IS NULL
    GROUP BY m.id
    ORDER BY m.started_at
  ) mailings
  GROUP BY 1 WITH ROLLUP
) x
LEFT JOIN core_mailing cm ON cm.id = x.id
LEFT JOIN core_mailingsubject ms ON ms.mailing_id = cm.id
GROUP BY 1
ORDER BY 2,1;

Mailing sent count

Description: Number of individual emails sent for a specified mailing since last run.
Short name: mailing_sent_count
User input: Mailing id
Used in: One-line Mailing stats
Fundraising Campaigns: Your Progress Report
SQL:  
SELECT COUNT(*) FROM core_usermailing WHERE mailing_id={mailing_id}

Major Donors

Description: List of ids for users who have donated at least $250 in the last year, including non-imported donations, product orders, all recurring payments. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: major_donors
User input: None
Used in: None
SQL:  
SELECT user_id
FROM core_order co
JOIN core_transaction ct ON (co.id=ct.order_id)
WHERE co.status = 'completed' AND ct.success = 1
  AND ct.created_at > date_sub(now(), interval 1 year)
GROUP BY user_id
  HAVING sum(ct.amount) >= 250

Month's Actions

Description: Count of actions for all mailings sent in the month of a given date.
Short name: months_actions
User input: date
Used in: Rolling 12 Month Email Performance Rates
SQL:  
select coalesce(sum( (   select count( distinct user_id )
      from core_action
      left join core_unsubscribeaction u  on (u.action_ptr_id=core_action.id)
      where core_action.mailing_id = core_mailing.id
      and core_action.status = 'complete'
      and u.action_ptr_id is null  )), 0) as actions
from core_mailing
where left(core_mailing.started_at, 7) = left({{date}}, 7);

Month's Bounces

Description: Count of bounces for all mailings sent in the month of a given date.
Short name: months_bounces
User input: date
Used in: Rolling 12 Month Email Performance Rates
SQL:  
select count(distinct core_action.user_id, core_mailing.id) as bounces
from core_action
join core_unsubscribeaction us on (us.action_ptr_id=core_action.id)
join core_subscriptionhistory sh on (core_action.id=sh.action_id)
join core_mailing on (core_mailing.id = core_action.mailing_id)
join core_subscriptionchangetype ct on (ct.id=sh.change_id)
where core_action.mailing_id = core_mailing.id
and ct.name='unsubscribe_bounce'
and left(core_mailing.started_at, 7) = left({{date}}, 7);

Month's Clicks

Description: Count of clicks for all mailings sent in the month of a given date.
Short name: months_clicks
User input: date
Used in: Rolling 12 Month Email Performance Rates
SQL:  
select  coalesce(sum( ( select count( distinct user_id )
from core_click join core_clickurl cu on (cu.id=core_click.clickurl_id) where core_click.mailing_id = core_mailing.id AND NOT cu.url like '%/unsubscribe/%' ) ), 0) as clicks
from core_mailing
where left(core_mailing.started_at, 7) = left({{date}}, 7);

Month's Complaints

Description: Count of spam complaints for all mailings sent in the month of a given date.
Short name: months_complaints
User input: date
Used in: Rolling 12 Month Email Performance Rates
SQL:  
select count( distinct core_action.user_id, core_mailing.id ) as complaints
from core_action
join core_unsubscribeaction us on (us.action_ptr_id=core_action.id)
join core_mailing on (core_mailing.id = core_action.mailing_id)
join core_subscriptionhistory sh on (core_action.id=sh.action_id)
join core_subscriptionchangetype ct on (ct.id=sh.change_id)
where core_action.mailing_id = core_mailing.id
and ct.name='unsubscribe_email'
and left(core_mailing.started_at, 7) = left({{date}}, 7);

Month's Opens

Description: Count of opens for all mailings sent in the month of a given date.
Short name: months_opens
User input: date
Used in: Rolling 12 Month Email Performance Rates
SQL:  
select coalesce(sum( ( select count( distinct user_id ) from core_open where core_open.mailing_id = core_mailing.id  )), 0) as opens
from core_mailing
where left(core_mailing.started_at, 7) = left({{date}}, 7);

Month's Sent Email

Description: Count of all emails sent in the month of a given date.
Short name: months_sent_email
User input: date
Used in: Rolling 12 Month Email Performance Rates
SQL:  
select coalesce(sum( ( select count(*) from core_usermailing where core_usermailing.mailing_id = core_mailing.id )), 0) as sent_mailings
from core_mailing
where left(core_mailing.started_at, 7) = left({{date}}, 7);

Month's Unsubscribes

Description: Count of total unsubscribes for all mailings sent in the month of a given date.
Short name: months_unsubscribes
User input: date
Used in: Rolling 12 Month Email Performance Rates
SQL:  
select count( distinct core_action.user_id, core_mailing.id ) as unsubs
from core_action
join core_unsubscribeaction us on (us.action_ptr_id=core_action.id)
join core_mailing on core_action.mailing_id = core_mailing.id
where core_action.mailing_id = core_mailing.id
and left(core_mailing.started_at, 7) = left({{date}}, 7);

New Users

Description: Displays the number of subscribed users who were created in the last week.
Short name: users_new
User input: None
Used in: list stats mini
List Stats
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscription
  ON (core_user.id = core_subscription.user_id)
WHERE date_sub(current_date(), interval 1 week) <= core_user.created_at;

New Users by Month

Description: Displays the number of users who joined a list in the last year broken down by month, regardless of whether the user is currently subscribed.
Short name: progress_new_users_chart
User input: None
Used in: List Growth: Your Monthly Progress Report
SQL:  
SELECT left(created_at,7), COUNT(*)
FROM (
  SELECT user_id, min(id) id
  FROM core_subscriptionhistory
  WHERE change_id IN (1,2,7)
  GROUP BY 1) sh
JOIN core_subscriptionhistory using(id)
WHERE created_at >= date_sub(CONCAT(left(now(),7),'-01 00:00:00'), interval 1 year)
GROUP BY 1;

New Users from Page

Description: Returns user_id for all users that subscribed from a given page and completed their action on the page. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: new_users_from_page
User input: Page id
Used in: None
SQL:  
SELECT user_id
FROM core_action
WHERE page_id = {page_id}
  AND subscribed_user = 1 AND status = 'complete'

New Users, from actions

Description: Number of currently subscribed users added in the last week, excluding those where the user source is "import".
Short name: users_new_action
User input: None
Used in: List Stats
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscription ON (core_user.id = core_subscription.user_id)
WHERE date_sub(current_date(), interval 1 week) <= core_user.created_at
  AND source <> 'import';

New Users, imported

Description: Number of currently subscribed users created in the last week with the user source "import".
Short name: users_new_import
User input: None
Used in: List Stats
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscription ON (core_user.id = core_subscription.user_id)
WHERE date_sub(current_date(), interval 1 week) <= core_user.created_at
  AND source = 'import';

New Users, Spec List

Description: Number of users currently subscribed to list 2 who were added to list 2 in the last week.
Short name: users_new_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscription AS cs
  ON (core_user.id = cs.user_id)
JOIN core_subscriptionhistory AS csh
  ON (cs.user_id = csh.user_id)
WHERE cs.list_id = 2
  AND csh.list_id = 2
  AND date_sub(current_date(), interval 1 week) <= csh.created_at;

New Users, Std List

Description: Number of users currently subscribed to list 1 who were added to list 1 in the last week.
Short name: users_new_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscription AS cs
  ON (core_user.id = cs.user_id)
JOIN core_subscriptionhistory AS csh
  ON (cs.user_id = csh.user_id)
WHERE cs.list_id = 1 AND csh.list_id = 1
  AND date_sub(current_date(), interval 1 week) <= csh.created_at;

New-to-database users

Description: Users added to database n days ago. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: new_to_database
User input: Number of days
Used in: None
SQL:  
SELECT id
FROM core_user u
WHERE
  created_at between curdate() - interval {{ days }} day
  AND curdate() - interval ({{ days }}-1) day;

Note

If you use this for targeting in a recurring mailing, be sure that you have this set to run every time. With caching, there can be slight variations in when the report runs. So if you cache this report, depending on when the report runs and when the mailing gets sent, you may end up targeting the same users as the previous day, or missing users that should have been targeted.

New-to-list users

Description: List of users that have been added in for a specified number of days to a specified list. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: new_to_list
User input: Number of days
list id
Used in: None
SQL:  
SELECT user_id
FROM core_subscription
WHERE
  list_id = {{ list_id }}
  AND created_at between curdate() - interval {{ days }} day
  AND curdate() - interval ({{ days }}-1) day;

Note

If you use this for targeting in a recurring mailing, be sure that you have this set to run every time. With caching, there can be slight variations in when the report runs. So if you cache this report, depending on when the report runs and when the mailing gets sent, you may end up targeting the same users as the previous day, or missing users that should have been targeted.

Notification Mailing Performance

Description: This report shows performance for the notification mailings associated with a page. The count of opens and clicks are unique per-send so multiple opens or clicks by a recipient don't count here.
Short name: notification_mailing_performance
User input: Page ID
Used in: None
SQL:  
SELECT subject,
       sent,
       clicks,
       actions,
       NTL as new_to_list
FROM (
        SELECT tm.subject,
           tm.type,
           COUNT(DISTINCT(tms.id)) as sent,
           COUNT(DISTINCT tmo.transactional_mailing_sent_id) as opens,
           COUNT(DISTINCT tmc.transactional_mailing_sent_id) as clicks,
           COUNT(DISTINCT tma.id) as actions,
           COUNT(DISTINCT(tma_ntl.id)) as NTL
        FROM core_transactionalmailing tm
        JOIN core_transactionalmailingsent tms
          ON (tm.id = tms.transactional_mailing_id)
        LEFT JOIN core_transactionalmailingopen tmo
          ON (tms.id = tmo.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingclick tmc
          ON (tms.id = tmc.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingaction tma
          ON (tms.id = tma.transactional_mailing_sent_id)
        LEFT JOIN core_action tma_ntl
          ON (tma_ntl.id = tma.action_id AND tma_ntl.subscribed_user = 1)
        WHERE tm.page_id = {{ page_id }} AND tm.type = 'notification'
 GROUP BY 1, 2
) stats;

Open rate (weekly)

Description: Rate of mailings opened to mailings sent in the last week.
Short name: open_rate_week
User input: None
Used in: None
SQL:  
SELECT IF(COUNT(um.id), ROUND( COUNT(DISTINCT o.user_id)
  COUNT(um.id) * 100, 1), 0.0)
  AS open_rate
FROM core_mailing m
JOIN core_usermailing um ON (m.id = um.mailing_id)
LEFT JOIN core_open o ON (o.mailing_id = um.mailing_id AND o.user_id = um.user_id)
WHERE DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) <= m.started_at

Open rate by Mailbox Provider (daily)

Description: Percentage of messages opened w/r/t messages sent, per day, grouped by Mailbox Provider
Short name: mailings_deliverability_by_day_by_provider
User input: n_days
Used in: Daily Open Rate by Mailbox Provider
SQL:  
WITH
  -- List of ALL dates, for pivot report columns
  dates_in_range AS (
      SELECT DATE(now()-INTERVAL `number` DAY) AS a_date
      FROM numeric_9999
      WHERE `number` <= {n_days}
      ORDER BY `number` DESC
  ),

  -- Subset of mailings we care about, by date
  mailings_in_range AS (
      SELECT cm.id, DATE(cm.started_at) as started_at
      FROM core_mailing cm
      WHERE DATE(cm.started_at) >= DATE(now()-INTERVAL {n_days} DAY)
  ),

  -- User/mailings we care about
  user_mailing_provider AS (
      SELECT um.mailing_id, um.user_id, su.mailbox_provider
      FROM core_usermailing um
      JOIN summary_user su ON su.user_id=um.user_id
      WHERE um.mailing_id IN (SELECT id FROM mailings_in_range)
              AND su.mailbox_provider != "typo"
  ),

  -- Unique list of ALL mailbox providers, for pivot report rows
  mailbox_provider AS (
      SELECT DISTINCT(ump.mailbox_provider) as mailbox_provider
      FROM user_mailing_provider ump
  ),

  -- Count of messages sent, grouped by mailing id and provider
  sent_by_mailing_by_provider AS (
      SELECT ump.mailing_id, ump.mailbox_provider, COUNT(*) AS sends
      FROM user_mailing_provider ump
      GROUP BY 1,2
  ),

  -- Count of opens, grouped by mailing id and provider
  opened_by_mailing_by_provider AS (
      SELECT co.mailing_id, su.mailbox_provider, COUNT(*) AS opens
      FROM core_open co JOIN summary_user su ON su.user_id=co.user_id
      WHERE co.mailing_id IN (SELECT id FROM mailings_in_range)
      GROUP BY 1,2
  )

-- Finally, group/sum by date and mailbox provider
SELECT
  DATE_FORMAT(dir.a_date, "%b %e") as send_date,
  mp.mailbox_provider,
  IFNULL(sum(sb.sends), 0) as sent,
  IFNULL(sum(ob.opens), 0) as opened

FROM dates_in_range dir
  JOIN mailbox_provider mp
  LEFT JOIN mailings_in_range ms
      ON dir.a_date=ms.started_at
  LEFT JOIN sent_by_mailing_by_provider sb
      ON (ms.id=sb.mailing_id and mp.mailbox_provider=sb.mailbox_provider)
  LEFT JOIN opened_by_mailing_by_provider ob
      ON (ms.id=ob.mailing_id AND mp.mailbox_provider=ob.mailbox_provider)

GROUP BY 1,2
ORDER BY dir.a_date,mp.mailbox_provider

Overview: Actions

Description: Action and donation counts for a given timeframe.
Short name: overview_actions
User input: 1. Start on date YYYY-MM-DD
2. End by date YYYY-MM-DD
Used in: Daily Overview Dashboard
SQL:  
SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SELECT
  IFNULL(type,'TOTAL') AS 'Page Type',
  FORMAT(SUM(pages),0) AS Pages,
  FORMAT(SUM(actions),0) AS 'Actions',
  FORMAT(SUM(ntl),0) AS 'NTL',
  FORMAT(100*SUM(ntl)/SUM(actions),1) AS 'NTL%',
  FORMAT(SUM(donors),0) AS 'Donors',
  FORMAT(SUM(payments),0) AS 'Payments',
  FORMAT(SUM(paid),2) AS '$',
  FORMAT(IFNULL(SUM(paid)/SUM(payments),0),2) AS 'Average $'
FROM (
  SELECT
    p.type,
    COUNT(DISTINCT p.id) AS pages,
    COUNT(a.id) AS actions,
    COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) AS ntl,
    0 AS donors,
    0 AS payments,
    0 AS paid
  FROM core_action a
  JOIN core_page p ON p.id = a.page_id
  WHERE a.status = 'complete'
  AND a.created_at BETWEEN @start AND @end
  AND p.real_actions = 1
  AND p.type <> 'Donation'
  GROUP BY 1
  UNION ALL
  SELECT
    p.type AS type,
    COUNT(DISTINCT p.id) AS pages,
    COUNT(t.id) AS actions,
    COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 AND a.created_at BETWEEN @start AND @end THEN a.user_id ELSE NULL END) AS ntl,
    COUNT(DISTINCT a.user_id) AS donors,
    COUNT(DISTINCT t.id) AS payments,
    SUM(t.amount_converted) AS paid
  FROM core_transaction t
  JOIN core_order o ON  t.order_id = o.id
  JOIN core_action a ON o.action_id = a.id
  JOIN core_page p ON p.id = a.page_id
  WHERE t.status IN ('completed','')
  AND t.type = 'sale'
  AND t.success = 1
  AND t.created_at BETWEEN @start AND @end
  GROUP BY 1
) x
GROUP BY type WITH ROLLUP;

Overview: Mailed

Description: Count of mails sent, users sent to and unsubs for a given timeframe.
Short name: overview_mailed
User input: 1. Start on date YYYY-MM-DD
2. End by date YYYY-MM-DD
Used in: Daily Overview Dashboard
SQL:  
SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SET @unsubs = (SELECT COUNT(DISTINCT sh.user_id)
        FROM core_subscriptionhistory sh
        JOIN core_subscriptionchangetype sct ON sct.id = sh.change_id
        WHERE sct.subscribed = 0
          AND sh.created_at BETWEEN @start AND @end);

SELECT FORMAT(COUNT(*),0) AS 'Sent Mails',
  FORMAT(COUNT(DISTINCT um.user_id),0) AS 'Users Mailed',
  FORMAT(COUNT(*) - COUNT(DISTINCT um.user_id),0) AS 'Duplicate Mails',
  FORMAT(@unsubs,0) AS 'Unsubs'
FROM core_usermailing um
WHERE um.created_at BETWEEN @start AND @end;

Overview: Mailing Stats

Description: Mailing stats (sent, open%, clicks/open, action%, payments) for mailings sent during a given timeframe.
Short name: overview_mailing_stats
User input: 1. Start on date YYYY-MM-DD
2. End by date YYYY-MM-DD
Used in: Daily Overview Dashboard
SQL:  
SET SQL_BIG_SELECTS=1;
SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SELECT
    CONCAT('/mailings/reports/',mailing_id,' ', mailing_id) AS ID,
    started_at AS 'Sent At',
    subject_line AS 'Subject Line',
    notes AS Notes,
    FORMAT(sent, 0) AS Sent,
    FORMAT(100 * opens / sent, 1) AS 'Open%',
    IFNULL(FORMAT(100 * clicks / opens, 1),'No opens') AS 'Clicks /Open',
    IFNULL(FORMAT(100 * unsubs / opens, 2),'No opens') AS 'Unsubs /Open',
    FORMAT(actions, 0) AS 'Action Takers',
    FORMAT(100 * actions / sent, 2) AS 'Act%',
    FORMAT(ntl, 0) AS NTL,
    FORMAT(100 * (ntl-unsubs) / (sent/1000), 2) AS 'Net NTL /1000 Mailed',
    FORMAT(payments,0) AS Payments,
    FORMAT(paid,2) AS $,
    FORMAT(paid / (sent/100), 2) AS '$ /100 Mailed'
FROM (
    SELECT
        cm.id AS mailing_id,
        cm.started_at AS started_at,
        (SELECT text FROM core_mailingsubject ms WHERE ms.mailing_id = cm.id LIMIT 1) AS subject_line,
        cm.notes,
        cm.progress AS sent,
        COALESCE((SELECT COUNT(DISTINCT co.user_id)
            FROM core_open co
            WHERE co.mailing_id = cm.id), 0) AS opens,
        COALESCE((SELECT COUNT(DISTINCT cc.user_id)
            FROM core_click cc
            JOIN core_clickurl ccu ON ( ccu.id = cc.clickurl_id )
            WHERE cc.mailing_id = cm.id
            AND NOT ccu.url LIKE '%/cms/unsubscribe/%'), 0) AS clicks, -- do not count unsub clicks
        COALESCE((SELECT COUNT(DISTINCT ca.user_id)
            FROM core_unsubscribeaction cu
            JOIN core_action ca ON cu.action_ptr_id = ca.id
            WHERE ca.mailing_id = cm.id), 0) AS unsubs,
        COALESCE(d.payments,0) AS payments,
        COALESCE(d.paid,0) AS paid,
        COALESCE(acts.actions,0) AS actions,
        COALESCE(acts.new_to_list,0) AS NTL
    FROM core_mailing cm
    LEFT JOIN (
        SELECT mailing_id,
            COUNT(DISTINCT user_id) AS actions,
            COUNT(DISTINCT CASE WHEN subscribed_user = 1 THEN user_id ELSE NULL END) AS new_to_list
        FROM core_action a
        JOIN core_page p ON p.id = a.page_id
        WHERE p.real_actions = 1
            AND a.status = 'complete'
            AND a.created_at BETWEEN @start AND @end
        GROUP BY mailing_id
    ) acts ON acts.mailing_id = cm.id
    LEFT JOIN (
        SELECT mailing_id,
            COUNT(t.id) AS payments,
            SUM(t.amount_converted) AS paid
        FROM core_order o
        JOIN core_action a ON a.id = o.action_id
        JOIN core_transaction t ON t.order_id = o.id AND t.type = 'sale' AND t.status = 'completed' AND t.success = 1
        WHERE a.status = 'complete'
            AND t.created_at BETWEEN @start AND @end
        GROUP BY mailing_id
    ) d ON d.mailing_id = cm.id
    WHERE cm.started_at BETWEEN @start AND @end
    GROUP BY cm.id
) tt
ORDER BY started_at, mailing_id;

Overview: Recurring Donations

Description: Recurring donation stats for a given timeframe.
Short name: overview_recurring_donations
User input: 1. Start on date YYYY-MM-DD
2. End by date YYYY-MM-DD
Used in: Daily Overview Dashboard
SQL:  
SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SELECT
  paid_profiles AS 'Profiles with Payments',
  current_payments AS 'Payments',
  FORMAT(current_dollars,2) AS '$',
  new_payments AS 'New Profiles',
  FORMAT(new_dollars,2) AS 'New $',
  failed_payments AS 'Failed Payments',
  FORMAT(failed_dollars,2) AS 'Failed $',
  cancelled_payments AS 'Cancelled Profiles',
  FORMAT(cancelled_dollars,2) AS 'Cancelled $'
FROM (
  SELECT
    COUNT(DISTINCT cor.id) AS paid_profiles,
    COUNT(DISTINCT t.id) AS current_payments,
    COALESCE(SUM(t.amount_converted),0) AS current_dollars
  FROM core_orderrecurring cor
  JOIN core_transaction t ON t.order_id = cor.order_id
  WHERE t.created_at BETWEEN @start AND @end
  AND t.type = 'sale'
  AND t.status IN ('completed','')
  AND t.success = 1
) current
JOIN (
  SELECT
    COUNT(id) AS new_payments,
    COALESCE(SUM(amount_converted),0) AS new_dollars
  FROM (
    SELECT cor.id, MIN(t.created_at) AS 'first_payment', cor.amount_converted
      FROM core_orderrecurring cor
    JOIN core_transaction t ON t.order_id = cor.order_id
    WHERE t.status IN ('completed','')
    AND t.type = 'sale'
    AND t.success = 1
    AND t.created_at < @end
      GROUP BY 1
  ) a
  WHERE first_payment >= @start
) new_payments
JOIN (
  SELECT
    COALESCE(SUM(failed_month_payments),0) AS failed_payments,
    COALESCE(SUM(failed_month_dollars),0) AS failed_dollars
  FROM (
    SELECT cor.id,
    MONTH(t.created_at) AS mon,
    IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',1,0)) AS failed_month_payments,
    IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',cor.amount_converted,0)) AS failed_month_dollars
    FROM core_orderrecurring cor
    JOIN core_transaction t ON t.order_id = cor.order_id
    WHERE t.created_at >= @start AND t.created_at < @end
    AND t.type = 'sale'
    GROUP BY 1,2
  ) a
) failed
JOIN (
  SELECT
    COUNT(cor.id) AS cancelled_payments,
    COALESCE(SUM(cor.amount_converted),0) AS cancelled_dollars
  FROM core_orderrecurring cor
  WHERE cor.status LIKE 'cancel%'
    AND cor.updated_at > @start AND cor.updated_at < @end
) cancelled;

Overview: Top 10 Sources

Description: Top 10 sources by number of actions for a given timeframe. Includes NTL and payment amounts.
Short name: overview_source_info
User input: 1. Start on date YYYY-MM-DD
2. End by date YYYY-MM-DD
Used in: Daily Overview Dashboard
SQL:  
SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SELECT a.source AS Source,
  COUNT(DISTINCT a.user_id) AS 'Action Takers',
  COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) AS NTL,
  FORMAT(100*COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) / COUNT(DISTINCT a.user_id),1) AS 'NTL %',
  COUNT(DISTINCT t.id) AS Payments,
  IFNULL(SUM(t.amount_converted),'0.00') AS '$',
  FORMAT(IFNULL(SUM(t.amount_converted)/COUNT(DISTINCT t.id),0),2) AS 'Average $'
FROM core_action a
  JOIN core_page p ON p.id = a.page_id
  LEFT JOIN core_order o ON o.action_id = a.id
  LEFT JOIN core_transaction t ON t.order_id = o.id AND t.type = 'sale' AND t.status = 'completed' AND t.success = 1
WHERE a.status = 'complete'
  AND a.created_at BETWEEN @start AND @end
  AND p.real_actions = 1
GROUP BY a.source
ORDER BY 2 DESC
LIMIT 10;

Overview: Top 20 Action Pages

Description: Top 20 pages by number of actions for a given timeframe. Includes NTL and payment amounts.
Short name: overview_top_action_pages
User input: 1. Start on date YYYY-MM-DD
2. End by date YYYY-MM-DD
Used in: Daily Overview Dashboard
SQL:  
SET @start = {1._Start_on_date_YYYY-MM-DD};
SET @end = {2._End_by_date_YYYY-MM-DD};

SELECT *
FROM (
  SELECT
    CONCAT('/admin/core/page/',a.page_id,' ',a.page_id) AS 'Page ID',
    IF(p.type <> 'Import', CONCAT('/act/',p.name,' ',p.title), p.title) AS 'Title',
    p.type AS 'Type',
    IFNULL(notes,'') AS 'Notes',
    COUNT(DISTINCT a.user_id) AS 'Action Takers',
    COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) AS 'NTL',
    FORMAT(100*COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) / COUNT(DISTINCT a.user_id),1) AS 'NTL%',
    COUNT(DISTINCT t.id) AS 'Payments',
    FORMAT(IFNULL(SUM(t.amount_converted),0),2) AS '$',
    FORMAT(IFNULL(SUM(t.amount_converted)/COUNT(DISTINCT t.id),0),2) AS 'Avg $',
    CONCAT('/report/page_drilldown_dashboard/?page_id=',a.page_id,' ','Report') AS ''
  FROM core_action a
  JOIN core_page p ON p.id = a.page_id
  -- then include order tables because you can have payments on import pages, and import pages do not deal with recurring
  LEFT JOIN core_order o ON o.action_id = a.id
  LEFT JOIN core_transaction t ON t.order_id = o.id
    AND t.status IN ('completed','')
    AND t.type = 'sale'
    AND t.success = 1
    AND t.created_at BETWEEN @start AND @end
  WHERE a.status = 'complete'
  AND a.created_at BETWEEN @start AND @end
  AND p.real_actions = 1
  AND p.type <> 'donation'
  GROUP BY a.page_id
  UNION
  -- need a separate query for donations because recurring donations will have action timestamps in the past
  -- so need to look at transaction timestamps within this date range instead of action timestamps
  SELECT
    CONCAT('/admin/core/page/',a.page_id,' ',a.page_id) AS 'Page ID',
    CONCAT('/act/',p.name,' ',p.title) AS 'Title',
    p.type AS 'Type',
    IFNULL(notes,'') AS 'Notes',
    COUNT(DISTINCT a.user_id) AS 'Action takers',
    -- a.created_at can be outside of the date range
    -- because it may be associated with a recurring profile created outside of the range
    -- so make sure we limit NTL count to just the date range
    COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 AND a.created_at BETWEEN @start AND @end THEN a.user_id ELSE NULL END) AS 'NTL',
    FORMAT(100*COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 AND a.created_at BETWEEN @start AND @end THEN a.user_id ELSE NULL END) / COUNT(DISTINCT a.user_id),1) AS 'NTL%',
    COUNT(DISTINCT t.id) AS 'Payments',
    FORMAT(IFNULL(SUM(t.amount_converted),0),2) AS '$',
    FORMAT(IFNULL(SUM(t.amount_converted)/COUNT(DISTINCT t.id),0),2) AS 'Avg $',
    CONCAT('/report/page_drilldown_dashboard/?page_id=',a.page_id,' ','Report') AS ''
  FROM core_transaction t
  JOIN core_order o ON  t.order_id = o.id
  JOIN core_action a ON o.action_id = a.id
  JOIN core_page p ON p.id = a.page_id
  WHERE p.type = 'donation'
  AND t.status IN ('completed','')
  AND t.type = 'sale'
  AND t.success = 1
  AND t.created_at BETWEEN @start AND @end
  GROUP BY a.page_id
) x
ORDER BY 5 DESC, 9 DESC
LIMIT 20;

Page: Action Notification Mailing Stats

Description: Action notification sent, open, clicks and actions for the given page.
Short name: page_action_notification_mailing_stats
User input: page_id
Used in: Page Drilldown Dashboard
SQL:  
SELECT id AS ID,
  subject AS 'Subject Line',
  FORMAT(sent, 0) AS Sent,
  FORMAT(opens, 0) AS Opens,
  FORMAT(100 * opens / sent, 1) AS 'Open%',
  FORMAT(clicks, 0) AS Clicks,
  IFNULL(FORMAT(100 * clicks / opens, 1),'No opens') AS 'Clicks /Open',
  FORMAT(actions, 0) AS Actions,
  FORMAT(100 * actions / sent, 2) AS 'Act%'
FROM (
  SELECT tm.id,
    tm.subject,
    COUNT(DISTINCT tms.id) AS sent,
    COUNT(DISTINCT CASE WHEN tmo.id IS NOT NULL THEN a.user_id ELSE NULL END) AS opens,
    COUNT(DISTINCT CASE WHEN tmc.id IS NOT NULL THEN a.user_id ELSE NULL END) AS clicks,
    COUNT(DISTINCT CASE WHEN tma.id IS NOT NULL THEN a.user_id ELSE NULL END) AS actions,
    COUNT(DISTINCT CASE WHEN tmu.id IS NOT NULL THEN a.user_id ELSE NULL END) AS unsubs
  FROM core_notificationmailing
  JOIN core_transactionalmailing tm ON core_notificationmailing.transactionalmailing_ptr_id = tm.id
  JOIN core_transactionalmailingsent tms ON tms.transactional_mailing_id = tm.id
  JOIN core_action a ON a.id = tms.action_id
  LEFT JOIN core_transactionalmailingopen tmo ON tmo.transactional_mailing_sent_id = tms.id
  LEFT JOIN core_transactionalmailingclick tmc ON tmc.transactional_mailing_sent_id = tms.id
  LEFT JOIN core_transactionalmailingaction tma ON tma.transactional_mailing_sent_id = tms.id
  LEFT JOIN core_transactionalmailingunsub tmu ON tmu.transactional_mailing_sent_id = tms.id AND tmu.action_id = tms.action_id
  WHERE tm.page_id = {page_id}
  GROUP BY tm.id
) a;

Page: Confirmation Mailing Stats

Description: Confirmation mailing sent, opens, clicks and actions for the given page.
Short name: page_confirmation_mailing_stats
User input: page_id
Used in: Page Drilldown Dashboard
SQL:  
SELECT id AS ID,
  created_at AS 'Started',
  subject AS 'Subject Line',
  FORMAT(sent, 0) AS Sent,
  FORMAT(opens, 0) AS Opens,
  FORMAT(100 * opens / sent, 1) AS 'Open%',
  FORMAT(clicks, 0) AS Clicks,
  IFNULL(FORMAT(100 * clicks / opens, 1),'No opens') AS 'Clicks /Open',
  FORMAT(actions, 0) AS Actions,
  FORMAT(100 * actions / sent, 2) AS 'Act%',
  FORMAT(unsubs, 0) AS Unsubs,
  IFNULL(FORMAT(100 * unsubs / opens, 2),'No opens') AS 'Unsubs /Open'
FROM (
  SELECT tm.id,
    tm.subject,
    tm.created_at,
    COUNT(DISTINCT tms.id) AS sent,
    COUNT(DISTINCT CASE WHEN tmo.id IS NOT NULL THEN a.user_id ELSE NULL END) AS opens,
    COUNT(DISTINCT CASE WHEN tmc.id IS NOT NULL THEN a.user_id ELSE NULL END) AS clicks,
    COUNT(DISTINCT CASE WHEN tma.id IS NOT NULL THEN a.user_id ELSE NULL END) AS actions,
    COUNT(DISTINCT CASE WHEN tmu.id IS NOT NULL THEN a.user_id ELSE NULL END) AS unsubs
  FROM core_confirmationmailing
  JOIN core_transactionalmailing tm ON core_confirmationmailing.transactionalmailing_ptr_id = tm.id
  JOIN core_transactionalmailingsent tms ON tms.transactional_mailing_id = tm.id
  JOIN core_action a ON a.id = tms.action_id
  LEFT JOIN core_transactionalmailingopen tmo ON tmo.transactional_mailing_sent_id = tms.id
  LEFT JOIN core_transactionalmailingclick tmc ON tmc.transactional_mailing_sent_id = tms.id
  LEFT JOIN core_transactionalmailingaction tma ON tma.transactional_mailing_sent_id = tms.id
  LEFT JOIN core_transactionalmailingunsub tmu ON tmu.transactional_mailing_sent_id = tms.id AND tmu.action_id = tms.action_id
  WHERE tm.page_id = {page_id}
  GROUP BY tm.id
) a;

Page: Donations Totals

Description: Payment totals and average payment size for the given page.
Short name: page_donations_totals
User input: page_id
Used in: Page Drilldown Dashboard
SQL:  
SELECT COUNT(DISTINCT t.id) AS 'All payments',
  IFNULL(SUM(IF(t.amount_converted, t.amount_converted,o.total)),0) AS 'Total paid',
  CONCAT('$',FORMAT(IFNULL(SUM(IF(t.amount_converted, t.amount_converted,o.total)),0) / COUNT(DISTINCT t.id),2)) AS 'Average payment'
FROM core_action a
JOIN core_order o ON o.action_id = a.id
JOIN core_transaction t ON t.order_id = o.id AND t.status IN ('completed','') AND t.type = 'sale' AND t.success = 1
WHERE a.page_id = {page_id};

Page: Mailed

Description: Mail counts for the given page.
Short name: page_mailed
User input: page_id
Used in: Page Drilldown Dashboard
SQL:  
SELECT FORMAT(COUNT(*),0) AS 'Sent mails',
  FORMAT(COUNT(DISTINCT um.user_id),0) AS 'Users mailed',
  FORMAT(COUNT(*) - COUNT(DISTINCT um.user_id),0) AS 'Duplicate mails'
FROM core_usermailing um
JOIN (
    SELECT mailing_id
    FROM core_action
    WHERE page_id = { page_id }
    GROUP BY mailing_id
) yy ON yy.mailing_id = um.mailing_id;

Page: Rates per Mailed

Description: Actions, new-to-list and unsubs per mailed for the given page.
Short name: page_rates_per_mailed
User input: page_id
unsubs
Used in: Page Drilldown Dashboard
SQL:  
SET @sent = (SELECT IFNULL(SUM(m.progress),0) AS sent
        FROM core_mailing m
        JOIN (
            SELECT DISTINCT mailing_id
            FROM core_action
            WHERE page_id = { page_id }
        ) yy ON yy.mailing_id = m.id);
SET @unsubs = { unsubs };

SELECT CONCAT(FORMAT(100*COUNT(DISTINCT a.user_id)/@sent,1),'%') AS 'Actions / mailed',
  CONCAT(FORMAT(100*COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END)/@sent,2),'%') AS 'NTL / mailed',
  CONCAT(FORMAT(100*@unsubs/@sent,2),'%') AS 'Unsubs / mailed',
  FORMAT((COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) - @unsubs)/(@sent/1000),2) AS 'Net NTL / 1000 mailed'
FROM core_action a
WHERE a.status = 'complete'
AND a.page_id = {page_id};

Page: Rates per Mailed for Donation Pages

Description: Donations and payments per mailed for the given page.
Short name: page_rates_per_mailed_donations
User input: page_id
unsubs
Used in: Page Drilldown Dashboard
SQL:  
SET @sent = (SELECT IFNULL(SUM(m.progress),0) AS sent
        FROM core_mailing m
        JOIN (
            SELECT DISTINCT mailing_id
            FROM core_action
            WHERE page_id = { page_id }
        ) yy ON yy.mailing_id = m.id);
SET @unsubs = { unsubs };

SELECT CONCAT(FORMAT(100*COUNT(DISTINCT t.id)/@sent,2),'%') AS 'Payments / mailed',
  CONCAT('$',FORMAT(IFNULL(SUM(IF(t.amount_converted, t.amount_converted,o.total)),0)/(@sent/100),2)) AS '$ / 100 mailed',
  FORMAT(@unsubs,0) AS 'Unsubs from mailings',
  CONCAT(FORMAT(100*COUNT(DISTINCT t.id)/@unsubs,2),'%') AS 'Payments / unsub',
  CONCAT('$',FORMAT(IFNULL(SUM(IF(t.amount_converted, t.amount_converted,o.total)),0)/@unsubs,2)) AS '$ / unsub'
FROM core_action a
JOIN core_order o ON o.action_id = a.id
JOIN core_transaction t ON t.order_id = o.id AND t.status IN ('completed','') AND t.type = 'sale' AND t.success = 1
WHERE a.page_id = {page_id};

Page: Recurring Donations Created

Description: Recuring donations created on the given page with monthly value and average donation size.
Short name: page_recurring_donations_created
User input: page_id
Used in: Page Drilldown Dashboard
SQL:  
SELECT COUNT(*) AS 'Profiles created',
  FORMAT(SUM(cor.amount_converted),2) AS 'Monthly value',
  FORMAT(SUM(cor.amount_converted) / COUNT(*),2) AS 'Average donation'
FROM core_action a
JOIN core_order o ON a.id = o.action_id
JOIN core_orderrecurring cor ON cor.order_id = o.id
JOIN core_transaction t ON t.order_id = o.id AND t.success = 1 AND t.type = 'recurring_order_create'
WHERE a.page_id = {page_id}

Page: Signers and Subs

Description: Action taker, new-to-list, unsub and net new-to-list counts for the given page.
Short name: page_signers_subs
User input: page_id
unsubs
Used in: Page Drilldown Dashboard
SQL:  
SET @unsubs = { unsubs };

SELECT FORMAT(COUNT(DISTINCT a.user_id),0) AS 'Distinct action takers',
  FORMAT(COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END),0) AS 'NTL',
  FORMAT(@unsubs,0) AS 'Unsubs from mailings',
  FORMAT(COUNT(DISTINCT CASE WHEN a.subscribed_user = 1 THEN a.user_id ELSE NULL END) - @unsubs,0) AS 'Net NTL'
FROM core_action a
WHERE a.status = 'complete'
AND a.page_id = {page_id};

Page: Source Info

Description: Action takers or donations by source for the given page.
Short name: page_source_info
User input: page_id
page_type
Used in: Page Drilldown Dashboard
SQL:  
SET @pagetype = { page_type };
SELECT ca.source AS Source,
  COUNT(DISTINCT ca.user_id) AS 'Action Takers',
{% if page_type == 'donation' %}
  COUNT(DISTINCT ct.id) AS Payments,
  SUM(ct.amount_converted) AS '$',
  ROUND(SUM(ct.amount_converted)/COUNT(DISTINCT ca.user_id),2) AS '$ / Action Taker'
{% else %}
  COUNT(DISTINCT CASE WHEN ca.subscribed_user = 1 THEN ca.user_id ELSE NULL END) AS NTL,
  FORMAT(100*COUNT(DISTINCT CASE WHEN ca.subscribed_user = 1 THEN ca.user_id ELSE NULL END) / COUNT(DISTINCT ca.user_id),1) AS 'NTL %'
{% endif %}
FROM core_action ca
{% if page_type == 'donation' %}
  JOIN core_order co ON co.action_id = ca.id
  JOIN core_transaction ct ON ct.order_id = co.id AND ct.type = 'sale' AND ct.status = 'completed' AND ct.success = 1
{% endif %}
WHERE ca.status = 'complete'
AND ca.page_id = {page_id}
GROUP BY ca.source
ORDER BY 2 DESC, 3 DESC;

Page: Unsubs from Mailings Count

Description: Count of all unsubs from mailings associated with the given page.
Short name: page_unsubs_mailings_count
User input: page_id
Used in: Page Drilldown Dashboard
SQL:  
SELECT COUNT(DISTINCT a.user_id) AS unsubs
FROM core_unsubscribeaction cu
JOIN core_action a ON cu.action_ptr_id = a.id
JOIN (SELECT mailing_id
    FROM core_action
    WHERE page_id = { page_id }
    GROUP BY mailing_id
) m ON a.mailing_id = m.mailing_id;

People who recently received a recurring series

Description: Excludes recent recurring mailing recipients. For "days", enter 1 to exclude those mailed in the past 24h, 2 for 48h, etc. You can enter a specific recurring_schedule_id (not mailing_id) or leave blank to exclude people getting any recurring mailing. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: recently-received-recurring-mailing
User input: Number of days
recurring schedule id
Used in: None
SQL:  
SELECT user_id
FROM core_mailing m
JOIN core_usermailing um on mailing_id = m.id
WHERE
  m.started_at > now() - interval {{ days }} day
  AND ({{ recurring_schedule_id }} = 0
  OR recurring_schedule_id = {{ recurring_schedule_id }});

Product Fulfillment Report

Description: List orders by start/end date and product id(s).
Short name: product_fulfillment_report
User input: Start date
End date
product id
Used in: None
SQL:  
SELECT
  core_order.id AS 'Order ID',
  core_order.created_at AS 'Order Date/Time',
  core_user.id AS 'user ID',
  core_product.id AS 'product ID',
  core_product.name AS 'product name',
  core_product.price AS 'price',
  core_order_detail.quantity AS 'quantity',
  core_order_detail.amount AS 'amount',
  core_order_user_detail.prefix AS 'prefix',
  core_order_user_detail.first_name AS 'first_name',
  core_order_user_detail.middle_name AS 'middle_name',
  core_order_user_detail.last_name AS 'last_name',
  core_order_user_detail.suffix AS 'suffix',
  core_order_user_detail.address1 AS 'address1',
  core_order_user_detail.address2 AS 'address2',
  core_order_user_detail.city AS 'city',
  core_order_user_detail.state AS 'state',
  core_order_user_detail.region AS 'region',
  core_order_user_detail.postal AS 'postal',
  core_order_user_detail.zip AS 'zip',
  core_order_user_detail.plus4 AS 'plus-4',
  core_order_user_detail.country AS 'country',
  core_order_shipping_address.address1 AS 'ship-to: address1',
  core_order_shipping_address.address2 AS 'ship-to: address2',
  core_order_shipping_address.city AS 'ship-to: city',
  core_order_shipping_address.state AS 'ship-to: state',
  core_order_shipping_address.region AS 'ship-to: region',
  core_order_shipping_address.postal AS 'ship-to: postal',
  core_order_shipping_address.zip AS 'ship-to: zip',
  core_order_shipping_address.plus4 AS 'ship-to: plus-4',
  core_order_shipping_address.country AS 'ship-to: country'
FROM core_order
LEFT JOIN core_order_detail
  ON core_order_detail.order_id=core_order.id
LEFT JOIN core_product
  ON core_product.id=core_order_detail.product_id
LEFT JOIN core_user
  ON core_user.id=core_order.user_id
LEFT JOIN core_order_shipping_address
  ON core_order_shipping_address.id=core_order.shipping_address_id
LEFT JOIN core_order_user_detail
ON core_order_user_detail.id=core_order.user_detail_id
WHERE core_order.status = 'completed'
  AND DATE_FORMAT(core_order_detail.created_at, '%Y-%m-%d') >= {{ 1. start_date YYYY-MM-DD }}
  AND DATE_FORMAT(core_order_detail.created_at, '%Y-%m-%d') <= {{ 2. end_date YYYY-MM-DD }}
  AND core_order_detail.product_id={{ product_id }}
GROUP BY  core_order.id, core_product.id;

Pull current time

Description: Gets 'now'.
Short name: now
User input: None
Used in: None
SQL:  
SELECT now();

Recent Donors

Description: List of users donating in the last 3 months, excluding all imported or failed donations. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: recent_donors
User input: None
Used in: None
SQL:  
SELECT user_id
FROM core_order co
JOIN core_transaction ct
  ON (co.id=ct.order_id)
WHERE co.status = 'completed' AND ct.success = 1
  AND ct.created_at > date_sub(now(), interval 3 month)

Recent Donors (Last N Days)

Description: Users donating in the last N days.
Short name: engagement_donors
User input: Days
Used in: None
SQL:  
select user_id
from core_order co
    join core_transaction ct on(co.id=ct.order_id)
where co.status = 'completed'
    and ct.success = 1
    and ct.created_at > date_sub(now(), interval {{ days }} day)

Recent Mail Actions

Description: Users taking action from mailings in the last N days.
Short name: engagement_actions
User input: Days
Used in: None
SQL:  
select user_id
from core_action
where mailing_id is not null
    and referring_user_id is null
    and created_at > date_sub(now(), interval {{ days }} day)

Recent Mail Clickers

Description: Users clicking mailings in the last N days.
Short name: engagement_clickers
User input: Days
Used in: None
SQL:  
select user_id
from core_click
where mailing_id is not null
    and created_at > date_sub(now(), interval {{ days }} day)

Recent Mail Openers

Description: Users opening mailings in the last N days.
Short name: engagement_openers
User input: Days
Used in: None
SQL:  
select user_id
from core_open
where mailing_id is not null
    and created_at > date_sub(now(), interval {{ days }} day)

Recent New Users (Last N Days)

Description: Users added to the database in the last N days.
Short name: engagement_new_to_database
User input: Days
Used in: None
SQL:  
select id
from core_user
where created_at > date_sub(now(), interval {{ days }} day)

Recurring mailing clicks breakdown

Description: Clicks by link for given version(s) of a recurring series's content.
Preview
Short name: recurring_clicks
User input: Number of days
mailing id
recurring mailings schedule id
Used in: None
SQL:  
# links in given version(s)
select
link_number '#',
url 'URL',
clicks 'Clickers',
concat(format(ifnull(clicks/sent,0)*100,1),'%') as 'Click %'
from (
  select
    link_number,
    url,
    count(distinct user_id) clicks
  from
    core_recurringmailingschedule rms
    join core_mailing m on rms.id = m.recurring_schedule_id
    join core_click c on c.mailing_id = m.id
    join core_clickurl cu on cu.id = c.clickurl_id
  where
    rms.id = {{ recurringmailingschedule_id }} and
    started_at > now() - interval {{ days }} day and
    m.status in ('completed','died')
  group by 1
) as clickstats
join (
  select
    sum(progress) as sent
  from
    core_recurringmailingschedule rms
    join core_mailing m on rms.id = m.recurring_schedule_id
  where
    rms.id = {{ recurringmailingschedule_id }} and
    (recurring_source_mailing_id = {{ mailing_id }} or {{ mailing_id }} = 0) and
    started_at > now() - interval {{ days }} day and
    m.status in ('completed','died')
) as totals;

Recurring mailing subject test results

Description: Rates by subject for given version(s) of a recurring series's content.
Preview
Short name: recurring_subjects
User input: Number of days
mailing id
recurring mailings schedule id
Used in: None
SQL:  
# subjects in given verison(s)
select
  text as 'Subject',
  progress as 'Sent',
  opens as 'Openers',
  concat(format(ifnull(opens/progress,0)*100,1),'%') as 'Open %',
  clicks as 'Clickers',
  concat(format(ifnull(clicks/progress,0)*100,1),'%') as 'Click %',
  actions as 'Action takers',
  concat(format(ifnull(actions/progress,0)*100,1),'%') as 'Act %',
  unsubs as 'Unsubs',
  concat(format(ifnull(unsubs/progress,0)*100,1),'%') as 'Unsub %',
  bounces as 'Bounces',
  concat(format(ifnull(bounces/progress,0)*100,1),'%') as 'Bounce %'
from (
select
  ms.text,
  count(*) as progress,
  sum(exists(select * from core_open where user_id=um.user_id and mailing_id=um.mailing_id)) as opens,
  sum(exists(select * from core_click where user_id=um.user_id and mailing_id=um.mailing_id)) as clicks,
  sum(exists(select * from core_action join core_page p on p.id=page_id where p.type!='unsubscribe' and user_id=um.user_id and mailing_id=um.mailing_id)) as actions,
  sum(exists(select * from core_action join core_page p on p.id=page_id where p.type='unsubscribe' and user_id=um.user_id and mailing_id=um.mailing_id)) as unsubs,
  sum(exists(select * from core_bounce where user_id=um.user_id and mailing_id=um.mailing_id)) as bounces
from
  core_recurringmailingschedule rms
  join core_mailing m on rms.id = m.recurring_schedule_id
  join core_usermailing um on m.id = um.mailing_id
  join core_mailingsubject ms on ms.id = subject_id
where
  rms.id = {{ recurringmailingschedule_id }} and
  (recurring_source_mailing_id = {{ mailing_id }} or {{ mailing_id }} = 0) and
  started_at > now() - interval {{ days }} day and
  m.status in ('completed','died')
group by 1
) t;

Recurring Donation Dollars By Month

Description: Historical breakdown of dollars from recurring donations by month.
Short name: recurring_donations_dollars_by_month
User input: None
Used in: None
SQL:  
SELECT DATE_FORMAT(t.created_at,'%Y-%m'), SUM(t.amount)
FROM core_transaction t
JOIN core_orderrecurring p
  ON (t.order_id=p.order_id
  AND t.type='sale' AND t.success=1)
GROUP BY 1
ORDER BY 1 DESC

Recurring Donation Totals for month and year to date

Description: Up to the minute totals for Recurring Donations.
Short name: recurring_donations_to_date
User input: None
Used in: Recurring Donations Report
SQL:  
SELECT 'Month' AS '',
  COUNT(p.id) AS 'Donation Count',
  sum(p.amount) AS 'Donation Total',
  avg(p.amount) AS 'Donation Average'
FROM core_orderrecurring r
JOIN core_transaction p ON (
  r.order_id=p.order_id AND p.success=1 AND p.type='sale'
  )
WHERE DATE_FORMAT(p.created_at, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')
UNION
  SELECT 'Year' AS '',
    COUNT(p.id) AS 'Donation Count',
    sum(p.amount) AS 'Donation Total',
    avg(p.amount) AS 'Donation Average'
  FROM core_orderrecurring r
  JOIN core_transaction p ON (
    r.order_id=p.order_id AND p.success=1 AND p.type='sale'
    )
  WHERE YEAR(p.created_at) = YEAR(NOW())

Recurring Donations Monthly Report

Description: Monthly Report of Recurring Donations broken down by status.
Short name: recurring_donations_monthly_report
User input: year-month
Used in: Recurring Donations Report
SQL:  
SELECT 'Paid' AS {{ year_month }},
  COUNT(DISTINCT r.id) Donors,
  COALESCE(SUM(p.amount),'0.00') Dollars
FROM core_orderrecurring r
JOIN core_transaction p ON (r.order_id=p.order_id AND p.type='sale' AND p.success = 1)
WHERE DATE_FORMAT(p.created_at, '%Y-%m') = {{ year_month }}
UNION
  SELECT 'Failed to pay',
    COUNT(DISTINCT r.id) Donors,
    COALESCE(SUM(r.amount),'0.00') Dollars
  FROM core_orderrecurring r
  LEFT JOIN core_transaction success
   ON (r.order_id=success.order_id AND success.success=1
     AND success.type='sale'
     AND DATE_FORMAT(success.created_at, '%Y-%m') = {{ year_month }})
  WHERE success.id IS NULL
    AND r.status IN ('active')
    AND DATE_FORMAT(r.created_at, '%Y-%m') < {{ year_month }}
UNION
  SELECT 'Canceled by users',
    COUNT(DISTINCT r.id) Donors,
    COALESCE(SUM(r.amount),'0.00') Dollars
  FROM core_orderrecurring r
  WHERE DATE_FORMAT(r.updated_at, '%Y-%m') = {{ year_month }}
    AND r.status IN ('canceled_by_user', 'canceled_by_admin')
UNION
  SELECT 'Canceled for failure',
    COUNT(DISTINCT r.id) Donors,
    COALESCE(SUM(r.amount),'0.00') Dollars
  FROM core_orderrecurring r
  WHERE DATE_FORMAT(r.updated_at, '%Y-%m') = {{ year_month }}
    AND r.status IN ('canceled_by_sync', 'canceled_by_processor', 'canceled_by_failure', 'canceled_by_expired')

Recurring Donations with About to Expire Credit Cards

Description: Count and sum of active recurring commitments with about to expire credit cards.
Short name: recurring_donations_will_expire
User input: year-month
Used in: Recurring Donations Report
SQL:  
SELECT COUNT(r.id) Count,
  COALESCE(SUM(r.amount),'0.00') Dollars
FROM core_orderrecurring r
WHERE  exp_date = CONCAT(RIGHT({{ year_month }},2),RIGHT(LEFT({{ year_month }}, 4),2) )
  AND status = 'active'

Recurring Donations: active profiles by month since last year

Description: Counts of active recurring donation profiles by month for the past year and YTD.
Short name: recurring_donations_active_profiles_month_last_year
User input: 'donations' or 'dollars'
Used in: Recurring donations summary charts
SQL:  
SET @start = DATE_FORMAT(NOW(),'%Y-01-01') - INTERVAL 1 YEAR;
SET @curyear = YEAR(NOW());
SET @lastyear = @curyear - 1;
SET @option = {{ option }};

SELECT
  DATE_FORMAT(months.startmonth, '%b') AS 'Month',
{% if option == 'donations' %}
  COUNT(CASE WHEN YEAR(startmonth) = @lastyear THEN id ELSE NULL END) AS 'Previous year',
  COUNT(CASE WHEN YEAR(startmonth) = @curyear THEN id ELSE NULL END) AS 'Current year'
{% else %}
  IFNULL(SUM(CASE WHEN YEAR(startmonth) = @lastyear THEN amount_converted ELSE 0 END),0) AS 'Previous year $',
  IFNULL(SUM(CASE WHEN YEAR(startmonth) = @curyear THEN amount_converted ELSE 0 END),0) AS 'Current year $'
{% endif %}
FROM (
  SELECT MIN(date) as startmonth
  FROM numeric_date
  WHERE numeric_date.date >= @start
  AND numeric_date.date <= CURDATE()
  GROUP BY DATE_FORMAT(numeric_date.date, '%Y-%m')
) months
LEFT JOIN core_orderrecurring cor ON
cor.created_at < (months.startmonth + INTERVAL 1 MONTH) -- profiles created before the end of the month
AND cor.status NOT IN ( 'failed', 'pending' ) -- failed profiles were never active, pending profiles are not yet active
AND (cor.status = 'active' OR cor.updated_at >= months.startmonth) -- either the profile is still active
-- or the change in status occurred after the end of the month
AND months.startmonth < CURDATE() -- not projecting future months
GROUP BY 1
ORDER BY months.startmonth;

Recurring Donations: active recurring profiles

Description: List of all active recurring profiles with amount pledged, total paid, missed payments, and next run date
Short name: recurring_donations_active_recurring_profiles
User input: None
Used in: None
SQL:  
SELECT
  user_id AS 'user ID',
  name,
  profile_id AS 'recurring profile ID',
  started,
  FORMAT(payment_amount,2) AS 'payment amount',
  payments,
  FORMAT(total_paid,2) AS 'total paid',
  failed_payments AS 'missed payments',
  FORMAT(failed_dollars,2) AS 'amount missed',
  last_payment_date AS 'last payment date',
  IF(CURDATE() > calcdate OR last_payment_date = CURDATE(), calcplus, calcdate) AS 'next run date'
FROM (
  SELECT
    cor.id,
    CONCAT('/admin/core/user/',cor.user_id,' ',cor.user_id) AS user_id,
    CONCAT(u.first_name, ' ', u.last_name) AS name,
    CONCAT('/admin/orderrecurring/',cor.id,' ',COALESCE(cor.recurring_id,'None')) AS profile_id,
    DATE_FORMAT(DATE(MIN(t.created_at)), '%Y-%m-%d') AS started,
    cor.amount_converted AS payment_amount,
    COUNT(DISTINCT t.id) AS payments,
    SUM(t.amount_converted) AS total_paid,
    DATE_FORMAT(DATE(MAX(t.created_at)), '%Y-%m-%d') AS last_payment_date,
    CAST(cor.start + INTERVAL (PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'), DATE_FORMAT(cor.start,'%Y%m'))) MONTH AS CHAR(10)) AS calcdate,
    CAST(cor.start + INTERVAL (PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'), DATE_FORMAT(cor.start,'%Y%m')))+1 MONTH AS CHAR(10)) AS calcplus
  FROM core_order o
  JOIN core_orderrecurring cor ON o.id = cor.order_id
  JOIN core_transaction t ON t.order_id = o.id  AND t.type = 'sale' AND t.status IN ('completed', '') AND t.success = 1
  JOIN core_user u ON u.id = o.user_id
  WHERE o.status = 'completed'
  AND cor.status = 'active'
  GROUP BY cor.id
) actives
JOIN ( -- dedupe multiple failures in a month, and don't count failures for a given month if there is a successful transaction that month
  SELECT
    id,
    SUM(failed_month_payments) AS failed_payments,
    SUM(failed_month_dollars) AS failed_dollars
  FROM (
    SELECT
      cor.id,
      DATE_FORMAT(t.created_at, '%Y-%m') AS yearmonth,
      IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',1,0)) AS failed_month_payments,
      IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',cor.amount_converted,0)) AS failed_month_dollars
    FROM core_orderrecurring cor
    JOIN core_transaction t ON t.order_id = cor.order_id
    WHERE t.type = 'sale'
    GROUP BY 1,2
  ) a
  GROUP BY 1
) failed ON failed.id = actives.id
GROUP BY actives.id
ORDER BY 11;

Recurring Donations: cancelled profiles by month since last year

Description: Counts of cancelled recurring donation profiles by month for the past year and YTD.
Short name: recurring_donations_cancellations_month_last_year
User input: 'donations' or 'dollars'
Used in: Recurring donations summary charts
SQL  
SET @start = DATE_FORMAT(NOW(),'%Y-01-01') - INTERVAL 1 YEAR;
SET @curyear = YEAR(NOW());
SET @lastyear = @curyear - 1;
SET @option = {{ option }};

SELECT
  DATE_FORMAT(numeric_date.date, '%b') AS 'Month',
{% if option == 'donations' %}
  COUNT(CASE WHEN YEAR(updated_at) = @lastyear THEN id ELSE NULL END) AS 'Previous year',
  COUNT(CASE WHEN YEAR(updated_at) = @curyear THEN id ELSE NULL END) AS 'Current year'
{% else %}
  SUM(CASE WHEN YEAR(updated_at) = @lastyear THEN amount_converted ELSE 0 END) AS 'Previous year',
  SUM(CASE WHEN YEAR(updated_at) = @curyear THEN amount_converted ELSE 0 END) AS 'Current year'
{% endif %}
FROM numeric_date
LEFT JOIN core_orderrecurring cor ON DATE(cor.updated_at) = numeric_date.date AND cor.status LIKE 'cancel%' AND cor.updated_at >= @start
WHERE numeric_date.date >= @start
GROUP BY 1
ORDER BY numeric_date.date;

Recurring Donations: cards expiring next month

Description: List of active profiles with cards expiring next month
Short name: recurring_donations_cards_expiring_next_month
User input: None
Used in: None
SQL:  
SET @start = DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL 1 MONTH;

SELECT
  CONCAT('/admin/core/user/',cor.user_id,' ',cor.user_id) AS 'user ID',
  CONCAT(u.first_name, ' ', u.last_name) AS name,
  CONCAT('/admin/orderrecurring/',cor.id,' ',COALESCE(cor.recurring_id,'None')) AS 'recurring profile ID',
  DATE_FORMAT(DATE(MIN(t.created_at)), '%Y-%m-%d') AS started,
  CONCAT(LEFT(cor.exp_date,2),'-',RIGHT(cor.exp_date,2)) AS 'expiration date',
  cor.amount_converted AS 'payment amount',
  COUNT(DISTINCT t.id) AS payments,
  SUM(t.amount_converted) AS 'total paid',
  DATE_FORMAT(DATE(MAX(t.created_at)), '%Y-%m-%d') AS 'last payment date',
  CAST(IF(cor.status != 'active', cor.status, IF(cor.period = 'months', cor.start + interval ceiling( datediff( current_date() - interval 1 day, cor.start ) / 30.4375 ) month, IF( cor.period = 'weeks', cor.start + interval ceiling( datediff( current_date() - interval 1 day, cor.start ) / 7 ) week, 'unknown' ) ) ) AS CHAR(50))  as 'next run date'
FROM core_order o
JOIN core_orderrecurring cor ON o.id = cor.order_id
JOIN core_transaction t ON t.order_id = o.id  AND t.type = 'sale' AND t.status IN ('completed', '') AND t.success = 1
JOIN core_user u ON u.id = o.user_id
WHERE o.status = 'completed'
AND cor.status = 'active'
AND cor.exp_date = DATE_FORMAT(@start,'%m%y')
GROUP BY 1, cor.id
ORDER BY 4;

Recurring Donations: failed payments by month since last year

Description: Counts of recurring payment failures by month for the past year and YTD.
Short name: recurring_donations_failures_month_last_year
User input: 'donations' or 'dollars'
Used in: Recurring donations summary charts
SQL:  
SET @start = DATE_FORMAT(NOW(),'%Y-01-01') - INTERVAL 1 YEAR;
SET @curyear = YEAR(NOW());
SET @lastyear = @curyear - 1;
SET @option = {{ option }};

SELECT
  DATE_FORMAT(numeric_date.date, '%b') AS 'Month',
{% if option == 'donations' %}
  SUM(CASE WHEN YEAR(a.created_at) = @lastyear THEN failed_month_donations ELSE 0 END) AS 'Previous year',
  SUM(CASE WHEN YEAR(a.created_at) = @curyear THEN failed_month_donations ELSE 0 END) AS 'Current year'
{% else %}
  SUM(CASE WHEN YEAR(a.created_at) = @lastyear THEN a.failed_month_dollars ELSE 0 END) AS 'Previous year',
  SUM(CASE WHEN YEAR(a.created_at) = @curyear THEN a.failed_month_dollars ELSE 0 END) AS 'Current year'
{% endif %}
FROM numeric_date
LEFT JOIN (
  SELECT cor.id,
  MONTH(t.created_at) AS mon,
  DATE(MIN(t.created_at)) AS created_at,
  IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',1,0)) AS failed_month_donations,
  IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',cor.amount_converted,0)) AS failed_month_dollars
  FROM core_orderrecurring cor
  JOIN core_transaction t ON t.order_id = cor.order_id
  WHERE t.created_at >= @start
  AND t.type = 'sale'
  GROUP BY 1,2
) a ON a.created_at = numeric_date.date
WHERE numeric_date.date >= @start
GROUP BY 1
ORDER BY numeric_date.date;

Recurring Donations: Inactive profiles in a date range

Description: List of inactive profiles within a date range, with amount pledged, total paid, missed payments, last payment date, end date and profile status
Short name: recurring_donations_inactive_recurring_profiles_date_range
User input: Start date and end date in the form 'YYYY-MM-DD'
Used in: None
SQL:  
SELECT
  user_id AS 'user ID',
  name,
  profile_id AS 'recurring profile ID',
  started,
  FORMAT(payment_amount,2) AS 'payment amount',
  payments,
  FORMAT(total_paid,2) AS 'total paid',
  failed_payments AS 'missed payments',
  FORMAT(failed_dollars,2) AS 'amount missed',
  last_payment_date AS 'last payment date',
  end_date AS 'end date',
  status
FROM (
  SELECT
    cor.id,
    CONCAT('/admin/core/user/',cor.user_id,' ',cor.user_id) AS user_id,
    CONCAT(u.first_name, ' ', u.last_name) AS name,
    CONCAT('/admin/orderrecurring/',cor.id,' ',COALESCE(cor.recurring_id,'None')) AS profile_id,
    DATE_FORMAT(DATE(MIN(t.created_at)), '%Y-%m-%d') AS started,
    cor.amount_converted AS payment_amount,
    COUNT(DISTINCT t.id) AS payments,
    SUM(t.amount_converted) AS total_paid,
    DATE_FORMAT(DATE(MAX(t.created_at)), '%Y-%m-%d') AS last_payment_date,
    DATE_FORMAT(DATE(cor.updated_at), '%Y-%m-%d') AS end_date,
    cor.status
  FROM core_order o
  JOIN core_orderrecurring cor ON o.id = cor.order_id
  JOIN core_transaction t ON t.order_id = o.id  AND t.type = 'sale' AND t.status IN ('completed', '') AND t.success = 1
  JOIN core_user u ON u.id = o.user_id
  WHERE o.status = 'completed'
  AND cor.status NOT IN ( 'active', 'pending' )
  AND cor.updated_at >= {{ 1. Start on date YYYY-MM-DD }}
  AND cor.updated_at < {{ 2. End by date YYYY-MM-DD }}
  GROUP BY cor.id
) inactives
JOIN ( -- dedupe multiple failures in a month, and don't count failures for a given month if there is a successful transaction that month
  SELECT
    id,
    SUM(failed_month_payments) AS failed_payments,
    SUM(failed_month_dollars) AS failed_dollars
  FROM (
    SELECT
      cor.id,
      DATE_FORMAT(t.created_at, '%Y-%m') AS yearmonth,
      IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',1,0)) AS failed_month_payments,
      IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',cor.amount_converted,0)) AS failed_month_dollars
    FROM core_orderrecurring cor
    JOIN core_transaction t ON t.order_id = cor.order_id
    WHERE t.type = 'sale'
    GROUP BY 1,2
  ) a
  GROUP BY 1
) failed ON failed.id = inactives.id
GROUP BY inactives.id
ORDER BY 11;

Recurring Donations: Missed payments in a date range

Description: List of profiles with missed payments for months within a date range, with amount pledged, total paid, missed payments and profile status
Short name: recurring_donations_missed_monthly_payments_date_range
User input: Start date and end date in the form 'YYYY-MM-DD'
Used in: None
SQL:  
SELECT
  linked_user_id AS 'user ID',
  name,
  profile_id AS 'recurring profile ID',
  started,
  FORMAT(payment_amount,2) AS 'payment amount',
  payments,
  FORMAT(total_paid,2) AS 'total paid',
  last_payment_date AS 'last payment date',
  payments_in_period AS 'payments in period',
  paid_in_period AS 'total paid in period',
  failed_payments AS 'missed payments in period',
  FORMAT(failed_dollars,2) AS 'amount missed in period',
  status
FROM (
  SELECT
    cor.id,
    cor.user_id,
    CONCAT('/admin/core/user/',cor.user_id,' ',cor.user_id) AS linked_user_id,
    CONCAT(u.first_name, ' ', u.last_name) AS name,
    CONCAT('/admin/orderrecurring/',cor.id,' ',COALESCE(cor.recurring_id,'None')) AS profile_id,
    DATE_FORMAT(DATE(MIN(t.created_at)), '%Y-%m-%d') AS started,
    cor.amount_converted AS payment_amount,
    COUNT(t.id) AS payments,
    SUM(t.amount_converted) AS total_paid,
    COUNT(CASE WHEN t.created_at >= {{ 1. Start on date YYYY-MM-DD }} AND t.created_at < {{ 2. End by date YYYY-MM-DD }}
      THEN t.id ELSE NULL END) AS payments_in_period,
    SUM(CASE WHEN t.created_at >= {{ 1. Start on date YYYY-MM-DD }} AND t.created_at < {{ 2. End by date YYYY-MM-DD }}
      THEN t.amount_converted ELSE 0 END) AS paid_in_period,
    DATE_FORMAT(DATE(MAX(t.created_at)), '%Y-%m-%d') AS last_payment_date,
    cor.status
  FROM core_order o
  JOIN core_orderrecurring cor ON o.id = cor.order_id
  JOIN core_transaction t ON t.order_id = o.id  AND t.type = 'sale' AND t.status IN ('completed', '') AND t.success = 1
  JOIN core_user u ON u.id = o.user_id
  WHERE o.status = 'completed'
  GROUP BY cor.id
) profiles
JOIN ( -- dedupe multiple failures in a month, and don't count failures for a given month if there is a successful transaction that month
  SELECT
    id,
    SUM(failed_month_payments) AS failed_payments,
    SUM(failed_month_dollars) AS failed_dollars
  FROM (
    SELECT
      cor.id,
      DATE_FORMAT(t.created_at, '%Y-%m') AS yearmonth,
      IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',1,0)) AS failed_month_payments,
      IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',cor.amount_converted,0)) AS failed_month_dollars
    FROM core_orderrecurring cor
    JOIN core_transaction t ON t.order_id = cor.order_id
    WHERE t.type = 'sale'
    AND t.created_at >= {{ 1. Start on date YYYY-MM-DD }}
    AND t.created_at < {{ 2. End by date YYYY-MM-DD }}
    GROUP BY 1,2
  ) a
  GROUP BY 1
  HAVING failed_payments > 0
) failed ON failed.id = profiles.id
GROUP BY profiles.id
ORDER BY profiles.user_id;

Recurring Donations: new profiles by month since last year

Description: Counts of new recurring donation profiles by month for the past year and YTD.
Short name: recurring_donations_new_profiles_month_last_year
User input: 'donations' or 'dollars'
Used in: Recurring donations summary charts
SQL:  
SET @start = DATE_FORMAT(NOW(),'%Y-01-01') - INTERVAL 1 YEAR;
SET @curyear = YEAR(NOW());
SET @lastyear = @curyear - 1;
SET @option = {{ option }};

SELECT
  DATE_FORMAT(numeric_date.date, '%b') AS 'Month',
{% if option == 'donations' %}
  COUNT(CASE WHEN YEAR(first_payment) = @lastyear THEN id ELSE NULL END) AS 'Previous year',
  COUNT(CASE WHEN YEAR(first_payment) = @curyear THEN id ELSE NULL END) AS 'Current year'
{% else %}
  SUM(CASE WHEN YEAR(first_payment) = @lastyear THEN amount_converted ELSE 0 END) AS 'Previous year',
  SUM(CASE WHEN YEAR(first_payment) = @curyear THEN amount_converted ELSE 0 END) AS 'Current year'
{% endif %}
FROM numeric_date
LEFT JOIN (
  SELECT cor.id,
    MIN(t.created_at) AS first_payment,
    cor.amount_converted,
    DATE(MIN(t.created_at)) as date
    FROM core_orderrecurring cor
  JOIN core_transaction t ON t.order_id = cor.order_id
  WHERE t.status IN ('completed','')
  AND t.type = 'sale'
  AND t.success = 1
    GROUP BY 1
) a ON a.date = numeric_date.date
WHERE numeric_date.date >= @start
GROUP BY 1
ORDER BY numeric_date.date;

Recurring Donations: next month totals for active profiles and expiring cards

Description: Count and total pledged for active profiles and profiles with cards expiring next month
Short name: recurring_donations_next_month_totals_active_expiring
User input: None
Used in: Recurring donations summary dashboard
SQL:  
SET @start = DATE_FORMAT(NOW(),'%Y-%m-01') + INTERVAL 1 MONTH;

SELECT
  COUNT(cor.id) AS 'active profiles',
  FORMAT(SUM(cor.amount_converted),2) AS 'active $',
  COUNT(CASE WHEN cor.exp_date = DATE_FORMAT(@start,'%m%y') THEN cor.id ELSE NULL END) AS 'expiring cards',
  FORMAT(SUM(CASE WHEN cor.exp_date = DATE_FORMAT(@start,'%m%y') THEN cor.amount_converted ELSE 0 END),2) AS 'expiring $'
FROM core_orderrecurring cor
WHERE cor.status = 'active';

Recurring Donations: payments by month since last year

Description: Counts of recurring donation payments by month for the past year and YTD.
Short name: recurring_donations_payments_month_last_year
User input: 'donations' or 'dollars'
Used in: Recurring donations summary charts
SQL:  
SET @start = DATE_FORMAT(NOW(),'%Y-01-01') - INTERVAL 1 YEAR;
SET @curyear = YEAR(NOW());
SET @lastyear = @curyear - 1;
SET @option = {{ option }};

SELECT
  DATE_FORMAT(numeric_date.date, '%b') AS 'Month',
{% if option == 'donations' %}
  COUNT(CASE WHEN YEAR(a.date) = @lastyear THEN a.id ELSE NULL END) AS 'Previous year',
  COUNT(CASE WHEN YEAR(a.date) = @curyear THEN a.id ELSE NULL END) AS 'Current year'
{% else %}
  SUM(CASE WHEN YEAR(a.date) = @lastyear THEN a.amount_converted ELSE 0 END) AS 'Previous year',
  SUM(CASE WHEN YEAR(a.date) = @curyear THEN a.amount_converted ELSE 0 END) AS 'Current year'
{% endif %}
FROM numeric_date
LEFT JOIN (
  SELECT DATE(t.created_at) as date,
    t.id,
    t.amount_converted
  FROM core_orderrecurring cor
  JOIN core_transaction t ON t.order_id = cor.order_id
  WHERE t.created_at >= @start
  AND t.type = 'sale'
  AND t.status IN ('completed','')
  AND t.success = 1
) a ON a.date = numeric_date.date
WHERE numeric_date.date >= @start
GROUP BY 1
ORDER BY numeric_date.date;

Recurring Donations: successful payments in a date range

Description: List of profiles with successful payments within a date range, with amount pledged, total paid, and profile status
Short name: recurring_donations_successful_payments_date_range
User input: Start date and end date in the form 'YYYY-MM-DD'
Used in: None
SQL:  
SELECT
  linked_user_id AS 'user ID',
  name,
  profile_id AS 'recurring profile ID',
  started,
  FORMAT(payment_amount,2) AS 'payment amount',
  payments,
  FORMAT(total_paid,2) AS 'total paid',
  last_payment_date AS 'last payment date',
  payments_in_period AS 'payments in period',
  paid_in_period AS 'total paid in period',
  status AS 'profile status'
FROM (
  SELECT
    cor.id,
    cor.user_id,
    CONCAT('/admin/core/user/',cor.user_id,' ',cor.user_id) AS linked_user_id,
    CONCAT(u.first_name, ' ', u.last_name) AS name,
    CONCAT('/admin/orderrecurring/',cor.id,' ',COALESCE(cor.recurring_id,'None')) AS profile_id,
    DATE_FORMAT(DATE(MIN(t.created_at)), '%Y-%m-%d') AS started,
    cor.amount_converted AS payment_amount,
    COUNT(t.id) AS payments,
    SUM(t.amount_converted) AS total_paid,
    COUNT(CASE WHEN t.created_at >= {{ 1. Start on date YYYY-MM-DD }} AND t.created_at < {{ 2. End by date YYYY-MM-DD }}
      THEN t.id ELSE NULL END) AS payments_in_period,
    SUM(CASE WHEN t.created_at >= {{ 1. Start on date YYYY-MM-DD }} AND t.created_at < {{ 2. End by date YYYY-MM-DD }}
      THEN t.amount_converted ELSE 0 END) AS paid_in_period,
    DATE_FORMAT(DATE(MAX(t.created_at)), '%Y-%m-%d') AS last_payment_date,
    cor.status
  FROM core_order o
  JOIN core_orderrecurring cor ON o.id = cor.order_id
  JOIN core_transaction t ON t.order_id = o.id  AND t.type = 'sale' AND t.status IN ('completed', '') AND t.success = 1
  JOIN core_user u ON u.id = o.user_id
  WHERE o.status = 'completed'
  GROUP BY cor.id
  HAVING payments_in_period > 0
) profiles
ORDER BY profiles.user_id;

Recurring Donations: summary stats

Description: Top-line summary stats of active profiles, payments, new donors, failures, cancellations and net change for recurring donations for distinct periods (current month, previous month, YTD)
Short name: recurring_donations_summary_stats
User input: Period: 'month', 'year' or other, where other returns the stats for the previous month
Used in: Recurring donations summary dashboard
SQL:  
SET @period = {{period}};
{% if period == 'month' %}
SET @current = DATE_FORMAT(NOW(),'%Y-%m-01');
SET @prev = @current - INTERVAL 1 MONTH;
SET @end = DATE(NOW());
{% elif period == 'year' %}
SET @current = DATE_FORMAT(NOW(),'%Y-01-01');
SET @prev = @current - INTERVAL 1 YEAR;
SET @end = DATE(NOW());
{% else %}
SET @current = DATE_FORMAT(NOW(),'%Y-%m-01') - INTERVAL 1 MONTH;
SET @prev = @current - INTERVAL 1 MONTH;
SET @end = @current + INTERVAL 1 MONTH;
{% endif %}

SELECT
  paid_profiles AS 'profiles with payments',
  current_payments AS payments,
  FORMAT(current_dollars,2) AS '$',
  new_payments AS 'new profiles',
  FORMAT(new_dollars,2) AS 'new $',
  failed_payments AS 'failed payments',
  FORMAT(failed_dollars,2) AS 'failed $',
  cancelled_payments AS 'cancelled profiles',
  FORMAT(cancelled_dollars,2) AS 'cancelled $',
# prev_payments AS 'previous payments',
# FORMAT(prev_dollars,2) AS 'previous $',
  current_payments - prev_payments AS 'net change in payments',
  FORMAT(current_dollars - prev_dollars,2) AS 'net change in $'
FROM (
  SELECT
    COUNT(DISTINCT CASE WHEN t.created_at >= @current THEN cor.id ELSE NULL END) AS paid_profiles,
    COUNT(DISTINCT CASE WHEN t.created_at >= @current THEN t.id ELSE NULL END) AS current_payments,
    SUM(CASE WHEN t.created_at >= @current THEN t.amount_converted ELSE 0 END) AS current_dollars,
    COUNT(DISTINCT CASE WHEN t.created_at < @current THEN t.id ELSE NULL END) AS prev_payments,
    SUM(CASE WHEN t.created_at < @current THEN t.amount_converted ELSE 0 END) AS prev_dollars
  FROM core_orderrecurring cor
  JOIN core_transaction t ON t.order_id = cor.order_id
  WHERE t.created_at >= @prev AND t.created_at < @end
  AND t.type = 'sale'
  AND t.status IN ('completed','')
  AND t.success = 1
) current_prev
JOIN (
  SELECT
    COUNT(id) AS new_payments,
    SUM(amount_converted) AS new_dollars
  FROM (
    SELECT cor.id, MIN(t.created_at) AS 'first_payment', cor.amount_converted
      FROM core_orderrecurring cor
    JOIN core_transaction t ON t.order_id = cor.order_id
    WHERE t.status IN ('completed','')
    AND t.type = 'sale'
    AND t.success = 1
    AND t.created_at < @end
      GROUP BY 1
  ) a
  WHERE first_payment >= @current
) new_payments
JOIN (
  SELECT
    SUM(failed_month_payments) AS failed_payments,
    SUM(failed_month_dollars) AS failed_dollars
  FROM (
    SELECT cor.id,
    MONTH(t.created_at) AS mon,
    IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',1,0)) AS failed_month_payments,
    IF(SUM(CASE WHEN t.status IN ('completed','') AND t.success = 1 THEN 1 ELSE 0 END),0,IF(t.status = 'failed',cor.amount_converted,0)) AS failed_month_dollars
    FROM core_orderrecurring cor
    JOIN core_transaction t ON t.order_id = cor.order_id
    WHERE t.created_at >= @current AND t.created_at < @end
    AND t.type = 'sale'
    GROUP BY 1,2
  ) a
) failed
JOIN (
  SELECT
    COUNT(cor.id) AS cancelled_payments,
    SUM(cor.amount_converted) AS cancelled_dollars
  FROM core_orderrecurring cor
  WHERE cor.status LIKE 'cancel%'
    AND cor.updated_at > @current AND cor.updated_at < @end
) cancelled;

Recurring Donations: Top 10 mailings YTD

Description: Mailing performance for the top 10 mailings YTD that have brought in recurring donations
Short name: recurring_donations_mailings_ytd
User input: None
Used in: Recurring donations summary dashboard
SQL:  
SET @ytd = DATE_FORMAT(NOW(),'%Y-01-01');

SELECT
CONCAT('/mailings/reports/',id,' ', id) AS id,
DATE(started_at) AS date,
CONCAT('/mailings/drafts/',id,' ', text) AS 'subject line',
notes,
progress AS sent,
recurring_profiles AS 'recurring profiles',
payments AS payments,
dollars,
FORMAT(dollars / (progress/1000),2) AS '$ /1000 mailed',
FORMAT(payments / unsubs,2) AS 'payments /unsub',
FORMAT(dollars / unsubs, 2) AS '$ /unsub'
FROM (
  SELECT x.id, x.started_at, x.notes, x.progress, x.recurring_profiles, x.payments, x.dollars,
    COALESCE((SELECT COUNT(DISTINCT ca.user_id)
      FROM core_unsubscribeaction cu
      JOIN core_action ca ON cu.action_ptr_id = ca.id
      WHERE ca.mailing_id = x.id), 0) AS unsubs,
    (SELECT text FROM core_mailingsubject ms WHERE ms.mailing_id = x.id LIMIT 1) AS text
  FROM (
    SELECT m.id, m.started_at, m.notes, m.progress,
      COUNT(DISTINCT cor.id) AS recurring_profiles,
      COUNT(DISTINCT t.id) AS payments,
      FORMAT(SUM(t.amount_converted),2) AS dollars
    FROM core_orderrecurring cor
    JOIN core_order o ON o.id = cor.order_id
    JOIN core_transaction t ON t.order_id = o.id
    JOIN core_action a ON o.action_id = a.id
    JOIN core_mailing m ON a.mailing_id = m.id
    WHERE t.status IN ( 'completed', '' )
    AND t.type = 'sale'
    AND t.success = 1
    AND t.created_at >= @ytd
    AND m.started_at >= @ytd
    GROUP BY 1
    ORDER BY 5 DESC
    LIMIT 10
  ) x
) y;

Recurring Donations: Top 10 pages YTD

Description: Performance for the top 10 pages YTD that have brought in recurring donations
Short name: recurring_donations_pages_ytd
User input: None
Used in: Recurring donations summary dashboard
SQL:  
SET @ytd = DATE_FORMAT(NOW(),'%Y-01-01');

SELECT
  CONCAT('/admin/core/page/',a.page_id,' ',a.page_id) AS page_id,
  CONCAT('/donate/',p.name,' ',p.title) AS title,
  COUNT(DISTINCT cor.id) AS 'recurring profiles',
  COUNT(DISTINCT t.id) AS payments,
  FORMAT(SUM(t.amount_converted),2) AS dollars
FROM core_orderrecurring cor
JOIN core_order o ON o.id = cor.order_id
JOIN core_transaction t ON t.order_id = o.id
JOIN core_action a ON o.action_id = a.id
JOIN core_page p ON p.id = a.page_id
WHERE t.status IN ( 'completed', '' )
AND t.type = 'sale'
AND t.success = 1
AND t.created_at >= @ytd
GROUP BY a.page_id
ORDER BY 3 DESC
LIMIT 10;

Recurring Donations: Top 10 sources YTD

Description: Totals for the top 10 sources YTD that have brought in recurring donations
Short name: recurring_donations_sources_ytd
User input: None
Used in: Recurring donations summary dashboard
SQL:  
SET @ytd = DATE_FORMAT(NOW(),'%Y-01-01');

SELECT a.source,
COUNT(DISTINCT cor.id) AS 'recurring profiles',
COUNT(DISTINCT t.id) AS payments,
FORMAT(SUM(t.amount_converted),2) AS dollars
FROM core_orderrecurring cor
JOIN core_order o ON o.id = cor.order_id
JOIN core_transaction t ON t.order_id = o.id
JOIN core_action a ON o.action_id = a.id
WHERE t.status IN ( 'completed', '' )
AND t.type = 'sale'
AND t.success = 1
AND t.created_at >= @ytd
GROUP BY a.source
ORDER BY 2 DESC
LIMIT 10;

Re-engagement Activity By Date

Description: Daily counts of users moved on and off of re-engagement list.
Short name: reengagement_by_date
User input: None
Used in: None
SQL:  
select date( created_at ) as date,
  sum( added ) as added,
  sum( removed ) as removed,
  sum( unsubscribed ) as unsubscribed
from core_reengagementlog
where dry_run = false
group by 1

Share Stats By Date for Date Range

Description: Breakdown of sharing by date for a specified date range
Short name: shares_date_dates
User input: 1. start_date YYYY-MM-DD
2. end_date YYYY-MM-DD
Used in: Share Stats for Date Range
SQL:  
select traffic_dates.stat_date as 'Date',
    share_users as 'Sharers',
    share_count as 'Shares',
--    clicked_shares as 'Shares Clicked',
    click_count as 'Clicks',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    action_shares as 'Shares Acted On',
--    action_count as 'Actions from Shares',
    unique_users as 'Action takers',
    FORMAT(unique_users / share_count,2) AS 'Action takers /share',
    subscribed_users as 'NTL',
    FORMAT(subscribed_users / share_count,2) AS 'NTL /share'
from (
    select date as stat_date
    from numeric_date
    where date >= {{ 1. start_date YYYY-MM-DD }} and date < {{ 2. end_date YYYY-MM-DD }}
) as traffic_dates
left join (
    SELECT
        stat_date,
        count(*) as share_count,
        count(distinct sharer_id) as share_users
    FROM (
        select
            date(share_link.created_at) as stat_date,
            share_link.user_id as sharer_id
        from share_link
        where share_link.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        UNION ALL
        SELECT
            DATE(tms.created_at) AS stat_date,
            a.user_id AS sharer_id
        FROM core_transactionalmailingsent tms
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action a ON a.id = tms.action_id
        WHERE tms.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    ) a
    group by stat_date
) as shares on shares.stat_date = traffic_dates.stat_date
left join(
    select
        stat_date,
        count(distinct click_id) as clicked_shares,
        count(*) as click_count
    from
    (
        select
            date(share_click.created_at) as stat_date,
            share_link.id as click_id
        from share_click
            join share_link on ( share_click.share_id = share_link.id )
        where share_click.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        UNION ALL
        SELECT
            date(tmc.created_at) as stat_date,
            tmc.id as click_id
        FROM core_transactionalmailingclick tmc
            JOIN core_transactionalmailingsent tms ON tms.id = tmc.transactional_mailing_sent_id
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action a ON a.id = tms.action_id
        WHERE tmc.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    ) b
    group by stat_date
) as clicks on clicks.stat_date = traffic_dates.stat_date
left join (
    select
        stat_date,
        count(distinct action_share_id) as action_shares,
        count(action_share_id) as action_count,
        count(distinct action_taker_id) as unique_users,
        COUNT(DISTINCT CASE WHEN subscribed_user = 1 THEN action_taker_id ELSE null END) AS subscribed_users
    from
    (
        select
            date(share_action.created_at) as stat_date,
            share_link.id as action_share_id,
            core_action.user_id as action_taker_id,
            core_action.subscribed_user as subscribed_user
        from core_action
            join share_action on ( core_action.id = share_action.action_id )
            join share_link on ( share_action.share_id = share_link.id )
        where share_action.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        UNION ALL
        select -- this select covers the cases where AK can't track the share and we only know it's TAF or mailto through the action source
            DATE(core_action.created_at) as stat_date,
            core_action.id as action_share_id,
            core_action.user_id as action_taker_id,
            core_action.subscribed_user as subscribed_user
        from core_action
        where core_action.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        and (core_action.source like 'taf%' or core_action.source like 'mailto%')
        UNION  -- dedupe rows because TAF actions could show up in core_transactionalmailingaction and core_action.source='TAF'
        SELECT
            DATE(new.created_at) as stat_date,
            new.id AS action_share_id,
            new.user_id AS action_taker_id,
            new.subscribed_user AS subscribed_user
        FROM core_transactionalmailingaction tma
            JOIN core_transactionalmailingsent tms ON tms.id = tma.transactional_mailing_sent_id
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action original ON original.id = tms.action_id
            JOIN core_action new ON new.id = tma.action_id AND new.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    ) c
    group by stat_date
) as actions on actions.stat_date = traffic_dates.stat_date
ORDER BY traffic_dates.stat_date;

Share Stats By Generation for Date Range

Description: Breakdown of sharing by generation for a specified date range
Short name: shares_gen_dates
User input: 1. start_date YYYY-MM-DD
2. end_date YYYY-MM-DD
Used in: Share Stats for Date Range
SQL:  
select shares.share_gen as 'Share Generation',
    share_users as 'Sharers',
    share_count as 'Shares',
--    clicks.source_shares as 'Shares Clicked',
    click_count as 'Clicks',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    actions.source_shares as 'Shares Acted On',
--    action_count as 'Actions from Shares',
    unique_users as 'Action takers',
    FORMAT(unique_users / share_count,2) AS 'Action takers /share',
    subscribed_users as 'NTL',
    FORMAT(subscribed_users / share_count,2) AS 'NTL /share'
from (
    select
      coalesce( share_link.generation, 0 ) as share_gen,
      count(*) as share_count,
      count(distinct share_link.user_id) as share_users
    from share_link
    where share_link.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    group by share_link.generation
) as shares left join (
    select
      coalesce( share_link.generation, 0 ) as share_gen,
      count(*) as click_count,
      count(distinct share_link.id) as source_shares
    from share_click
      left join share_link on ( share_click.share_id = share_link.id )
    where share_click.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    group by share_link.generation
) as clicks on shares.share_gen = clicks.share_gen left join (
    select
      coalesce( share_link.generation, 0 ) as share_gen,
      count(distinct share_link.id) as source_shares,
      count(*) as action_count,
      count(distinct core_action.user_id) as unique_users,
      sum(subscribed_user) as subscribed_users
    from core_action
      join share_action on ( core_action.id = share_action.action_id )
      join share_link on ( share_action.share_id = share_link.id )
    where share_action.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    group by share_link.generation
) as actions on shares.share_gen = actions.share_gen

Share Stats By Page for Date Range

Description: Breakdown of sharing for the top 20 pages for a specified date range
Short name: shares_page_dates
User input: 1. start_date YYYY-MM-DD
2. end_date YYYY-MM-DD
Used in: Share Stats for Date Range
SQL:  
SELECT
    page AS 'Page',
    COUNT(DISTINCT a.user_id) AS 'Signatures',
    share_users as 'Sharers',
    share_count as 'Shares',
    FORMAT(share_count / COUNT(DISTINCT a.user_id),2) AS 'Shares /signature',
--    clicked_shares as 'Shares Clicked',
    click_count as 'Clicks',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    action_shares as 'Shares Acted On',
--    action_count as 'Actions from Shares',
    unique_users as 'Action takers',
    FORMAT(unique_users / share_count,2) AS 'Action takers /share',
    subscribed_users as 'NTL',
    FORMAT(subscribed_users / share_count,2) AS 'NTL /share'
FROM core_action a
JOIN (
    SELECT
        core_page.id,
        concat( core_page.title, ' (', core_page.name, ')' ) as page,
        share_users,
        share_count,
        clicked_shares,
        click_count,
        action_shares,
        action_count,
        unique_users,
        subscribed_users
    FROM core_page
    LEFT JOIN (
        SELECT
            page_id,
            COUNT(DISTINCT sharer_id) as share_users,
            COUNT(sharer_id) as share_count
        FROM
        (
            select
                share_link.page_id,
                share_link.user_id as sharer_id
            from share_link
            where share_link.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
            UNION ALL
            SELECT
                a.page_id,
                a.user_id AS sharer_id
            FROM core_transactionalmailingsent tms
                JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
                JOIN core_action a ON a.id = tms.action_id
            WHERE tms.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        ) as a
        GROUP BY page_id
    ) shares ON core_page.id = shares.page_id
    LEFT JOIN (
        SELECT
            page_id,
            COUNT(DISTINCT click_id) as clicked_shares,
            COUNT(click_id) as click_count
        FROM
        (
            select
                share_link.page_id,
                share_link.id as click_id
            from share_click
                join share_link on ( share_click.share_id = share_link.id )
            where share_click.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
            UNION ALL
            SELECT
                a.page_id,
                tmc.id as click_id
            FROM core_transactionalmailingclick tmc
                JOIN core_transactionalmailingsent tms ON tms.id = tmc.transactional_mailing_sent_id
                JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
                JOIN core_action a ON a.id = tms.action_id
            WHERE tmc.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        ) b
        GROUP BY page_id
    ) clicks ON shares.page_id = clicks.page_id
    LEFT JOIN (
        SELECT
            page_id,
            COUNT(DISTINCT action_share_id) as action_shares,
            COUNT(action_share_id) as action_count,
            COUNT(DISTINCT action_taker_id) as unique_users,
            COUNT(DISTINCT CASE WHEN created_user = 1 THEN action_taker_id ELSE null END) as created_users,
            COUNT(DISTINCT CASE WHEN subscribed_user = 1 THEN action_taker_id ELSE null END) AS subscribed_users
        FROM
        (
            select
                core_action.page_id,
                share_link.id as action_share_id,
                core_action.user_id as action_taker_id,
                core_action.created_user as created_user,
                core_action.subscribed_user as subscribed_user
            from core_action
                join share_action on ( core_action.id = share_action.action_id )
                join share_link on ( share_action.share_id = share_link.id )
            where share_action.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
            UNION ALL
            select -- this select covers the cases where AK can't track the share and we only know it's TAF or mailto through the action source
                core_action.page_id,
                core_action.id as action_share_id,
                core_action.user_id as action_taker_id,
                core_action.created_user as created_user,
                core_action.subscribed_user as subscribed_user
            from core_action
            where core_action.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
            and (core_action.source like 'taf%' or core_action.source like 'mailto%')
            UNION  -- dedupe rows because TAF actions could show up in core_transactionalmailingaction and core_action.source='TAF'
            SELECT
                new.page_id,
                new.id AS action_share_id,
                new.user_id AS action_taker_id,
                new.created_user AS created_user,
                new.subscribed_user AS subscribed_user
            FROM core_transactionalmailingaction tma
                JOIN core_transactionalmailingsent tms ON tms.id = tma.transactional_mailing_sent_id
                JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
                JOIN core_action original ON original.id = tms.action_id
                JOIN core_action new ON new.id = tma.action_id AND new.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        ) c
        GROUP BY page_id
    ) actions ON actions.page_id = shares.page_id
    WHERE share_count > 0 OR click_count > 0 OR action_count > 0
    order by share_count desc
    limit 20
) pages on pages.id = a.page_id
WHERE a.created_at BETWEEN {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
GROUP BY pages.id
ORDER BY pages.share_count DESC;

Share Stats By Type for Date Range

Description: Breakdown of sharing by distribution type for a specified date range
Short name: shares_type_dates
User input: 1. start_date YYYY-MM-DD
2. end_date YYYY-MM-DD
Used in: Share Stats for Date Range
SQL:  
select share_type.name as 'Share Type',
    share_users as 'Sharers',
    share_count as 'Shares',
--    clicked_shares as 'Shares Clicked',
    click_count as 'Clicks',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    action_shares as 'Shares Acted On',
--    action_count as 'Actions from Shares',
    unique_users as 'Action takers',
    FORMAT(unique_users / share_count,2) AS 'Action takers /share',
    subscribed_users as 'NTL',
    FORMAT(subscribed_users / share_count,2) AS 'NTL /share'
from
  share_type left join
(
    select
      share_link.type as share_type,
      count(*) as share_count,
      count(distinct share_link.user_id) as share_users
    from share_link
    where share_link.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    group by share_link.type
) as shares on shares.share_type = share_type.type
left join(
    select
      share_link.type as share_type,
      count(distinct share_link.id) as clicked_shares,
      count(*) as click_count
    from share_click
      join share_link on ( share_click.share_id = share_link.id )
    where share_click.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    group by share_link.type
) as clicks on clicks.share_type = share_type.type
left join (
    select
      share_link.type as share_type,
      count(distinct share_link.id) as action_shares,
      count(*) as action_count,
      count(distinct core_action.user_id) as unique_users,
        sum(subscribed_user) as subscribed_users
    from core_action
      join share_action on ( core_action.id = share_action.action_id )
      join share_link on ( share_action.share_id = share_link.id )
    where share_action.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    group by share_link.type
) as actions on actions.share_type = share_type.type
where share_type.type <> 'em' -- skip the TAF email type in the share tables
UNION
SELECT 'TAF Email' AS 'Share Type',
    share_users as 'Users Sharing',
    share_count as 'Number of Shares',
--    clicked_shares as 'Shares Clicked',
    click_count as 'Clicks from Shares',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    action_shares as 'Shares Acted On',
--    action_count as 'Actions from Shares',
    unique_users as 'Action takers',
    FORMAT(unique_users / share_count,2) AS 'Action takers /share',
    subscribed_users as 'NTL',
    FORMAT(subscribed_users / share_count,2) AS 'NTL /share'
FROM  (
    SELECT
        COUNT(*) AS share_count,
        COUNT(DISTINCT a.user_id) AS share_users
    FROM core_transactionalmailingsent tms
        JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
        JOIN core_action a ON a.id = tms.action_id
    WHERE tms.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
) taf_sent
JOIN (
    SELECT
        count(distinct tmc.id) as clicked_shares,
        count(*) as click_count
    FROM core_transactionalmailingclick tmc
        JOIN core_transactionalmailingsent tms ON tms.id = tmc.transactional_mailing_sent_id
        JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
    WHERE tmc.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
) taf_clicks
JOIN (
    SELECT
        COUNT(DISTINCT action_share_id) as action_shares,
        COUNT(action_share_id) as action_count,
        COUNT(DISTINCT action_taker_id) as unique_users,
        COUNT(DISTINCT CASE WHEN subscribed_user = 1 THEN action_taker_id ELSE null END) AS subscribed_users
    FROM
    (
         select -- this select covers the cases where AK can't track the share and we only know it's TAF or mailto through the action source
            core_action.id as action_share_id,
            core_action.user_id as action_taker_id,
            core_action.subscribed_user as subscribed_user
        from core_action
        where core_action.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        and (core_action.source like 'taf%' or core_action.source like 'mailto%')
        UNION -- dedupe rows because TAF actions could show up in core_transactionalmailingaction and core_action.source='TAF'
        SELECT
            new.id AS action_share_id,
            new.user_id AS action_taker_id,
            new.subscribed_user AS subscribed_user
        FROM core_transactionalmailingaction tma
            JOIN core_transactionalmailingsent tms ON tms.id = tma.transactional_mailing_sent_id
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action original ON original.id = tms.action_id
            JOIN core_action new ON new.id = tma.action_id AND new.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    ) z
) taf_actions;

Share Stats By User for Date Range

Description: Breakdown of sharing for the top 20 users for a specified date range
Short name: shares_user_dates
User input: 1. start_date YYYY-MM-DD
2. end_date YYYY-MM-DD
Used in: Share Stats for Date Range
SQL:  
select u.email as 'User Email',
  SUM(num_shares) as 'Shares',
  SUM(action_count) as 'Action takers',
  SUM(new_users) as 'New Users',
  SUM(fb_shares) as 'Facebook Shares',
  SUM(fb_actions) as 'Facebook Actions',
  SUM(tw_shares) as 'Twitter Shares',
  SUM(tw_actions) as 'Twitter Actions',
  SUM(taf_shares) as 'TAF Shares',
  SUM(taf_actions) as 'TAF Actions'
FROM (
  SELECT
    share_link.user_id,
    count(*) as num_shares,
    sum( ( select count(*)
      from share_action
      join core_action on share_action.action_id = core_action.id
      where share_id = share_link.id )
    ) as action_count,
    sum( ( select count(*)
      from share_action
      join core_action on share_action.action_id = core_action.id
      where share_id = share_link.id and created_user )
    ) as new_users,
    sum( if( share_link.type = 'fb', 1, 0 ) ) as fb_shares,
    sum( if( share_link.type = 'fb', ( select count(*) from share_action join core_action on share_action.action_id = core_action.id where share_id = share_link.id ), 0 ) ) as fb_actions,
    sum( if( share_link.type = 'tw', 1, 0 ) ) as tw_shares,
    sum( if( share_link.type = 'tw', ( select count(*) from share_action join core_action on share_action.action_id = core_action.id where share_id = share_link.id ), 0 ) ) as tw_actions,
      0 as taf_shares,
      0 as taf_actions
  from share_link
  where share_link.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
  group by share_link.user_id
  UNION ALL
  SELECT
    user_id,
    COUNT(DISTINCT taf_tracked) as num_shares,
    COUNT(*) as action_count,
    SUM(created_user) as new_users,
      0 as fb_shares,
      0 as fb_actions,
      0 as tw_shares,
      0 as tw_actions,
      COUNT(DISTINCT taf_tracked) as taf_shares,
      count(*) as taf_actions
  FROM (
    SELECT
      MAX(taf_tracked) AS taf_tracked,
      MAX(user_id) AS user_id,
      action_share_id,
      action_taker_id,
      MAX(created_user) AS created_user,
      MAX(subscribed_user) AS subscribed_user
    FROM (
      select
        0 as taf_tracked,
        core_action.referring_user_id as user_id,
              core_action.id as action_share_id,
              core_action.user_id as action_taker_id,
              core_action.created_user as created_user,
              core_action.subscribed_user as subscribed_user
      from core_action
      where core_action.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        and (core_action.source like 'taf%' or core_action.source like 'mailto%')
      UNION
          SELECT
            tms.id AS taf_tracked,
            original.user_id AS user_id,
              new.id AS action_share_id,
              new.user_id AS action_taker_id,
              new.created_user AS created_user,
              new.subscribed_user AS subscribed_user
          FROM core_transactionalmailingaction tma
              JOIN core_transactionalmailingsent tms ON tms.id = tma.transactional_mailing_sent_id
              JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
              JOIN core_action original ON original.id = tms.action_id
              JOIN core_action new ON new.id = tma.action_id AND new.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    ) z
    GROUP BY action_share_id
  ) taf
) sharers
JOIN core_user u ON u.id = sharers.user_id
GROUP BY u.id
order by 3 desc, 2 desc
limit 20;

Share Totals for Date Range

Description: Totals for sharing stats for a specified date range
Short name: shares_total_dates
User input: 1. start_date YYYY-MM-DD
2. end_date YYYY-MM-DD
Used in: Share Stats for Date Range
SQL:  
select 'Totals' as '',
    share_users as 'Sharers',
    share_count as 'Shares',
--    clicked_shares as 'Shares Clicked',
    click_count as 'Clicks',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    action_shares as 'Shares Acted On',
--    action_count as 'Actions from Shares',
    unique_users as 'Action takers',
    FORMAT(unique_users / share_count,2) AS 'Action takers /share',
    subscribed_users as 'NTL',
    FORMAT(subscribed_users / share_count,2) AS 'NTL /share'
from (
    SELECT
        COUNT(DISTINCT sharer_id) as share_users,
        COUNT(sharer_id) as share_count
    FROM
    (
        select
            share_link.user_id as sharer_id
        from share_link
        where share_link.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        UNION ALL
        SELECT
            a.user_id AS sharer_id
        FROM core_transactionalmailingsent tms
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action a ON a.id = tms.action_id
        WHERE tms.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    ) as a
) shares
JOIN (
    SELECT
        COUNT(DISTINCT click_id) as clicked_shares,
        COUNT(click_id) as click_count
    FROM
    (
        select
            share_link.id as click_id
        from share_click
            join share_link on ( share_click.share_id = share_link.id )
        where share_click.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        UNION ALL
        SELECT
            tmc.id as click_id
        FROM core_transactionalmailingclick tmc
            JOIN core_transactionalmailingsent tms ON tms.id = tmc.transactional_mailing_sent_id
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action a ON a.id = tms.action_id
        WHERE tmc.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    ) b
) clicks
JOIN (
    SELECT
        count(distinct action_share_id) as action_shares,
        count(action_share_id) as action_count,
        COUNT(DISTINCT action_taker_id) as unique_users,
        COUNT(DISTINCT CASE WHEN subscribed_user = 1 THEN action_taker_id ELSE null END) AS subscribed_users
    FROM
    (
        select
            share_link.id as action_share_id,
            core_action.user_id as action_taker_id,
            core_action.subscribed_user as subscribed_user
        from core_action
            join share_action on ( core_action.id = share_action.action_id )
            join share_link on ( share_action.share_id = share_link.id )
        where share_action.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        UNION ALL
        select -- this select covers the cases where AK can't track the share and we only know it's TAF or mailto through the action source
            core_action.id as action_share_id,
            core_action.user_id as action_taker_id,
            core_action.subscribed_user as subscribed_user
        from core_action
        where core_action.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
        and (core_action.source like 'taf%' or core_action.source like 'mailto%')
        UNION -- dedupe rows because TAF actions could show up in core_transactionalmailingaction and core_action.source='TAF'
        SELECT
            new.id AS action_share_id,
            new.user_id AS action_taker_id,
            new.subscribed_user AS subscribed_user
        FROM core_transactionalmailingaction tma
            JOIN core_transactionalmailingsent tms ON tms.id = tma.transactional_mailing_sent_id
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action original ON original.id = tms.action_id
            JOIN core_action new ON new.id = tma.action_id AND new.created_at between {{ 1. start_date YYYY-MM-DD }} and {{ 2. end_date YYYY-MM-DD }}
    ) c
) actions;

Share Stats By Date for Page

Description: Breakdown of sharing by date for a page
Short name: shares_date_page
User input: page_id
Used in: Share Stats for Page
SQL:  
select traffic_dates.stat_date as 'Date',
    share_users as 'Sharers',
    share_count as 'Shares',
--    clicked_shares as 'Shares Clicked',
    click_count as 'Clicks',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    action_shares as 'Shares Acted On',
--    action_count as 'Actions from Shares',
    unique_users as 'Action takers',
    FORMAT(unique_users / share_count,2) AS 'Action takers /share',
    subscribed_users as 'NTL',
    FORMAT(subscribed_users / share_count,2) AS 'NTL /share'
from (
    select
        date(core_click.created_at) as stat_date
    from core_click
        join core_clickurl on ( core_click.clickurl_id = core_clickurl.id )
    where core_clickurl.page_id in ( { page_id } )
    group by stat_date
    order by stat_date desc
) as traffic_dates
left join (
    SELECT
        stat_date,
        count(*) as share_count,
        count(distinct sharer_id) as share_users
    FROM (
        select
            date(share_link.created_at) as stat_date,
            share_link.user_id as sharer_id
        from share_link
        where share_link.page_id in ( { page_id } )
        UNION ALL
        SELECT
            DATE(tms.created_at) AS stat_date,
            a.user_id AS sharer_id
        FROM core_transactionalmailingsent tms
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action a ON a.id = tms.action_id
        WHERE a.page_id IN ( { page_id } )
    ) a
    group by stat_date
) as shares on shares.stat_date = traffic_dates.stat_date
left join(
    select
        stat_date,
        count(distinct click_id) as clicked_shares,
        count(*) as click_count
    from
    (
        select
            date(share_click.created_at) as stat_date,
            share_link.id as click_id
        from share_click
            join share_link on ( share_click.share_id = share_link.id )
        where share_click.page_id in ( { page_id } )
        UNION ALL
        SELECT
            date(tmc.created_at) as stat_date,
            tmc.id as click_id
        FROM core_transactionalmailingclick tmc
            JOIN core_transactionalmailingsent tms ON tms.id = tmc.transactional_mailing_sent_id
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action a ON a.id = tms.action_id
        WHERE a.page_id IN ( { page_id } )
    ) b
    group by stat_date
) as clicks on clicks.stat_date = traffic_dates.stat_date
left join (
    select
        stat_date,
        count(distinct action_share_id) as action_shares,
        count(action_share_id) as action_count,
        count(distinct action_taker_id) as unique_users,
        COUNT(DISTINCT CASE WHEN subscribed_user = 1 THEN action_taker_id ELSE null END) AS subscribed_users
    from
    (
        select
            date(share_action.created_at) as stat_date,
            share_link.id as action_share_id,
            core_action.user_id as action_taker_id,
            core_action.subscribed_user as subscribed_user
        from core_action
            join share_action on ( core_action.id = share_action.action_id )
            join share_link on ( share_action.share_id = share_link.id )
        where core_action.page_id in ( { page_id } )
        UNION ALL
        select -- this select covers the cases where AK can't track the share and we only know it's TAF or mailto through the action source
            DATE(core_action.created_at) as stat_date,
            core_action.id as action_share_id,
            core_action.user_id as action_taker_id,
            core_action.subscribed_user as subscribed_user
        from core_action
        where core_action.page_id in ( { page_id } )
        and (core_action.source like 'taf%' or core_action.source like 'mailto%')
        UNION  -- dedupe rows because TAF actions could show up in core_transactionalmailingaction and core_action.source='TAF'
        SELECT
            DATE(new.created_at) as stat_date,
            new.id AS action_share_id,
            new.user_id AS action_taker_id,
            new.subscribed_user AS subscribed_user
        FROM core_transactionalmailingaction tma
            JOIN core_transactionalmailingsent tms ON tms.id = tma.transactional_mailing_sent_id
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action original ON original.id = tms.action_id AND original.page_id IN ( { page_id } )
            JOIN core_action new ON new.id = tma.action_id
    ) c
    group by stat_date
) as actions on actions.stat_date = traffic_dates.stat_date
ORDER BY traffic_dates.stat_date;

Share Stats By Generation for Page

Description: Breakdown of sharing by generation for a page
Short name: shares_gen_page
User input: page_id
Used in: Share Stats for Page
SQL:  
select shares.share_gen as 'Share Generation',
    share_users as 'Sharers',
    share_count as 'Shares',
--    clicks.source_shares as 'Shares Clicked',
    click_count as 'Clicks',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    actions.source_shares as 'Shares Acted On',
--    action_count as 'Actions from Shares',
    unique_users as 'Action takers',
    FORMAT(unique_users / share_count,2) AS 'Action takers /share',
    subscribed_users as 'NTL',
    FORMAT(subscribed_users / share_count,2) AS 'NTL /share'
from (
    select
        coalesce( share_link.generation, 0 ) as share_gen,
        count(*) as share_count,
        count(distinct share_link.user_id) as share_users
    from share_link
    where share_link.page_id in ( { page_id } )
    group by share_link.generation
) as shares left join (
    select
        coalesce( share_link.generation, 0 ) as share_gen,
        count(*) as click_count,
        count(distinct share_link.id) as source_shares
    from share_click
        left join share_link on ( share_click.share_id = share_link.id )
    where share_click.page_id in ( { page_id } )
    group by share_link.generation
) as clicks on shares.share_gen = clicks.share_gen left join (
    select
        coalesce( share_link.generation, 0 ) as share_gen,
        count(distinct share_link.id) as source_shares,
        count(*) as action_count,
        count(distinct core_action.user_id) as unique_users,
        sum(subscribed_user) as subscribed_users
    from core_action
        join share_action on ( core_action.id = share_action.action_id )
        join share_link on ( share_action.share_id = share_link.id )
    where core_action.page_id in ( {page_id} )
    group by share_link.generation
) as actions on shares.share_gen = actions.share_gen

Share Stats By Type for Page

Description: Breakdown of sharing by distribution type for a page
Short name: shares_type_page
User input: page_id
Used in: Share Stats for Page
SQL:  
select share_type.name as 'Type',
    share_users as 'Sharers',
    share_count as 'Shares',
--    clicked_shares as 'Shares Clicked',
    click_count as 'Clicks',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    action_shares as 'Shares Acted On',
--    action_count as 'Actions from Shares',
    unique_users as 'Action takers',
    FORMAT(unique_users / share_count,2) AS 'Action takers /share',
    subscribed_users as 'NTL',
    FORMAT(subscribed_users / share_count,2) AS 'NTL /share'
from
    share_type left join
(
    select
        share_link.type as share_type,
        count(*) as share_count,
        count(distinct share_link.user_id) as share_users
    from share_link
    where share_link.page_id in ( { page_id } )
    group by share_link.type
) as shares on shares.share_type = share_type.type
left join(
    select
        share_link.type as share_type,
        count(distinct share_link.id) as clicked_shares,
        count(*) as click_count
    from share_click
        join share_link on ( share_click.share_id = share_link.id )
    where share_click.page_id in ( { page_id } )
    group by share_link.type
) as clicks on clicks.share_type = share_type.type
left join (
    select
        share_link.type as share_type,
        count(distinct share_link.id) as action_shares,
        count(*) as action_count,
        count(distinct core_action.user_id) as unique_users,
        sum(subscribed_user) as subscribed_users
    from core_action
        join share_action on ( core_action.id = share_action.action_id )
        join share_link on ( share_action.share_id = share_link.id )
    where core_action.page_id in ( {page_id} )
    group by share_link.type
) as actions on actions.share_type = share_type.type
where share_type.type <> 'em' -- skip the TAF email type in the share tables
UNION
SELECT 'TAF' as 'Type',
    share_users as 'Sharers',
    share_count as 'Shares',
--    clicked_shares as 'Shares Clicked',
    click_count as 'Clicks from Shares',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    action_shares as 'Shares Acted On',
--    SUM(action_count) as 'Actions from Shares',
    SUM(unique_users) as 'Action takers',
    FORMAT(SUM(unique_users) / share_count,2) AS 'Action takers /share',
    SUM(subscribed_users) as 'NTL',
    FORMAT(SUM(subscribed_users) / share_count,2) AS 'NTL /share'
FROM  (
    SELECT
        COUNT(*) AS share_count,
        COUNT(DISTINCT a.user_id) AS share_users
    FROM core_transactionalmailingsent tms
        JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
        JOIN core_action a ON a.id = tms.action_id
    WHERE a.page_id IN ( {page_id} )
) taf_sent
JOIN (
    SELECT
        count(distinct tmc.id) as clicked_shares,
        count(*) as click_count
    FROM core_transactionalmailingclick tmc
        JOIN core_transactionalmailingsent tms ON tms.id = tmc.transactional_mailing_sent_id
        JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
        JOIN core_action a ON a.id = tms.action_id
    WHERE a.page_id IN ( {page_id} )
) taf_clicks
JOIN (
    SELECT
        COUNT(DISTINCT action_share_id) as action_shares,
        COUNT(action_share_id) as action_count,
        COUNT(DISTINCT action_taker_id) as unique_users,
        COUNT(DISTINCT CASE WHEN subscribed_user = 1 THEN action_taker_id ELSE null END) AS subscribed_users
    FROM
    (
         select -- this select covers the cases where AK can't track the share and we only know it's TAF or mailto through the action source
            core_action.id as action_share_id,
            core_action.user_id as action_taker_id,
            core_action.subscribed_user as subscribed_user
        from core_action
        where core_action.page_id in ( {page_id} )
        and (core_action.source like 'taf%' or core_action.source like 'mailto%')
        UNION -- dedupe rows because TAF actions could show up in core_transactionalmailingaction and core_action.source='TAF'
        SELECT
            new.id AS action_share_id,
            new.user_id AS action_taker_id,
            new.subscribed_user AS subscribed_user
        FROM core_transactionalmailingaction tma
            JOIN core_transactionalmailingsent tms ON tms.id = tma.transactional_mailing_sent_id
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action original ON original.id = tms.action_id AND original.page_id IN ( {page_id} )
            JOIN core_action new ON new.id = tma.action_id
    ) z
) taf_actions;

Share Stats By User for Page

Description: Breakdown of sharing for the top 20 users for a page
Short name: shares_user_page
User input: page_id
Used in: Share Stats for Page
SQL:  
select u.email as 'User Email',
  SUM(num_shares) as 'Shares',
  SUM(action_count) as 'Action takers',
  SUM(new_users) as 'NTL',
  SUM(fb_shares) as 'Facebook Shares',
  SUM(fb_actions) as 'Facebook Actions',
  SUM(tw_shares) as 'Twitter Shares',
  SUM(tw_actions) as 'Twitter Actions',
  SUM(taf_shares) as 'TAF Shares',
  SUM(taf_actions) as 'TAF Actions'
FROM (
  SELECT
    share_link.user_id,
    count(*) as num_shares,
    sum( ( select count(*)
      from share_action
      join core_action on share_action.action_id = core_action.id
      where share_id = share_link.id )
    ) as action_count,
    sum( ( select count(*)
      from share_action
      join core_action on share_action.action_id = core_action.id
      where share_id = share_link.id and created_user )
    ) as new_users,
    sum( if( share_link.type = 'fb', 1, 0 ) ) as fb_shares,
    sum( if( share_link.type = 'fb', ( select count(*) from share_action join core_action on share_action.action_id = core_action.id where share_id = share_link.id ), 0 ) ) as fb_actions,
    sum( if( share_link.type = 'tw', 1, 0 ) ) as tw_shares,
    sum( if( share_link.type = 'tw', ( select count(*) from share_action join core_action on share_action.action_id = core_action.id where share_id = share_link.id ), 0 ) ) as tw_actions,
      0 as taf_shares,
      0 as taf_actions
  from share_link
  where share_link.page_id in ( { page_id } )
  group by share_link.user_id
  UNION ALL
  SELECT
    user_id,
    COUNT(DISTINCT taf_tracked) as num_shares,
    COUNT(*) as action_count,
    SUM(subscribed_user) as new_users,
      0 as fb_shares,
      0 as fb_actions,
      0 as tw_shares,
      0 as tw_actions,
      COUNT(DISTINCT taf_tracked) as taf_shares,
      count(*) as taf_actions
  FROM (
    SELECT
      MAX(taf_tracked) AS taf_tracked,
      MAX(user_id) AS user_id,
      action_share_id,
      action_taker_id,
      MAX(subscribed_user) AS subscribed_user
    FROM (
      select
        0 as taf_tracked,
        core_action.referring_user_id as user_id,
              core_action.id as action_share_id,
              core_action.user_id as action_taker_id,
              core_action.subscribed_user as subscribed_user
      from core_action
      where core_action.page_id in ( {page_id} )
        and (core_action.source like 'taf%' or core_action.source like 'mailto%')
      UNION
          SELECT
            tms.id AS taf_tracked,
            original.user_id AS user_id,
              new.id AS action_share_id,
              new.user_id AS action_taker_id,
              new.subscribed_user AS subscribed_user
          FROM core_transactionalmailingaction tma
              JOIN core_transactionalmailingsent tms ON tms.id = tma.transactional_mailing_sent_id
              JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
              JOIN core_action original ON original.id = tms.action_id AND original.page_id IN ( {page_id} )
              JOIN core_action new ON new.id = tma.action_id
    ) z
    GROUP BY action_share_id
  ) taf
) sharers
JOIN core_user u ON u.id = sharers.user_id
GROUP BY u.id
order by 3 desc, 2 desc
limit 20;

Share Totals for Page

Description: Totals for sharing stats for a page
Short name: shares_total_page
User input: page_id
Used in: Share Stats for Page
SQL:  
SET @sigs = (SELECT COUNT(DISTINCT user_id) FROM core_action WHERE page_id IN ( {page_id} ) );
select 'Totals' as '',
    @sigs as 'Signatures',
    share_users as 'Sharers',
    share_count as 'Shares',
    FORMAT(share_count / @sigs,2) as 'Shares /signature',
--    clicked_shares as 'sigs Clicked',
    click_count as 'Clicks',
    FORMAT(click_count/ share_count,2) AS 'Clicks /share',
--    action_shares as 'Shares Acted On',
--    action_count as 'Actions from Shares',
    unique_users as 'Action takers',
    FORMAT(unique_users / share_count,2) AS 'Action takers /share',
    subscribed_users as 'NTL',
    FORMAT(subscribed_users / share_count,2) AS 'NTL /share'
from (
    SELECT
        COUNT(DISTINCT sharer_id) as share_users,
        COUNT(sharer_id) as share_count
    FROM
    (
        select
            share_link.user_id as sharer_id
        from share_link
        where share_link.page_id in ( { page_id } )
        UNION ALL
        SELECT
            a.user_id AS sharer_id
        FROM core_transactionalmailingsent tms
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action a ON a.id = tms.action_id
        WHERE a.page_id IN ( { page_id } )
    ) as a
) shares
JOIN (
    SELECT
        COUNT(DISTINCT click_id) as clicked_shares,
        COUNT(click_id) as click_count
    FROM
    (
        select
            share_link.id as click_id
        from share_click
            join share_link on ( share_click.share_id = share_link.id )
        where share_click.page_id in ( { page_id } )
        UNION ALL
        SELECT
            tmc.id as click_id
        FROM core_transactionalmailingclick tmc
            JOIN core_transactionalmailingsent tms ON tms.id = tmc.transactional_mailing_sent_id
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action a ON a.id = tms.action_id
        WHERE a.page_id IN ( { page_id } )
    ) b
) clicks
JOIN (
    SELECT
        count(distinct action_share_id) as action_shares,
        count(action_share_id) as action_count,
        COUNT(DISTINCT action_taker_id) as unique_users,
        COUNT(DISTINCT CASE WHEN subscribed_user = 1 THEN action_taker_id ELSE null END) AS subscribed_users
    FROM
    (
        select
            share_link.id as action_share_id,
            core_action.user_id as action_taker_id,
            core_action.subscribed_user as subscribed_user
        from core_action
            join share_action on ( core_action.id = share_action.action_id )
            join share_link on ( share_action.share_id = share_link.id )
        where core_action.page_id in ( { page_id } )
        UNION ALL
        select -- this select covers the cases where AK can't track the share and we only know it's TAF or mailto through the action source
            core_action.id as action_share_id,
            core_action.user_id as action_taker_id,
            core_action.subscribed_user as subscribed_user
        from core_action
        where core_action.page_id in ( { page_id } )
        and (core_action.source like 'taf%' or core_action.source like 'mailto%')
        UNION -- dedupe rows because TAF actions could show up in core_transactionalmailingaction and core_action.source='TAF'
        SELECT
            new.id as action_share_id,
            new.user_id AS action_taker_id,
            new.subscribed_user AS subscribed_user
        FROM core_transactionalmailingaction tma
            JOIN core_transactionalmailingsent tms ON tms.id = tma.transactional_mailing_sent_id
            JOIN core_tafmailing t ON t.transactionalmailing_ptr_id = tms.transactional_mailing_id
            JOIN core_action original ON original.id = tms.action_id AND original.page_id IN ( { page_id } )
            JOIN core_action new ON new.id = tma.action_id
    ) c
) actions;

Sleep 7 seconds

Description: Waits 7 seconds (for UI testing).
Short name: 7_second_wait
User input: Cache buster
Used in: None
SQL:  
SELECT sleep(7), {cache_buster} AS cache_buster;

SQL: Per-send recurring mailing statistics

Description: Used internally by "Per-send recurring mailing statistics" report.
Short name: recurring_rates_by_send_sql
User input: number of days
recurring mailing schedule id
Used in: Per-send recurring mailing statistics
SQL:  
SELECT
  ifnull(id, 'TOTAL') as 'ID',
  if(id is null, '--', started_at) as 'Started at',
  progress as 'Sent',
  distinct_opens as 'Openers',
  concat(format(ifnull(distinct_opens/progress,0)*100,1),'%') as 'Open %',
  distinct_clicks as 'Clickers',
  concat(format(ifnull(distinct_clicks/progress,0)*100,1),'%') as 'Click %',
  distinct_action_takers as 'Action takers',
  concat(format(ifnull(distinct_action_takers/progress,0)*100,1),'%') as 'Act %',
  unsubs as 'Unsubbers',
  concat(format(ifnull(unsubs/progress,0)*100,1),'%') as 'Unsub %',
  bounces as 'Bouncers',
  concat(format(ifnull(bounces/progress,0)*100,1),'%') as 'Bounce %'
FROM
(
SELECT
  id,
  started_at,
  sum(progress) as progress,
  sum((SELECT count(distinct user_id) from core_open where mailing_id=m.id)) as distinct_opens,
  sum((SELECT count(distinct user_id) from core_click where mailing_id=m.id)) as distinct_clicks,
  sum((SELECT count(distinct user_id) from core_action join core_page p on p.id=page_id where p.type!='unsubscribe' and mailing_id=m.id)) as distinct_action_takers,
  sum((SELECT count(distinct user_id) from core_action join core_page p on p.id=page_id where p.type='unsubscribe' and mailing_id=m.id)) as unsubs,
  sum((SELECT count(distinct user_id) from core_bounce where mailing_id=m.id)) as bounces
FROM
  core_mailing m
WHERE
  started_at > now() - interval {{ days }} day
  and recurring_schedule_id = {{ recurringmailingschedule_id }}
  and m.status in ('completed','died')
GROUP by 1 with rollup
UNION
SELECT
  'DISTINCT' as 'ID',
  '--' as 'Started at',
  (SELECT count(distinct user_id) from core_usermailing join core_mailing m on mailing_id=m.id where started_at > now() - interval {{ days }} day and recurring_schedule_id = {{ recurringmailingschedule_id }} and m.status in ('completed','died')) as 'Sent',
  (SELECT count(distinct user_id) from core_open join core_mailing m on mailing_id=m.id where started_at > now() - interval {{ days }} day and recurring_schedule_id = {{ recurringmailingschedule_id }} and m.status in ('completed','died')) as 'Openers',
  (SELECT count(distinct user_id) from core_click join core_mailing m on mailing_id=m.id where  started_at > now() - interval {{ days }} day and recurring_schedule_id = {{ recurringmailingschedule_id }} and m.status in ('completed','died')) as 'Clickers',
  (SELECT count(distinct user_id) from core_action join core_mailing m on mailing_id=m.id where started_at > now() - interval {{ days }} day and recurring_schedule_id = {{ recurringmailingschedule_id }} and m.status in ('completed','died')) as 'Action takers',
  (SELECT count(distinct user_id) from core_action join core_page p on p.id=page_id and p.type!='unsubscribe' join core_mailing m on mailing_id=m.id where started_at > now() - interval {{ days }} day and recurring_schedule_id = {{ recurringmailingschedule_id }} and m.status in ('completed','died')) as 'Unsubs',
  (SELECT count(distinct user_id) from core_bounce join core_mailing m on mailing_id=m.id where started_at > now() - interval {{ days }} day and recurring_schedule_id = {{ recurringmailingschedule_id }} and m.status in ('completed','died')) as 'Bounces'
) as raw_stats

SQL: Recurring series statistics

Description: Used internally by "Recurring series statistics" report.
Short name: recurring_rates_sql
User input: number of days
recurring mailing schedule id
Used in: Recurring series statistics
SQL:  
SELECT
  series as 'Series',
  progress as 'Sent',
  opens as 'Openers',
  concat(format(ifnull(opens/progress,0)*100,1),'%') as 'Open %',
  clicks as 'Clickers',
  concat(format(ifnull(clicks/progress,0)*100,1),'%') as 'Click %',
  action_takers as 'Action takers',
  concat(format(ifnull(action_takers/progress,0)*100,1),'%') as 'Act %',
  unsubs as 'Unsubs',
  concat(format(ifnull(unsubs/progress,0)*100,1),'%') as 'Unsub %',
  bounces as 'Bounces',
  concat(format(ifnull(bounces/progress,0)*100,1),'%') as 'Bounce %'
FROM (
SELECT
  concat(rms.id, ' - ', rms.name) as series,
  sum(progress) as progress,
  sum((select count(distinct user_id) from core_open where mailing_id=m.id)) as opens,
  sum((select count(distinct user_id) from core_click where mailing_id=m.id)) as clicks,
  sum((select count(distinct user_id) from core_action join core_page p on p.id=page_id where p.type!='unsubscribe' and mailing_id=m.id)) as action_takers,
  sum((select count(distinct user_id) from core_action join core_page p on p.id=page_id where p.type='unsubscribe' and mailing_id=m.id)) as unsubs,
  sum((select count(distinct user_id) from core_bounce where mailing_id=m.id)) as bounces
FROM
  core_mailing m
  JOIN core_recurringmailingschedule rms on rms.id=m.recurring_schedule_id
WHERE
  ({{ recurringmailingschedule_id }} = 0 or rms.id = {{ recurringmailingschedule_id }}) and
  started_at > now() - interval {{ days }} day and
  m.status in ('completed','died')
GROUP by 1
) totals;

Steady Users (Actions on N or More Days)

Short name: steady_action_takers
Description: Who are the steady users who have taken actions on N or more days?
User input: None
Associated with:
SQL:  
SELECT user_id,
       email,
       count(*) AS action_day_count
FROM (
    SELECT DISTINCT user_id,
                    email,
                    date(core_action.created_at)
    FROM core_action
    JOIN core_page on core_action.page_id = core_page.id
    JOIN core_user on core_action.user_id = core_user.id
    WHERE core_page.real_actions = 1
    AND core_action.status = 'complete'
) AS action_dates
GROUP BY 1
HAVING action_day_count >= {{ minimum_action_days }}
ORDER BY action_day_count DESC

Subscribed Count History

Description: Subscription counts by list over a specified time frame.
Short name: subscribed_count_history
User input: List name
Start date
With total
Used in: Subscribed Users History Dashboard
SQL:  
SELECT sch.date as date,
   IFNULL(list.name, 'Total Subscribers'),
   subscribers
FROM core_subscriptioncounthistory sch
LEFT JOIN core_list list ON (sch.list_id=list.id)
WHERE sch.date > { start_date }
{% if list_name %}
 AND ( list.name = { list_name }
 {% if with_total %}
       OR list.id IS NULL # { with_total }
 {% endif %}
     )
{% endif %}

Super Actives

Description: List of users who meet one of the following criteria: took more than the specified number of actions in the last three months on pages of any type except import, unsubscribe, and recurring donation management; signed up as the host of an active event ever; or gave more than $250 in the last year, including donations, product orders, recurring donations but excluding any imported donations. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: super_actives
User input: Number of actions
Used in: None
SQL:  
SELECT ca.user_id
FROM core_action ca
JOIN core_page cp
  ON (ca.page_id=cp.id)
WHERE ca.status = 'complete'
  AND cp.type not IN ('Import', 'Unsubscribe', 'RecurringDonationCancel', 'RecurringDonationUpdate')
  AND ca.created_at > date_sub(now(), interval 3 month)
GROUP BY user_id
  HAVING COUNT(ca.id) >= {how_many_recent_actions}
UNION
  SELECT user_id
  FROM events_eventsignup
  WHERE role = 'host' AND status = 'active'
UNION
  SELECT user_id
  FROM core_order co
  JOIN core_transaction ct ON (co.id=ct.order_id)
  WHERE co.status = 'completed' AND ct.success = 1
    AND ct.created_at > date_sub(now(), interval 1 year)
  GROUP BY user_id
    HAVING sum(ct.amount) >= 250

Survey Respondents by Question

Description: Users answering a specified survey question. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: survey_respondents
User input: Name of survey question
Used in: None
SQL:  
SELECT user_id
FROM core_action ca
JOIN core_actionfield caf ON (ca.id=caf.parent_id)
WHERE name = {survey_question} AND status = 'complete'

Tell-a-Friend Mailing Perfomance

Description: This report shows performance for the tell-a-friend mailings associated with a page. The count of clicks is unique per-send. This only includes sends by ActionKit, not sends in the user's mail client via mailto: or copy-and-paste.
Short name: taf_mailing_perfomance
User input: Page ID
Used in: None
SQL:  
SELECT subject,
       sent,
       clicks,
       actions,
       NTL as new_to_list
FROM (
        SELECT tm.subject,
           tm.type,
           COUNT(DISTINCT(tms.id)) as sent,
           COUNT(DISTINCT tmo.transactional_mailing_sent_id) as opens,
           COUNT(DISTINCT tmc.transactional_mailing_sent_id) as clicks,
           COUNT(DISTINCT tma.id) as actions,
           COUNT(DISTINCT(tma_ntl.id)) as NTL
        FROM core_transactionalmailing tm
        JOIN core_transactionalmailingsent tms
          ON (tm.id = tms.transactional_mailing_id)
        LEFT JOIN core_transactionalmailingopen tmo
          ON (tms.id = tmo.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingclick tmc
          ON (tms.id = tmc.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingaction tma
          ON (tms.id = tma.transactional_mailing_sent_id)
        LEFT JOIN core_action tma_ntl
          ON (tma_ntl.id = tma.action_id AND tma_ntl.subscribed_user = 1)
        WHERE tm.page_id = {{ page_id }} AND tm.type = 'taf'
 GROUP BY 1, 2
) stats;

Top Superconnector Referrers

Short name: member_referrals_over_25
Description: Who are the "superconnectors" who have referred the most new users to the list?
User input: None
Associated with:
SQL:  
select core_action.referring_user_id as "User_ID",
    concat_ws( ' ', if( referring_user.first_name != '', referring_user.first_name, null ), if( referring_user.middle_name != '', referring_user.middle_name, null ), if( referring_user.last_name != '', referring_user.last_name, null ) ) as "Full_Name",
    referring_user.email as "Email",
    count(distinct core_action.id) as "Referred_Members"
from core_action
    left join core_user as referring_user on referring_user.id = core_action.referring_user_id
where core_action.created_user
    and referring_user.subscription_status = 'subscribed'
group by 1, 2, 3
having referred_members >= 25
order by 4 DESC

Total Dollars Paid from Recurring Donations

Description: Total dollars ever from recurring donations.
Short name: recurring_donations_dollars_paid
User input: None
Used in: None
SQL:  
SELECT COALESCE(SUM(t.amount), 0)
FROM core_order o
JOIN core_orderrecurring recur ON (o.id=recur.order_id)
JOIN  core_transaction t ON (o.id=t.order_id)
WHERE o.status = 'completed'
  AND t.type = 'sale'
  AND t.success = 1
  AND t.created_at between {last_run} AND {now}

Total Raised in the Last Week

Description: Donations completed in the past week. Includes donations, product orders, and initial payments toward recurring commitments, from donation or import pages.
Short name: donations_week
User input: None
Used in: List Stats
SQL:  
SELECT COALESCE(SUM(total),0)
FROM core_order
WHERE status = 'completed'
  AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) <= created_at

Total Subscribed and Disengaged Users

Description: Total number of users who are currently subscribed, plus those who were unsubscribed due to inactivity via the re-engagement list.
Short name: users_subscribed_and_disengaged
User input: None
Used in: None
SQL:  
select count(distinct core_user.id)
from core_user
where core_user.subscription_status = 'subscribed'
    or (
        core_user.subscription_status = 'unsubscribed'
        and ( select core_subscriptionchangetype.name from core_subscriptionhistory join core_subscriptionchangetype on ( change_id = core_subscriptionchangetype.id ) where user_id = core_user.id order by core_subscriptionhistory.id desc limit 1 ) = 'unsubscribe_reengagement'
    )

Transactional Mailing Performance

Description: This report shows performance for the all transactional mailings associated with a page. The count of opens and clicks are unique per-send so multiple opens or clicks by a recipient don't count here.
Short name: transactional_mailing_performance
User input: Page ID
Used in: None
SQL:  
SELECT subject,
       type,
       sent,
       opens,
       opens / sent * 100 as open_pct,
       clicks,
       clicks / opens * 100 as clicks_per_open,
       actions,
       actions / opens * 100 as actions_per_open,
       NTL as new_to_list
FROM (
        SELECT tm.subject,
           tm.type,
           COUNT(DISTINCT tms.id) as sent,
           COUNT(DISTINCT tmo.transactional_mailing_sent_id) as opens,
           COUNT(DISTINCT tmc.transactional_mailing_sent_id) as clicks,
           COUNT(DISTINCT tma.id) as actions,
           COUNT(DISTINCT(tma_ntl.id)) as NTL
        FROM core_transactionalmailing tm
        JOIN core_transactionalmailingsent tms
          ON (tm.id = tms.transactional_mailing_id)
        LEFT JOIN core_transactionalmailingopen tmo
          ON (tms.id = tmo.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingclick tmc
          ON (tms.id = tmc.transactional_mailing_sent_id)
        LEFT JOIN core_transactionalmailingaction tma
          ON (tms.id = tma.transactional_mailing_sent_id)
        LEFT JOIN core_action tma_ntl
          ON (tma_ntl.id = tma.action_id AND tma_ntl.subscribed_user = 1)

        WHERE tm.page_id = {{ page_id }} GROUP BY 1, 2
) stats;

Unsubscribe Reasons

Description: This report shows the user_id and the reason entered on your unsubscribe form (if you have a field for users to enter the reason).
Short name: unsubscribe_reasons
User input: None
Used in: None
SQL:  
SELECT ca.user_id, af.value
FROM core_actionfield af
JOIN core_action ca ON (ca.id=af.parent_id)
JOIN core_page cp ON (cp.id=ca.page_id)
WHERE af.name='survey'
  AND length(af.value) > 0
  AND cp.type='unsubscribe'
  AND af.value not like '%@%.%'

US Users

Description: Number of mailable users in the United States.
Short name: users_US
User input: None
Used in: list stats mini
List Stats
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscription
  ON (core_user.id = core_subscription.user_id)
WHERE country = 'United States';

US Users, Special List

Description: Number of US users currently subscribed to list 2.
Short name: users_US_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscription
  ON (core_user.id = core_subscription.user_id)
WHERE list_id = 2
  AND country = 'United States';

US Users, Standard List

Description: Number of US users currently subscribed to list 1.
Short name: users_US_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscription
  ON (core_user.id = core_subscription.user_id)
WHERE list_id = 1
  AND country = 'United States';

User phones, by zip radius

Description: Displays user name, city, phone type and phone number for all users a radius (in miles) of a given location. The location can be specified by zip code, city and state, or city and country (for location outside of the United States).
Short name: phone_by_zip_radius
User input: Location,
radius
Used in: None
SQL:  
SELECT first_name, last_name, city, type, phone
FROM core_user
JOIN core_phone
  ON (core_user.id = core_phone.user_id)
WHERE core_user.id
  IN {{ users_near_location }}
GROUP BY core_user.id;

Users by Source

Description: List of users for a specified source. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: user_source
User input: Source
Used in: None
SQL:  
SELECT id
FROM core_user
WHERE source = {source}

Mailing A/B Test Variation Recipients

Description: Users who received a specific variation of an A/B mailing test. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: mailing_test_recipients
User input: maling_id, variation_letter
Used in: None
SQL:  
SELECT vu.user_id
FROM core_mailingvariation_users vu
JOIN core_mailingvariation v ON (v.id = vu.mailingvariation_id)
WHERE v.mailing_id = {{ mailing_id }}
AND v.letter = {{ variation_letter }}

Users by UserField Value

Description: List of users whose specified userfield contains the value you specify. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: userfield_value
User input: userfield
userfield value
Used in: None
SQL:  
SELECT cu.id
FROM core_user cu
JOIN core_userfield cuf ON (cu.id=cuf.parent_id)
WHERE name = {user_field} AND value = {value_to_target}

Users taking action

Description: Number of subscribed users who have completed at least one action in the last week where the action source is not "import".
Short name: actions_users
User input: None
Used in: list stats mini
List Stats
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE date_sub(current_timestamp(), interval 1 week) <= core_action.created_at
  AND status = 'complete' AND core_action.source <> 'import';

Users taking action (main)

Description: Number of users subscribed to list 1 who have completed at least one action in the last week regardless of action source.
Short name: actions_users_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE date_sub(current_timestamp(), interval 1 week) <= core_action.created_at
  AND status = 'complete'
  AND list_id = 1;

Users taking action (weekly)

Description: Number of users subscribed to list 2 who have completed at least one action in the last week regardless of action source.
Short name: actions_users_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE date_sub(current_timestamp(), interval 1 week) <= core_action.created_at
  AND status = 'complete'
  AND list_id = 2;

Users unsubscribed

Description: Number of currently unsubscribed users who removed themselves or were removed by an admin since last run.
Short name: users_unsub
User input: None
Used in: list stats mini
List Stats
Users Unsubscribed
SQL:  
SELECT COUNT(DISTINCT core_user.id)
FROM core_user
JOIN core_subscriptionhistory ON (core_user.id = core_subscriptionhistory.user_id)
WHERE change_id IN (4,5,6,8)
{% if partial_run %}
AND core_subscriptionhistory.created_at BETWEEN {last_run} AND {now}
{% endif %}
AND core_user.subscription_status != 'subscribed';

Users unsubscribed main list

Description: Number of users, regardless of current subscription status, who removed themselves or were removed by an admin from list 1 in the last week.
Short name: users_unsub_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscriptionhistory
  ON (core_user.id = core_subscriptionhistory.user_id)
WHERE list_id = 1
  AND change_id IN (4,5)
  AND date_sub(current_date(), interval 1 week) <= core_subscriptionhistory.created_at;

Users unsubscribed spec list

Description: Number of users, regardless of current subscription status, who removed themselves or were removed by an admin from list 2 in the last week.
Short name: users_unsub_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_user
JOIN core_subscriptionhistory
  ON (core_user.id = core_subscriptionhistory.user_id)
WHERE list_id = 2
  AND change_id IN (4,5)
  AND date_sub(current_date(), interval 1 week) <= core_subscriptionhistory.created_at;

Users w/ 1 action in last year

Description: Number of users who completed at least one action in the last year.
Short name: users_1act_yr
User input: None
Used in: list stats mini
List Stats
SQL:  
SELECT COUNT(distinct user_id)
FROM core_user
JOIN core_action
  ON (core_user.id = core_action.user_id)
WHERE date_sub(current_date(), interval 1 year) <= core_action.created_at
  AND status = 'complete';

Users w/ 1 Action last 90 days

Description: Number of subscribers who completed at least one action with a source other than "import" in the last 3 months.
Short name: users_1act_90d
User input: None
Used in: list stats mini
List Stats
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE date_sub(current_timestamp(), interval 90 day) <= core_action.created_at
  AND core_action.source <> 'import' AND core_action.status = 'complete';

Users w/ 1 Action last 90 days (main)

Description: Number of users subscribed to list 1 who completed at least one action, regardless of action source, in the last 3 months.
Short name: users_1act_90d_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE date_sub(current_timestamp(), interval 90 day) <= core_action.created_at
  AND core_action.status = 'complete'
  AND list_id = 1;

Users w/ 1 Action last 90 days (weekly)

Description: Number of users subscribed to list 2 who completed at least one action, with any action source, in the last 3 months.
Short name: users_1act_90d_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE date_sub(current_timestamp(), interval 90 day) <= core_action.created_at
  AND core_action.status = 'complete'
  AND list_id = 2;

Users w/ 1 Action last year

Description: Number of subscribers who completed at least one action in the last year where the action source is not "import".
Short name: users_action_year
User input: None
Used in: List Stats
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user
  ON (core_subscription.user_id = core_user.id)
JOIN core_action
  ON (core_user.id = core_action.user_id)
WHERE date_sub(current_timestamp(), interval 1 year) <= core_action.created_at
  AND status = 'complete'
  AND core_action.source <> 'import';

Users w/ 1 Action last year (main list)

Description: Number of users subscribed to list 1 who completed at least one action, with any action source, in the last year.
Short name: users_1act_yr_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user
  ON (core_subscription.user_id = core_user.id)
JOIN core_action
  ON (core_user.id = core_action.user_id)
WHERE date_sub(current_timestamp(), interval 1 year) <= core_action.created_at
  AND status = 'complete'
  AND list_id = 1;

Users w/ 1 Action last year (special list)

Description: Number of users subscribed to list 2 who completed at least one action, with any action source, in the last year.
Short name: users_1act_yr_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user
  ON (core_subscription.user_id = core_user.id)
JOIN core_action
  ON (core_user.id = core_action.user_id)
WHERE date_sub(current_timestamp(), interval 1 year) <= core_action.created_at
  AND status = 'complete'
  AND list_id = 2;

Users w/ 3 Actions in last 90 days

Description: Number of subscribers who completed at least three actions, where the action source is not "import", in the last 3 months.
Short name: users_3act_90d
User input: None
Used in: list stats mini
List Stats
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE (
  SELECT COUNT(*)
  FROM core_action AS ca
  WHERE ca.user_id = core_user.id
    AND ca.source <> 'import'
    AND ca.status = 'complete'
    AND date_sub(current_timestamp(), interval 90 day) <= created_at
  ) >= 3;

Users w/ 3 Actions in last 90 days (main)

Description: Users currently subscribed to list 1 who completed at least three actions in the last 3 months.
Short name: users_3act_90d_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE (
  SELECT COUNT(*)
  FROM core_action AS ca
  WHERE ca.user_id = core_user.id
    AND ca.status = 'complete'
    AND date_sub(current_timestamp(), interval 90 day) <= created_at
  ) >= 3
  AND list_id = 1;

Users w/ 3 Actions in last 90 days (weekly)

Description: Users currently subscribed to list 2 who completed at least three actions in the last 3 months.
Short name: users_3act_90d_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE (
  SELECT COUNT(*)
  FROM core_action AS ca
  WHERE ca.user_id = core_user.id
    AND ca.status = 'complete'
    AND date_sub(current_timestamp(), interval 90 day) <= created_at
  ) >= 3
  AND list_id = 2;

Users w/ 3 Actions in last year

Description: Number of subscribers who completed at least three actions, where the action source is not "import", in the last year.
Short name: users_3act_yr
User input: None
Used in: list stats mini
List Stats
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE (
  SELECT COUNT(*)
  FROM core_action AS ca
  WHERE ca.user_id = core_user.id
    AND ca.source <> 'import'
    AND ca.status = 'complete'
    AND date_sub(current_timestamp(), interval 1 year) <= created_at
  ) >= 3;

Users w/ 3 Actions last year (main list)

Description: Number of users subscribed to list one who completed at least three actions, with any source, in the last year.
Short name: users_3act_yr_main
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE (
  SELECT COUNT(*)
  FROM core_action AS ca
  WHERE ca.user_id = core_user.id
    AND ca.status = 'complete'
    AND date_sub(current_timestamp(), interval 1 year) <= created_at
  ) >= 3
  AND status = 'complete'
  AND list_id = 1;

Users w/ 3 Actions last year (spec list)

Description: Number of users subscribed to list two who completed at least three actions, with any source, in the last year.
Short name: users_3act_yr_weekly
User input: None
Used in: list stats mini
SQL:  
SELECT COUNT(distinct core_user.id)
FROM core_subscription
JOIN core_user ON (core_subscription.user_id = core_user.id)
JOIN core_action ON (core_user.id = core_action.user_id)
WHERE (
  SELECT COUNT(*)
  FROM core_action AS ca
  WHERE ca.user_id = core_user.id
    AND ca.status = 'complete'
    AND date_sub(current_timestamp(), interval 1 year) <= created_at
  ) >= 3
  AND status = 'complete'
  AND list_id = 2;

Users who Clicked Mailing

Description: IDs of users who clicked a link in the designated mailing.
Short name: clicks_from_mailing
User input: mailing_id
Used in: None
SQL:  
SELECT user_id
FROM core_click
WHERE mailing_id = {mailing_id}

Users who Opened Mailing

Description: IDs of users who opened the designated mailing.
Short name: opens_from_mailing
User input: mailing_id
Used in: None
SQL:  
SELECT user_id
FROM core_open
WHERE mailing_id = {mailing_id}

Visualize Recent Actions by State

Description: Count of users who completed an action in the last month by state.
Short name: visualize_actions_by_state
User input: None
Used in: Visualizing Your Base
SQL:  
SELECT state, COUNT(*) 'users'
FROM core_user cu
JOIN core_action ca
  ON (cu.id=ca.user_id)
WHERE cu.state IN ('AL','AK','AZ','AR','CA','CO','CT','DE','DC','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY')
  AND ca.status = 'complete'
  AND ca.created_at > date_sub(now(), interval 1 month)
GROUP BY 1

Visualize Users by City

Description: Count of US subscribers by city, limited to the top 50 cities by count.
Short name: visualize_users_by_city
User input: None
Used in: Visualizing Your Base
SQL:  
SELECT city, COUNT(distinct cu.id) 'users'
FROM core_user cu
JOIN core_subscription cs
  ON (cu.id=cs.user_id)
WHERE (cu.country = 'United States'
  OR (cu.country = 'unknown'
  AND length(cu.zip) = 5))
  AND city <> ''
  AND city is not null
GROUP BY 1
ORDER BY users desc limit 50

Visualize Users by Source

Description: Count of subscribers by user source.
Short name: visualize_users_by_source
User input: None
Used in: Visualizing Your Base
SQL:  
SELECT source, COUNT(distinct user_id) 'users'
FROM core_user cu
JOIN core_subscription cs
  ON (cu.id=cs.user_id)
GROUP BY 1
ORDER BY users desc

Visualize Users by State

Description: Count of subscribers by state.
Short name: visualize_users_by_state
User input: None
Used in: Visualizing Your Base
SQL:  
SELECT state, COUNT(distinct cu.id) 'users'
FROM core_user cu
JOIN core_subscription cs
  ON (cu.id=cs.user_id)
WHERE cu.state IN ('AL','AK','AZ','AR','CA','CO','CT','DE','DC','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY')
GROUP BY 1

Weekly slice of list

Description: For recurring mailings meant to gradually trickle out to your list. 'Modulus' is the number of slices: enter 52 (*not* 1/52) to hit each user yearly, 26 for every six months, 13 for quarterly. Weekly sends shouldn't use a multiple of 7 for modulus. This query is displayed in the Query Library dropdown for targeting mailing recipients.
Short name: modulus_slice
User input: Number of weeks
Used in: None
SQL:  
SELECT id
  FROM core_user
  WHERE (id+to_days(now())) % {{modulus}} = 0;

Built-In Dashboard Reports Reference

24 Hour Mailing Response Dashboard

Description: 1 hour and 24 hour mailing stats (sent, open%, clicks/open, act%, payments) for mailings sent in the past X days. Also includes optional charts showing open and action rates by hour over the first 24 hours which you can omit if you want the report to run faster when you're looking at a lot of mailings/days. Does not include recurring mailings.
Preview
Short name: 24_hour_mailing_response_dashboard
User input: Days Past
Show Charts (optional, defaults to yes. Recommend entering 'no' if you're looking at many days worth of mailings.)
Time Zone (optional, defaults to 'US/Eastern')
Associated with: Mailing Response: Mailing IDs
Mailing Response: Mailing Rates
Mailing Response: Mailing Stats
HTML:  
<style type="text/css">
.dashboard h1, .dashboard h2 {
    font-size: 18px;
    line-height: 25px;
    font-weight:bold;
    border-bottom:0px;
    margin-bottom: 3px;
}
.dashboard h2 { font-size: 15px; line-height: 22px; }
.dashboard h3, .dashboard h4 {
    font-size: 1em;
    font-style:normal;
    margin-bottom: 3px;
}
.dashboard p { font-size: 13px; line-height: 18px; margin-bottom: 3px; }
table { cell-padding: 20px; margin-bottom:1em;}
td, th {
    text-align: left;
    padding: 3px;
    border: 1px;
    border-style: solid;
    border-color: #CCC;
    font-size: 12px;
}
tr:last-child { background: #f0f0f0; font-weight: bold; }
tr:last-child td.column-sent_at { opacity: 0; }
strong { font-weight: bold; }
.google-chart { display: inline-block; }
</style>

<div class="dashboard">

{% load actionkit_tags %}
{% required_parameter "Days_Past" %}
{% required_parameter "Show_Charts" %}
{% required_parameter "Time_Zone" %}
{% with Time_Zone|default:'US/Eastern' as tz %}

<p>Times are in {{tz}}.</p>

<h2>1 Hour Mailing Response</h2>
{% report 'mailing_response_mailing_stats' with Days_Past as Days_Past 1 as Hours_Since_Send tz as Time_Zone %}

<h2>24 Hour Mailing Response</h2>
{% report 'mailing_response_mailing_stats' with Days_Past as Days_Past 24 as Hours_Since_Send tz as Time_Zone %}

{% with Show_Charts|default:'yes' as show %}
{% if show == 'yes' %}
{% cache_report 'mailing_response_mailing_ids' with Days_Past as Days_Past tz as Time_Zone %}

<div class="google-chart LineChart" id="open_rates_chart">
    <table>
        <tr>
            <th>Hour</th>
            {% with mailing_response_mailing_ids|striptags|split:',' as mailing_list %}
            {% for mailing in mailing_list %}
            <th>{{ mailing }}</th>
            {% endfor %}
            {% endwith %}
        </tr>
        {% cache_report 'mailing_response_mailing_rates' with Days_Past as Days_Past tz as Time_Zone 'opens' as Type %}
        {% with mailing_response_mailing_rates|striptags|split:'|' as hours %}
        {% for hour in hours %}
        <tr>
            <td>{{ forloop.counter0 }}</td>
            {% for open_rate in hour|split:',' %}
            <td>{{ open_rate }}</td>
            {% endfor %}
        </tr>
        {% endfor %}
        {% endwith %}
    </table>
</div>

<div class="google-chart LineChart" id="action_rates_chart">
    <table>
        <tr>
            <th>Hour</th>
            {% with mailing_response_mailing_ids|striptags|split:',' as mailing_list %}
            {% for mailing in mailing_list %}
            <th>{{ mailing }}</th>
            {% endfor %}
            {% endwith %}
        </tr>
        {% cache_report 'mailing_response_mailing_rates' with Days_Past as Days_Past tz as Time_Zone 'actions' as Type %}
        {% with mailing_response_mailing_rates|striptags|split:'|' as hours %}
        {% for hour in hours %}
        <tr>
            <td>{{ forloop.counter0 }}</td>
            {% for open_rate in hour|split:',' %}
            <td>{{ open_rate }}</td>
            {% endfor %}
        </tr>
        {% endfor %}
        {% endwith %}
    </table>
</div>
{% endif %} <!-- show charts -->
{% endwith %} <!-- show variable -->
{% endwith %} <!-- tz variable -->

</div>


<script type="text/javascript">
reports.open_rates_chart = {
  options: {
    title: 'Open Rates for the First 24 Hours',
    height: 300,
    width: 500,
    hAxis: { title: 'Hours'},
    vAxis: { title: 'Open %' }
  }
};
reports.action_rates_chart = {
  options: {
    title: 'Action Rates for the First 24 Hours',
    height: 300,
    width: 500,
    hAxis: { title: 'Hours'},
    vAxis: { title: 'Action %' }
  }
};
</script>

Action Rates: Your Monthly Progress Report

Description: Charts of monthly progress and statistics related to action rates.
Preview
Short name: action_rates
User input: None
Associated with: Actions by Month Chart
(Monthly) Emails
(Monthly) List Size
(Monthly) Mailings
HTML:  
<script type="text/javascript">
  reports.actionsByMonthChart = {
    options: {
      title: 'Advocacy Totals by Month',
      hAxis: { title: 'Month',
           textStyle: { fontSize: 11 }
      },
      vAxis: { title: 'Actions',
           gridlines: { count: 5 },
           minorGridlines: { count: 1 }
      },
      legend: { position: 'none' }
    }
  };
</script>
<style type='text/css'>
  <!--
    th {
      text-align:center;
    }
    table {
       cell-padding: 20px;
    }
    td {
      text-align:center;
    }
     p {
       font-size: 1.0em;
     }-->
</style>
{% right_now %}

<h1>Action Rates: Your Monthly Progress Report</h1><br />


<table>
  <tr>
  <th><b>Monthly Results</b>&nbsp;&nbsp;
  <th>List Size&nbsp;&nbsp;
  <th>No. Mailings&nbsp;&nbsp;
  <th>Total Emails&nbsp;&nbsp;
  <th>Advocacy Actions&nbsp;&nbsp;
  <th>Actions/Sub&nbsp;&nbsp;
  </tr>
  {% for month in now|months_pastyr %}
  {% cache_report 'progress_list' with month as month %}
  {% cache_report 'progress_mailings' with month as month %}
  {% cache_report 'progress_emails' with month as month %}
  {% cache_report 'progress_actions' with month as month %}
  {% record reportresult progress_actions in series_actions %}
  <tr>
  <td>{{ month|month_year }}&nbsp;&nbsp;
  <td>{{ progress_list }}
  <td>    {{ progress_mailings }}
  <td>{{ progress_emails }}
  <td>{{ progress_actions }}
  <td>{% divide progress_actions progress_list 2 %}
  </tr>
  {% endfor %}
  </table>
<br />
<p style="font-size: 1.0em">* Doesn't include sign-ups, imports, unsubscribes, or donations.</p>
<br />
<div id="actionsByMonthChart" class="google-chart ColumnChart" style="height: 500px">
  {{ reports.progress_actions_chart }}
</div>

Amounts Raised by Source (ROI) Dashboard

Description: Distribution of payments by user source. Includes $/user and $/months on list per user.
Preview
Short name: amounts_raised_source_roi_dashboard
User input: None
Associated with: Amounts Raised by Source
HTML:  
<style type="text/css">
.dashboard h1, .dashboard h2 {
    font-size: 18px;
    line-height: 25px;
    font-weight:bold;
    border-bottom:0px;
    margin-bottom: 3px;
}
.dashboard h2 { font-size: 15px; line-height: 22px; }
.dashboard h3, .dashboard h4 {
    font-size: 1em;
    font-style:normal;
    margin-bottom: 3px;
}
.dashboard p, .dashboard li { font-size: 13px; line-height: 18px; margin-bottom: 3px; }
strong { font-weight: bold; }
</style>

{% load actionkit_tags %}

<div class="dashboard">
{{ reports.amounts_raised_source }}

<h3>Definitions</h3>
<ul>
<li><strong>Source</strong>: The users's source, which is set the first time the user is added to your database.</li>
<li><strong>Users</strong>: Number of users with that source, regardless of subscription status.</li>
<li><strong>Payments and Paid</strong>: Number and sum of successful payments (converted to USD) made by users with that source. Includes all recurring payments, not just the first.</li>
<li><strong>$ / user</strong>: Paid / number of users by source.</li>
<li><strong>Avg Days on list</strong>: Average number of days since users for that source have first joined your database.</li>
<li><strong>$ / User months on list</strong>: Dollars per user per month that they've been in your database for that source. A proxy for $/mailing since users who have been on your list longer generally will have received more mailings.</li>
</ul>
</div>

Call Page Dashboard

Description: Displays the number of actions submitted on a call page by advocacy target. Call pages include a box the user can check to indicate who they called, so we've also included the count of actions where the box was checked. This report uses the autofill function so you can enter part of the page_id or name, then select one or more pages to include in the results.
Preview
Short name: call_page_dashboard
User input: Call page id
Associated with: Calls by State Chart
Calls made by Target
HTML:  
<script type="text/javascript">
  reports.callsByStateChart = {
    options: {
      region: 'US',
      resolution: 'provinces'
    }
  };
  </script>
  {% required_parameter "callpage_id" %}
  {% cache_list_of Page as callpages where type = Call %}
  {% for cp in callpages %}
  {% if cp.id|stringformat:"s" == callpage_id %}
  <h1> {{ cp.title}} ({{ cp.name}}, {{ callpage_id }})</h1>
  {% endif %}
  {% endfor %}

  <h2>Calls made by target</h2><br />
  {% report "calls_made_by_target" with callpage_id as page_id %}
  <br />

  <p>Definitions:</p>
  <p>Actions shows a count of every action submitted with this target shown.</p>
  <p>Confirmed shows a count of every action submitted where the user checked the box next to who they called before submitting.</p>

  <h2>Map of calls made to targets' states</h2>
  <div id="callsByStateChart" class="google-chart GeoChart" style="height: 500px">
  {% report "calls_by_state_chart" with callpage_id as page_id %}
  </div>

Daily Open Rate by Mailbox Provider

Description: Open rate by day, grouped by mailbox provider, over previous two weeks
Short name: daily_openrate_by_provider
User input: None
Associated with: Open rate by Mailbox Provider (daily)
HTML:  
{% with data_only=1 n_days=14 %}
{% pivot_report "mailings_deliverability_by_day_by_provider" %}

<style>
  .delivery_by_provider_report th,td {
      padding: 10px !important;
  }

  .sent_and_opened div {
      text-align: center;
  }

  .sent_and_opened .tiny {
      color: #999;
      height: 15%;
  }

  .sent_and_opened .normal {
      height: 85%;
  }

  td.rate_warning {
      background-color: #ffa !important;
  }

  .rate_warning .open_rate {
      color: #d33 !important;
      font-weight: bold;
  }

  td.incomplete {
      background-color: #eee !important;
  }


  .reports .generic table th {
      background-color: #e3d1dc;
      border-left: 1px solid #d3a7c3;
  }

  .generic table td {
      line-height: 18px;
      border-left: 1px solid #ddd;
      border-bottom: 1px solid #ddd;
      border-top: 1px solid #ddd;
  }

  .rate_warning .open_rate {
      color: #d00 !important;
      font-weight: bold;
  }

  td.rate_warning {
      background-color: #ffeeee !important;
      border: 1px solid #fcc !important;
  }

  .reports #report_result td.incomplete {
      padding: 0px 5px;
      color: #aaa;
  }
</style>

<table>
  <thead>
  <tr>
      {% for col in mailings_deliverability_by_day_by_provider.column_names %}
          <th {% if forloop.last %}
              class="hasTooltip"
              data-tooltip="Today's data is only partially complete"
              {% endif %}>{{ col }}</th>
      {% endfor %}
  </tr>
  </thead>
  <tbody>
  {% for row in mailings_deliverability_by_day_by_provider.rows %}
  <tr>
      {% remember "" as mailbox_provider %}

      {% for col in row %}

          {% if forloop.first %}
              {# First column: Mailbox provider name #}
              {% remember col as mailbox_provider %}
              <td><div class="normal">{{ mailbox_provider }}</div></td>

          {% else %}

              {% remember col|split as sent_and_opened %}
              {% if sent_and_opened %}
                  {% remember sent_and_opened.0|add:"0" as sent %}
                  {% remember sent_and_opened.1|add:"0" as opened %}
                  {% if opened > 0 %}
                      {% remember opened|divide:sent|multiply:100 as open_rate %}
                  {% else %}
                      {% remember 0 as open_rate %}
                  {% endif %}

                  {# Only show decimal if rate < 1.0 (but non-zero) #}
                  {% if open_rate > 1 or open_rate == 0 %}
                      {% remember open_rate|floatformat:0|add:"0" as open_rate %}
                  {% else %}
                      {% remember open_rate|floatformat:1|add:"0" as open_rate %}
                  {% endif %}

                  {# Show warning for provider/day? #}
                  {% remember 1 as rate_warning %}

                  {# Apple is weird, and gives off low open rates normally #}
                  {% if mailbox_provider == "apple" %}
                      {% remember 2 as min_rate %}
                      {% remember 250 as min_sends %}
                  {% else %}
                      {% remember 5 as min_rate %}
                      {% remember 250 as min_sends %}
                  {% endif %}

                  {% if open_rate >= min_rate or sent < min_sends %}
                      {% remember 0 as rate_warning %}
                  {% endif %}

                  <td class="sent_and_opened {% if forloop.last %}incomplete{% elif rate_warning %}rate_warning{% endif %}">
                      <div class="tiny">{{ sent|suffixed }}</div>
                      <div class="normal open_rate">{{ open_rate }}%</div>
                  </td>

              {% else %}
                  {# No messages/data for this provider/day #}
                  <td class="sent_and_openrate">
                      <div class="tiny">&mdash;</div>
                  </td>

              {# endif sent_and_opened #}
              {% endif %}

          {# endif forloop.first #}
          {% endif %}

      {% endfor %}


  </tr>
  {% endfor %}
  <tbody>
</table>

{% endwith %}

Daily Overview Dashboard

Description: Top line stats on actions, donations and mailings for the previous 24 hours.
Preview
Short name: daily_overview_dashboard
User input: None
Associated with: Overview: Actions
Overview: Mailed
Overview: Mailing Stats
Overview: Recurring Donations
Overview: Top 10 Sources
Overview: Top 20 Action Pages
HTML:  
<style type="text/css">
.dashboard h1, .dashboard h2 {
    font-size: 18px;
    line-height: 25px;
    font-weight:bold;
    border-bottom:0px;
    margin-bottom: 3px;
}
.dashboard h2 { font-size: 15px; line-height: 22px; }
.dashboard h3, .dashboard h4 {
    font-size: 1em;
    font-style:normal;
    margin-bottom: 3px;
}
.dashboard p, .dashboard li { font-size: 13px; line-height: 18px; margin-bottom: 3px; }
.dashboard ul { list-style-type: none; }
table { cell-padding: 20px; margin-bottom:1em;}
td, th {
    text-align: left;
    padding: 3px;
    border: 1px;
    border-style: solid;
    border-color: #CCC;
    font-size: 12px;
}
table.report.query-overview_actions tr:last-child { background: #f0f0f0; font-weight: bold; }
strong { font-weight: bold; }
.dashboard li { margin-left: 20px;}
</style>

{% load actionkit_tags %}
{% right_now %}
<div class="dashboard">

{% with now|date_add:"days=-1" as yesterday %}

<h2>Mailed</h2>
{% report 'overview_mailed' with yesterday as 1._Start_on_date_YYYY-MM-DD now as 2._End_by_date_YYYY-MM-DD %}

<h2>Mailings</h2>
{% report 'overview_mailing_stats' with yesterday as 1._Start_on_date_YYYY-MM-DD now as 2._End_by_date_YYYY-MM-DD %}

<h2>Actions</h2>
{% report 'overview_actions' with yesterday as 1._Start_on_date_YYYY-MM-DD now as 2._End_by_date_YYYY-MM-DD %}

<h2>Recurring Donations</h2>
{% report 'overview_recurring_donations' with yesterday as 1._Start_on_date_YYYY-MM-DD now as 2._End_by_date_YYYY-MM-DD %}

<h2>Top 20 Action pages</h2>
{% report 'overview_top_action_pages' with yesterday as 1._Start_on_date_YYYY-MM-DD now as 2._End_by_date_YYYY-MM-DD %}

<h2>Top 10 Sources</h2>
{% report 'overview_source_info' with yesterday as 1._Start_on_date_YYYY-MM-DD now as 2._End_by_date_YYYY-MM-DD %}

{% endwith %}

</div>

Event Report

Description: An overview of the events for your campaigns.
Short name: event_report
User input: Campaign name
Associated with: Event Count by Signups
Event Count by States
Event Count by City
Event Count by Date
HTML:  
<script type="text/javascript">
  reports.eventCountBySignups = {
    options: {
      title: 'Event Count by Attendees',
      hAxis: { title: 'Number of Attendees',
               textStyle: { fontSize: 11 }
      },
      vAxis: { title: 'Number of Events' },
      legend: { position: 'none' }
    }
  };
  reports.eventCountByState = {
    options: {
      region: 'US',
      resolution: 'provinces',
      datalessRegionColor: 'white'
    }
  };
  reports.eventCountByCity = {
    options: {
      region: 'US',
      resolution: 'provinces',
      displayMode: 'markers',
      colorAxis: { colors: ['yellow', 'red']}
    }
  };
  </script>
  <style type="text/css">
    <!--
    th {
        text-align:center;
    }
    table {
        cell-padding: 20px;
    }
    td {
        text-align:center;
    }
    p {
        font-size: 1.0em;
    }-->
  </style>
  <h1>Event Campaign Report</h1>
  {% load actionkit_tags %}
  {% required_parameter "campaign_name" %}

  <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 %}

  <h3>Total events:</h3><br />

  {% load_quick_link_info campaign_name %}
  {% using quick_link_info %}
  <div><a href="{{ all.url }}">{{ all.count }} events</a></div>
  <div>Counts by status:</div>
  <div style="margin-left: 10px;">
  <div>
     <a href="{{ open.url }}">{{ open.count }} open</a>
     (<a href="{{ open_public.url }}">{{ open_public.count }} public</a>,
     <a href="{{ open_private.url }}">{{ open_private.count }} private</a>)
  </div>
  {% if campaign.require_email_confirmation %}
  <div><a href="{{ unconfirmed.url }}">{{ unconfirmed.count }} waiting for host confirmation</a></div>
  {% endif %}
  {% if campaign.require_staff_approval %}
  <div><a href="{{ unapproved.url }}">{{ unapproved.count }} waiting for staff approval</a></div>
  {% endif %}
  <div><a href="{{ cancelled.url }}">{{ cancelled.count }} cancelled or deleted</a></div>
  </div>
  {% endusing %}

  <h3>Total sign ups:</h3><br />

  Events w/ 1 sign up (aside from host) :
  {% report "event_count_by_signups" with campaign_name as campaign_name 1 as min_signups 1 as max_signups %}
  <br / >
  Events w/ 2-5 sign ups :
  % report "event_count_by_signups" with campaign_name as campaign_name 2 as min_signups 5 as max_signups %}
  <br />
  Events w/ more than 5 :
  {% report "event_count_by_signups" with campaign_name as campaign_name 6 as min_signups 999999 as max_signups %}
  <br />

  <div id="eventCountBySignups" class="google-chart ColumnChart" style="height: 400px">
  <table>
    <tr><th>Attendees</th><th>Events</th></tr>
    <tr><td>only 1</td>
        <td>{% report "event_count_by_signups" with campaign_name as campaign_name 1 as min_signups 1 as max_signups %}</td></tr>
    <tr><td>2-4</td>
        <td>{% report "event_count_by_signups" with campaign_name as campaign_name 2 as min_signups 4 as max_signups %}</td></tr>
    <tr><td>5-9</td>
        <td>{% report "event_count_by_signups" with campaign_name as campaign_name 5 as min_signups 9 as max_signups %}</td></tr>
    <tr><td>10-24</td>
        <td>{% report "event_count_by_signups" with campaign_name as campaign_name 10 as min_signups 24 as max_signups %}</td></tr>
    <tr><td>25+</td>
        <td>{% report "event_count_by_signups" with campaign_name as campaign_name 25 as min_signups 999999 as max_signups %}</td></tr>
  </table>
  </div>

  <h3>Event count by date:</h3><br />
  {% report "event_count_by_date" with campaign_name as campaign_name %}
  <br />
  <p>Note: the attendance column gives a count of users who were marked by hosts as having attended the event.  This count may be too low if hosts did not consistently mark attendance.  The attendance will be zero for events that occurred before this feature was available.</p>

  <h3>Event count by state:</h3><br />
  <div id="eventCountByState" class="google-chart GeoChart" style="height: 500px">
  {% report "event_count_by_states" with campaign_name as campaign_name %}
  </div>

  <h3>Event count by major metro:</h3><br />
  <div id="eventCountByCity" class="google-chart GeoChart" style="height: 500px">
  {% report "event_count_by_city" with campaign_name as campaign_name %}
  </div>
  <br />
  <p>Note: the above charts and data only include events and signups with a status of "active"</p>

Fundraising Campaigns: Your Progress Report

Description: Tables of donation statistics by page and mailing.
Preview
Short name: fundraising_campaigns
User input: None
Associated with: (Mailing) Revenue
(Page) Revenue by Source
(Page) Revenue
(Page) Gifts by Source
(Page) Gifts
(Mailing) Gifts
(Monthly) One-Time Donations
Mailing sent count
HTML:  
<script type="text/javascript">
  reports.revenueByMonthChart = {
    options: {
      title: 'Donation Revenue by Month',
      hAxis: { title: 'Month',
               textStyle: { fontSize: 11 }
      },
      vAxis: { title: 'Revenue' },
      legend: { position: 'none' }
    }
  };
  reports.revenueByPageChart = {
    options: {
      title: 'Donation Revenue for Recent Pages',
      hAxis: { title: 'page id',
               textStyle: { fontSize: 11 }
      },
      series: [ { color: 'blue',
                  targetAxisIndex: 0,
                  visibleInLegend: false
                },
                { color: 'green',
                  targetAxisIndex: 1,
                  visibleInLegend: false
                }
              ],
      vAxes: [
               { title: 'Revenue ($)',
                 textStyle: { color: 'blue' },
                 gridlines: { count: 5 },
                 minorGridlines: { count: 1 }
               },
               { title: 'Gifts (#)',
                 textStyle: { color: 'green' },
                 gridlines: { count: 5 },
                 minorGridlines: { count: 1 }
               }
             ],
      legend: { position: 'none' }
    }
  };
  </script>
  <style type='text/css'>
    <!--
    th {
        text-align:center;
    }
    table {
        cell-padding: 20px;
    }
    td {
        text-align:center;
    }
    p {
        font-size: 1.0em;
    }-->
  </style>
  <h1>Fundraising Campaigns: Your Progress Report</h1><br />
  <p>Note: the tables and charts below do not include recurring donations</p>
  <!-- one table for each DonationPage -->
  {% cache_list_of Page as don_pages where type = Donation %}
  {% for don in don_pages reversed %}
  <table>
    <tr>
      <th>&nbsp;{{ don.title }}&nbsp;({{ don.id }})&nbsp;</th>
      <th>&nbsp;Recipients&nbsp;</th>
      <th>&nbsp;Tot. Gifts&nbsp;</th>
      <th>&nbsp;CTR&nbsp;</th>
      <th>&nbsp;Page Conv&nbsp;</th>
      <th>&nbsp;Resp. Rate&nbsp;</th>
      <th>&nbsp;Avg. Gift&nbsp;</th>
      <th>&nbsp;Revenue&nbsp;</th>
    </tr>
    {% cache_mailings_for_page don.id as don_mail %}

    <!-- one row for each mailing linking to this page -->
    {% for dm in don_mail %}
    {% cache_report 'mailing_sent_count' with dm.id as mailing_id %}
    {% cache_report 'progress_mail_gifts' with dm.id as mailing_id don.id as page_id %}
    {% cache_report 'progress_mail_page_clicks' with dm.id as mailing_id don.id as page_id %}
    {% cache_report 'progress_mail_revenue' with dm.id as mailing_id don.id as page_id %}
    <tr>
      <td>&nbsp;Email {{ dm.id }}&nbsp;</td>
      <td>&nbsp;{{ mailing_sent_count }}&nbsp;</td>
      <td>&nbsp;{{ progress_mail_gifts }}&nbsp;</td>
      <td>&nbsp;{{ progress_mail_page_clicks|percent_of:mailing_sent_count }}&nbsp;</td>
      <td>&nbsp;{{ progress_mail_gifts|percent_of:progress_mail_page_clicks }}&nbsp;</td>
      <td>&nbsp;{{ progress_mail_gifts|percent_of2:mailing_sent_count }}&nbsp;</td>
      <td>&nbsp;${% divide progress_mail_revenue progress_mail_gifts 2 %}&nbsp;</td>
      <td>&nbsp;${{ progress_mail_revenue }}&nbsp;</td>
    </tr>
    {% endfor %}

    <!-- now a row for each source besides 'mailing' -->
    {% cache_sources_for_page don.id as don_sources %}
    {% for source in don_sources %}
    {% ifnotequal source 'mailing' %}
    {% cache_report 'progress_source_gifts' with don.id as page_id source as source %}
    {% cache_report 'progress_source_revenue' with don.id as page_id source as source %}
    <tr>
      <td>&nbsp;{{ source }}&nbsp;</td>
      <td></td>
      <td>&nbsp;{{ progress_source_gifts }}&nbsp;</td>
      <td></td><td></td><td></td>
      <td>&nbsp;${% divide progress_source_revenue progress_source_gifts 2 %}&nbsp;</td>
      <td>&nbsp;${{ progress_source_revenue }}&nbsp;</td>
    </tr>
    {% endifnotequal %}
    {% endfor %}

    <!-- one last row for page totals -->
    {% cache_report 'progress_page_gifts' with don.id as page_id %}
    {% cache_report 'progress_page_revenue' with don.id as page_id %}
    {% record don.id in series_labels %}
    <tr>
      <td>&nbsp;{{ don.name }} Totals&nbsp;</td>
      <td></td>
      <td>&nbsp;{{ progress_page_gifts }}&nbsp;</td>
      <td></td>
      <td></td>
      <td></td>
      <td>&nbsp;${% divide progress_page_revenue progress_page_gifts 2 %}&nbsp;</td>
      <td>&nbsp;${{ progress_page_revenue }}&nbsp;</td>
    </tr>
  </table>
  <br />
  {% endfor %}

  <div id="revenueByPageChart" class="google-chart ColumnChart" style="height: 500px; overflow: hidden">
  <table>
      <tr><th>Page id</th><th>Revenue</th><th>Orders</th></tr>
      {% for page_id in series_labels|islice:"0:15" %}
      {% cache_report "progress_page_gifts" with page_id as page_id %}
      {% ifnotequal progress_page_gifts|strip_nondigits '0' %}
      <tr><td>#{{ page_id }}</td>
          <td>{% report "progress_page_revenue" with page_id as page_id %}</td>
          <td>{{ progress_page_gifts }}</td>
      </tr>
      {% endifnotequal %}
      {% endfor %}
  </table>
  </div>
  <br />

  {% right_now %}
  <div id="revenueByMonthChart" class="google-chart LineChart" style="height: 500px">
  <table>
      <tr><th>Month</th><th>Revenue</th></tr>
      {% for month in now|months_pastyr %}
      <tr><td>{{ month|month_year }}</td>
          <td>{% report "progress_revenue_one" with month as month %}</td>
      </tr>
      {% endfor %}
  </table>
  </div>
  <p style="font-size: 0.9em">*Does not include recurring donation revenue</p>
  <br />

  <h2> Definitions: </h2><br />
  <p style="font-size: 1.2em"><b><u>Columns</u>:</b></p>
  <p style="font-size: 1.0em"><b>Recipients:</b>  Total delivered emails which include a link to this donation page</p>
  <p style="font-size: 1.0em"><b>Gifts:</b>  Total gifts trackable back to email-coded URL (does not include monthly pledges)</p>
  <p style="font-size: 1.0em"><b>CTR:</b>  The clickthrough rate is unique clicks divided by the number of recipients</p>
  <p style="font-size: 1.0em"><b>Page Conversion:</b>  Gifts / page views (the percent of people landing on the fundraising pg who donate)</p>
  <p style="font-size: 1.0em"><b>Response Rate:</b>  Gifts / email recipients (recipients based only on emails sent, not web traffic)</p>
  <p style="font-size: 1.0em"><b>Average Gift:</b>  Total amount raised / number of gifts</p>
  <p style="font-size: 1.0em"><b>Revenue:</b>  Total amount raised (does not include monthly pledges)</p>
  <p style="font-size: 1.2em"><b><u>Rows</u>:</b>  Each table represents one donation page (recurring donation pages are not included) </p>
  <p style="font-size: 1.0em"><b>Email:</b>  Each email sent (identified by id number) that contains a link to this page</p>
  <p style="font-size: 1.0em"><b>Website Donations:</b> Aggregate result for donations on the webpage not attributable to an email source</p>
  <p style="font-size: 1.0em"><b>Total:</b> Total numbers for the page</p>

List Growth: Your Monthly Progress Report

Description: Charts of monthly progress and statistics related to list growth.
Preview
Short name: list_growth
User input: None
Associated with: (Monthly) List Size
(Monthly) New Users
(Monthly) Bounces
(Monthly) Emails
New Users by Month
(Monthly) Unsubs
HTML:  
<script type="text/javascript">
  reports.listSizeByMonthChart = {
    options: {
      title: 'List Size by Month',
      hAxis: { title: 'Month',
               textStyle: { fontSize: 11 }
      },
      vAxis: { title: 'Subscribed users (any list)' },
      legend: { position: 'none' }
    }
  };
  reports.monthlyNewUsersChart = {
    options: {
      title: 'New Users by Month',
      hAxis: { title: 'Month',
               textStyle: { fontSize: 11 }
      },
      vAxis: { title: 'Users',
               gridlines: { count: 5 },
               minorGridlines: { count: 1 }
      },
      legend: { position: 'none' }
    }
  };
  </script>
  <style type='text/css'>
    <!--
      th {
            text-align:center;
      }
       table {
         cell-padding: 20px;
     }
       td {
            text-align:center;
      }
       p {
         font-size: 1.0em;
       }-->
  </style>
  {% right_now %}

  <h1>List Growth: Your Monthly Progress Report</h1><br />

  <table>
    <tr>
      <th><b>List Activity</b>&nbsp;&nbsp;
      <th>List Size&nbsp;&nbsp;
      <th>New Members&nbsp;&nbsp;
      <th>Unsub/Bounce&nbsp;&nbsp;
      <th>Unsub Rate&nbsp;&nbsp;
      <th>Bounce Rate&nbsp;&nbsp;
      <th>Churn &nbsp;&nbsp;
      <th>Avg Msg/Sub &nbsp;&nbsp;
    </tr>
    {% for month in now|months_pastyr %}
    {% if forloop.first %}
        {% cache_report 'progress_list' with month|month_ago as month %}
        {% save_sum progress_list as progress_list_old %}
    {% endif %}
    {% cache_report 'progress_list' with month as month %}
    {% cache_report 'progress_new_users' with month as month %}
    {% cache_report 'progress_unsubs' with month as month %}
    {% cache_report 'progress_bounces' with month as month %}
    {% cache_report 'progress_emails' with month as month %}
    {% save_sum progress_list_old progress_new_users as churn_base %}
    {% record reportresult progress_new_users in series_newusers %}
    <tr>
      <td>{{ month|month_year }}&nbsp;&nbsp;
      <td>{{ progress_list }}
      <td>{{ progress_new_users }}
      <td>{{ progress_unsubs|add:progress_bounces }}
      <td>{{ progress_unsubs|percent_of:progress_list }}
      <td>{{ progress_bounces|percent_of:progress_list }}
      <td>{{ churn_base|subtract:progress_list|percent_of:churn_base }}
      <td>{% divide progress_emails progress_list 1 %}
    </tr>
    {% endfor %}
  </table>
  <br /><br />
  <div id="monthlyNewUsersChart" class="google-chart ColumnChart" style="height: 500px">
  {{ reports.progress_new_users_chart }}
  </div>
  <div id="listSizeByMonthChart" class="google-chart LineChart" style="height: 500px">
  <table>
      <tr><th>Month</th><th>Members</th></tr>
      {% for month in now|months_pastyr %}
      <tr>
          <td>{{month|month_year}}</td>
          <td>{% report "progress_list" with month as month %}</td>
      </tr>
      {% endfor %}
  </table>
  </div>
  <h2> Definitions: </h2>
  <p style="font-size: 1.0em"><b>List size:</b> This is the total number of mailable users your group has at the end of each month.</p>
  <p style="font-size: 1.0em"><b>New Members:</b> This is the number of users joining your group for the first time during each month.</p>
  <p style="font-size: 1.0em"><b>Unsubs/bounces:</b> This is the number of users who unsubscribed from or bounced off of one of your lists during each month.</p>
  <p style="font-size: 1.0em"><b>Unsubscribe Rate:</b> The percentage of users during each month who unsubscribed from one of your lists.</p>
  <p style="font-size: 1.0em"><b>Bounce Rate:</b> The percentage of users during each month who bounced off of one of your lists.</p>
  <p style="font-size: 1.0em"><b>Churn:</b> This is defined as (mailable list size at the start of the month + new users during the month - mailable list size at the end of the month) / (mailable list size at month start + new users during month).  This corresponds to the percentage of the total users during each month who left your group by the end of the month.</p>
  <p style="font-size: 1.0em"><b>Average Msg/Sub:</b> This is defined as the number of emails delivered during a month divided by the number of subscribers at the end of the month.</p>
  <p style="font-size: 1.0em">Note: If a user was subscribed to multiple lists, and remains on at least one, that user will still be mailable.  So in general it is <b>not</b> true that this month's list size = last month's list size + this month's new users - this month's unsubs/bounces</p>

List Stats

Description: (The Big One) Overview of list size, and recent actions and donations.
Preview
Short name: liststats
User input: None
Associated with: Mailable Users
US Users
Mailable US Users
New Users
New Users, Imported
New Users, from Actions
Lost Users bounces
Users unsubscribed
Users w/1 action last year
Users w/3 actions last year
Users w/1 action last 90 days
Users w/3 actions last 90 days
Total Raised this Week
# of Donations
Average Donation
Actions taken
Users taking action
Action Details (Recent)
Donations Details
HTML:  
<style type="text/css">
  <!--
      th {
          text-align:center;
      }
      table {
          cell-padding: 20px;
      }
      td {
          text-align:center;
      }-->
</style>

  <h2><b>List size and stats:</b></h2>
  <table>
    <tr><th>&nbsp;</th>
        <th>&nbsp;Full Base&nbsp;</th>
    </tr>
    <tr>
        <td>&nbsp;Mailable Users&nbsp;</td>
        <td>&nbsp;{{ reports.users_mail }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;US Users&nbsp;</td>
        <td>&nbsp;{{ reports.users_US }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Mailable US Users&nbsp;</td>
        <td>&nbsp;{{ reports.users_mail_US }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;New Users&nbsp;</td>
        <td>&nbsp;{{ reports.users_new }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;New Users (imported)&nbsp;</td>
        <td>&nbsp;{{ reports.users_new_import }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;New Users (actions)&nbsp;</td>
        <td>&nbsp;{{ reports.users_new_action }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Lost users: bounces&nbsp;</td>
        <td>&nbsp;{{ reports.users_bounced }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Lost users: unsubscribed&nbsp;</td>
        <td>&nbsp;{{ reports.users_unsub }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Users: 1 action last year&nbsp;</td>
        <td>&nbsp;{{ reports.users_1act_yr }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Users: 3 actions last year&nbsp;</td>
        <td>&nbsp;{{ reports.users_3act_yr }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Users: 1 action last 90d</td>
        <td>&nbsp;{{ reports.users_1act_90d }}</td>
    </tr>
    <tr>
        <td>&nbsp;Users: 3 actions last 90d&nbsp;</td>
        <td>&nbsp;{{ reports.users_3act_90d }}&nbsp;</td>
    </tr>
  </table>

  <h2><b>Donations in the last week:</b></h2>
  <table>
    <tr><th>&nbsp;</th><th>&nbsp;Full Base&nbsp;</th>
    </tr>
    <tr>
        <td>&nbsp;Total Raised&nbsp;</td>
        <td>&nbsp;${{ reports.donations_week }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;# of Donations&nbsp;</td>
        <td>&nbsp;{{ reports.donations_count }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Average&nbsp;</td>
        <td>&nbsp;${{ reports.donations_average }}&nbsp;</td>
    </tr>
  </table>

  <h2><b>Actions in the last week:</b></h2>
  <table>
    <tr><th>&nbsp;</th>
        <th>&nbsp;Full Base&nbsp;</th>
    </tr>
    <tr>
        <td>&nbsp;Actions taken&nbsp;</td>
        <td>&nbsp;{{ reports.actions_week }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Unique users&nbsp;</td>
        <td>&nbsp;{{ reports.actions_users }}&nbsp;</td>
    </tr>
  </table>

  <h2><b>Action Details</b></h2>
  {{ reports.actions_details }}

  <h2><b>Donations Details</b></h2>
  {{ reports.donations_details }}

list stats mini

Description: This report generates all the stats associated with your user lists. (Bigger than the big one.)
Preview
Short name: liststatsmini
User input: None
Associated with: Mailable Users
Mailable Users, Standard List
Mailable Users, Special List
US Users, Special List
US Users, Standard List
US Users
Mailable US Users
Mailable US Users, Standard List
Mailable US Users, Special List
New Users
New Users, Spec List
New Users, Std List
Lost Users bounces
Lost Users bounces, Std List
Lost Users bounces, Spec List
Users unsubscribed
Users unsubscribed, Main List
Users unsubscribed, Spec List
Users w/1 action last year
Users w/1 action last year, Main List
Users w/1 action last year, Special List
Users w/3 actions last year
Users w/3 actions last year, Main List
Users w/3 actions last year, Special List
Users w/1 action last 90 days
Users w/1 action last 90 days (main)
Users w/1 action last 90 days (weekly)
Users w/3 actions last 90 days
Users w/3 actions last 90 days (main)
Users w/3 actions last 90 days (weekly)
Actions taken
Actions taken (weekly list)
Actions taken (main list)
Users taking action
Users taking action (weekly)
Users taking action (main)
HTML:  
<style type="text/css">
  <!--
      th {
          text-align:center;
      }
      td {
          text-align:center;
      }-->
</style>
  <h2><b>List size and stats:</b></h2>
  <table>
    <tr><th>&nbsp;</th>
        <th>&nbsp;Total&nbsp;</th>
        <th>&nbsp;Standard List&nbsp;</th>
        <th>&nbsp;Special List&nbsp;</th>
    </tr>
    <tr>
        <td>&nbsp;Mailable Users&nbsp;</td>
        <td>&nbsp;{{ reports.users_mail }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_mail_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_mail_weekly }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;US Users&nbsp;</td>
        <td>&nbsp;{{ reports.users_US }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_US_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_US_weekly }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Mailable US Users&nbsp;</td>
        <td>&nbsp;{{ reports.users_mail_US }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_mail_US_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_mail_US_weekly }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;New Users&nbsp;</td>
        <td>&nbsp;{{ reports.users_new }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_new_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_new_weekly }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Lost users: bounces&nbsp;</td>
        <td>&nbsp;{{ reports.users_bounced }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_bounced_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_bounced_weekly }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Lost users: unsubscribed&nbsp;</td>
        <td>&nbsp;{{ reports.users_unsub }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_unsub_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_unsub_weekly }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Users: 1 action last year&nbsp;</td>
        <td>&nbsp;{{ reports.users_1act_yr }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_1act_yr_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_1act_yr_weekly }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Users: 3 actions last year&nbsp;</td>
        <td>&nbsp;{{ reports.users_3act_yr }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_3act_yr_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_3act_yr_weekly }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Users: 1 action last 90d&nbsp;</td>
        <td>&nbsp;{{ reports.users_1act_90d }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_1act_90d_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_1act_90d_weekly }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Users: 3 actions last 90d&nbsp;</td>
        <td>&nbsp;{{ reports.users_3act_90d }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_3act_90d_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.users_3act_90d_weekly }}&nbsp;</td>
    </tr>
  </table>

  <h2><b>Actions in the last week:</b></h2>
  <table>
    <tr><th>&nbsp;</th>
        <th>&nbsp;Total&nbsp;</th>
        <th>&nbsp;Standard List&nbsp;</th>
        <th>&nbsp;Special List&nbsp;</th>
    </tr>
    <tr>
        <td>&nbsp;Actions taken&nbsp;</td>
        <td>&nbsp;{{ reports.actions_week }}&nbsp;</td>
        <td>&nbsp;{{ reports.actions_week_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.actions_week_weekly }}&nbsp;</td>
    </tr>
    <tr>
        <td>&nbsp;Unique users&nbsp;</td>
        <td>&nbsp;{{ reports.actions_users }}&nbsp;</td>
        <td>&nbsp;{{ reports.actions_users_main }}&nbsp;</td>
        <td>&nbsp;{{ reports.actions_users_weekly }}&nbsp;</td>
    </tr>
  </table>

LTE Downloads

Description: Get all of the LTEs for a specified LTE Page.
Short name: lte_downloads
User input: LTE page id
Associated with: None
HTML:  
{% required_parameter "page_id" %}
{% cache_ltes_for_page page_id as lte_actions %}
<h1>LTE Download</h1>
<h2>{{ lte_actions.0.page.title }} ({{ lte_actions.0.page.name }}, {{ lte_actions.0.page.id }})</h2>

{% for lte in lte_actions %}
<hr>
<p class="target">
  {{ lte.target.name }}<br />
  {{ lte.target.city }}, {{ lte.target.state}} {{ lte.target.zip }}<br />
</p>

<p class="subject">{{ lte.subject }}</p>

<div class="body">{{ lte.letter_text|escape|linebreaks }}</div>
{% with lte.user as u %}
<p class="signature">
  {{ u.name }}<br>
  {{ u.address1 }}<br>
  {{ u.city }}, {{ u.state }} {{ u.zip }}<br>
  id: <a href="http://{% client_domain %}/admin/core/user/{{ u.id }}">{{ u.id }}</a>
</p>
{% endwith %}
{% endfor %}

LTE Basic Dashboard

Description: Basic stats for a specified LTE page.
Short name: lte_basic_dashboard
User input: LTE page id
Associated with: LTE Basic Total Letters Submitted
LTE Basic Total Users Submitting Letters
LTE Basic Average Letters per User
LTE Basic Letters Submitted by State
LTE Basic Letters Submitted per Newspaper type
LTE Basic Letters Submitted per Newspaper
HTML:  
{% required_parameter "lte_page_id" %}

<h1>Stats for LTE Page {lte_page_id}</h1>

<h2>Total Letters Submitted</h2>
{% report "lte_basic_total_letters_submitted" with lte_page_id as lte_page_id %}

<h2>Users Submitting One or More Letters</h2>
{% report "lte_basic_total_users_submitting_letters" with lte_page_id as lte_page_id %}

<h2>Average Letters Per User</h2>
{% report "lte_basic_average_letters_per_user" with lte_page_id as lte_page_id %}

<h2>Letters Submitted per State</h2>
{% report "lte_basic_letters_submitted_by_state" with lte_page_id as lte_page_id %}

<h2>Letters Submitted per Newspaper Type</h2>
{% report "lte_basic_letters_submitted_by_newspaper_type" with lte_page_id as lte_page_id %}

<h2>Letters Submitted per Newspaper</h2>
{% report "lte_basic_letters_submitted_per_newspaper" with lte_page_id as lte_page_id %}

One-line Mailing stats

Description: One-line Mailing stats for openers, sent, and clicks.
Short name: mailing_minidash
User input: Mailing id
Associated with: Mailing openers
Mailing sent count
Clickers for a mailing
HTML:  
{{ reports.mailing_openers.value|percent_of:reports.mailing_sent_count.value }} opened,
{{ reports.mailing_clickers.value|percent_of:reports.mailing_sent_count.value }} clicked

Page Drilldown Dashboard

Description: Detailed stats for a given page, showing action takers, new subscribers, payments, sources, associated mailings, confirmation mailings and action notifications.
Preview
Short name: page_drilldown_dashboard
User input: Page id
Associated with: Page: Action Notification Mailing Stats
Page: Confirmation Mailing Stats
Page: Donations Totals
Page: Mailed
Page: Rates per Mailed
Page: Rates per Mailed for Donation Pages
Page: Recurring Donations Created
Page: Related Mailing Stats
Page: Signers and Subs
Page: Source Info
Page: Unsubs from Mailings Count
HTML:  
<style type="text/css">
.dashboard h1, .dashboard h2 {
    font-size: 18px;
    line-height: 25px;
    font-weight:bold;
    border-bottom:0px;
    margin-bottom: 3px;
}
.dashboard h2 { font-size: 15px; line-height: 22px; }
.dashboard h3, .dashboard h4 {
    font-size: 1em;
    font-style:normal;
    margin-bottom: 3px;
}
.dashboard p, .dashboard li { font-size: 13px; line-height: 18px; margin-bottom: 3px; }
.dashboard ul { list-style-type: none; }
table { cell-padding: 20px; margin-bottom:1em;}
td, th {
    text-align: left;
    padding: 3px;
    border: 1px;
    border-style: solid;
    border-color: #CCC;
    font-size: 12px;
}
strong { font-weight: bold; }
.dashboard li { margin-left: 20px;}
</style>

{% load actionkit_tags %}
{% required_parameter "page_id" %}
{% cache_report 'page_unsubs_mailings_count' %}
{% with type=page.derived.type|lower %}
{% with unsubs=page_unsubs_mailings_count|striptags %}

<div class="dashboard">

{% if page_id|force_list|length == 1 %}
  <h1>{{page.title}} ({{ page_id }})</h1>
  {% if page.derived.notes %}<p><em>{{ page.derived.notes }}</em></p>{% endif %}
  <p>
  <a href="/admin/core/{{ type }}page/{{ page_id }}" target="_blank">Edit Page</a> | <a href="{{page.derived.canonical_url}}" target="_blank">View Page</a>
  </p>
{% endif %}

{% if type == 'donation' %}
  <h2>Donations</h2>
  {% report 'page_donations_totals' %}

  <h2>Recurring donations</h2>
  {% report 'page_recurring_donations_created' %}

{% else %}
  <h2>Actions</h2>
  {% report 'page_signers_subs' with unsubs as unsubs %}
{% endif %}

<h2>Mailed</h2>
{% report 'page_mailed' %}

<h2>Rates per Mailed</h2>
{% if type == 'donation' %}
  {% report 'page_rates_per_mailed_donations' with unsubs as unsubs %}
{% else %}
  {% report 'page_rates_per_mailed' with unsubs as unsubs %}
{% endif %}

{% endwith %} <!-- unsubs -->

<h2>Sources</h2>
{% report 'page_source_info' with type as page_type %}

<h2>Mailing Stats</h2>
{% report 'page_related_mailing_stats' with type as page_type %}

<h2>Confirmation Mailing Stats</h2>
{% report 'page_confirmation_mailing_stats' %}

<h2>Action Notification Mailing Stats</h2>
{% report 'page_action_notification_mailing_stats' %}

{% if page_id|force_list|length == 1 %}
  <h2><a href="/report/shares_stats_page/?page_id={{page_id}}">Sharing Stats</a></h2>
{% else %}
  <h2>Sharing Stats</h2>
{% endif %}
{% report 'shares_type_page' %}

<hr>

<h2>Definitions</h2>
{% if type == 'donation' %}
  <p>Donations</p>
  <li><strong>All payments and Total paid:</strong> Count and sum of successful payments (one-time or recurring) from this page. Includes all recurring payments, not just the first, for profiles created on this page.</li>
  <li><strong>Average payment:</strong> Average amount given for successful payments (one-time or recurring) made on this page.</li>

  <p>Recurring donations</p>
  <li><strong>Profiles created and Monthly value:</strong> Count and sum of monthly commitments for successful recurring profiles created on this page.</li>
  <li><strong>Average donation:</strong> Average amount pledged for recurring profiles created on this page.</li>

  <p>Mailed</p>
  <li><strong>Sent mail:</strong> Sum of mailing counts for mailings that led to actions on this page.</li>
  <li><strong>Users mailed:</strong> Distinct users who received a mailing that led to actions on this page.</li>
  <li><strong>Duplicate mails:</strong> Sent mail - Users mailed; shows the number of mailings sent to people who'd already received one ask for this page.</li>

  <p>Rates per mailed</p>
  <li><strong>$ /100 mailed:</strong> The amount you raised by sending an email to 100 people (a variation on $/mailed but shifts the decimal point over to show more distinction between lower-raising mailings). Gives a sense of campaign performance.
  <li><strong>Unsubs from mailings:</strong> Distinct users who unsubbed from the mailing (includes all unsubs - user-initiated, bounces, spam clicks - from any lists).</li>
  <li><strong>Payments/unsub and $/unsub:</strong> Count and sum of successful payments (one-time or recurring) from this page divided by the number of unsubs generated by mailings to this page. For comparing the trade-off between the contributions generated by mailing this page to members versus the unsubs generated by such mailings.</li>

  <p>Sources</p>
  <li><strong>Source:</strong> Action source</li>
  <li><strong>Action Takers:</strong> Distinct users who took action on this page and had this source for their action.</li>
  <li><strong>Payments and $:</strong> Count and sum of successful payments (one-time or recurring) because of an action with this source.</li>

{% else %}
  <p>Actions</p>
  <li><strong>Action takers:</strong> Count of distinct action takers.</li>
  <li><strong>NTL:</strong> Count of users who were subscribed to a list because of this action.</li>
  <li><strong>Unsubs from mailings:</strong> Count of users who were unsubscribed from any list due to a mailing that led to actions on this page.</li>
  <li><strong>Net NTL:</strong> NTL - unsubs; compares the trade-off between the new subscriptions generated by this campaign versus the unsubs generated by mailings of this campaign and allows you to see if you grew your list.</li>

  <p>Mailed</p>
  <li><strong>Sent mail:</strong> Sum of mailing counts for mailings that led to actions on this page.</li>
  <li><strong>Users mailed:</strong> Distinct users who received a mailing that led to actions on this page.</li>
  <li><strong>Duplicate mails:</strong> Sent mail - Users mailed; shows the number of mailings sent to people who'd already received one ask for this page.</li>

  <p>Rates per mailed</p>
  <li><strong>Actions/mailed:</strong> Ratio of distinct action takers to total mailings sent for this page.</li>
  <li><strong>Net NTL/1000 mailed:</strong> Gives a sense of campaign performance in terms of net new subscriptions for the size of the mailed universe, but shifts the decimal point over to show more distinction between lower-NTL campaigns.</li>

  <p>Sources</p>
  <li><strong>Source:</strong> Action source</li>
  <li><strong>Action Takers:</strong> Distinct users who took action on this page and had this source for their action.</li>
  <li><strong>NTL:</strong> Count of users who were subscribed to a list because of an action with this source.</li>
  <li><strong>NTL %:</strong> NTL / Action Takers</li>
{% endif %}

<p>Mailings -- mailings listed are those which are the source for any action on this page.</p>
  <li><strong>Sent:</strong> Sent count.</li>
  <li><strong>Opens:</strong> Count of distinct openers.</li>
  <li><strong>Clicks:</strong> Count of distinct clickers.</li>
  <li><strong>Unsubs:</strong> Count of distinct users who unsubbed from the mailing (includes all unsubs - user-initiated, bounces, spam clicks - from any lists).</li>
{% if type == 'donation' %}
  <li><strong>Payments and $:</strong> Count and sum of successful payments (one-time or recurring) generated from the mailing that were made from this page.</li>
  <li><strong>$ /100 mailed:</strong> The amount you raised by sending an email to 100 people (a variation on $/mailed but shifts the decimal point over to show more distinction between lower-raising mailings).</li>
{% else %}
  <li><strong>Actions:</strong> Count of distinct action takers on this page with this mailing source.</li>
  <li><strong>NTL:</strong> Count of users who were subscribed to a list with this mailing source.</li>
  <li><strong>Net NTL:</strong> NTL - unsubs</li>
  <li><strong>Net NTL/1000 mailed:</strong> Gives a sense of campaign performance in terms of net new subscriptions for the size of the mailed universe, but shifts the decimal point over to show more distinction between lower-NTL campaigns.</li>
{% endif %}

<p>Sharing</p>
  <li><strong>Sharers:</strong> Count of distinct users who shared from this page.</li>
  <li><strong>Shares:</strong> Number of shares from this page.</li>
  <li><strong>Clicks:</strong> Number of clicks on these shares.</li>
  <li><strong>Action takers:</strong> Number of actions taken on this page due to shares from this page.</li>
  <li><strong>NTL:</strong> Count of users subscribed to a list because of this action from a share.</li>

{% endwith %} <!-- page type -->

</div>

Page mini-dashboard

Description: One-line Page stats for homepage.
Short name: page_minidash
User input: Page id
Associated with: Action takers for a page
HTML:  
{{reports.action_takers }} people took action.

Per-send recurring mailing statistics

Description: For each send of a recurring mailing (e.g., each day or week), count recipients, openers, clickers, action-takers, unsubbers, and bouncers.
Preview
Short name: recurring_rates_by_send
User input: Number of days and recurring mailing schedule id.
Associated with: SQL: Per-sendrecurring mailing statistics
HTML:  
{{ reports.recurring_rates_by_send_sql }}
<br />
<p><b>TOTAL</b> rates are simply a weighted average of the rates for each send (bigger sends count more). They're the best to compare against regular (non-recurring) mailing rates.</p>

<p><b>DISTINCT</b> rates only count users once if they got many sends of the mailing, acted several times, etc. Counts will be lower.</p>

<p style="display: none;" id="perf_by_mailing_link">For more statistics, see the <a href="">performance by mailing report</a> for these mailings.</p>

<script>
// Using browser TZ because using server TZ preference is hard and most
// don't have it set. But we need to tell folks what this uses. So...
function getTZName() {
    var timestr = (new Date()).toString();
    var tzmatch = /\((.*?)\)$/.exec(timestr) || /([A-Z]+) \d+$/.exec(timestr);
    if (tzmatch) return tzmatch[1];
    var hoursAheadOfGMT = -((new Date()).getTimezoneOffset()/60);
    return 'GMT' + (hoursAheadOfGMT >= 0 ? '+' : '-') + Math.abs(hoursAheadOfGMT)
}

var tzname = getTZName();

function fixTimes() {
  $('td.column-started_at:not(.time-fixed)').each(function() {
      var $this = $(this);
      var gmt_dt_str = $this.text().trim();
      var dt, local_dt_str;
      try {
        dt = new Date(gmt_dt_str+'Z');
        local_dt_str = dt.toISOString().replace('T',' ').substring(0,13)+':00 ' + tzname;
      } catch(e) {};
      if ( !local_dt_str ) return;
      $this.text(local_dt_str);
      $this.addClass('time-fixed');
  });
}

var ids = [];

// Link to mailing reports
function linkToMailings() {
  $('td.column-id').each(function() {
      var $this = $(this);
      var id = $this.text().trim();
      if ( !parseInt(id) ) return;
      ids.push(id);
      $this.html('<a href="/mailings/reports/'+id+'/">'+id+'</a>');
  });
}

// Link to performance-by-mailing report
// uses 'ids' list created in linkToMailings above
function linkToPerfByMailing() {
    var argbits = [];
    for ( var i = 0; i < ids.length; ++i )
        argbits.push('mailing='+ids[i]);
    var qs = argbits.join('&');
    var href = '/admin/reports/email_rates/?' + qs;
    $('#perf_by_mailing_link a').attr('href',href);
    $('#perf_by_mailing_link').show();
}

function tweakReport() {
    window.reportTweaked = 1;
    fixTimes();
    linkToMailings();
    linkToPerfByMailing();
}

if (!window.reportTweaked) tweakReport();
</script>

Recurring Donations Report

Description: Totals recurring donations for last month, year to date, and expiring this month.
Short name: recurringdonations
User input: None
Associated with: Recurring Donations Monthly Report
Recurring Donations with About to Expire Credit Cards
Recurring Donation Totals for month and year to date
HTML:  
<h1>Recurring Donations Report</h1>

<h2>Last Month Totals</h2>

{{ reports.recurringdonations_monthly_report }}

<h2>Current Totals</h2>

{{ reports.recurring_donations_to_date }}

<h2>Expiring this Month</h2>

{{ reports.recurring_donations_will_expire }}

Recurring donations summary charts

Description: Year-over-year visualization of recurring donation payments, active, new and cancelled profiles, and failed payments
Preview
Short name: recurring_donations_summary_charts
User input: None
Associated with: Recurring donations: active profiles by month since last year
Recurring donations: cancelled profiles by month since last year
Recurring donations: failed payments by month since last year
Recurring donations: new profiles by month since last year
Recurring donations: payments by month since last year
HTML:  
<style type="text/css">
.dashboard h1, .dashboard h2 {
    font-size: 18px;
    line-height: 25px;
    font-weight: bold;
    border-bottom: 0px;
    margin-bottom: 3px;
}
.dashboard h2 { font-size: 15px; line-height: 22px; }
.dashboard h3, .dashboard h4 {
    font-size: 1em;
    font-style: normal;
    margin-bottom: 3px;
}
.dashboard p, .dashboard li { font-size: 13px; line-height: 18px; margin-bottom: 3px; }
.dashboard ul { list-style-type: none; }
strong { font-weight: bold; }
.google-chart {
  height: 200px;
  width: 500px;
  margin: 0px 0px 20px;
  padding: 0px;
  float: left;
}
</style>
<script type="text/javascript">
reports.PaymentsByMonth = {
  options: {
    title: 'Payments by month',
    hAxis: { textStyle: { fontSize: 11 } },
    vAxis: { title: 'Payments',
              gridlines: { count: 5 },
              minorGridlines: { count: 1 }
    },
    legend: { position: 'bottom' },
    series: { 1: {type: 'bars' } },
    seriesType: 'area'
  }
};

reports.DollarsByMonth = {
  options: {
    title: 'Dollars by month',
    hAxis: { textStyle: { fontSize: 11 } },
    vAxis: { title: 'Dollars',
              gridlines: { count: 5 },
              minorGridlines: { count: 1 }
    },
    legend: { position: 'bottom' },
    series: { 1: {type: 'bars' }},
    seriesType: 'area'
  }
};

reports.ActiveProfilesByMonth = {
  options: {
    title: 'Active profiles by month',
    hAxis: { textStyle: { fontSize: 11 } },
    vAxis: { title: 'Payments',
              gridlines: { count: 5 },
              minorGridlines: { count: 1 }
    },
    legend: { position: 'bottom' },
    series: { 1: {type: 'bars' } },
    seriesType: 'area'
  }
};

reports.ActiveDollarsByMonth = {
  options: {
    title: 'Active dollars by month',
    hAxis: { textStyle: { fontSize: 11 } },
    vAxis: { title: 'Dollars',
              gridlines: { count: 5 },
              minorGridlines: { count: 1 }
    },
    legend: { position: 'bottom' },
    series: { 1: {type: 'bars' }},
    seriesType: 'area'
  }
};

reports.NewProfilesByMonth = {
  options: {
    title: 'New profiles by month',
    hAxis: { textStyle: { fontSize: 11 } },
    vAxis: { title: 'Payments',
              gridlines: { count: 5 },
              minorGridlines: { count: 1 }
    },
    legend: { position: 'bottom' },
    series: { 1: {type: 'bars' } },
    seriesType: 'area'
  }
};

reports.NewDollarsByMonth = {
  options: {
    title: 'New dollars by month',
    hAxis: { textStyle: { fontSize: 11 } },
    vAxis: { title: 'Dollars',
              gridlines: { count: 5 },
              minorGridlines: { count: 1 }
    },
    legend: { position: 'bottom' },
    series: { 1: {type: 'bars' }},
    seriesType: 'area'
  }
};

reports.PaymentFailuresByMonth = {
  options: {
    title: 'Failed payments by month',
    hAxis: { textStyle: { fontSize: 11 } },
    vAxis: { title: 'Payments',
              gridlines: { count: 5 },
              minorGridlines: { count: 1 }
    },
    legend: { position: 'bottom' },
    series: { 1: {type: 'bars' } },
    seriesType: 'area'
  }
};

reports.DollarFailuresByMonth = {
  options: {
    title: 'Failed dollars by month',
    hAxis: { textStyle: { fontSize: 11 } },
    vAxis: { title: 'Dollars',
              gridlines: { count: 5 },
              minorGridlines: { count: 1 }
    },
    legend: { position: 'bottom' },
    series: { 1: {type: 'bars' } },
    seriesType: 'area'
  }
};

reports.PaymentCancellationsByMonth = {
  options: {
    title: 'Cancelled profiles by month',
    hAxis: { textStyle: { fontSize: 11 } },
    vAxis: { title: 'Payments',
              gridlines: { count: 5 },
              minorGridlines: { count: 1 }
    },
    legend: { position: 'bottom' },
    series: { 1: {type: 'bars' } },
    seriesType: 'area'
  }
};

reports.DollarCancellationsByMonth = {
  options: {
    title: 'Cancelled dollars by month',
    hAxis: { textStyle: { fontSize: 11 } },
    vAxis: { title: 'Dollars',
              gridlines: { count: 5 },
              minorGridlines: { count: 1 }
    },
    legend: { position: 'bottom' },
    series: { 1: {type: 'bars' } },
    seriesType: 'area'
  }
};
</script>

{% load actionkit_tags %}
<div class="dashboard">
<div id="PaymentsByMonth" class="google-chart ComboChart">
{% report 'recurring_donations_payments_month_last_year' with 'donations' as option %}
</div>

<div id="DollarsByMonth" class="google-chart ComboChart">
{% report 'recurring_donations_payments_month_last_year' with 'dollars' as option %}
</div>

<div style="clear:both;"></div>

<div id="ActiveProfilesByMonth" class="google-chart ComboChart">
{% report 'recurring_donations_active_profiles_month_last_year' with 'donations' as option %}
</div>

<div id="ActiveDollarsByMonth" class="google-chart ComboChart">
{% report 'recurring_donations_active_profiles_month_last_year' with 'dollars' as option %}
</div>

<div style="clear:both;"></div>

<div id="NewProfilesByMonth" class="google-chart ComboChart">
{% report 'recurring_donations_new_profiles_month_last_year' with 'donations' as option %}
</div>

<div id="NewDollarsByMonth" class="google-chart ComboChart">
{% report 'recurring_donations_new_profiles_month_last_year' with 'dollars' as option %}
</div>

<div style="clear:both;"></div>

<div id="PaymentFailuresByMonth" class="google-chart ComboChart">
{% report 'recurring_donations_failures_month_last_year' with 'donations' as option %}
</div>

<div id="DollarFailuresByMonth" class="google-chart ComboChart">
{% report 'recurring_donations_failures_month_last_year' with 'dollars' as option %}
</div>

<div style="clear:both;"></div>

<div id="PaymentCancellationsByMonth" class="google-chart ComboChart">
{% report 'recurring_donations_cancellations_month_last_year' with 'donations' as option %}
</div>

<div id="DollarCancellationsByMonth" class="google-chart ComboChart">
{% report 'recurring_donations_cancellations_month_last_year' with 'dollars' as option %}
</div>

<div style="clear:both;"></div>

<h2>Handy links</h2>
<p><a href="../recurring_donations_summary_dashboard/" target="_blank">Recurring donations summary dashboard</a></p>
<p><a href="../recurring_donations_active_recurring_profiles/" target="_blank">Active profiles list</a></p>
<p><a href="../recurring_donations_successful_payments_date_range/" target="_blank">Successful payments for a date range</a></p>
<p><a href="../recurring_donations_cards_expiring_next_month/" target="_blank">Expiring CC next month</a></p>
<p><a href="../recurring_donations_inactive_recurring_profiles_date_range/" target="_blank">Inactive profiles for a date range</a></p>
<p><a href="../recurring_donations_missed_monthly_payments_date_range/" target="_blank">Profiles with unsuccessful monthly payments for a date range</a></p>
<br>

<h2>Definitions</h2>
<li>Each recurring profile could have multiple payments, one per month, unless you customized your donation form to use a different interval.</li>
<li>All payment amounts are converted into USD.</li>
<li><strong>total payments / $:</strong> count and sum of successful payments from recurring profiles in that month. The sum may sometimes appear high if you are using Braintree. BT will charge for all missed payments in one lump sum. So if the original pledge was for $10, and it failed for 2 months, then by month 3 BT will be attempting to process $30, not $10. You can change this by <a href="https://articles.braintreepayments.com/support/guides/recurring-billing/recurring-advanced-settings" target="_blank">updating your Braintree Advanced Settings</a>.</li>
<li><strong>active profiles / $:</strong> count and amount pledged for recurring profiles whose status was 'active' during that month.</li>
<li><strong>new profiles / $:</strong> count of new profiles and amount pledged that had their first recurring payment in AK that month.</li>
<li><strong>failed payments / $:</strong> count and amount pledged for canceled or failed payments. Although multiple attempts may have been made, we only count one per month for each profile where all attempts failed for the month. If there is a successful payment for that month then any failures do not count for that month. </li>
<li><strong>cancelled profiles / $:</strong> count  and amount pledged for recurring profiles with a status of 'canceled_by_admin' or 'canceled_by_user' with an updated_at timestamp with that month.</li>
</div>

Recurring donations summary dashboard

Description: YTD and MTD top-level stats for recurring donations
Preview
Short name: recurring_donations_summary_dashboard
User input: None
Associated with: Recurring donations: next month totals for active profiles and expiring cards
Recurring donations: summary stats
Recurring donations: Top 10 mailings YTD
Recurring donations: Top 10 pages YTD
Recurring donations: Top 10 sources YTD
HTML:  
<style type="text/css">
.dashboard h1, .dashboard h2 {
    font-size: 18px;
    line-height: 25px;
    font-weight:bold;
    border-bottom:0px;
    margin-bottom: 3px;
}
.dashboard h2 { font-size: 15px; line-height: 22px; }
.dashboard h3, .dashboard h4 {
    font-size: 1em;
    font-style:normal;
    margin-bottom: 3px;
}
.dashboard p, .dashboard li { font-size: 13px; line-height: 18px; margin-bottom: 3px; }
.dashboard ul { list-style-type: none; }
table { cell-padding: 20px; margin-bottom:1em;}
td, th {
    text-align: left;
    padding: 3px;
    border: 1px;
    border-style: solid;
    border-color: #CCC;
    font-size: 12px;
}
strong { font-weight: bold; }
</style>

{% load actionkit_tags %}
<div class="dashboard">
<h2>YTD</h2>
{% report 'recurring_donations_summary_stats' with 'year' as period %}

<h2>Previous month</h2>
{% report 'recurring_donations_summary_stats' with 'previous' as period %}

<h2>Current month</h2>
{% report 'recurring_donations_summary_stats' with 'month' as period %}

<h2>Next month totals (Details on
<a href="../recurring_donations_active_recurring_profiles/" target="_blank">active profiles</a>,
<a href="../recurring_donations_cards_expiring_next_month/" target="_blank">expirations</a>)</h2>
{{ reports.recurring_donations_next_month_totals_active_expiring }}

<h2>Top 10 sources for recurring donations YTD</h2>
{{ reports.recurring_donations_sources_ytd }}

<h2>Top 10 pages for recurring donations YTD</h2>
{{ reports.recurring_donations_pages_ytd }}

<h2>Top 10 mailings sent this year for recurring donations YTD</h2>
{{ reports.recurring_donations_mailings_ytd }}

<h2>Handy links</h2>
<p><a href="../recurring_donations_summary_charts/" target="_blank">Recurring donations summary charts</a></p>
<p><a href="../recurring_donations_active_recurring_profiles/" target="_blank">Active profiles</a></p>
<p><a href="../recurring_donations_successful_payments_date_range/" target="_blank">Successful payments for a date range</a></p>
<p><a href="../recurring_donations_cards_expiring_next_month/" target="_blank">Expiring CC next month</a></p>
<p><a href="../recurring_donations_inactive_recurring_profiles_date_range/" target="_blank">Inactive profiles for a date range</a></p>
<p><a href="../recurring_donations_missed_monthly_payments_date_range/" target="_blank">Profiles with unsuccessful monthly payments for a date range</a></p><br>

<h2>Definitions</h2>
<li>Each recurring profile could have multiple payments, one per month, unless you customized your donation form to use a different interval.</li>
<li>All payment amounts are converted into USD.</li>
<li><strong>profiles with payments:</strong> count of distinct recurring profiles that had a successful payment during the timeframe.</li>
<li><strong>total payments / $:</strong> count and sum of successful payments from recurring profiles in the timeframe. The sum may sometimes appear high if you are using Braintree. BT will charge for all missed payments in one lump sum. So if the original pledge was for $10, and it failed for 2 months, then by month 3 BT will be attempting to process $30, not $10. You can change this by <a href="https://articles.braintreepayments.com/support/guides/recurring-billing/recurring-advanced-settings" target="_blank">updating your Braintree Advanced Settings</a>.</li>
<li><strong>new profiles / $:</strong> count of new profiles and amount pledged that had their first recurring payment in AK that timeframe.</li>
<li><strong>failed payments / $:</strong> count and amount pledged for canceled or failed payments in that timeframe. Although multiple attempts may have been made, we only count one per month for each profile where all attempts failed for the month. If there is a successful payment for that month then any failures do not count for that month. </li>
<li><strong>cancelled profiles / $:</strong> count  and amount pledged for recurring profiles with a status of 'canceled_by_admin' or 'canceled_by_user' with an updated_at timestamp within that timeframe.</li>
<li><strong>net change in payments / $:</strong> difference between the current period donations and the previous period donations.</li>
<li><strong>active profiles / $:</strong> count and amount pledged for recurring profiles whose status was 'active' during that month.</li>
<li><strong>expiring cards / $:</strong> count and amount pledged for recurring profiles whose cards are expiring next month.</li>
</div>

Recurring series statistics

Description: Count openers, clickers, action-takers, unsubbers, and bouncers for a recurring series (or multiple series) as a whole.
Preview
Short name: recurring_rates
User input: Number of days and recurring mailing schedule id
Associated with: SQL: Recurring series statistics
HTML:  
{{ reports.recurring_rates_sql }}

<script>
// Link to mailing reports
function linkToSeries() {
  $('td.column-series').each(function() {
      var $this = $(this);
      var seriesId = parseInt($this.text());
      if ( !seriesId ) return;
      $this.html('<a href="/mailings/openrecurring/'+seriesId+'/">'+$this.text()+'</a>');
  });
}

function tweakReport() {
    window.reportTweaked = 1;
    linkToSeries();
}

if (!window.reportTweaked) tweakReport();
</script>

Rolling 12 Month Email Performance Rates

Description: Open, click, action, unsubscribe, complaint, and bounce rates for a rolling 12 month period, grouped by month.
Preview
Short name: rolling_12_email_performance_rates
User input: None
Associated with: Month's Actions
Month's Bounces
Month's Clicks
Month's Complaints
Month's Opens
Month's Sent Email
Month's Unsubscribes
HTML:  
{% right_now %}
<div id="openRate" class="google-chart LineChart"></div>
<div id="clickRate" class="google-chart LineChart"></div>
<div id="actionRate" class="google-chart LineChart"></div>
<div id="unsubscribeRate" class="google-chart LineChart"></div>
<div id="complaintRate" class="google-chart LineChart"></div>
<div id="bounceRate" class="google-chart LineChart"></div>

<script>
var dates = [];
{% comment %}
Calculate all monthly send-counts ahead of time,
then re-use the results in the calculation of metrics.
{% endcomment %}
{% for date in now|months_past:13 %}
{% if not forloop.last %}
dates.push('{{date|slice:":7"}}');
{% cache_report 'months_sent_email' with date as date %}{% record reportresult months_sent_email in sent %}
{% cache_report 'months_opens' with date as date %}{% record reportresult months_opens in opens %}
{% cache_report 'months_clicks' with date as date %}{% record reportresult months_clicks in clicks %}
{% cache_report 'months_actions' with date as date %}{% record reportresult months_actions in actions %}
{% cache_report 'months_unsubscribes' with date as date %}{% record reportresult months_unsubscribes in unsubscribes %}
{% cache_report 'months_complaints' with date as date %}{% record reportresult months_complaints in complaints %}
{% cache_report 'months_bounces' with date as date %}{% record reportresult months_bounces in bounces %}
{% else %}{# forloop.last #}
var data = {
     sent: ["Sent", {{ sent }}],
     opens: ["Open Rate", {{ opens }}],
     clicks: ["Click Rate", {{ clicks }}],
     actions: ["Action Rate", {{ actions }}],
     unsubscribes: ["Unsubscribe Rate", {{ unsubscribes }}],
     complaints: ["Complaint Rate", {{ complaints }}],
     bounces: ["Bounce Rate", {{ bounces }}]
};
{% endif %}
{% endfor %}
var table = {};
for (d in data) {
  var columns = data[d][1].map(function(item, i) {
    return [dates[i], item / data.sent[1][i]];
  });
  table[d] = [['Year-month', data[d][0]]].concat(columns);
}
</script>
<br />
<p><i>Note: Interactive charts are not included when the results are emailed; only the data used in the charts will be included.</i></p>
<h2> Definitions: </h2><br />
<p style="font-size: 1.0em"><b>Rates:</b> All rates are calculated by summing the unique count of relevant users per mailing by month, and dividing that by the month's send count.</p>
<p style="font-size: 1.0em"><b>Rolling 12 months:</b> Date range for mailing sends that starts at the beginning of the calendar month that is 12 months ago, and ends on the last day of last month.</p>
<p style="font-size: 1.0em"><b>Clicks:</b> Users who clicked on a mailing, excluding clicks on unsubscribe links.</p>
<p style="font-size: 1.0em"><b>Actions:</b> Users who took action on a mailing, excluding bounce, spam, and other unsubscribe actions.</p>
<p style="font-size: 1.0em"><b>Bounces:</b> Users who became unmailable because their email address had a hard bounce.</p>
<p style="font-size: 1.0em"><b>Complaints:</b> Users who became unmailable because they marked a mailing as spam or junk.</p>
<p style="font-size: 1.0em"><b>Total Unsubscribes:</b>  Users that became unsubscribed as a result of a mailing, including bounces and complaints.</p>
<script type='text/javascript'>
reports.openRate = {
  options: {
  title: 'Open Rate by Month',
  pointSize: 2,
  hAxis: { title: 'Year-Month',
       textStyle: { fontSize: 11 }
  },
  vAxis: { title: 'Open Rate',
       format: '0.00%',
       minValue: 0,
       gridlines: { count: 5 },
       minorGridlines: { count: 1 }
  },
  legend: { position: 'none' }
  }
};
reports.clickRate = {
  options:{
  title: 'Click Rate by Month',
  pointSize: 2,
  hAxis: { title: 'Year-Month',
       textStyle: { fontSize: 11 }
  },
  vAxis: { title: 'Click Rate',
       gridlines: { count: 5 },
       format: '0.00%',
       minValue: 0,
       minorGridlines: { count: 1 }
  },
  legend: { position: 'none' }
  }
};
reports.actionRate = {
  options:{
  title: 'Action Rate by Month',
  pointSize: 2,
  hAxis: { title: 'Year-Month',
       textStyle: { fontSize: 11 }
  },
  vAxis: { title: 'Action Rate',
       gridlines: { count: 5 },
       format: '0.00%',
       minValue: 0,
       minorGridlines: { count: 1 }
  },
  legend: { position: 'none' }
  }
};
reports.unsubscribeRate = {
  options:{
  title: 'Total Unsubscribe Rate by Month',
  pointSize: 2,
  hAxis: { title: 'Year-Month',
       textStyle: { fontSize: 11 }
  },
  vAxis: { title: 'Total Unsubscribe Rate',
       gridlines: { count: 5 },
       format: '0.00%',
       minValue: 0,
       minorGridlines: { count: 1 }
  },
  legend: { position: 'none' }
  }
};
reports.complaintRate = {
  options:{
  title: 'Complaint Rate by Month',
  pointSize: 2,
  hAxis: { title: 'Year-Month',
       textStyle: { fontSize: 11 }
  },
  vAxis: { title: 'Complaint Rate',
       gridlines: { count: 5 },
       format: '0.00%',
       minValue: 0,
       minorGridlines: { count: 1 }
  },
  legend: { position: 'none' }
  }
};
reports.bounceRate = {
  options:{
  title: 'Bounce Rate by Month',
  pointSize: 2,
  hAxis: { title: 'Year-Month',
       textStyle: { fontSize: 11 }
  },
  vAxis: { title: 'Bounce Rate',
       gridlines: { count: 5 },
       format: '0.00%',
       minValue: 0,
       minorGridlines: { count: 1 }
  },
  legend: { position: 'none' }
  }
};

percent_formatter = new google.visualization.NumberFormat({pattern: '##.##%'});

function render_email_perf_metric_as_linechart(data, destination, options){
  $.each(data.slice(1), function(index){this[1] = parseFloat(this[1]);});
  dt = google.visualization.arrayToDataTable(data);
  percent_formatter.format(dt,1);
  chart = new google.visualization.LineChart(document.getElementById(destination));
  chart.draw(dt, options);
}
render_email_perf_metric_as_linechart(table.opens, 'openRate', reports.openRate.options);
render_email_perf_metric_as_linechart(table.clicks, 'clickRate', reports.clickRate.options);
render_email_perf_metric_as_linechart(table.actions, 'actionRate', reports.actionRate.options);
render_email_perf_metric_as_linechart(table.unsubscribes, 'unsubscribeRate', reports.unsubscribeRate.options);
render_email_perf_metric_as_linechart(table.complaints, 'complaintRate', reports.complaintRate.options);
render_email_perf_metric_as_linechart(table.bounces, 'bounceRate',reports.bounceRate.options);
</script>

Share Stats for Date Range

Description: Summary of sharing statistics (Facebook, Twitter, TAF/mailto, and others) for a given date range. Includes totals, and also shares broken down by date, generation, distribution type, top pages and top users.
Preview
Short name: shares_stats_dates
User input: 1. start date YYYY-MM-DD
2. end date YYYY-MM-DD
Associated with: Share Stats By Date for Date Range
Share Stats By Generation for Date Range
Share Stats By Page for Date Range
Share Stats By Type for Date Range
Share Stats By User for Date Range
Share Totals for Date Range
HTML:  
{% required_parameter "1. start_date YYYY-MM-DD" %}
{% required_parameter "2. end_date YYYY-MM-DD" %}

<style>
.dashboard h1, .dashboard h2 {
    font-size: 18px;
    line-height: 25px;
    font-weight:bold;
    border-bottom:0px;
    margin-bottom: 3px;
}
.dashboard h2 { font-size: 15px; line-height: 22px; }
.dashboard h3, .dashboard h4 {
    font-size: 1em;
    font-style:normal;
    margin-bottom: 3px;
}
.dashboard p, .dashboard li { font-size: 13px; line-height: 18px; margin-bottom: 3px; }
.dashboard ul { list-style-type: none; }
table { cell-padding: 20px; margin-bottom:1em;}
td, th {
    text-align: left;
    padding: 3px;
    border: 1px;
    border-style: solid;
    border-color: #CCC;
    font-size: 12px;
}
strong { font-weight: bold; }
</style>
<div class="dashboard">
<h2>Share Totals</h2>
{% report "shares_total_dates" %}

<h2>Shares By Page (Top 20)</h2>
{% report "shares_page_dates" %}

<h2>Shares By Type</h2>
{% report "shares_type_dates" %}

<h2>Shares By Date</h2>
{% report "shares_date_dates" %}

<h2>Shares By User (Top 20)</h2>
{% report "shares_user_dates" %}

<h2>Shares By Generation</h2>
<p>Note that this will not include TAF or mailto shares.</p>
{% report "shares_gen_dates" %}

<h2>Notes</h2>
<li>Shares count FB, Twitter, other and TAF/mailto sharing.</li>
<li>Facebook, Twitter and other sharing is based on the sharing tables that track shares and actions through the built-in ActionKit share pages.</li>
<li>TAF sharing is counted using the transactional mailing tables, plus any actions with an action source like "taf".</li>
<li>Mailto shares and clicks cannot be tracked because those are not sent through ActionKit; therefore only actions from Mailto that have an action source like "mailto" will be counted.</li>
<li><strong>Signatures:</strong> Unique users who have taken action.</li>
<li><strong>Sharers:</strong> Unique users who have shared.</li>
<li><strong>Shares:</strong> Count of shares. A single user can have multiple shares.</li>
<li><strong>Clicks:</strong> Count of clicks on shares.</li>
<li><strong>Action takers:</strong> Unique action takers that came from shares.</li>
<li><strong>NTL:</strong> Unique newly subscribed action takers that came from shares.</li>
</div>

Share Stats for Page

Description: Summary of sharing statistics (Facebook, Twitter, TAF/mailto, and others) for a given page. Includes totals, and also shares broken down by date, generation, distribution type, and top users.
Preview
Short name: shares_stats_page
User input: page_id
Associated with: Share Stats By Date for a Page
Share Stats By Generation for a Page
Share Stats By Type for a Page
Share Stats By User for a Page
Share Totals for a Page
HTML:  
{% required_parameter "page_id" %}

<style>
.dashboard h1, .dashboard h2 {
    font-size: 18px;
    line-height: 25px;
    font-weight:bold;
    border-bottom:0px;
    margin-bottom: 3px;
}
.dashboard h2 { font-size: 15px; line-height: 22px; }
.dashboard h3, .dashboard h4 {
    font-size: 1em;
    font-style:normal;
    margin-bottom: 3px;
}
.dashboard p, .dashboard li { font-size: 13px; line-height: 18px; margin-bottom: 3px; }
.dashboard ul { list-style-type: none; }
table { cell-padding: 20px; margin-bottom:1em;}
td, th {
    text-align: left;
    padding: 3px;
    border: 1px;
    border-style: solid;
    border-color: #CCC;
    font-size: 12px;
}
strong { font-weight: bold; }
</style>
<div class="dashboard">
<h2>Share Totals</h2>
{% report "shares_total_page" %}

<h2>Shares By Type</h2>
{% report "shares_type_page" %}

<h2>Shares By Date</h2>
{% report "shares_date_page" %}

<h2>Shares By User (Top 20)</h2>
{% report "shares_user_page" %}

<h2>Shares By Generation</h2>
<p>Note that this does not include TAF or mailto shares.</p>
{% report "shares_gen_page" %}

<h2>Notes</h2>
<li>Shares count FB, Twitter, other and TAF/mailto sharing.</li>
<li>Facebook, Twitter and other sharing is based on the sharing tables that track shares and actions through the built-in ActionKit share pages.</li>
<li>TAF sharing is counted using the transactional mailing tables, plus any actions with an action source like "taf".</li>
<li>Mailto shares and clicks cannot be tracked because those are not sent through ActionKit; therefore only actions from Mailto that have an action source like "mailto" will be counted.</li>
<li><strong>Signatures:</strong> Unique users who have taken action on the page.</li>
<li><strong>Sharers:</strong> Unique users who have shared.</li>
<li><strong>Shares:</strong> Count of shares. A single user can have multiple shares.</li>
<li><strong>Clicks:</strong> Count of clicks on shares.</li>
<li><strong>Action takers:</strong> Unique action takers that came from shares.</li>
<li><strong>NTL:</strong> Unique newly subscribed action takers that came from shares.</li>
</div>

Subscribed Users History Dashboard

Description: A chart displaying the subscription counts to various mailing lists, and optionally also over all lists, over a specified time period.
Short name: subscribed_count_history_dashboard
User input: List name
Start date
With total
Associated with: Subscribed Count History
HTML:  
<div id="subscribed_users_line_chart" style="width: 900px; height: 500px"></div>

<script type="text/javascript">

    var historical_counts = {{ reports.subscribed_count_history }};
    function pivotTable() {
      // table is date, list, count
      // output is
      //      data:   [Date, Count, Count, Count, ...],
      //      labels: [Date, List Name, List Name, List Name, ...]
      console.log("pivoting!")

      historical_counts.shift(); // no headers

      var table  = {} // [date][list] = count
      var unique_lists  = {}
      var labels = []

      $(historical_counts).each(function(i, el) {
        var date      = el[0]
        var list_name = el[1]
        var count     = el[2]

        if (!table[date]) {
          table[date] = {}
          table[date][list_name] = count
        }
        else {
          table[date][list_name] = count
        }

        unique_lists[list_name] = true
      })

      var dates = Object.keys(table).sort()
      var list_names = Object.keys(unique_lists).sort()
      var graph = []

     console.log(table)

      $.each(dates, function (i, date) {
           var ymd = $.map(
               date.split('-'),
               function(el) { return parseInt(el) })
           ymd[1] = ymd[1] - 1
           var row = [new Date(Date.UTC(ymd[0], ymd[1], ymd[2]))]
           $.each(list_names, function(i, list_name) {
                console.log(date + ' ' + list_name + ' = ' + table[date][list_name])
                row[i+1] = table[date][list_name] || 0
              })

           graph.push(row)
     })

     console.log(graph)

     // sort by date
     // graph.sort(function (a, b) {        return a[0].value - b[0].value;     })
     return { "graph": graph, "labels": list_names }
    }

    function drawLineChart() {

      console.log("drawLineChart called!")

      var data = new google.visualization.DataTable()

      var pivot = pivotTable()
      var labels = pivot.labels
      var counts = pivot.graph
      console.log(counts)

      data.addColumn('date', 'Date')
      $(labels).each(function (i, el) { data.addColumn('number', el); })
      data.addRows(counts)
      console.log("drawLineChart ready to draw!")

      var options = {
        title: 'Subscribed User History',
        curveType: 'function',
        animation: 3000,
      };

      var chart = new google.visualization.LineChart(
          document.getElementById('subscribed_users_line_chart'));
      chart.draw(data, options);
    }

    drawLineChart();

  </script>

Visualizing Your Base

Description: Some charts to help you visualize your user base.
Preview
Short name: visualize_base
User input: None
Associated with: Visualize Users by Source
Visualize Users by State
Visualize Recent Actions by State
Visualize Users by City
HTML:  
<script type="text/javascript">
  reports.usersBySourceChart = {
    options: {  }
  };
  reports.usersByStateChart = {
    options: {
      region: 'US',
      resolution: 'provinces'
    }
  };
  reports.actionsByStateChart = {
    options: {
      region: 'US',
      resolution: 'provinces',
      colorAxis: { colors: ['yellow', 'blue']}
    }
  };
  reports.usersByCityChart = {
    options: {
      region: 'US',
      resolution: 'provinces',
      displayMode: 'markers'
    }
  };
</script>
<h2>Users by Source</h2>
<div id="usersBySourceChart" class="google-chart PieChart" style="height: 400px">
  {{ reports.visualize_users_by_source }}
</div>
<h2>Mailable Users by US State</h2>
<div id="usersByStateChart" class="google-chart GeoChart" style="height: 500px">
  {{ reports.visualize_users_by_state }}
</div>
<h2>Recent Actions by US State</h2>
<div id="actionsByStateChart" class="google-chart GeoChart" style="height: 500px">
  {{ reports.visualize_actions_by_state }}
</div>
<h2>Top Cities (mailable US users only)</h2>
<div id="usersByCityChart" class="google-chart GeoChart" style="height: 500px">
  {{ reports.visualize_users_by_city }}
</div>

Your Progress

Description: Topline org-wide stats for the homepage.
Preview
Short name: your_progress
User input: None
Associated with: Mailable Users
Actions taken, all-time
Dollars raised (all time)
Users unsubscribed
HTML:  
{% spaceless %}
  <ul>
    <li><em>{{ reports.users_mail }}</em> mailable users<br />
      <span style="font-size:13px; line-height: 13px;">&nbsp;&nbsp;&nbsp;{% report "users_mail" with "1 hour" as interval %} new users/hour &bull; {% report "users_mail" with "1 day" as interval %}/day &bull; {% report "users_mail" with "7 day" as interval %}/week</span></li>
    <li><em>{{ reports.actions_ever }}</em> actions taken</li>
      <span style="font-size:13px; line-height: 13px;">&nbsp;&nbsp;&nbsp;{% report "actions_ever" with "1 hour" as interval %} actions/hour &bull; {% report "actions_ever" with "1 day" as interval %}/day &bull; {% report "actions_ever" with "7 day" as interval %}/week</span></li>
    <li><em>${{ reports.dollars_total }}</em> raised (${% report "dollars_total" with "7 day" as interval %} this week)</li>
    <li><em>{{ reports.users_unsub }}</em> unsubscribed ({% report "users_unsub" with "7 day" as interval %} this week)</li>
  </ul>
{% endspaceless %}

Built-In Query Templates Reference

Query templates define the appearance of your query reports. The following query templates are built-in. You can create new query templates.

Standard Query Template

Default appearance.

{% load actionkit_tags %}{% filter collapse_spaces %}{% if result.is_value %}<span class="query-{{ result.query.short_name }}">{{ result.value }}</span>{% else %}
    <table class="report query-{{ result.query.short_name }}">
        <tr class="head-row">
        {% for column_name in result.column_names %}
            <th class="column-{{ column_name|spaces_to_underscores }}">{{ column_name }}</th>
        {% endfor %}
        </tr>
        {% for row in result.rows %}
            <tr class="{% cycle 'row1' 'row2' %}">
            {% for value in row %}
                <td class="column-{{ result.column_names|nth:forloop.counter0|spaces_to_underscores }}">
                    {{ value }}
                </td>
            {% endfor %}
            </tr>
        {% endfor %}
    </table>
{% endif %}
{% endfilter %}

Commify Query Template

{% load humanize %}{% load actionkit_tags %}{% filter collapse_spaces %}{% if result.is_value %}<span class="query-{{ result.query.short_name }}">{{ result.value|intcomma }}</span>{% else %}
    <table class="report query-{{ result.query.short_name }}">
        <tr class="head-row">
        {% for column_name in result.column_names %}
            <th class="column-{{ column_name|spaces_to_underscores }}" style="padding:0px 5px">{{ column_name }}</th>
        {% endfor %}
        </<