Usage Analytics Workflow

Usage Analytics Workflow
Description	Get analytics on how customers use Parvenu
Platform	Parabola.io
Workflow	Usage Reporting - ACTIVE
Schedule	1st of each month

1. Formatting

We start by importing the Dashboard API that gives us a breakdown of how each user uses each feature. Then we use a Row Filter to remove test accounts and internal accounts and a Rename to standardize the column headers.

2. Active Customers Usage

Next we'll create a Sheet that keeps track of the usage for Paying Users. We'll start by Customer and Subscription data from Stripe Imports and Join them together by Stripe Customer ID. Then we use a Column Filter to keep the relevant columns and Rename it to standardize the headers. Then we use a Format Dates step on all the dates in the table to format them to YYYY-MM-DD. Then we'll use a Join step with the Paying Users segment detailed in step 3 below.

Then we use a Row Filter to keep people who have an active subscription and use a Sheets Export to send the data to Parvenu Active Customers Usage Sheet.

3. Aggregate Usage and Join

Totals

Then we use a Math step to create the Total Emails Found column.

{Domain Enrichment - Emails Found} + {Scrape Websites - Emails Found} + {Contact Enrichment - Emails Found} + {Linkedin Search - Emails Found}

.After that we use another Math step to create Average Emails Per Month.

{Total Emails Found} / {Invoice Count}

.Then we use two Row Filters to segment paying customers and registered users who never paid.

Not Paying Customers

Down the users who registered by never paid path, we create the Trial Users Activated but No Conversion column by using a Row Filter to keep rows where Total Emails Found is not equal to 0. Then we use a Count by Group step to create the Trial Users Activated but No Conversion and Insert Column to add a Common column.

We create the Registered No Conversion using a Count by Group step on all users who didn't pay. Then we use an Insert Column step to add Common.

Then we create Registered Never Used the Trial by using a Row Filter where Total Emails Found equals 0 and then using a Count by Group. Then we add the Common column using the Insert Column step.

Paying Customers

Down the paying customers path we create the Invoice Count using a Sum by Group column on Invoice Count to aggregate it. Then we add a Common column using an Insert Column step.

Then we create the Total Emails Found column using a Sum by Group on Total Emails Found to aggregate it. Then we add a Common column using an Insert Column step again.

Then we aggregate the Paying Users column using a Count by Group step. Then we add another Common step using an Insert Column step.

Next we'll use a Sort step on Average Emails Per Month in descending order and use an Insert Row Numbers step. This is path A. We'll break off to Path B from Path A using Find Maximum by Group step on Row Number. Then we'll create the Median Row Number column using {Row Number (max)} / 2 and a Format Numbers step to round to the nearest whole number. Then we use a Join step on path A and path B. Then we add the Common column using Insert Column. Then we use Column Filter step to keep Average Emails Per Month and Common and Rename Average Emails Per Month to Median Emails Per Month - Paying Customers.

Next we create individual feature analytics. Each feature uses a Sum by Count step on that feature to aggregate it, then a math step, Column Filter to remove Total Emails Found, and then add the Common step using Insert Column.

Native Search Exports Total

{Native Search - B2B} + {Native Search - B2C}

Contact Enrichment - Percentage of Total

({Contact Enrichment - Emails Found} / {Total Emails Found}) * 100

Scraper - Percentage of Total

({Scrape Websites - Emails Found} / {Total Emails Found}) * 100

Domain Enrichment - Percentage of Total

({Domain Enrichment - Emails Found} / {Total Emails Found}) * 100

Linkedin Search - Percentage

({Linkedin Search - Emails Found} / {Total Emails Found}) * 100

Then we use two Count by Group steps on Sub Users and Paying Users to aggregate their counts. Then we use Insert Column on both to add Common. Then we create the Mean Sub Users using a Math step and a Column Filter to remove Paying Users.

Mean Sub Users

{Sub Users} / {Paying Users}

Finally we use the Common Join workflow to combine all the aggregated data onto a single line. After the Join we use a Math step to add Emails Per Month - Paying Customers.

Emails Per Month - Paying Customers

{Total Emails Found} / {Invoice Count}

4. Format and Export

Then we use a Format Numbers step to keep a maximum of 2 decimal places for all numbers in the table. Then we use a Column Filter step to remove Common and a Find and Replace step to replace ".00" with nothing. Then we use an Insert Data and Time step to create the Timestamp Date column. Then we use a Format Dates step to format the Timestamp Date as YYYY-MM-DD and a Reorder step to bring it to the beginning of the table.

Next we will send the results to the Parvenu Usage Sheet by using a Sheets Import, Stack Tables, De-Dupe, and Sheets Export steps.

Finally we will export the data via a Webhook to Integrately. To do this we will create two paths. Path A uses a Column Filter step to only keep the Timestamp Date column and will use an Insert Column step to add Common. Path B uses a Split Column step on the Timestamp Date to split by "-" to separate month, day and year into three separate columns. Then we use a Rename step to rename them Timestamp Day, Timestamp Month and Timestamp Year.

Then we use a Find and Replace step on Convert Month Numbers to Month Names to replace 01 with January, 02 with February, etc. Then we use a Combine Columns step to merge Timestamp Month and Timestamp Year to create Month, ie January 2021. Then we use an Insert Column to add Common. Then we use a Common Join workflow to Join Common from Path A and Path B and use an API Export step to send to Integrately, triggering the Usage Analytics to monday.com workflow.

Usage Analytics to monday.com