"Frontend" to interact with databases for analytics

BCN

$$$$ ¯\_(ツ)_/¯ $$$$
Joined
Dec 26, 2015
Messages
98
Likes
125
Degree
0
For my mediabuying and websites, I usually dump a lot of data into SQL (Postgres). I.e. stuff like costs, revenue, clicks, ads etc.

It doesn't have a frontend really, just some small python scripts parsing csv or some cmd scripts to add new ads.

It helps me a lot to do cost reports for JVs, and unlike spreadsheets they ensure data integrity better.

I.e. tables

ads_copies
ads_imgs
ads (connecting ads_copies and ads_imgs)
ad_accounts

fb_data (parse from CSV)
tracker_clicks
tracker_conversions

currency_rates

So I can backtrack, and see CPM i paid in UK for a certain campaign, working ads, working imgs, targetings ....

I've been playing with using Jupyter notebooks, and Data Studio (they have SQL connection). But any other cookie cutter data base connection I can use, i.e. to make "views" of sql queries, i.e. where I can change the input (data scope, filters)?

Doesn't have to be free.

PS: Before deciding to use SQL, I used Airtable, but it's limited for my use.
 

Ryuzaki

女性以上のお金
Staff member
BuSo Pro
Digital Strategist
Joined
Sep 3, 2014
Messages
3,578
Likes
6,720
Degree
7
I've not done this myself, so I can't comment if it's any better than Jupyter or anything else, but I'm aware of Laravel as a PHP framework.

Here's an example I found when doing a quick search to make sure I wasn't talking out of my ass: Visualization of data using Highcharts in Laravel

I'm not entirely sure you can create filters and all of that with Highcharts, which I have used before. But there's tons of those graphing libraries where one should be able to do it.
 
Joined
Jan 26, 2015
Messages
129
Likes
31
Degree
0
Look at Google data studio. Lots of connectors(db, wordpress, fb/Google ads etc.) and easy to setup. Downside...its from Google.

Else I would checkout open source business intelligence solutions. Haven't found any useful yet, cause I use an old version of Cognos. Would love to get some recommendations on this.
 

eliquid

SERPWoo
Digital Strategist
Joined
Nov 26, 2014
Messages
640
Likes
1,533
Degree
3
The more popular solutions today for this is

Google Data Studio
PowerBI
 
Joined
Apr 7, 2016
Messages
285
Likes
183
Degree
1
The more popular solutions today for this is

Google Data Studio
PowerBI
Power BI seems solid. I've only played around in it but, It creates charts and visualizations similar to excel and it's fairly easy to use especially if your database is well designed. The desktop version is only as powerful as your PC, so there could be some limitations there if you're doing some heavy lifting but there's always the pro version.

I use Supermetrics to make database connections and pull custom SQL into Google Sheets. From there manipulating and presenting data is as easy as designing a spreadsheet.

On an enterprise level there is Sisense, likely overkill for what you need unless you're dealing with some serious heavy lifting and messy database structure.
 

BCN

$$$$ ¯\_(ツ)_/¯ $$$$
Joined
Dec 26, 2015
Messages
98
Likes
125
Degree
0
Thanks guys!

I am testing with Google Data Studio, I like it so far. I've previously worked with IBM Cognos, in a hedge fund when I was in university, but it seems overkill for this.

I've also looked at tableau, which looks cool, but also a bit too much right now.
 
Joined
Mar 25, 2019
Messages
22
Likes
27
Degree
0
If you're mostly interacting with sql dbs, you can consider self hosting metabase. I was able to get up and running with it in 10 mins with their docker setup. The dashboard is quite pretty, I have to say.

You can do your ETL externally like you're already doing too.