it's the privacy

Very often, I promise myself that I will write more. I am a writer, after all. That's my qualification. Sure, I've been writing software for web apps since the invention of the internet, but my degree is in Creative Writing. Go figure. So I go round in circles, because I want to create things, and I want to SHARE, but I can't just talk candidly about the people in my life, because that is a violation of privacy, and I am a pretty private person. But I also don't want to write things that no one will read. And as far as I can tell, unless I want to be a 'real' writer, and start my first novel, I am stuck being a blogger, and bloggers live and die by page views. Plus, my life revolves around a 4 year old and a 1 year old, and I don't feel right posting lots of photos and talking about them online. So it's quite a waste of time. I don't really even want particularly to be a successful blogger - I just want to feel like I am writing.

So what is there to talk about? Two things, really. The last few weeks were spent vetting new BI platforms, and although Tableau Online and Mode Analytics were both close, it became pretty clear that the only option for us is to continue with Periscope. They look the best, and they make my life the easiest. Sold. So here is a good example - my proof of concept was basically to recreate part of the main dashboard, and see how easy that is.

So, in Periscope, for the first chart the query is this:

select sum(amount) day_total, sum(f1) count_of_items, f3 merchant, ds
from temp_ps 
where p_type='Periscope Retailer 180'
and [f2=partner]
and ds > DATE_FORMAT(CURRENT_DATE - INTERVAL 31 DAY, '%Y-%m-%d')
group by ds, f3
order by ds, sum(amount) desc

Now, Tableau won't even let you give it a query, it likes to sit on a whole table and you drag fields around until you are happy. Not how I like to work. But Mode, on the other hand, looked pretty good, until you realize that:

  1. there is no Top X functionality;
  2. If you want to do that yourself you have to: write the query yourself, and handle the the settings in Python

So just as an example, the query, compared to above, for 'rolling your own' top x query - in this case, top 20:

select * from 
 (
( select * from
(SELECT (@rownum:=@rownum + 1) AS myrank,
merchant,
total
FROM ( SELECT merchant,
SUM(day_total) AS total
FROM {{@dash1}}
WHERE partner = "Facebook"
GROUP BY 1
) AS merchants,
(SELECT @rownum := 0) r
ORDER BY total DESC) z
where myrank <= 20 ) x
right join 
(
select sum(amount) day_total, sum(f1) count_of_items, f3 merchant, ds, f2 partner
from temp_ps 
where p_type='Periscope Retailer 180'
and ds > DATE_FORMAT(CURRENT_DATE - INTERVAL 31 DAY, '%Y-%m-%d')
group by f2, ds, f3
order by ds, sum(amount) desc
) y on x.merchant = y.merchant )

which as you can see is brutal, mostly because MySQL has no native ranking function, but to be fair that is before you even handle the presentation using python.

So it just quickly becomes a mess. I love the level of control, but I also need a simple workflow, and for Periscope you just click the damn checkbox. Yes please.

So it is nice to feel like I put us in the right direction. I hope that we can work with Periscope for a long time.

The only other piece of news is that I will be going to Austin at the end of the year to record an album with my buddy Shawn Pander as producer. I am super excited. Seriously this is going to be a pretty big deal. This is the song list (album working title: 'Nothing Can Keep Us'):

Go My Way
Roll Easy
Seeing Stars
You Got Me Running
Never Say Goodbye
A Little Dying
There's Nothing Beneath This
Dernier Coup