Chapter 16: BigQuery
16.13. BigQuery & Looker Studio Case Study: Affiliate Performance Dashboard
The Challenge: Handling ClickBank Data
A few years ago, I encountered a challenge that led me to dive into the world of BigQuery. We were working with affiliate performance data from ClickBank, which initially provided us with CSV exports of their daily transactions. I started by putting this data into a Google Sheet, but quickly hit its limitations.
Solution: Connecting to ClickBank API
To overcome these limitations, we decided to explore BigQuery and learn how it could help us manage this data more effectively. We eventually hired someone who used Google Cloud functions to directly connect to the ClickBank API and extract all the information about how their affiliates were performing.
Valuable Insights: Subscriber Value, Gross Sales, and More
With the data now in BigQuery and accessible through Looker Studio, we could provide valuable insights like:
- The value of each subscriber
- Gross sales
- Re-bill subscriptions
- Chargebacks
- Charges
Our client was particularly interested in identifying affiliates who employed questionable tactics that resulted in high refunds and chargebacks.
Ongoing Success: Three Years and Counting
This solution has been running on BigQuery for three years now, still providing our client with valuable insights. They can filter the data by different vendors or affiliates as needed. It's important to note that this isn't web data; it is raw data coming from another service tool like ClickBank.
By processing and modeling this raw data from ClickBank, we've equipped our client with crucial numbers they can use to grow their business more efficiently.
📩 Receive my weekly Looker Studio tips
🖇 Connect with me on LinkedIn