"Frontend" to interact with databases for analytics

[BCN]

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]

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.

 

[algospider]

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]

The more popular solutions today for this is

Google Data Studio
PowerBI

 

[ryandiscord]

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]

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.

 

[FIREman]

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.