Duct tape and Bubblegum: Simple Cohorts
For the Rick Steven's type of "shoestring budget" sales ops
I’m a bit rusty with the tech skills. Time to brush up and flex a little to remind myself that we all started somewhere. I started with learning technical skills before climbing up the ladder.
There are some things you just cannot build within Salesforce. It’s maddening! It’s one of the reasons Tableau was acquired. The data visualization and business intelligence arms race continues.
So for those businesses earning $1M in ARR…
The founder is probably doing everything themselves.
I’m hopeful a series like this will be helpful to someone out there on operating on a shoestring budget.
We’re going to try to build an extremely simple cohort analysis using:
PythonAnywhere.com
Google BigQuery
Google Data Studio
Here’s what we’ll try to build.
First, set up your PythonAnywhere account
Sign up here! Yes that’s a referral link.
Install the appropriate Python packages by clicking on the big blue button called BASH under the section titled NEW CONSOLE
Type in the following commands to install the appropriate packages
pip3 install simple_salesforce
pip3 install pandas
pip3 install numpy
Second, set up your Google BigQuery account
At the top you’ll see Google Cloud Console with a dropdown. Click on it and select “New Project”. This will create a new workspace to store your data tables.
You’ll need to create an access key for your PythonAnywhere account to access Google BigQuery
To the left, select Service Accounts
Click Create Service Account
Give them Project, Owner access (Viewer is also just fine)
Click Continue
Click Done
Now highlight over the newly created Service Account and click on it
Click Add Key
Select JSON
This will download a file
Third, go back to your Python Anywhere account
Create a new directory called Keys
Upload your file into the newly created directory
Fourth, we’ll set up a simple CSV file
Create a csv file with the following columns: Customer ID, Amount, First Order Date, Last Order Date
OR use this sample file I’ve created for you
Prepping the data (CSV sample). Download the file then upload it into your Python Anywhere file directory
Fifth, in Python Anywhere use this code:
Sixth, access Google Data Studio
Create a report
Add the BigQuery table as a data source
Hopefully this will set you up to creating a very basic cohort report in a cost effective fashion!