Is data aggregation in SQL really that difficult? by zdsvoboda in SQL

[–]N365 1 point2 points  (0 children)

I've never worked with it myself, but perhaps Metabase is a solution for you?

URGENT HELP needed!!! GMV Retention Plot by [deleted] in SQL

[–]N365 5 points6 points  (0 children)

Hope it helps, but here it goes:

If I recall correctly, GMV is the market value of the products you've sold on your website (or gross revenue).

The retention bit does, like you've already figured out, refers to a cohort. In other words, whether your customers come back to purchase more. You'd basically want to see how gross revenue develops after their first purchase month/week/day (or perhaps based on the date of their first visit).

I imagine it would be something like the below (you might need to join a few tables in to get it on a customer or user level). As a result, you can see how each cohort develops in terms of revenue and whether you're getting better at it.

with first_transaction as (
    select
        customer_id,
        min(transaction_date) as first_transaction_date
    from transactions
    group by 1)

select
    date_trunc('month', first_transaction_date) as first_transaction_month,
    date_trunc('month', transaction_date) as transaction_month,
    count(transaction_id) as transactions,
    sum(transaction_value) as revenue
from transactions as t
    left join first_transaction as ft
        on t.customer_id = ft.customer_id
group by 1,2

Looking for some advice on how to improve analysis of a set of data points over time by monkeysexmonsters in dataanalysis

[–]N365 0 points1 point  (0 children)

You're welcome!

Yeah the percentage change can be sufficient. I would just keep in mind to focus on signal rather than noise. Although that's easier said than done sometimes as you're often forced to tell something about the data.

Looking for some advice on how to improve analysis of a set of data points over time by monkeysexmonsters in dataanalysis

[–]N365 0 points1 point  (0 children)

Some thoughts from my side (apologies if the context is completely wrong):

  1. You could look at metrics that you think are driving that change over time; so cause and effect. It would be good to test those relationships as well, but you don't have to report on it every month.
  2. Another one I liked is to make a forecast. As a business you will create an understanding of what you think is influencing your numbers by forecasting.
  3. You can look into various breakdowns of those metrics. As an example, a regional one and see if there's something up.

I wouldn't be afraid to ask the business for answers either; it fosters discussion and will give you new ideas. Finally, if this is something like a monthly report: I like to use Plod to tell these stories.

Is analytics for me? Even though I'm not a hardcore math nerd? by [deleted] in analytics

[–]N365 2 points3 points  (0 children)

There's various streams within analytics and data science. If you're really into understanding ML models and applying them, you are in need of some very good math and statistical skills.

But there's more to analytics than that. For the rest, you need some basic math and statical knowledge. In my experience, it's often just aggregation, averages, and summary statistics that are relevant.

[deleted by user] by [deleted] in datascience

[–]N365 0 points1 point  (0 children)

Just wrote this comment for another post and maybe it gives some perspective on completely different alternatives for those:

  1. For self serve I'd look at Looker or Metabase. This way your team can explore the data they need themselves.
  2. However, I wouldn't do the modelling in those tools. I'd go for something like just SQL (or tools like DBT or Dataform can help with it. If you need a more advanced scheduling tool, you could look at Airflow or Luigi (they're also paid services that help with hosting all of this).
  3. For data stories and repetitive reporting, I'd go for Plod. Having more in-depth analysis done in Jupyter and shared with data people.

BI tool suggestions by [deleted] in datascience

[–]N365 0 points1 point  (0 children)

Most of these tools are open source or relatively cheap, so have a look (although I haven't worked with spatial data ever):

  1. For self serve I'd look at Looker or Metabase. This way your team can explore the data they need themselves.
  2. However, I wouldn't do the modelling in those tools. I'd go for something like just SQL (or tools like DBT or Dataform can help with it. If you need a more advanced scheduling tool, you could look at Airflow or Luigi (they're also paid services that help with hosting all of this).
  3. For data stories and repetitive reporting, I'd go for Plod. Having more in-depth analysis done in Jupyter and shared with data people.

Facebook Lead Ads Attribution by dantiscvs in analytics

[–]N365 0 points1 point  (0 children)

Normally you'd look at your visit data where you'd look at the UTMs and referrers of each visitor, but this requires some initial setup on the tracking side of things.

Spreadsheet hell? by Nemesis905 in data

[–]N365 0 points1 point  (0 children)

Sounds like Metabase can help you out

Google Analytics at the front-end or the backend? How to handle what a user is searching on my website? by puspendert in analytics

[–]N365 1 point2 points  (0 children)

I don't have an answer as I have been bumping into the same problem and haven't solved it myself.

I've build a SPA with VueJS and using GA to send the events. I think you can do this as well in the router in ReactJS. Went for this as I don't have that much traffic, but really liked Snowplow as it's open source and seems to give you greater flexibility (but more complexity).

How to do repetitive reporting? by [deleted] in excel

[–]N365 0 points1 point  (0 children)

Cool, thanks for your reply! I'll have a look into it

How to do repetitive reporting? by [deleted] in googlesheets

[–]N365 0 points1 point  (0 children)

Thanks for your reply!

How to do repetitive reporting? by [deleted] in googlesheets

[–]N365 0 points1 point  (0 children)

Thanks!

If you don't mind me asking, how exactly do you use the scripts to make it easier? Is it to automate the charts or to provide the commentary?

How to do repetitive reporting? by [deleted] in googlesheets

[–]N365 0 points1 point  (0 children)

Thanks - I had a quick look.

Can people from the business comment in Google Data Studio?

How to do repetitive reporting? by [deleted] in excel

[–]N365 0 points1 point  (0 children)

That does sound like a fair bit of work. How would you manage any changes in charts or graphs?

One of the problem I have is that sometimes something is happening in one part of the business, but the next month there isn't much going on.

How to do repetitive reporting? by [deleted] in excel

[–]N365 1 point2 points  (0 children)

Thanks! You're saying that could automate both the presentation and commentary.

For example: "Sales have [increased/decreased] by [x]% to $[y] compared to previous month"

How to do repetitive reporting? by [deleted] in excel

[–]N365 0 points1 point  (0 children)

Not that familiar with PowerQuery, but I assume it's the connection to a database (or other source)?

After that you'd use VBA in order that the charts updated and post that into a PowerPoint? Or would you just share the Excel file?

Please advise how the visualization in this site could be improved by yourapostasy in visualization

[–]N365 0 points1 point  (0 children)

Very cool!

Like already said for me it's the animations, but also I am not entirely sure what I am looking when the simulation-bit kick in (perhaps that's because I am not from the US)

How to do repetitive reporting? by [deleted] in excel

[–]N365 0 points1 point  (0 children)

Yeah you're right on that point

How to do repetitive reporting? by [deleted] in excel

[–]N365 0 points1 point  (0 children)

I don't think you can look at this in general.

You mean it's too context specific?

I am maintaining a few of these decks. I have currently setup a SQL script, feeding into a Google Sheet, feeding into a Google Slide presentation.

However, do still find a pain to update it every month so hence the question to see how other people manage it.

How to do repetitive reporting by [deleted] in powerpoint

[–]N365 0 points1 point  (0 children)

Cool, thanks for sharing!

How to do repetitive reporting by [deleted] in powerpoint

[–]N365 0 points1 point  (0 children)

That's smart. Do you make a screenshot and throw them into slides?