top of page

Connection Point: Business Health Monitoring

Summary:

In my role as Director of Growth at ConnectionPoint Systems, I created around 50% of a full business health monitoring reporting suite. I was in role for ~8 months. After the first 4 months, I had set up a MVP data/growth stack (details below). In the second 4 months, I began work on what I termed 'business health monitoring'. 

​

I pursued the endeavor for three main reasons. 

  • Core to my role was being a steward of the business model. It's hard to manage what you can't measure. 

  • In the organization, I was uniquely well suited to deliver value in the data and reporting area. 

  • I learned the value of having an accurate and precise process for understanding the business at AMZN. 

    • This was my attempt to roll out a seed stage startup's version of AMZN's WBR. ​

​

Below, you'll find several examples of this work. Enjoy!

Growth Stack Building

My top priority in standing up a growth function was getting the infrastructure layers in place, for the present and future. Summarizing a bunch of learning, frustration, work, here's what the MVP stack looked like: 

  • MySQL -- product database

  • In-house analytics web app linked into MySQL -- self-service data exploration and reporting

  • AWS Redshift -- Analytics DW

  • Posthog -- product and user analytics, experimentation, event data stream

  • Crazyegg -- website surface testing, user behavior data

  • Hevo, Zapier, Knime, Matillion, n8n, etc. -- data pipelines (knowing what I know now, this is asinine)

  • Equals, Count, Einblick, Patterns, Spreadsheets -- data science, transformations, reporting

Anchor 1
T-30D Revenue Age Report

This report shows revenue from different buckets of age. Age is the number of days since the object was created. The objects I was most interested in understanding where campaign, user, organization (org), and recurring payment (subscription). 

SQL Script for age and revenue SELECT cau.cause_id, ct.cause_id, ct.time AS ct_date, DATE(cau.created_time) as cause_created_time, uc.created AS uc_user_created_time, us.created_time AS us_user_created_time, DATE(org.created_time) as org_created_time, us.user_id AS user_user_id, cau.user_id AS cause_user_id, org.organization_id, ct.currency, ct.amount, ct.revenue, ct.fees, ct.tip, ct.pricing_model, ct.fee_recovery_percentage, cau.category_id AS cause_category_id, cau.title, CONCAT('https://fnd.us/', SHORT_ID(cau.cause_id, cau.resource_id)) AS campaign_url, SHORT_ID(cau.cause_id, cau.resource_id) AS internal_campaign_ID FROM CreditTransaction as ct LEFT JOIN Cause as cau ON ct.cause_id = cau.cause_id LEFT JOIN UserConversion AS uc ON ct.user_id = uc.id LEFT JOIN User AS us ON ct.user_id = us.user_id LEFT JOIN Organization AS org ON cau.organization_id = org.organization_id WHERE 1=1 AND ct.revenue >'0' AND ((ct.time BETWEEN '2022-06-07' AND '2022-07-07') OR (ct.time BETWEEN '2021-06-07' AND '2021-07-07')) AND ct.status = 'ACCEPTED' AND ct.type = 'PAYMENT' AND cau.parent_cause_id IS NULL AND ct.exclude = false AND cau.exclude = FALSE AND cau.vetting_status != 'DENIED' AND cau.payment_enabled = 'LIVE_ENABLED'

SQL Script recurring payments SELECT cau.cause_id, ct.cause_id, ct.time AS ct_date, DATE(cau.created_time) as cause_created_time, uc.created AS uc_user_created_time, us.created_time AS us_user_created_time, DATE(org.created_time) as org_created_time, us.user_id AS user_user_id, cau.user_id AS cause_user_id, org.organization_id, ct.currency, ct.amount, ct.revenue, ct.fees, ct.tip, ct.pricing_model, ct.fee_recovery_percentage, cau.category_id AS cause_category_id, cau.title, CONCAT('https://fnd.us/', SHORT_ID(cau.cause_id, cau.resource_id)) AS campaign_url, SHORT_ID(cau.cause_id, cau.resource_id) AS internal_campaign_ID, ps.id as payment_subscription_id, ps.amount FROM CreditTransaction as ct LEFT JOIN Cause as cau ON ct.cause_id = cau.cause_id LEFT JOIN UserConversion AS uc ON ct.user_id = uc.id LEFT JOIN User AS us ON ct.user_id = us.user_id LEFT JOIN Organization AS org ON cau.organization_id = org.organization_id INNER JOIN PaymentSubscription AS ps ON ct.cause_id = ps.cause_id WHERE ct.revenue >'0' AND ((ct.time BETWEEN '2022-05-03' AND '2022-06-03') OR (ct.time BETWEEN '2021-05-03' AND '2021-06-03')) AND ct.status = 'ACCEPTED' AND ct.type = 'PAYMENT' AND cau.parent_cause_id IS NULL AND ct.exclude = false AND cau.exclude = FALSE AND cau.vetting_status != 'DENIED' AND cau.payment_enabled = 'LIVE_ENABLED' AND 1=1

The SQL is an extraction, minimal transformation and massaging done there. Most of that work was done in a spreadsheet template, linked to the SQL queries through an ODBC driver.

 

The spreadsheet used pivot tables and formulas to calculate, then display relevant data. The report summary tabs are shown below with fictional data. 

*Editor's note: this has nothing to do with revenue age. The data to create a top campaign YoY view was already there, so I created this view as well. 

Social Sharing

A core benefit of our platforms was social sharing. A fundraiser needs to tell their story, people to engage with the story, and process payments. Processing payments is common, so the story telling and audience engagement are the important parts.

 

And take a guess as to what the #1 problem fundraisers face?

(Hint, it's the #1 problem for every business)

​

Customer acquisition. For fundraisers, most of their audience acquisition comes through their existing supporters. They care about the cause and the mission, so they share to their social circles. More social sharing creates more supporters for customers and more transactions for us. 

​

The social sharing was a core product capability I inherited in my role. The objective: measure it, manage it, grow it.

SQL Script for shares and payments by campaign set @thisyear = CURDATE(); set @lastyear = date_sub(now(), INTERVAL 52 WEEK); SELECT DATE(ct.time) AS transaction_date, YEAR(ct.time) AS transaction_year, MONTH(ct.time) AS transaction_month, WEEK(ct.time) AS transaction_week, COUNT(uar.primary_referrer_source) AS known_share_count, ct.cause_id FROM UserActivityReferral AS uar LEFT OUTER JOIN User AS User ON uar.id = User.user_id LEFT OUTER JOIN CreditTransaction AS ct ON uar.id = ct.user_id WHERE 1=1 AND ct.status = "ACCEPTED" AND ( ct.time BETWEEN date_sub(now(), INTERVAL 104 WEEK) AND date_sub(NOW(), INTERVAL 0 WEEK) ) GROUP BY transaction_date ;

SQL Script for share type and events by campaign SELECT DATE(ct.time) AS transaction_date, YEAR(ct.time) AS transaction_year, MONTH(ct.time) AS transaction_month, WEEK(ct.time) AS transaction_week, ct.cause_id, CASE WHEN uar.primary_referrer_source LIKE 'OPEN_GRAPH_ACTION%' THEN 'Facebook' WHEN uar.primary_referrer_source LIKE 'FACEBOOK%' THEN 'Facebook' WHEN uar.primary_referrer_source LIKE 'GOOGLE_PLUS%' THEN 'Google+' WHEN uar.primary_referrer_source LIKE 'EMAIL%' THEN 'Email' WHEN uar.primary_referrer_source LIKE 'ADDRESS_BAR%' THEN 'Direct From Browser Bar' WHEN uar.primary_referrer_source LIKE 'SHORT_URL%' THEN 'Link Sharing' WHEN uar.primary_referrer_source LIKE 'LINKED_IN%' THEN 'LinkedIn' WHEN uar.primary_referrer_source LIKE 'PINTEREST%' THEN 'Pinterest' WHEN uar.primary_referrer_source LIKE 'TEXT_MESSAGE%' THEN 'SMS' WHEN uar.primary_referrer_source LIKE 'TWITTER%' THEN 'Twitter' WHEN uar.primary_referrer_source LIKE 'COMMUNITY_LINK%' THEN 'Unknown Share Source' WHEN uar.primary_referrer_source LIKE 'WHATSAPP%' THEN 'Whatsapp' WHEN uar.primary_referrer_source IS NULL THEN 'Unknown Share Source' ELSE 'Unknown Share Source' END AS primary_referrer_source_decoded FROM UserActivityReferral AS uar LEFT OUTER JOIN User AS User ON uar.id = User.user_id LEFT OUTER JOIN CreditTransaction AS ct ON uar.id = ct.user_id WHERE 1=1 AND ct.status = "ACCEPTED" AND ( ct.time BETWEEN date_sub(now(), INTERVAL 104 WEEK) AND date_sub(NOW(), INTERVAL 0 WEEK) ) ;

Like in the last example, most of the lift was done in an excel template (plug and play, auto-refresh). Below are images from the summary page, with fictional data. 

User Acquisition

When I joined, we could measure top of funnel activity pretty well. The middle of the funnel - somewhat. Bottom of the funnel - barely. Post acquisition journey - barely. I worked with our technical team to take data from our product logs and drive it to the data warehouse. Then I started to connect the dots from top of the funnel traffic to product activity and ultimately through to business outcomes.

 

The first part of this effort is detailed here. Measuring and understanding where users came from, what our funnel marketing mix looked like in all stages and how successful cohorts of users were with our product. This enables management and optimization, as well as better line of sight to future revenue. 

SQL Script for traffic channel source DROP TEMPORARY TABLE if EXISTS T12_referral_source_count; CREATE TEMPORARY TABLE T12_referral_source_count SELECT uc.user_id, COUNT(uc.user_id) AS users, ui.country, MONTH(uc.created) AS user_created_month, uc.landing_page_url, uc.referrer_url, uc.ad, IF(ct.transaction_id IS NOT NULL, "Yes", "No") AS Donor_Flag, IF(cau.cause_id IS NOT NULL, "Yes", "No") AS Fundraiser_Flag, IF(cau.organization_id IS NULL, "Personal", "Org") AS Entity_type, CASE WHEN uc.referrer_url LIKE 'google.%' THEN 'Search' WHEN uc.referrer_url LIKE 'duckduckgo.%' THEN 'Search' WHEN uc.referrer_url LIKE 'search.yahoo.%' THEN 'Search' WHEN uc.referrer_url LIKE '%bing.%' THEN 'Search' WHEN uc.referrer_url LIKE 'ecosia.%' THEN 'Search' WHEN uc.referrer_url LIKE 'yandex%' THEN 'Search' WHEN uc.referrer_url LIKE '%android-app://com.google.android.googlequicksearchbox%' THEN 'Search' WHEN uc.referrer_url LIKE '%facebook%' THEN 'Social' WHEN uc.referrer_url LIKE 't.co%' THEN 'Social' WHEN uc.referrer_url LIKE '%instagram%' THEN 'Social' WHEN uc.referrer_url LIKE '%lnkd.in%' THEN 'Social' WHEN uc.referrer_url LIKE '%messenger.com%' THEN 'Social' WHEN uc.referrer_url LIKE '%reddit%' THEN 'Social' WHEN uc.referrer_url LIKE '%linkedin%' THEN 'Social' WHEN uc.referrer_url LIKE '%tiktok%' THEN 'Social' WHEN uc.referrer_url LIKE '%t.me%' THEN 'Social' WHEN uc.referrer_url LIKE '%youtube%' THEN 'Social' WHEN uc.referrer_url LIKE '%pinterest%' THEN 'Social' WHEN uc.referrer_url LIKE '%capterra%' THEN 'Review Site' WHEN uc.referrer_url LIKE '%trustpilot%' THEN 'Review Site' WHEN uc.referrer_url LIKE '%fundly.com%' THEN 'Competitor Referral' WHEN uc.referrer_url LIKE '%kickstarter.com%' THEN 'Competitor Referral' WHEN uc.referrer_url LIKE '%gofundme.com%' THEN 'Competitor Referral' WHEN uc.referrer_url LIKE '%campaignhq.com%' THEN 'CPSI Site' WHEN uc.referrer_url LIKE '%charityvillage.com%' THEN 'CPSI Site' WHEN uc.referrer_url LIKE '%fundrazr.com%' THEN 'CPSI Site' WHEN uc.referrer_url LIKE '%crowdfunding.alberta.ca%' THEN 'CPSI Site' WHEN uc.referrer_url LIKE '%crowdfunding.ca%' THEN 'CPSI Site' WHEN uc.referrer_url LIKE '%id.connectionpoint.com%' THEN 'CPSI Account Login' WHEN uc.referrer_url LIKE '%email.%' THEN 'Email' WHEN uc.referrer_url LIKE '%mail.%' THEN 'Email' WHEN uc.referrer_url LIKE '%android-app://com.google.android.gm%' THEN 'Email' WHEN uc.referrer_url LIKE '%blog.hubspot.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%cmf-fmc.ca%' THEN 'Referral' WHEN uc.referrer_url LIKE '%puppyrescuemission.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%skyrocket.is%' THEN 'Referral' WHEN uc.referrer_url LIKE '%stripe.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%thehustlestory.%' THEN 'Referral' WHEN uc.referrer_url LIKE '%crowdcrux.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%paypal.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%investopedia.%' THEN 'Referral' WHEN uc.referrer_url LIKE '%merchantmaverick.%' THEN 'Referral' WHEN uc.referrer_url LIKE '%moneycrashers.%' THEN 'Referral' WHEN uc.referrer_url LIKE '%topbestalternatives.%' THEN 'Referral' WHEN uc.referrer_url LIKE '%trippingly.%' THEN 'Referral' WHEN uc.referrer_url LIKE '%visionaryfund.%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%wdparry.%' THEN 'Referral' WHEN uc.referrer_url LIKE '%linktr.ee%' THEN 'Referral' WHEN uc.referrer_url LIKE '%downforthechallenge.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%jnjhockeytournament%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%mystrikingly.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%butterflywalkforlupusandfibro.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%ahrefs.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%10bestseo%' THEN 'Referral' WHEN uc.referrer_url LIKE '%apronsforgloves.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%docs.google.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%mission-possible.ca%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%beavertonedfoundation.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%academicresilience.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%cankorcomic.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%thealinker.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%sasapost.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%bgcbc.ca%' THEN 'Referral' WHEN uc.referrer_url LIKE '%eventbrite.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%moneypantry.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%whatdoesitmean.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%vdmresearch.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%microsetta.ucsd.edu%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%masjidhalton.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%mariposahousehospice.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%googleweblight.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%waystopayforschool.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%vx-labs.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%wikipedia%' THEN 'Referral' WHEN uc.referrer_url LIKE '%hospiceorillia.ca%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%overtheedgeglobal.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%banan.%' THEN 'Referral' WHEN uc.referrer_url LIKE '%onlinejobsacademy.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%statics.teams.cdn.office.net/%' THEN 'Referral' WHEN uc.referrer_url LIKE '%dbqunitedway.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%efundyourhealth.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%pedalroom.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%patch.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%url-opener.com%' THEN 'Direct' WHEN uc.referrer_url LIKE '%attendanceworks.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%radsociety.ca%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%pigeonrescue.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%crowdfight.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%android-app://com.linkedin.android%' THEN 'Social' WHEN uc.referrer_url LIKE '%globalpeacecareers.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%easterseals.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%phdreamers.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%fremedica.com' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%swling.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%smore.com%' THEN 'Competitor Referral' WHEN uc.referrer_url LIKE '%kcfringe.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%ukrainefreedomproject.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%lushdollar.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%ideaplotting.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%walkthewall.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%newbethanyministries.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%android-app://org.telegram.messenger%' THEN 'Direct' WHEN uc.referrer_url LIKE '%seotool.studio45.in%' THEN 'Referral' WHEN uc.referrer_url LIKE '%4seohelp.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%edukick.com%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%cancercare.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%britishgut.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%mashable.com%' THEN 'Referral' WHEN uc.referrer_url LIKE '%todobacklinks.%' THEN 'Referral' WHEN uc.referrer_url LIKE '%americangut.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%ingersolllockwood.%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%vetscount.org%' THEN 'Supporter Referral' WHEN uc.referrer_url LIKE '%86.104.32.161:9494%' THEN 'Unknown' WHEN uc.referrer_url LIKE '%startpage.com%' THEN 'Unknown' WHEN uc.landing_page_url LIKE '%utm_medium=email%' THEN 'Email' WHEN uc.referrer_url IS NULL THEN 'Unknown/Direct' ELSE 'Unclassified Referral' END AS referral_source FROM User AS us LEFT OUTER JOIN UserConversion AS uc ON us.user_id = uc.id LEFT OUTER JOIN Cause AS cau ON us.user_id = cau.user_id LEFT OUTER JOIN UserInfo AS ui ON us.user_id = ui.user_id LEFT OUTER JOIN CreditTransaction AS ct ON us.user_id = ct.user_id WHERE 1=1 AND uc.created >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) -- AND us.user_id IN ; SELECT t12.users, t12.referral_source FROM T12_referral_source_count AS t12

SQL Script for campaign revenue SELECT ct.cause_id, DATE(ct.time) AS ct_date, ct.currency, ct.amount, ct.revenue, ct.fees, ct.tip FROM CreditTransaction as ct INNER JOIN Cause as cau ON ct.cause_id = cau.cause_id WHERE ct.time > '2020-12-31' AND cau.donation_count >'0' AND ct.status = 'ACCEPTED' AND ct.type = 'PAYMENT' AND cau.parent_cause_id IS NULL AND ct.exclude = false AND cau.exclude = FALSE AND cau.vetting_status != 'DENIED' AND cau.payment_enabled = 'LIVE_ENABLED' AND 1=1 GROUP BY ct_date, ct.cause_id

SQL Script for users and campaigns matching and classification SELECT uc.user_id, uc.created AS user_created_time, uc.landing_page_url, uc.referrer_url, uc.ad, cau.cause_id, cau.organization_id, cau.total_revenue AS campaign_total_Revenue, cau.created_time AS campaign_created_time, cau.run_status, cau.started_time AS campaign_started_time, cau.setup_completed_time AS campaign_set_up_time, cau.donation_count, cau.current_sum AS campaign_total_raised, cau.goal, cau.activation_time, cau.success_time, cau.raise_on_behalf_enabled, cau.parent_cause_id, CONCAT('https://fnd.us/', SHORT_ID(cau.cause_id, cau.resource_id)) AS campaign_url, CASE WHEN cau.parent_cause_id IS NOT NULL THEN 'Child' WHEN cau.raise_on_behalf_enabled = FALSE THEN 'Standard' WHEN cau.parent_cause_id IS NULL THEN 'Parent' ELSE 'Unknown' END AS campaign_type FROM UserConversion AS uc INNER JOIN Cause AS cau ON uc.user_id = cau.user_id WHERE 1=1 AND uc.created >= '2020-01-01' AND cau.exclude = FALSE AND cau.vetting_status != 'DENIED' AND cau.payment_enabled = 'LIVE_ENABLED'

As above, much of the wrangling and melding was done in a spreadsheet template (auto-refresh, plug and play). And the data shown is fictional. 

Step 1: Calculate the campaign revenue by day and by year. 

Step 2: Meld users data to campaigns data

Step 3: Analyze!

User count by channel

Paid vs. Organic Snapshot

Revenue per user by channel

Temporal cohort breakdown

Campaign type breakdown

bottom of page