Duct tape and Bubblegum: Simple Cohorts

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

1. Sign up here! Yes that’s a referral link.
2. Install the appropriate Python packages by clicking on the big blue button called BASH under the section titled NEW CONSOLE
3. Type in the following commands to install the appropriate packages
4. pip3 install simple_salesforce
5. pip3 install pandas
6. pip3 install numpy

Second, set up your Google BigQuery account

1. Visit https://console.cloud.google.com/
2. 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.
3. You’ll need to create an access key for your PythonAnywhere account to access Google BigQuery
4. To the left, select Service Accounts
5. Click Create Service Account
6. Give them Project, Owner access (Viewer is also just fine)
7. Click Continue
8. Click Done
9. Now highlight over the newly created Service Account and click on it
10. Click Add Key
11. Select JSON
12. This will download a file

Third, go back to your Python Anywhere account

1. Create a new directory called Keys
2. Upload your file into the newly created directory

Fourth, we’ll set up a simple CSV file

1. Create a csv file with the following columns: Customer ID, Amount, First Order Date, Last Order Date
2. OR use this sample file I’ve created for you
3. Prepping the data (CSV sample). Download the file then upload it into your Python Anywhere file directory

Fifth, in Python Anywhere use this code:

1. OR simply use the code in this file

Sixth, access Google Data Studio

1. Visit https://datastudio.google.com/
2. Create a report
3. Add the BigQuery table as a data source
4. Set up your dashboard similar to this dashboard

Hopefully this will set you up to creating a very basic cohort report in a cost effective fashion!