SQL Query / Plugin - custom request

[Gauravk]

Hi There,

Can any SQL or IPS expert please help in creating a query or plugin to fetch following data in excel for our paid members (only)?

Please send me your offers on PM.

If you can do anything closer, then also quote your price on PM, please.

Thanks in advance.

 

email

phone

fn

ln

zip

ct

st

country

gen

value

 

Email: Email address

Phone: Mobile Number

fn: First Name

ln: Last Name

Zip: PO Box

ct: City

st: City

country: Country

Gen: Gender

Value: Total Spent

[CoffeeCake]

I'm having total deja vu!

 

[CoffeeCake]

What classifies a member as a paid member only? You want results for members that have spent anything ever?

[CoffeeCake]

This was a fun little exercise. However, some questions:

• Customers have shipping and billing addresses. Which values are you interested in?
• Where is gender?
• Where is "mobile phone"? (Customers have a single phone entry)

[Gauravk]

9 minutes ago, Paul E. said:

What classifies a member as a paid member only? You want results for members that have spent anything ever?

Yes Please.

As we have had free community for 8 years and then paid since last 2 years. So need to fetch only members details who have spent any amount with us, along with total amount spent .

[Gauravk]

1 minute ago, Paul E. said:

This was a fun little exercise. However, some questions:

• Customers have shipping and billing addresses. Which values are you interested in?

Billing address

1 minute ago, Paul E. said:

This was a fun little exercise. However, some questions:

• Where is gender?
• Where is "mobile phone"? (Customers have a single phone entry)

It's in profile. I can do inspect item or use backend to get you exact field id or name in sql (if that helps, guide me and I can get you the value)

 

 

[CoffeeCake]

1 minute ago, Gauravk said:

It's in profile. I can do inspect item or use backend to get you exact field id or name in sql (if that helps, guide me and I can get you the value)

We'll need to figure out which field they are in the table core_pfields_content. There should be one there that represents mobile phone and one that represents gender.

 

[Gauravk]

OK, Im on it

[CoffeeCake]

1 minute ago, Gauravk said:

OK, Im on it

We're looking for the column name for each (something like field_1 or field_4, etc.).

[Gauravk]

Gender: field_5

 

Mobile: field_15

THANKS A LOT FOR HELPING WITH THIS.

This is very crucial to us, and that's why to get developer attention I posted it again (dejavu).

[CoffeeCake]

That'll do it! This should get you what you're looking for. I've included spend_currency as IPS aggregates total customer spend by currency type. If you only accept one type of currency, you can remove this column from the query. Otherwise, you'll probably want to do some sort of currency conversion, so you're comparing apples to apples.

Here you go! If you find value and want to donate to a worthy cause, feel free to reach out via PM.

SELECT
     cm.`email` AS `email`
    ,cpc.`field_15` AS `phone`
    ,nc.`cm_first_name` AS `fn`
    ,nc.`cm_last_name` AS `ln`
    ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.postalCode')) AS `zip`
    ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.city')) AS `ct`
    ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.region')) AS `st`
    ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.country')) AS `country`
    ,cpc.`field_5` AS `gen`
    ,ncs.`spend_amount` AS `value`
    /*
     * Spend is aggregated per currency type. If you only
     * accept payments via one type of currency, you can
     * leave off the following column
     */
    ,ncs.`spend_currency`
FROM
    `core_members` cm
INNER JOIN
    `nexus_customers` nc
        ON nc.`member_id` = cm.`member_id`
INNER JOIN
    `nexus_customer_spend` ncs
        ON ncs.`spend_member_id` = cm.`member_id`
LEFT JOIN
    `nexus_customer_addresses` nca
        ON nca.`member` = cm.`member_id`
        /* Use primary billing address */
        AND nca.`primary_billing` = 1
LEFT JOIN
    `core_pfields_content` cpc
        ON cpc.`member_id` = cm.`member_id`

 

[Gauravk]

SUPER THANKS PAUL. Ill reach u on PM for charity link.

First run was MAGIC to my eyes.

Couple of issues: 

• Some members data isnt captured.
• Some members data captured twice in AED and USD currency. We prefer all data should be in AED (UAE currency)
• Billing address all fields are'nt captured.
• Can we have a date range as well so I can play with last two years only, as some earlier junk data made in too.

 

 

 

[CoffeeCake]

48 minutes ago, Gauravk said:

Couple of issues: 

• Some members data isnt captured.

What do you mean? The members returned by that query are those with a customer record in Commerce, and that have spent money.

49 minutes ago, Gauravk said:

Some members data captured twice in AED and USD currency. We prefer all data should be in AED (UAE currency)

The total spend is being calculated by Commerce based on the currency of the transaction. As you have people paying in different forms of currency, you'll need to figure out how you want to handle that as an additional exercise. If you want to convert non-AED amounts into AED, you'd need to sort out the current exchange rate between the currencies (or the exchange rate at the time of the transaction). This is not data available within IPS.

Right now, Google tells me that AED is tied to the US dollar at a fixed rate and has been for some time, so if you're confident that works for you (I know nothing about foreign currencies), and it's true that 1 US dollar = 3.6725 AED, then this is something that you could convert in the query by multiplying any USD values by 3.6725 and then adding them to any AED transactions the member has made. Have you accepted any other types of currency?

57 minutes ago, Gauravk said:

Billing address all fields are'nt captured.

The entire address is stored as a JSON string within the database in a single column. If there's a field missing, it means that the piece missing wasn't stored. Do you have examples? I'm not sure what you're showing in your screenshot (perhaps that the first row doesn't have a state of "Dubai"? What is the full value of nexus_customer_address.address for the member in question (where primary_billing = 1)?

1 hour ago, Gauravk said:

Can we have a date range as well so I can play with last two years only, as some earlier junk data made in too.

This would be a very different query, as you'd have to look at the transaction/invoice level information to filter by date. This solution looks at the aggregate value stored by Commerce for all time.

Assuming we'd want to remove refunds, that the AED calculation above is true, and that you've only ever had transactions in USD or AED, this might get you what you want for the past two years:

SELECT
     cm.`email` AS `email`
    ,cpc.`field_15` AS `phone`
    ,nc.`cm_first_name` AS `fn`
    ,nc.`cm_last_name` AS `ln`
    ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.postalCode')) AS `zip`
    ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.city')) AS `ct`
    ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.region')) AS `st`
    ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.country')) AS `country`
    ,cpc.`field_5` AS `gen`
    /*
     * This factors in only the stored amount value for transactions of type "okay" and "rfnd" in USD
     * or AED currencies. Any other transactions in any other currencies are ignored, as are partial refund values, etc.
     */
    ,ROUND(`aggregate_transactions`.`usd_and_aed_transactions_only_sum`,2) AS `value`
FROM
    `core_members` cm
INNER JOIN
    `nexus_customers` nc
        ON nc.`member_id` = cm.`member_id`
INNER JOIN
    ( SELECT
     	t_member
       ,SUM(
           CASE
               /* Convert AED to USD at fixed rate of 1 USD to 3.6725 AED */
               WHEN `t_currency` = 'USD' THEN `signed_amount` * 3.6725
               WHEN `t_currency` = 'AED' THEN `signed_amount`
           END
        ) AS `usd_and_aed_transactions_only_sum`
      FROM
        ( SELECT 
             `t_member`
            ,CASE
                 /* negative amount for refunds */
                 WHEN `t_status` = 'rfnd' THEN -1 * `t_amount`
                 /* okay transaction */
                 WHEN `t_status` = 'okay' THEN `t_amount`
                 ELSE NULL
             END AS `signed_amount`
            ,`t_currency`
          FROM
            `nexus_transactions`
          WHERE
            /* Last two years from execution only */
     	    `t_date` >= UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 2 YEAR))
            /* Transcations in currencies other than USD and AED are out of scope */
            AND `t_currency` IN ('USD','AED')
        ) AS `transactions`
      GROUP BY
     	`t_member`
    ) AS `aggregate_transactions`
        ON `aggregate_transactions`.`t_member` = cm.`member_id`
LEFT JOIN
    `nexus_customer_addresses` nca
        ON nca.`member` = cm.`member_id`
        /* Use primary billing address */
        AND nca.`primary_billing` = 1
LEFT JOIN
    `core_pfields_content` cpc
        ON cpc.`member_id` = cm.`member_id`

 

[Gauravk]

Thanks Paul, for helping again.

It's really very weird as I just wanted to cross check from the recent members who have paid and spotted that Rajesh name is not showing anywhere in this current and previous query results. He has paid money few days back and that's what I was trying to show earlier that he is a paid subscriber but his name isn't showing in the query result.

It could be my sql is freaking out....? Should I restart or clear some cache...?

 

For the date range can we have a code that I can manually change from (code) from 2 to 1 year or 3 months, etc.?

USD to AED conversion is fine and exchange is also perfect. Working as we want. Thanks.

For refund, can we just remove from the results please as that (-) sign will be confusing.

 

Also we can eliminate the null value too, as these are the people who created invoice and didn't paid (abandoned cart case).

[CoffeeCake]

37 minutes ago, Gauravk said:

It could be my sql is freaking out....? Should I restart or clear some cache...?

No, it should be real time unless you have replication turned on (multiple MySQL servers, this is not typical).

Without looking at your database directly, I'd be taking guesses. The query doesn't look at invoices, but rather looks at transactions. You may be getting NULL values if you have transactions in currencies other than AED and USD, or had transactions with a status other than okay and rfnd.

You'd see negative values if you've refunded more than a person has paid, or if the person paid prior to the two year cut off and the refund was after the two year cut off. If you'd like to remove negative and null values, add the following to the end of the query:

WHERE ROUND(`aggregate_transactions`.`usd_and_aed_transactions_only_sum`,2) >= 0

Change this line to be whatever date range you'd like: 

`t_date` >= UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 2 YEAR))

Edited November 25, 2020 by Paul E.

[Gauravk]

MANY THANKS PAUL.

You are a true champ.